On 8/24/22 13:11, Bryn Llewellyn wrote:
*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?
create table c1 (id integer, constraint pk1 primary key(id));
CREATE TABLE
create table c2 (id integer, constraint pk1 primary key(id));
ERROR: relation "pk1" already exists
create table test.c2 (id integer, constraint pk1 primary key(id));
CREATE TABLE
select conname, connamespace from pg_constraint where conname = 'pk1';
conname | connamespace
---------+--------------
pk1 | 2200
pk1 | 59706
From:
https://www.postgresql.org/docs/current/catalog-pg-constraint.html
conname name
Constraint name (not necessarily unique!)
So connamespace makes it unique.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
