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

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

> On Tue, Apr 15, 2025 at 1:45 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >>
> >> 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.
> >
>
>
> I am not sure what you mean fails
> ``CREATE INDEX qqindxname``
> ?
>
> for example:
>
> >> >> 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);
>
> with the patch, pg_dump output will be
> ```
> DROP INDEX IF EXISTS public.tp_a_idx;
> DROP TABLE IF EXISTS public.tp_11;
> DROP TABLE IF EXISTS public.tp;
> ....
> CREATE INDEX tp_a_idx ON ONLY public.tp USING btree (a);
> CREATE INDEX tp_11_a_idx ON public.tp_11 USING btree (a);
> ALTER INDEX public.tp_a_idx ATTACH PARTITION public.tp_11_a_idx;
> ```
>
> What's your expectation?
>

I am not sure at this moment.

There is a question why dump exports DROP INDEX, although the index will be
dropped implicitly when table is dropped.

So your fix can work, because all indexes will be dropped by DROP TABLE,
but on second hand it is not consistent with the current behavior of
pg_dump, where indexes are dropped explicitly.

I have not knowledge, why pg_dump exports DROP INDEX explicitly, although
it is redundant.

Regards

Pavel