BUG #15832: COPY into a partitioned table breaks its indexes
От | PG Bug reporting form |
---|---|
Тема | BUG #15832: COPY into a partitioned table breaks its indexes |
Дата | |
Msg-id | 15832-b1bf336a4ee246b5@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15832: COPY into a partitioned table breaks its indexes
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15832 Logged by: TAKATSUKA Haruka Email address: harukat@sraoss.co.jp 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.)
В списке pgsql-bugs по дате отправления: