Thread: pg_dump INDEX ATTACH versus --clean option
I noticed that pg_dump --clean does not work with partitioned
indexes. Given for instance
create schema s1;
create table s1.at1 (f1 int, f2 int, primary key(f1,f2)) partition by list(f1);
create table s1.at11 partition of s1.at1 for values in(11);
create table s1.at12 partition of s1.at1 for values in(12);
then "pg_dump -n s1 -c mydb >mydb.dump" will emit
ALTER TABLE ONLY s1.at12 DROP CONSTRAINT at12_pkey;
ALTER TABLE ONLY s1.at11 DROP CONSTRAINT at11_pkey;
ALTER TABLE ONLY s1.at1 DROP CONSTRAINT at1_pkey;
DROP TABLE s1.at12;
DROP TABLE s1.at11;
DROP TABLE s1.at1;
DROP SCHEMA s1;
... then create the objects ...
which naturally results in
psql:mydb.dump:19: ERROR: cannot drop inherited constraint "at12_pkey" of relation "at12"
psql:mydb.dump:20: ERROR: cannot drop inherited constraint "at11_pkey" of relation "at11"
ALTER TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP SCHEMA
That's not really okay, since it'd break a single-transaction
restore.
Since there's no ALTER INDEX DETACH PARTITION, it's not entirely
clear what to do about this. We could possibly not emit any
dropStmt for partition child indexes, but that seems very likely
to cause problems for partial-restore scenarios.
regards, tom lane
On 2021-Jan-12, Tom Lane wrote: > then "pg_dump -n s1 -c mydb >mydb.dump" will emit > > ALTER TABLE ONLY s1.at12 DROP CONSTRAINT at12_pkey; > ALTER TABLE ONLY s1.at11 DROP CONSTRAINT at11_pkey; > ALTER TABLE ONLY s1.at1 DROP CONSTRAINT at1_pkey; > DROP TABLE s1.at12; > DROP TABLE s1.at11; > DROP TABLE s1.at1; > DROP SCHEMA s1; > ... then create the objects ... > > which naturally results in > > psql:mydb.dump:19: ERROR: cannot drop inherited constraint "at12_pkey" of relation "at12" > psql:mydb.dump:20: ERROR: cannot drop inherited constraint "at11_pkey" of relation "at11" > That's not really okay, since it'd break a single-transaction > restore. Hmm. You complained about a related case in 3170626.1594842723@sss.pgh.pa.us and I posted a patch: https://www.postgresql.org/message-id/20200812214918.GA30353@alvherre.pgsql I suggested there to make the dropStmt empty, but ended up not pushing that patch. That would solve this problem also. > Since there's no ALTER INDEX DETACH PARTITION, it's not entirely > clear what to do about this. We could possibly not emit any > dropStmt for partition child indexes, but that seems very likely > to cause problems for partial-restore scenarios. Yeah, it would break the case of restoring a partition that already exists under --clean. (Of course, if the partition doesn't exist already, there's no problem, since nothing is to be dropped anyway.) About the only thing I can think of, is to make the dropStmt use a plpgsql DO block that drops conditionally (only if not an index partition). -- Álvaro Herrera 39°49'30"S 73°17'W
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2021-Jan-12, Tom Lane wrote:
>> Since there's no ALTER INDEX DETACH PARTITION, it's not entirely
>> clear what to do about this. We could possibly not emit any
>> dropStmt for partition child indexes, but that seems very likely
>> to cause problems for partial-restore scenarios.
> Yeah, it would break the case of restoring a partition that already
> exists under --clean. (Of course, if the partition doesn't exist
> already, there's no problem, since nothing is to be dropped anyway.)
> About the only thing I can think of, is to make the dropStmt use a
> plpgsql DO block that drops conditionally (only if not an index
> partition).
Don't much like that :-(. Aside from the fact that we'd then be
requiring plpgsql to exist to do a restore, I think this would
cause big compatibility problems in the future, since the DO block
would have to do some more-or-less-weird catalog query to find out
if the index is a partition. We'd be locked into guaranteeing that
that query works, pretty much forever.
I think actually the cleanest fix would be to invent ALTER INDEX DETACH
PARTITION and use that as the dropStmt for the INDEX ATTACH object.
No idea how painful that would be to do, though. I suppose it'd involve
reverting the parent index back to an invalid state.
regards, tom lane
On 2021-Jan-12, Tom Lane wrote: > I think actually the cleanest fix would be to invent ALTER INDEX DETACH > PARTITION and use that as the dropStmt for the INDEX ATTACH object. > No idea how painful that would be to do, though. I suppose it'd involve > reverting the parent index back to an invalid state. Right. The initial submitted patch did have DETACH, and on review we were kinda happy that we were able to remove that and avoid indexes that can revert from valid to invalid state. I don't recall the precise reason, but it can probably be found in the archives ... perhaps starting at https://postgr.es/m/flat/CAKJS1f9G6hnahJpoLaHavrKt0uPyYZncEi2rq__kLQcrGE_FMQ@mail.gmail.com As far as the code goes, DETACH was already in some version older than what got committed; I suppose we could easily crib stuff from there. It had a new alter table subcommand, so it'd not be a backpatchable fix in that way; we'd need some different parse node representation, I think. One problem that was definitely not solved, is that in multi-level partitioning setups, we would have to lock relations from the top down. -- Álvaro Herrera Valdivia, Chile