Re: pg_dump --if-exists --clean when drop index that is partition of a partitioned index

út 15. 4. 2025 v 7:33 odesílatel jian he <jian(dot)universality(at)gmail(dot)com>
napsal:

> On Mon, Apr 14, 2025 at 2:09 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >
> > Hi
> >
> > po 14. 4. 2025 v 7:54 odesílatel jian he <jian(dot)universality(at)gmail(dot)com>
> napsal:
> >>
> >> hi.
> >>
> >> CREATE TABLE tp(c int, a int, b int) PARTITION BY RANGE (b);
> >> CREATE TABLE tp_1(c int, a int, b int);
> >> ALTER TABLE tp ATTACH PARTITION tp_1 FOR VALUES FROM (0) TO (1);
> >> CREATE INDEX t_a_idx ON tp_1(a);
> >> CREATE INDEX tp_a_idx ON tp(a);
> >>
> >> pg_dump --schema=public --if-exists --clean --no-statistics
> >> --no-owner --no-data --table-and-children=tp > 1.sql
> >> pg_dump output file 1.sql excerpts:
> >> ----
> >> DROP INDEX IF EXISTS public.t_a_idx;
> >> DROP INDEX IF EXISTS public.tp_a_idx;
> >> DROP TABLE IF EXISTS public.tp_1;
> >> DROP TABLE IF EXISTS public.tp;
> >> ----
> >> if you execute the
> >> DROP INDEX IF EXISTS public.t_a_idx;
> >>
> >> ERROR: cannot drop index t_a_idx because index tp_a_idx requires it
> >> HINT: You can drop index tp_a_idx instead.
> >>
> >> Is this pg_dump output what we expected?
> >>
> >
> > It is a bug, I think, the implementation of these parts of code is older
> than partitioning support, and doesn't do necessary detach.
> >
>
>
> seems pretty easy to fix.
> we only need dropStmt when IndxInfo->parentidx oid is invalid.
>
> + if (!OidIsValid(indxinfo->parentidx))
> + appendPQExpBuffer(delq, "DROP INDEX %s;\n", qqindxname);
>

I don't think it is the correct fix.

because then fails CREATE INDEX qqindxname

The motivation for usage --clean and --if-exists option is possibility to
call restore idempotently. It should not fail when I do restore in an empty
database, and it shouldn't fail if I do restore in an existing database.

Regards

Pavel

>
> I have tested the above changes on PG11, master.
>