Horton

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

jian he <jian.universality@gmail.com>

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

On Tue, Jul 15, 2025 at 2:24 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2025-May-22, jian he wrote:
>
> > I actually found another bug.
> > create schema test;
> > CREATE DOMAIN test.d1 AS integer NOT NULL default 11;
> > pg_dump  --schema=test > 1.sql
> > ""
> > pg_dump: warning: could not resolve dependency loop among these items:
> > pg_dump: detail: TYPE d1  (ID 1415 OID 18007)
> > pg_dump: detail: CONSTRAINT d1_not_null  (ID 1416 OID 18008)
> > ""
>
> Oh, interesting.  I agree with the rough fix, but I think it's better if
> we keep the contype comparisons rather than removing them, relaxing to
> allow for one more char.
>
> I didn't like the idea of stashing the not-null constraint in the same
> array as the check constraints; it feels a bit dirty (especially because
> of the need to scan the array in order to find the not-null one).  I
> opted to add a separate TypeInfo->notnull pointer instead.  This feels
> more natural.  This works because we know a domain has only one not-null
> constraint.  Note that this means we don't need your proposed 0002
> anymore.
>
TypeInfo->notnull is much better than
TypeInfo->domChecks handle both check and not-null constraints.

>
> The attached applies on top of your patch.  Opinions?
>

+ constraint->contype = *(PQgetvalue(res, i, i_contype));
can change to
constraint->contype = contype;

in getDomainConstraints, we use pg_malloc
    constrinfo = (ConstraintInfo *) pg_malloc(ntups * sizeof(ConstraintInfo));
after
        constraint->condeferred = false;
would better also add
        constraint->conperiod = false;


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.