public inbox for [email protected]
help / color / mirror / Atom feedPostgres 17 domains with NOT NULL and pg_get_constraintdef()
2+ messages / 2 participants
[nested] [flat]
* Postgres 17 domains with NOT NULL and pg_get_constraintdef()
@ 2025-01-08 11:15 [email protected]
2025-01-08 13:10 ` Re: Postgres 17 domains with NOT NULL and pg_get_constraintdef() Alvaro Herrera <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: [email protected] @ 2025-01-08 11:15 UTC (permalink / raw)
To: [email protected]
Hello,
I am not sure if this is intended behavior or an oversight/bug.
Assume the following domain definition:
CREATE DOMAIN sample_domain AS numeric(12,2) NOT NULL constraint positive_value CHECK (value > 0);
Up until Postgres 16 it was possible to retrieve the domain constraints using:
select t.typname,
pg_catalog.pg_get_constraintdef(ci.oid, true)
from pg_catalog.pg_type t
join pg_catalog.pg_constraint ci on ci.contypid = t.oid
where t.typtype = 'd'
and t.typname = 'sample_domain'
However, because the domain is defined with NOT NULL, this fails with
> ERROR: invalid constraint type "n"
with 17.2 on Windows and Linux.
This is a result of storing the NOT NULL constraint in pg_constraint
and can easily be avoided by either using contype <> 'n' or by not calling
pg_get_constraintdef if contype = 'n'
Is this a bug/oversight in pg_get_constraintdef() or does it just not support NOT NULL
constraints? The manual does not show any restriction on the usage.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Postgres 17 domains with NOT NULL and pg_get_constraintdef()
2025-01-08 11:15 Postgres 17 domains with NOT NULL and pg_get_constraintdef() [email protected]
@ 2025-01-08 13:10 ` Alvaro Herrera <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Alvaro Herrera @ 2025-01-08 13:10 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On 2025-Jan-08, [email protected] wrote:
Hello,
> However, because the domain is defined with NOT NULL, this fails with
>
> > ERROR: invalid constraint type "n"
>
> with 17.2 on Windows and Linux.
>
> This is a result of storing the NOT NULL constraint in pg_constraint
> and can easily be avoided by either using contype <> 'n' or by not calling
> pg_get_constraintdef if contype = 'n'
Yeah, thanks for the report -- this is clearly a bug and it was fixed
already in the 17 branch, but it's not been released yet. It'll be in
the February release.
Author: Álvaro Herrera <[email protected]>
Branch: master [09d09d429] 2024-11-27 13:50:27 +0100
Branch: REL_17_STABLE [6e793582b] 2024-11-27 13:50:27 +0100
Fix pg_get_constraintdef for NOT NULL constraints on domains
We added pg_constraint rows for all not-null constraints, first for
tables and later for domains; but while the ones for tables were
reverted, the ones for domains were not. However, we did accidentally
revert ruleutils.c support for the ones on domains in 6f8bb7c1e961,
which breaks running pg_get_constraintdef() on them. Put that back.
This is only needed in branch 17, because we've reinstated this code in
branch master with commit 14e87ffa5c54. Add some new tests in both
branches.
I couldn't find anything else that needs de-reverting.
Reported-by: Erki Eessaar <[email protected]>
Reviewed-by: Magnus Hagander <[email protected]>
Discussion: https://postgr.es/m/AS8PR01MB75110350415AAB8BBABBA1ECFE222@AS8PR01MB7511.eurprd01.prod.exchangelabs....
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended." (Gerry Pourwelle)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-01-08 13:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-08 11:15 Postgres 17 domains with NOT NULL and pg_get_constraintdef() [email protected]
2025-01-08 13:10 ` Alvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox