Feedback-ID: ia2694551:Fastmail
Date: Thu, 21 Nov 2024 13:30:52 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Magnus Hagander <magnus@hagander.net>
Cc: Erki Eessaar <erki.eessaar@taltech.ee>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Function pg_get_constraintdef
Message-ID: <202411211230.ocnd65fqmdk2@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="dlg2ozuuyadg6s27"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CABUevEw=ohSx95+Ccdpg438w-3royWqMZxLur-PRQHnXEdHaSQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202411211230.ocnd65fqmdk2%40alvherre.pgsql>
Precedence: bulk


--dlg2ozuuyadg6s27
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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/

--dlg2ozuuyadg6s27
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment; filename="getconstraintdef-domains.patch"

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:
 
 			/*

--dlg2ozuuyadg6s27--