Re: pg_dump does not dump domain not-null constraint's comments

jian he <jian.universality@gmail.com>

From: jian he <jian.universality@gmail.com>

To: Álvaro Herrera <alvherre@kurilemu.de>

Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2025-07-15T07:44:10Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Fix pg_dump COMMENT dependency for separate domain constraints.
- a685c057ad4c 13.23 landed
- 85d6ed31fc7c 14.20 landed
- 0773f3a875f1 15.15 landed
- e127764b65da 17.7 landed
- 4ad846445dd0 18.0 landed
- 3cf328eca84a 16.11 landed
- c044b50d199c 19 (unreleased) landed
pg_dump: include comments on not-null constraints on domains, too
- f9545e95c5e7 18.0 landed
- da71717f0a7c 19 (unreleased) landed
- 6b755d8d70b2 17.6 landed
Fix dumping of comments on invalid constraints on domains
- e04aca1c4d33 14.19 landed
- dca0e9693b71 18.0 landed
- d07bc7c2b3ca 17.6 landed
- cef998ef8314 16.10 landed
- 5a261c135d43 15.14 landed
- 57949cea5af0 13.22 landed
- 0858f0f96ebb 19 (unreleased) landed
Catalog domain not-null constraints
- e5da0fe3c22b 17.0 cited
In pg_dump, don't dump a stats object unless dumping underlying table.
- 7418767f11d1 17.0 cited
Enable CHECK constraints to be declared NOT VALID
- 897795240cfa 9.2.0 cited

Attachments

v5-0001-fix-pg_dump-domain-not-valid-constraint-comments.no-cfbot (application/octet-stream)

On Tue, Jul 15, 2025 at 2:10 PM jian he <jian.universality@gmail.com> wrote:
>
> accidently found another existing bug.
>
> create schema test;
> CREATE DOMAIN test.d1 AS integer NOT NULL default 11;
> alter domain test.d1 add constraint a2 check(value > 1) not valid;
> comment on CONSTRAINT a2 ON DOMAIN test.d1 is 'test';
> dump output is:
>
> CREATE SCHEMA test;
> CREATE DOMAIN test.d1 AS integer NOT NULL DEFAULT 11;
> COMMENT ON CONSTRAINT a2 ON DOMAIN test.d1 IS 'test';
> ALTER DOMAIN test.d1
>     ADD CONSTRAINT a2 CHECK ((VALUE > 1)) NOT VALID;
>
> Obviously the COMMENT command will error out.
> currently working on a fix, just sharing the bug details in advance.

we should let:
dumpConstraint handles dumping separate "NOT VALID" domain constraints along
with their comments.
dumpDomain: handles dumping "inlined" valid (not separate) domain constraints
together with their comments.

tested locally, i didn't write the test on src/bin/pg_dump/t/002_pg_dump.pl....

v5-0001-fix-pg_dump-domain-not-valid-constraint-comments.no-cfbot
was based on
v4-0001-Improve-pg_dump-handling-of-domain-not-null-constraints.patch
and 0001-fix-tweak.patch.txt.