BUG #15832: COPY into a partitioned table breaks its indexes

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: harukat(at)sraoss(dot)co(dot)jp
Subject: BUG #15832: COPY into a partitioned table breaks its indexes
Date: 2019-06-04 04:41:55
Message-ID: 15832-b1bf336a4ee246b5@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15832
Logged by: TAKATSUKA Haruka
Email address: harukat(at)sraoss(dot)co(dot)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.)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kumar Harsh 2019-06-04 08:37:06 The default timezone is being shown as UCT instead of UTC
Previous Message Michael Paquier 2019-06-03 23:51:32 Re: BUG #15789: libpq compilation with OpenSSL 1.1.1b fails on Windows with Visual Studio 2017