Re: pg_dump INDEX ATTACH versus --clean option

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