public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_dump --if-exists --clean when drop index that is partition of a partitioned index
Date: Tue, 15 Apr 2025 13:33:09 +0800
Message-ID: <CACJufxFUkE+KBKPKabrQBOwtBowgWLfsmM3cf2GRB6WyHWEfXw@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRCjd9DV7Viov8WM7N0V1LLQ3OtXOpZ1qzy6eGUv_qVs0w@mail.gmail.com>
References: <CACJufxF0QSdkjFKF4di-JGWN6CSdQYEAhGPmQJJCdkSZtd=oLg@mail.gmail.com>
<CAFj8pRCjd9DV7Viov8WM7N0V1LLQ3OtXOpZ1qzy6eGUv_qVs0w@mail.gmail.com>
On Mon, Apr 14, 2025 at 2:09 PM Pavel Stehule <[email protected]> wrote:
>
> Hi
>
> po 14. 4. 2025 v 7:54 odesílatel jian he <[email protected]> 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.
Attachments:
[text/x-patch] v1-0001-fix-pg_dump-clean-on-index-inheritance-hierarchie.patch (1.6K, ../CACJufxFUkE+KBKPKabrQBOwtBowgWLfsmM3cf2GRB6WyHWEfXw@mail.gmail.com/2-v1-0001-fix-pg_dump-clean-on-index-inheritance-hierarchie.patch)
download | inline diff:
From 24cca288498cbbe7fb831e92567205fe13db5e19 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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=oLg@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
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: pg_dump --if-exists --clean when drop index that is partition of a partitioned index
In-Reply-To: <CACJufxFUkE+KBKPKabrQBOwtBowgWLfsmM3cf2GRB6WyHWEfXw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox