Horton

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

Álvaro Herrera <alvherre@kurilemu.de>

View thread
From: Álvaro Herrera <alvherre@kurilemu.de>
To: jian he <jian.universality@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-07-15T13:40:38Z
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 →

  1. Fix pg_dump COMMENT dependency for separate domain constraints.

  2. pg_dump: include comments on not-null constraints on domains, too

  3. Fix dumping of comments on invalid constraints on domains

  4. Catalog domain not-null constraints

  5. In pg_dump, don't dump a stats object unless dumping underlying table.

  6. Enable CHECK constraints to be declared NOT VALID

Attachments

On 2025-Jul-15, jian he wrote:

> 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;
> > 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.

Ouch, ugh.  I think this is as old as NOT VALID constraints on domains,
maybe from commit 897795240cfa (Jun 2011), or ... no, actually
7eca575d1c28 (Dec 2014) which enabled constraints on domains to have
comments.  In either case it's my fault, and the fix needs to be
backpatched all the way back, so I'm going to apply this bugfix one
ahead of the others, which are for newer bugs.

> 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.

Yeah, this makes sense.

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

I'll add something for coverage, but not yet sure if it's going to be
something in 002_pg_dump.pl, or objects to be left around for the
pg_upgrade test to pick up.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/