BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint

Поиск
Список
Период
Сортировка
Искать

BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.




Re: BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint

От:
shihao zhong <zhong950419@gmail.com>
Дата:
Вложения:
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

Re: BUG #19858: pg_dump --clean --if-exists fails on NOT VALID domain constraint

От:
Dilip Kumar <dilipbalaut@gmail.com>
Дата:
On Tue, Nov 17, 2026 at 7:50 AM shihao zhong  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


FAQ