On Mon, Apr 14, 2025 at 2:09 PM Pavel Stehule <pavel.steh...@gmail.com> wrote: > > Hi > > po 14. 4. 2025 v 7:54 odesílatel jian he <jian.universal...@gmail.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 have tested the above changes on PG11, master.
From 24cca288498cbbe7fb831e92567205fe13db5e19 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Tue, 15 Apr 2025 13:32:24 +0800 Subject: [PATCH v1 1/1] fix pg_dump --clean on index inheritance hierarchies 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 tp_1_a_idx ON tp_1(a); CREATE INDEX tp_a_idx ON tp(a); now the pg_dump output is ``` DROP INDEX IF EXISTS public.tp_a_idx; DROP TABLE IF EXISTS public.tp_1; `` discussion: https://postgr.es/m/CACJufxF0QSdkjFKF4di-JGWN6CSdQYEAhGPmQJJCdkSZtd=o...@mail.gmail.com commitfest entry: --- src/bin/pg_dump/pg_dump.c | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index c6e6d3b2b86..4eb33fefb56 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -17953,7 +17953,14 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo) qindxname); } - appendPQExpBuffer(delq, "DROP INDEX %s;\n", qqindxname); + /* + * for DROP statement, we don't need dump indexes that are partition of + * a partitioned index, since drop partitioned index will drop it as + * well. We support index inheritance hierarchies since version 11, but + * earlier than version 11, we unconditionally set parentidx to 0. + */ + if (!OidIsValid(indxinfo->parentidx)) + appendPQExpBuffer(delq, "DROP INDEX %s;\n", qqindxname); if (indxinfo->dobj.dump & DUMP_COMPONENT_DEFINITION) ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId, -- 2.34.1
