From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
cc: pgsql-hackers@lists.postgresql.org
Subject: Dependencies for partitioned indexes are still a mess
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3170625.1594842723.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 15 Jul 2020 15:52:03 -0400
Message-ID: <3170626.1594842723@sss.pgh.pa.us>
Precedence: bulk

I've been experimenting with trying to dump-and-restore the
regression database, which is a test case that for some reason
we don't cover in the buildfarm (pg_upgrade is not the same thing).
It seems like the dependency choices we've made for partitioned
indexes are a complete failure for this purpose.

Setup:

1. make installcheck
2. Work around the bug complained of at [1]:
   psql regression -c 'drop table gtest30_1, gtest1_1'
3. pg_dump -Fc regression >regression.dump

Issue #1: "--clean" does not work

1. createdb r2
2. pg_restore -d r2 regression.dump
3. pg_restore --clean -d r2 regression.dump

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6606; 1259 35458 INDEX idxpart32_a_idx postgres
pg_restore: error: could not execute query: ERROR:  cannot drop index publ=
ic.idxpart32_a_idx because index public.idxpart3_a_idx requires it
HINT:  You can drop index public.idxpart3_a_idx instead.
Command was: DROP INDEX public.idxpart32_a_idx;
pg_restore: from TOC entry 6605; 1259 35454 INDEX idxpart31_a_idx postgres
pg_restore: error: could not execute query: ERROR:  cannot drop index publ=
ic.idxpart31_a_idx because index public.idxpart3_a_idx requires it
HINT:  You can drop index public.idxpart3_a_idx instead.
Command was: DROP INDEX public.idxpart31_a_idx;
...
pg_restore: from TOC entry 6622; 2606 35509 CONSTRAINT pk52 pk52_pkey post=
gres
pg_restore: error: could not execute query: ERROR:  cannot drop inherited =
constraint "pk52_pkey" of relation "pk52"
Command was: ALTER TABLE ONLY regress_indexing.pk52 DROP CONSTRAINT pk52_p=
key;
pg_restore: from TOC entry 6620; 2606 35504 CONSTRAINT pk51 pk51_pkey post=
gres
pg_restore: error: could not execute query: ERROR:  cannot drop inherited =
constraint "pk51_pkey" of relation "pk51"
Command was: ALTER TABLE ONLY regress_indexing.pk51 DROP CONSTRAINT pk51_p=
key;
pg_restore: from TOC entry 6618; 2606 35502 CONSTRAINT pk5 pk5_pkey postgr=
es
pg_restore: error: could not execute query: ERROR:  cannot drop inherited =
constraint "pk5_pkey" of relation "pk5"
Command was: ALTER TABLE ONLY regress_indexing.pk5 DROP CONSTRAINT pk5_pke=
y;
...

(There seem to be some other problems as well, but most of the 54 complain=
ts
are related to partitioned indexes/constraints.)

Issue #2: parallel restore does not work

1. dropdb r2; createdb r2
2. pg_restore -j8 -d r2 regression.dump =


This is fairly timing-dependent, but some attempts fail with messages
like

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6684; 2606 29166 FK CONSTRAINT fk fk_a_fkey pos=
tgres
pg_restore: error: could not execute query: ERROR:  there is no unique con=
straint matching given keys for referenced table "pk"
Command was: ALTER TABLE fkpart3.fk
    ADD CONSTRAINT fk_a_fkey FOREIGN KEY (a) REFERENCES fkpart3.pk(a);

The problem here seems to be that some commands like this:

ALTER INDEX fkpart3.pk5_pkey ATTACH PARTITION fkpart3.pk52_pkey;
	=

are not executed soon enough, indicating that we lack dependencies
that would guarantee the restore order.

I have not analyzed these issues in any detail -- they're just bugs
I tripped over while testing parallel pg_restore.  In particular
I do not know if #1 and #2 have the same root cause.

			regards, tom lane

[1] https://www.postgresql.org/message-id/3169466.1594841366%40sss.pgh.pa.=
us