From: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Two questions about "pg_constraint" |
Date: | 2022-08-24 20:11:17 |
Message-ID: | 496793CE-A048-434B-ABC0-6BFC0C65C3EC@yugabyte.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
*Question 1: why does "pg_constraint" have a "connamespace" column?*
I created this temporary view (using PG 14.4):
create temporary view all_constraints(t_owner, t_schema, t_name, c_name, same) as
select
r.rolname,
s.nspname,
c.relname,
x.conname,
(x.connamespace = c.relnamespace)
from
pg_class c
inner join
pg_roles r
on c.relowner = r.oid
inner join
pg_namespace s
on c.relnamespace = s.oid
inner join pg_constraint x
on c.oid = x.conrelid
where c.relkind = 'r';
I created three tables, each with a user-created constraint. The tables also have implicitly created primary key constraints.
Then I did this:
select count(*) from all_constraints;
It said that the count is over a hundred. (All but the rows for my three tables are for rows for tables in the "pg_catalog" schema.)
Then I did this:
select exists(select 1 from all_constraints where not same)::text;
It said "false".
Over one hundred seems to be a fair sample size. So it seems to be reasonable to assume that "pg_constraint.connamespace = pg_class.relnamespace" is always true. Ordinary common-sense analysis of the query suggests this too. If the hypothesis is right, then "connamespace" is simply a derived value. And this would be a departure from usual table design practice.
What do you think?
*Question 2: what happened to the column "consrc"?*
The PG 11 account of "pg_constraint"
https://www.postgresql.org/docs/11/catalog-pg-constraint.html
describes "consrc" (text) thus:
« If a check constraint, a human-readable representation of the expression »
Ad hoc queries in my PG 11.9 env show results like « (v = lower(v)) » in this column for my tables. This is useful information. But the PG 14 version of "pg_constraint" has no such column (and nor does the doc mention it). Is this information now exposed somewhere else?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2022-08-24 20:17:20 | Re: Two questions about "pg_constraint" |
Previous Message | Adrian Klaver | 2022-08-24 20:09:23 | Re: Corrupted Postgresql Microsoft Binaries |