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(dot)1594842723(at)sss(dot)pgh(dot)pa(dot)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