PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15832
PG Version12beta1
OSCentOS 7.4.1708
Opened2019-06-04 04:41:55+00
Reported byTAKATSUKA Haruka
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15832
Logged by:          TAKATSUKA Haruka
Email address:      (redacted)
PostgreSQL version: 12beta1
Operating system:   CentOS 7.4.1708
Description:        

When I tested the performance improvement of 12beta1 at COPY into a
partitioned table,
I found the indexes broken.
It doesn't happen in version 11.3 or INSERT  as long as I tested.

Reproduce steps:

db1=# CREATE TABLE oya (id int primary key, v text) PARTITION BY RANGE
(id);
db1=# SELECT 'CREATE TABLE ko' || g || ' PARTITION OF oya FOR VALUES FROM ('
|| g * 10 - 10 || ') TO (' || g * 10 || ');' FROM generate_series(0, 3000)
as g;
db1=# \gexec

db1=# COPY oya FROM '/home/postgres/dat.csv' CSV;
COPY 30000

db1=# SELECT * FROM oya LIMIT 3;
 id |                v
----+----------------------------------
  0 | cfcd208495d565ef66e7dff9f98764da
  1 | c4ca4238a0b923820dcc509a6f75849b
  2 | c81e728d9d4c2f636f067f89cc14862c
(3 rows)

db1=# SELECT * FROM oya WHERE id = 1;
 id | v
----+---
(0 rows)

db1=# REINDEX TABLE ko1;
REINDEX

db1=# SELECT * FROM oya WHERE id = 1;
 id |                v
----+----------------------------------
  1 | c4ca4238a0b923820dcc509a6f75849b
(1 row)

db1=# \d ko2
                Table "public.ko2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 v      | text    |           |          |
Partition of: oya FOR VALUES FROM (10) TO (20)
Indexes:
    "ko2_pkey" PRIMARY KEY, btree (id)

db1=# SELECT * FROM oya WHERE id = 15;
 id | v
----+---
(0 rows)

db1=# REINDEX TABLE ko2;
REINDEX

db1=# SELECT * FROM oya WHERE id = 15;
 id |                v
----+----------------------------------
 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
(1 row)

db1=# DELETE FROM oya;
DELETE 30000

db1=# INSERT INTO oya SELECT g, md5(g::text) FROM generate_series(0, 30000 -
1) as g;
INSERT 0 30000

db1=# SELECT * FROM oya WHERE id = 25;
 id |                v
----+----------------------------------
 25 | 8e296a067a37563370ded05f5a3bf3ec
(1 row)

db1=# SELECT * FROM oya WHERE id = 35;
 id |                v
----+----------------------------------
 35 | 1c383cd30b7c298ab50293adfecb7b18
(1 row)

db1=# DELETE FROM oya;
DELETE 30000

db1=# COPY oya FROM '/home/postgres/dat.csv' CSV;
COPY 30000

db1=# SELECT * FROM oya WHERE id = 45;
 id | v
----+---
(0 rows)

db1=# REINDEX TABLE ko5;
REINDEX

db1=# SELECT * FROM oya WHERE id = 45;
 id |                v
----+----------------------------------
 45 | 6c8349cc7260ae62e3b1396831a8398f
(1 row)

(btw, "oya" and "ko" means "parent" and "child" in Japanese.)

Messages

DateAuthorSubject
2019-06-04 04:41:55+00PG Bug reporting formBUG #15832: COPY into a partitioned table breaks its indexes
2019-06-04 09:19:48+00Ashutosh SharmaRe: BUG #15832: COPY into a partitioned table breaks its indexes
2019-06-05 06:41:00+00David RowleyRe: BUG #15832: COPY into a partitioned table breaks its indexes
2019-06-05 06:57:14+00Ashutosh SharmaRe: BUG #15832: COPY into a partitioned table breaks its indexes