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

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.)

On Tue, 4 Jun 2019 at 21:20, Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
> Attached patch fixes the issue. It basically updates estate->es_result_relation_info with the correct resultRelInfo
inCopyMultiInsertBufferFlush(). I've also added the test-case for it.
 
>
> Andres, David, do you all agree with above analysis and the proposed fix ?

Thanks for the report Haruka and for the patch Ashutosh.

I've pushed this after changing the tests a little to reuse the
existing table. I also added an Assert into ExecInsertIndexTuples to
ensure the slot and the ResultRelInfo belong to the same relation. If
that had existing when this was being developed then I'd have noticed
the problem when testing it during dev.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services