Horton

Thread

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_get_constraintdef for NOT NULL constraints on domains

  1. Function pg_get_constraintdef

    Erki Eessaar <erki.eessaar@taltech.ee> — 2024-11-21T11:46:45Z

    Hello

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;

If I execute the query in two different servers with PostgreSQL 17.1 and in different databases in these servers, then I get the answer

ERROR:  invalid constraint type "n"

In case of an empty database I do not get the error. The query worked in the servers before upgrading to PostgreSQL 17.

If I execute the query in a database of a server with PostgreSQL 15, then I do not get the error.

Best regards
Erki Eessaar

  2. Re: Function pg_get_constraintdef

    Erki Eessaar <erki.eessaar@taltech.ee> — 2024-11-21T12:04:04Z

    Here is a followup. The problem occurs if the database has a domain with a check constraint. The problem does not exist in PostgreSQL 15.9

***************
PostgreSQL 17.1
***************

Empty database

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE TABLE A (a_id SMALLINT NOT NULL,
CONSTRAINT pk_a PRIMARY KEY (a_id),
CONSTRAINT chk_a_id CHECK (a_id>0));

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE DOMAIN d_a SMALLINT NOT NULL
CONSTRAINT chk_d_a CHECK (VALUE>=0);

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--ERROR:  invalid constraint type "n"


***************
PostgreSQL 15.9
***************

Empty database

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE TABLE A (a_id SMALLINT NOT NULL,
CONSTRAINT pk_a PRIMARY KEY (a_id),
CONSTRAINT chk_a_id CHECK (a_id>0));

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error

CREATE DOMAIN d_a SMALLINT NOT NULL
CONSTRAINT chk_d_a CHECK (VALUE>=0);

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;
--No error


Best regards
Erki Eessaar

________________________________
From: Erki Eessaar <erki.eessaar@taltech.ee>
Sent: Thursday, November 21, 2024 13:46
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Function pg_get_constraintdef

Hello

SELECT pg_get_constraintdef(c.oid)
FROM pg_catalog.pg_constraint c;

If I execute the query in two different servers with PostgreSQL 17.1 and in different databases in these servers, then I get the answer

ERROR:  invalid constraint type "n"

In case of an empty database I do not get the error. The query worked in the servers before upgrading to PostgreSQL 17.

If I execute the query in a database of a server with PostgreSQL 15, then I do not get the error.

Best regards
Erki Eessaar

  3. Re: Function pg_get_constraintdef

    Magnus Hagander <magnus@hagander.net> — 2024-11-21T12:16:45Z

    On Thu, Nov 21, 2024 at 1:01 PM Erki Eessaar <erki.eessaar@taltech.ee>
wrote:

> Hello
>
> SELECT pg_get_constraintdef(c.oid)
> FROM pg_catalog.pg_constraint c;
>
> If I execute the query in two different servers with PostgreSQL 17.1 and
> in different databases in these servers, then I get the answer
>
> ERROR:  invalid constraint type "n"
>
> In case of an empty database I do not get the error. The query worked in
> the servers before upgrading to PostgreSQL 17.
>
> If I execute the query in a database of a server with PostgreSQL 15, then
> I do not get the error.
>
>
Looks like this is an omission in the catalog-not-null-constraints patch.
It happens when you've got a domain that has a not null constraint on it.
Easily reproducible with

create domain test as int  int not null;
SELECT pg_get_constraintdef(c.oid) FROM pg_catalog.pg_constraint c;

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

  4. Re: Function pg_get_constraintdef

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-11-21T12:30:52Z

    On 2024-Nov-21, Magnus Hagander wrote:

> On Thu, Nov 21, 2024 at 1:01 PM Erki Eessaar <erki.eessaar@taltech.ee>
> wrote:
> 
> > SELECT pg_get_constraintdef(c.oid)
> > FROM pg_catalog.pg_constraint c;
> >
> > If I execute the query in two different servers with PostgreSQL 17.1 and
> > in different databases in these servers, then I get the answer
> >
> > ERROR:  invalid constraint type "n"

> Looks like this is an omission in the catalog-not-null-constraints patch.
> It happens when you've got a domain that has a not null constraint on it.
> Easily reproducible with
> 
> create domain test as int  int not null;
> SELECT pg_get_constraintdef(c.oid) FROM pg_catalog.pg_constraint c;

Ah, yeah.  The overall catalog-not-null-constraints patch was reverted
in 17 (and recently reintroduced in 18).  But we kept not-null
constraints for domains.  However, the ruleutils.c code to support
domain ones wasn't kept.  So we need something based on the attached
patch, which just copies what the code in 18 does for the domain case.

I wonder if there are other places that need to handle these constraint
entries, though.

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

  5. Re: Function pg_get_constraintdef

    Magnus Hagander <magnus@hagander.net> — 2024-11-21T12:43:11Z

    On Thu, Nov 21, 2024 at 1:30 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2024-Nov-21, Magnus Hagander wrote:
>
> > On Thu, Nov 21, 2024 at 1:01 PM Erki Eessaar <erki.eessaar@taltech.ee>
> > wrote:
> >
> > > SELECT pg_get_constraintdef(c.oid)
> > > FROM pg_catalog.pg_constraint c;
> > >
> > > If I execute the query in two different servers with PostgreSQL 17.1
> and
> > > in different databases in these servers, then I get the answer
> > >
> > > ERROR:  invalid constraint type "n"
>
> > Looks like this is an omission in the catalog-not-null-constraints patch.
> > It happens when you've got a domain that has a not null constraint on it.
> > Easily reproducible with
> >
> > create domain test as int  int not null;
> > SELECT pg_get_constraintdef(c.oid) FROM pg_catalog.pg_constraint c;
>
> Ah, yeah.  The overall catalog-not-null-constraints patch was reverted
> in 17 (and recently reintroduced in 18).  But we kept not-null
> constraints for domains.  However, the ruleutils.c code to support
> domain ones wasn't kept.  So we need something based on the attached
> patch, which just copies what the code in 18 does for the domain case.
>
> I wonder if there are other places that need to handle these constraint
> entries, though.
>

I can confirm that fixes it.

A quick grep for example shows that CONSTRAINT_TRIGGER is only used in that
same place, so that should at least be the only case-statement that needs
them all. But I really don't know enough about that code to comment on
whether there are other likely places for it :)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ <http://www.hagander.net/>
 Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>

  6. Re: Function pg_get_constraintdef

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-11-27T12:53:07Z

    On 2024-Nov-21, Magnus Hagander wrote:

> I can confirm that fixes it.

Thanks for testing it!

> A quick grep for example shows that CONSTRAINT_TRIGGER is only used in that
> same place, so that should at least be the only case-statement that needs
> them all. But I really don't know enough about that code to comment on
> whether there are other likely places for it :)

I looked around and couldn't find anything either.  I hope we're not
wrong.

Thanks, Erki, for reporting the issue.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"No tengo por qué estar de acuerdo con lo que pienso"
                             (Carlos Caszeli)