pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction pg_get_constraintdef
6+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2024-11-27 09d09d4297 6e793582bc Fix pg_get_constraintdef for NOT NULL constraints on domains
* Function pg_get_constraintdef
@ 2024-11-21 11:46 Erki Eessaar <erki.eessaar@taltech.ee>
2024-11-21 12:04 ` Re: Function pg_get_constraintdef Erki Eessaar <erki.eessaar@taltech.ee>
2024-11-21 12:16 ` Re: Function pg_get_constraintdef Magnus Hagander <magnus@hagander.net>
0 siblings, 2 replies; 6+ messages in thread
From: Erki Eessaar @ 2024-11-21 11:46 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function pg_get_constraintdef
2024-11-21 11:46 Function pg_get_constraintdef Erki Eessaar <erki.eessaar@taltech.ee>
@ 2024-11-21 12:04 ` Erki Eessaar <erki.eessaar@taltech.ee>
1 sibling, 0 replies; 6+ messages in thread
From: Erki Eessaar @ 2024-11-21 12:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function pg_get_constraintdef
2024-11-21 11:46 Function pg_get_constraintdef Erki Eessaar <erki.eessaar@taltech.ee>
@ 2024-11-21 12:16 ` Magnus Hagander <magnus@hagander.net>
2024-11-21 12:30 ` Re: Function pg_get_constraintdef Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 1 reply; 6+ messages in thread
From: Magnus Hagander @ 2024-11-21 12:16 UTC (permalink / raw)
To: Erki Eessaar <erki.eessaar@taltech.ee>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Alvaro Herrera <alvherre@alvh.no-ip.org>
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/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function pg_get_constraintdef
2024-11-21 11:46 Function pg_get_constraintdef Erki Eessaar <erki.eessaar@taltech.ee>
2024-11-21 12:16 ` Re: Function pg_get_constraintdef Magnus Hagander <magnus@hagander.net>
@ 2024-11-21 12:30 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-11-21 12:43 ` Re: Function pg_get_constraintdef Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2024-11-21 12:30 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Erki Eessaar <erki.eessaar@taltech.ee>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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/
Attachments:
getconstraintdef-domains.patch (text/x-diff, 486B) download
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index cd9c3eddd1d..c31d6fc1fb8 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2499,6 +2499,11 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
conForm->connoinherit ? " NO INHERIT" : "");
break;
}
+
+ case CONSTRAINT_NOTNULL:
+ appendStringInfoString(&buf, "NOT NULL");
+ break;
+
case CONSTRAINT_TRIGGER:
/*
* Re: Function pg_get_constraintdef
2024-11-21 11:46 Function pg_get_constraintdef Erki Eessaar <erki.eessaar@taltech.ee>
2024-11-21 12:16 ` Re: Function pg_get_constraintdef Magnus Hagander <magnus@hagander.net>
2024-11-21 12:30 ` Re: Function pg_get_constraintdef Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2024-11-21 12:43 ` Magnus Hagander <magnus@hagander.net>
2024-11-27 12:53 ` Re: Function pg_get_constraintdef Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 6+ messages in thread
From: Magnus Hagander @ 2024-11-21 12:43 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Erki Eessaar <erki.eessaar@taltech.ee>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Function pg_get_constraintdef
2024-11-21 11:46 Function pg_get_constraintdef Erki Eessaar <erki.eessaar@taltech.ee>
2024-11-21 12:16 ` Re: Function pg_get_constraintdef Magnus Hagander <magnus@hagander.net>
2024-11-21 12:30 ` Re: Function pg_get_constraintdef Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-11-21 12:43 ` Re: Function pg_get_constraintdef Magnus Hagander <magnus@hagander.net>
@ 2024-11-27 12:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2024-11-27 12:53 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Erki Eessaar <erki.eessaar@taltech.ee>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-11-27 12:53 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-21 11:46 Function pg_get_constraintdef Erki Eessaar <erki.eessaar@taltech.ee>
2024-11-21 12:04 ` Erki Eessaar <erki.eessaar@taltech.ee>
2024-11-21 12:16 ` Magnus Hagander <magnus@hagander.net>
2024-11-21 12:30 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-11-21 12:43 ` Magnus Hagander <magnus@hagander.net>
2024-11-27 12:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox