Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function pg_get_constraintdef

Function pg_get_constraintdef

From
Erki Eessaar
Date:
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



Re: Function pg_get_constraintdef

From
Erki Eessaar
Date:
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



Re: Function pg_get_constraintdef

From
Magnus Hagander
Date:
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;
 
--

Re: Function pg_get_constraintdef

From
Magnus Hagander
Date:

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 :)
 
--