pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint
3+ messages / 3 participants
[nested] [flat]

* BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint
@ 2026-11-09 13:22  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 3+ messages in thread

From: PG Bug reporting form @ 2026-11-09 13:22 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 125atishprak@gmail.com

The following bug has been logged on the website:

Bug reference:      19858
Logged by:          Atish Prakash
Email address:      125atishprak@gmail.com
PostgreSQL version: 18.6
Operating system:   Linux x86_64
Description:        

The pg_dump documentation says --if-exists suppresses "does not exist"
errors
when restoring with --clean. A separately dumped domain constraint is not
covered,
because the command it generates has no guard on the domain itself.

    CREATE DOMAIN d AS int;
    ALTER DOMAIN d ADD CONSTRAINT k CHECK (VALUE > 0) NOT VALID;

    pg_dump --clean --if-exists mydb > dump.sql

The dump starts with:

    ALTER DOMAIN public.d DROP CONSTRAINT IF EXISTS k;
    DROP DOMAIN IF EXISTS public.d;

Restoring into an empty database gives:

    ERROR:  type "public.d" does not exist

With psql -v ON_ERROR_STOP=1 the restore stops at that point, before
creating
anything. Other objects in the same test, using ALTER TABLE IF EXISTS and
DROP ...
IF EXISTS, gave no such error.

Expected: no "does not exist" error with --if-exists.

ALTER DOMAIN has no IF EXISTS form for the domain, so the fix could be to
add one,
or to skip the constraint drop since DROP DOMAIN follows it. I have not
written a
patch.

Found with dump and restore differential tests. I reproduced and checked the
result myself.








^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint
@ 2026-11-17 02:20  shihao zhong <zhong950419@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 3+ messages in thread

From: shihao zhong @ 2026-11-17 02:20 UTC (permalink / raw)
  To: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org

Thanks for the clear report. I can reproduce it on 17-master.

0001 skips the ALTER DOMAIN ... DROP CONSTRAINT when the domain itself
is being dropped too, since DROP DOMAIN removes the constraint anyway.

0002 fixes the same problem for NOT VALID constraints on foreign
tables.

The current implementation follows the pattern we already have.

Another way is to build the TOC dependencies for plain-text output as well
and
use them here. But that also changes the pg_dump -v output.

Thanks,
Shihao

Attachments: download all (2 files, .tar.gz)

v1-0001-Fix-pg_dump-clean-if-exists-for-NOT-VALID-domain-con.patch (application/octet-stream, 6.3K) download From 015c34f97411cefea9efd1162b60bcae7c005f73 Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Mon, 16 Nov 2026 21:03:25 -0500 Subject: [PATCH 1/2] Fix pg_dump --clean --if-exists for NOT VALID domain constraints. ALTER DOMAIN has no IF EXISTS, so skip the constraint drop when DROP DOMAIN will remove it anyway. Reported-by: Atish Prakash <125atishprak@gmail.com> Discussion: https://postgr.es/m/19858-5a734f08714a5c73@postgresql.org Backpatch-through: 14 --- src/bin/pg_dump/pg_backup_archiver.c | 54 ++++++++++++++++++++++++++++ src/bin/pg_dump/t/002_pg_dump.pl | 13 +++++++ src/bin/pg_dump/t/011_restore.pl | 53 +++++++++++++++++++++++++++ 3 files changed, 120 insertions(+) diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index d3e0006fa07..25c1c5fb96b 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -65,6 +65,7 @@ static void _doSetSessionAuth(ArchiveHandle *AH, const char *user); static void _reconnectToDB(ArchiveHandle *AH, const char *dbname); static void _becomeUser(ArchiveHandle *AH, const char *user); static void _becomeOwner(ArchiveHandle *AH, TocEntry *te); +static bool droppedWithDomain(ArchiveHandle *AH, TocEntry *te); static void _selectOutputSchema(ArchiveHandle *AH, const char *schemaName); static void _selectTablespace(ArchiveHandle *AH, const char *tablespace); static void _selectTableAccessMethod(ArchiveHandle *AH, const char *tableam); @@ -583,6 +584,15 @@ RestoreArchive(Archive *AHX) */ ahprintf(AH, "%s", te->dropStmt); } + else if (droppedWithDomain(AH, te)) + { + /* + * ALTER DOMAIN has no IF EXISTS option, so we can't + * make this command safe if the domain is missing. + * But the domain is being dropped too, which will + * take the constraint with it, so just skip this. + */ + } else { /* @@ -2044,6 +2054,50 @@ getTocEntryByDumpId(ArchiveHandle *AH, DumpId id) return NULL; } +/* + * Is te a separately-dumped domain constraint whose domain is also + * going to be dropped during this restore? + * + * We can't rely on te's dependencies, since pg_dump doesn't fill those in + * for plain-text output. Instead, match the domain name in the two drop + * commands, "ALTER DOMAIN name DROP CONSTRAINT ..." and "DROP DOMAIN name;". + */ +static bool +droppedWithDomain(ArchiveHandle *AH, TocEntry *te) +{ + const char *rest; + + if (strcmp(te->desc, "CHECK CONSTRAINT") != 0 && + strcmp(te->desc, "CONSTRAINT") != 0) + return false; + if (strncmp(te->dropStmt, "ALTER DOMAIN ", 13) != 0) + return false; + rest = te->dropStmt + 13; + + for (TocEntry *dte = AH->toc->next; dte != AH->toc; dte = dte->next) + { + const char *domname; + size_t len; + + if (strcmp(dte->desc, "DOMAIN") != 0 || + (dte->reqs & REQ_SCHEMA) == 0 || + dte->dropStmt == NULL || + strncmp(dte->dropStmt, "DROP DOMAIN ", 12) != 0) + continue; + /* the name is whatever is between "DROP DOMAIN " and ";\n" */ + domname = dte->dropStmt + 12; + len = strlen(domname); + if (len < 2 || strcmp(domname + len - 2, ";\n") != 0) + continue; + len -= 2; + if (strncmp(rest, domname, len) == 0 && + strncmp(rest + len, " DROP CONSTRAINT ", 17) == 0) + return true; + } + + return false; +} + int TocIDRequired(ArchiveHandle *AH, DumpId id) { diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index ec73c4d5be9..f6d83eaee91 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2355,6 +2355,19 @@ my %tests = ( }, }, + 'ALTER DOMAIN dump_test.d_notvalid DROP CONSTRAINT' => { + create_order => 30, + create_sql => 'CREATE DOMAIN dump_test.d_notvalid AS int; + ALTER DOMAIN dump_test.d_notvalid + ADD CONSTRAINT d_notvalid_check CHECK (VALUE > 0) NOT VALID;', + regexp => qr/^ + \QALTER DOMAIN dump_test.d_notvalid DROP CONSTRAINT\E + /xm, + + # with --if-exists, the domain's own drop takes care of this + like => { clean => 1, }, + }, + 'COMMENT ON CONSTRAINT ON DOMAIN (1)' => { regexp => qr/^ \QCOMMENT ON CONSTRAINT nn ON DOMAIN dump_test.us_postal_code IS 'not null';\E diff --git a/src/bin/pg_dump/t/011_restore.pl b/src/bin/pg_dump/t/011_restore.pl index 9ce275ee8da..e4a55df5b91 100644 --- a/src/bin/pg_dump/t/011_restore.pl +++ b/src/bin/pg_dump/t/011_restore.pl @@ -265,5 +265,58 @@ foreach my $c (@dumpall_cases) "restore from pg_dumpall --clean ($c->{name})"); } +# A NOT VALID domain constraint is dumped separately, and its drop command +# is ALTER DOMAIN ... DROP CONSTRAINT, which can't be made conditional on the +# domain existing. Check that --clean --if-exists works into an empty +# database, and still drops the constraint when it is restored alone. +$node->safe_psql('postgres', 'CREATE DATABASE notvalid_src'); +$node->safe_psql('postgres', 'CREATE DATABASE notvalid_dst'); +$node->safe_psql( + 'notvalid_src', q{ + CREATE DOMAIN d AS int; + ALTER DOMAIN d ADD CONSTRAINT k CHECK (VALUE > 0) NOT VALID; + CREATE DOMAIN "d x" AS int; + ALTER DOMAIN "d x" ADD CONSTRAINT k CHECK (VALUE > 0) NOT VALID; +}); +my $notvalid_dump = "$backupdir/notvalid-dump"; +$node->command_ok( + [ + 'pg_dump', + '--format' => 'custom', + '--no-sync', + '--file' => $notvalid_dump, + $node->connstr('notvalid_src'), + ], + 'dump NOT VALID constraints'); +$node->command_ok( + [ + 'pg_restore', '--clean', '--if-exists', '--exit-on-error', + '--dbname' => $node->connstr('notvalid_dst'), + $notvalid_dump, + ], + 'pg_restore --clean --if-exists of NOT VALID constraints into empty database' +); + +my ($toc, $toc_err); +run_log [ 'pg_restore', '--list', $notvalid_dump ], '>' => \$toc, + '2>' => \$toc_err; +my $toc_file = "$backupdir/notvalid-toc"; +open(my $fh, '>', $toc_file) or die "could not open $toc_file: $!"; +print $fh grep { /CHECK CONSTRAINT/ } split(/^/, $toc); +close($fh); +$node->command_ok( + [ + 'pg_restore', '--clean', '--if-exists', '--exit-on-error', + '--use-list' => $toc_file, + '--dbname' => $node->connstr('notvalid_dst'), + $notvalid_dump, + ], + 'pg_restore --clean --if-exists of only NOT VALID constraints'); +is( $node->safe_psql( + 'notvalid_dst', + "SELECT count(*) FROM pg_constraint WHERE NOT convalidated AND conname = 'k'" + ), + '2', + 'NOT VALID constraints restored'); done_testing(); -- 2.37.1 (Apple Git-137.1)
v1-0002-Fix-pg_dump-clean-if-exists-for-foreign-table-constr.patch (application/octet-stream, 3.5K) download From 080745adce718f1ebd1065bc953d51954327fc94 Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Mon, 16 Nov 2026 21:03:25 -0500 Subject: [PATCH 2/2] Fix pg_dump --clean --if-exists for foreign table constraints. Add IF EXISTS after ALTER FOREIGN TABLE too. This covers CHECK constraints and column defaults. Discussion: https://postgr.es/m/19858-5a734f08714a5c73@postgresql.org Backpatch-through: 14 --- src/bin/pg_dump/pg_backup_archiver.c | 9 ++++++++- src/bin/pg_dump/t/002_pg_dump.pl | 11 +++++++++++ src/bin/pg_dump/t/011_restore.pl | 9 +++++++-- 3 files changed, 26 insertions(+), 3 deletions(-) diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 25c1c5fb96b..0d6f981874c 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -614,7 +614,8 @@ RestoreArchive(Archive *AHX) /* * Need to inject IF EXISTS clause after ALTER - * TABLE part in ALTER TABLE .. DROP statement + * TABLE part in ALTER TABLE .. DROP statement, + * and likewise for ALTER FOREIGN TABLE */ if (strncmp(dropStmt, "ALTER TABLE", 11) == 0) { @@ -622,6 +623,12 @@ RestoreArchive(Archive *AHX) "ALTER TABLE IF EXISTS"); dropStmt = dropStmt + 11; } + else if (strncmp(dropStmt, "ALTER FOREIGN TABLE", 19) == 0) + { + appendPQExpBufferStr(ftStmt, + "ALTER FOREIGN TABLE IF EXISTS"); + dropStmt = dropStmt + 19; + } /* * ALTER TABLE..ALTER COLUMN..DROP DEFAULT does diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index f6d83eaee91..5bbc75772d9 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3041,6 +3041,17 @@ my %tests = ( }, }, + 'ALTER FOREIGN TABLE IF EXISTS foreign_table DROP CONSTRAINT' => { + create_order => 89, + create_sql => 'ALTER FOREIGN TABLE dump_test.foreign_table + ADD CONSTRAINT foreign_table_check CHECK (c1 > 0) NOT VALID;', + regexp => qr/^ + \QALTER FOREIGN TABLE IF EXISTS dump_test.foreign_table \E + \QDROP CONSTRAINT IF EXISTS foreign_table_check;\E + /xm, + like => { clean_if_exists => 1, }, + }, + 'CREATE USER MAPPING FOR regress_dump_test_role SERVER s1' => { create_order => 86, create_sql => diff --git a/src/bin/pg_dump/t/011_restore.pl b/src/bin/pg_dump/t/011_restore.pl index e4a55df5b91..5b58914cce4 100644 --- a/src/bin/pg_dump/t/011_restore.pl +++ b/src/bin/pg_dump/t/011_restore.pl @@ -277,6 +277,11 @@ $node->safe_psql( ALTER DOMAIN d ADD CONSTRAINT k CHECK (VALUE > 0) NOT VALID; CREATE DOMAIN "d x" AS int; ALTER DOMAIN "d x" ADD CONSTRAINT k CHECK (VALUE > 0) NOT VALID; + -- likewise for a foreign table, whose drop is ALTER FOREIGN TABLE + CREATE FOREIGN DATA WRAPPER w; + CREATE SERVER s FOREIGN DATA WRAPPER w; + CREATE FOREIGN TABLE ft (a int) SERVER s; + ALTER FOREIGN TABLE ft ADD CONSTRAINT fk CHECK (a > 0) NOT VALID; }); my $notvalid_dump = "$backupdir/notvalid-dump"; $node->command_ok( @@ -314,9 +319,9 @@ $node->command_ok( 'pg_restore --clean --if-exists of only NOT VALID constraints'); is( $node->safe_psql( 'notvalid_dst', - "SELECT count(*) FROM pg_constraint WHERE NOT convalidated AND conname = 'k'" + "SELECT count(*) FROM pg_constraint WHERE NOT convalidated AND conname IN ('k', 'fk')" ), - '2', + '3', 'NOT VALID constraints restored'); done_testing(); -- 2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint
@ 2026-11-18 09:57  Dilip Kumar <dilipbalaut@gmail.com>
  parent: shihao zhong <zhong950419@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Dilip Kumar @ 2026-11-18 09:57 UTC (permalink / raw)
  To: shihao zhong <zhong950419@gmail.com>; +Cc: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org

On Tue, Nov 17, 2026 at 7:50 AM shihao zhong <zhong950419@gmail.com> wrote:
>
> Thanks for the clear report. I can reproduce it on 17-master.
>
> 0001 skips the ALTER DOMAIN ... DROP CONSTRAINT when the domain itself
> is being dropped too, since DROP DOMAIN removes the constraint anyway.

Thanks for the patch Shihao,

Don't you think that skipping ALTER DOMAIN ... DROP CONSTRAINT breaks
"--clean --if-exists" restores into existing databases when the
constraint depends on objects dropped before the domain?

Check below example[1]:

[1]
createdb test_f1
psql -X -d test_f1 <<'SQL'
CREATE FUNCTION is_pos(int) RETURNS boolean
    LANGUAGE sql IMMUTABLE AS $$ SELECT $1 > 0 $$;
CREATE DOMAIN pos_int AS int;
ALTER DOMAIN pos_int
    ADD CONSTRAINT pos_int_chk CHECK (is_pos(VALUE)) NOT VALID;
SQL
dilipkumarb@dilipkumarb:~/PG/postgres$ pg_dump --clean --if-exists
test_f1 > x_dump
dilipkumarb@dilipkumarb:~/PG/postgres$ psql -X -v ON_ERROR_STOP=1 -d
test_f1 -f x_dump
SET
.....
SET
2026-11-18 09:54:03.565 UTC [1566167] ERROR:  cannot drop function
public.is_pos(integer) because other objects depend on it
2026-11-18 09:54:03.565 UTC [1566167] DETAIL:  constraint pos_int_chk
depends on function public.is_pos(integer)
2026-11-18 09:54:03.565 UTC [1566167] HINT:  Use DROP ... CASCADE to
drop the dependent objects too.
2026-11-18 09:54:03.565 UTC [1566167] STATEMENT:  DROP FUNCTION IF
EXISTS public.is_pos(integer);
psql:x_dump:22: ERROR:  cannot drop function public.is_pos(integer)
because other objects depend on it
DETAIL:  constraint pos_int_chk depends on function public.is_pos(integer)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

-- 
Regards,
Dilip Kumar
Google






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2026-11-18 09:57 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-09 13:22 BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint PG Bug reporting form <noreply@postgresql.org>
2026-11-17 02:20 ` shihao zhong <zhong950419@gmail.com>
2026-11-18 09:57   ` Dilip Kumar <dilipbalaut@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox