> [email protected] wrote:
>
>> [email protected]:
>>
>> Question 1: why does "pg_constraint" have a "connamespace" column?
>
> create table c1 (id integer, constraint pk1 primary key(id));
>
> 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));
>
> select conname, connamespace from pg_constraint where conname = 'pk1';
> conname | connamespace
> ---------+--------------
> pk1 | 2200
> pk1 | 59706
>
> [From the doc] — conname name — Constraint name (not necessarily unique!) So
> connamespace makes it unique.
I'll assume that, in Adrian's example, bare "create table c1" puts it into the
public schema. But it could be, equivalently, that the creating user has a
search path with some other schema (but not the schema 'test’) in
first-to-be-searched position.
The example implicitly brings indexes into the picture. The explanation of the
"index_parameters" rule in the “create table” syntax starts off thus:
« index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are... »
and it goes on to mention some choices that you can make about the indexes that
necessarily back the PK and unique constraints. (The notions apply at "alter
table" time too.) However, it says nothing to indicate that the user can
specify the name (or the schema) for this backing index. I can't find where the
rule is stated for how the index is (as it must be) automatically named. But it
seems, from experiment, that the name for the index that backs a PK or a unique
constraint is simply the name that the user gave the constraint—or the
generated name of the constraint if it wasn't named.
The schema-and-name combination (a.k.a. the qualified name) for an index must
be unique in "pg_rel". So "create table c2 ... constraint pk1) fails because
there already is a backing index called "pk1" in place in the schema in
question. That's why the error message says « relation "pk1" already exists »
and not « constraint "pk1" already exists ».
Here's a contrived example to emphasize this point:
create table s1.irrelevant_name_1(
k int,
v1 int,
v2 int,
constraint irrelevant_name_2 primary key(k));
create index c on s1.irrelevant_name_1(v1);
It seems odd that an index must be in the same schema as the table upon whose
columns (or expressions) it is created. The "create index" account documents
this restriction. An ad hoc test shows that the index must also have the same
owner as its table. I can't find where this restriction is documented. I don't
suppose that these restrictions pose a practical problem.
Now do this:
alter table s1.irrelevant_name_1 add
constraint c unique(v2);
It fails with the error "relation "c" already exists" (and not "constraint "c"
already exists"). And sure enough, there is no existing constraint called 'c'.
With me so far?
Now try this:
create table s1.irrelevant_name_1(
k int primary key,
v text,
constraint c check(v = lower(v)));
create domain s1.irrelevant_name_2 as int[]
constraint c check(cardinality(value) > 1);
No errors occur. B.t.w., the table and the domain must have different names
because of the secondary effect that the name of a table causes a row BOTH in
"pg_class" AND in "pg_type".
Now do Adrian's query:
select conname, connamespace from pg_constraint where conname = 'c';
This was my result. (Your oid values will vary.)
conname | connamespace
---------+--------------
c | 2114282
c | 2114282
We see two constraints with the same name "in" the same schema. I double quoted
"in" because it's the wrong way to talk about this. Constraints (just like
triggers) are not schema-objects. But the things that "pg_class", "pg_type",
and "pg_proc" list _are_ schema-objects.
A schema-object's business unique key is its name, the name of the schema in
which it's located, and the name of the catalog table where it's listed.
(Elsewhere, I called this third fact the "namespace" because its the scope
within which the name must be unique. But you might not like this use of
"namespace".)
Anyway, "in" is used correctly when we say that a schema-object is in a schema.
A schema-object also has (must have) an owner. But this isn't part of its
business unique key.
In contrast, a constraint (and a trigger) are not in a schema. Nor do they have
an owner. Rather, each hangs off a schema object (and must do this if it's to
exist). The business unique key of each of a constraint and a trigger is its
own name together with the business unique key of what it hangs off. You might
argue that it inherits the schema and the owner of the schema-object off which
it hangs. But I think that this is the wrong way to think about it. That's why
I argue that "pg_constraint" should not have a "connamespace" column—just as it
already doesn't have a "conowner" column.
The fact that "pg_constraint" does have a "connamespace" column is an example
of what I believe the argot of our trade calls a "transitive dependency" (the
criterion which, added to the criteria for 2NF elevates it to 3NF). "\d pg
constraint" says that the table has a unique constraint on "(conrelid,
contypid, conname)". And (as I read the doc for "conrelid" and "contypid") is
does imply the rule that I observed: either one is non zero and the other is
zero or vice versa (and so not both zero and not both non-zero). So "(conrelid,
contypid)" (i.e. just part of the unique key) does indeed translate to the
unique identifier of the item (table or domain) that the constraint hangs off.
When I know the value of (conrelid, contypid), I know the oid of the schema
that this schema object is in. Ergo, "connamespace" depends upon the partial
key "(conrelid, contypid)"—i.e. this is a transitive dependency.
This takes me back to the query that I used as the background to pose my « why
does "pg_constraint" have a "connamespace" column? » question. Here it is—in an
extended form. And here it does indeed use "(conrelid, contypid)" to look up
the schema oid in the appropriate one of "pg_class" or "pg_type".
It helps to establish the query, for re-use, as a temporary view.
create temporary view constraint_facts(
conname,
schema_object_oid,
"catalog table",
"schema_oid from pg_class/pg_type",
same)
as
with
c1(conname, schema_object_oid, c_tab, connamespace) as (
select
conname,
case contypid
when 0 then conrelid
else contypid
end,
case contypid
when 0 then 'pg_class'
else 'pg_type'
end,
connamespace
from pg_constraint),
c2(conname, schema_object_oid, c_tab, schema_oid, connamespace) as (
select
conname,
schema_object_oid,
c_tab,
case c_tab
when 'pg_class' then (select relnamespace from pg_class where oid =
schema_object_oid)
when 'pg_type' then (select typnamespace from pg_type where oid =
schema_object_oid)
end,
connamespace
from c1)
select
conname,
schema_object_oid,
c_tab,
schema_oid,
(schema_oid = connamespace)
from c2;
Now use it to look at our two constraints called 'c':
select conname, schema_object_oid, "catalog table", "schema_oid from
pg_class/pg_type", same::text
from constraint_facts
where conname = 'c'
order by conname, schema_object_oid, "catalog table";
This is the result:
conname | schema_object_oid | catalog table | schema_oid from pg_class/pg_type
| same
---------+-------------------+---------------+----------------------------------+------
c | 2114297 | pg_class | 2114282
| true
c | 2114306 | pg_type | 2114282
| true
Now try it on all the entire contents of "pg_constraint"—one's own stuff
together with the hundred or so rows that implement the PG system:
select exists(select 1 from constraint_facts where not same)::text;
The answer is "false". Please tell me if you can see a typo in my query, a
fault in the reasoning the led to how I spelled it, or a flaw in my overall
analysis. I realize, of course that the "false" result here merely shows that
the hypothesis that the query tests is not disproved. I don't think that it can
be proved. Rather, its truth, or otherwise, comes from the prose statement of
the requirements and the design of the entire set of catalog tables—before any
programming was done.
But it does seem to me that the "false" outcome is consistent with the common
sense analysis that leads to the conclusion that "pg_constraint" is not in 3NF.
Two more things. First, the results from my "constrainst" view (that uses my
"schema_objects" view). I showed these in this turn in the present thread:
https://www.postgresql.org/message-id/470B384D-5F76-449B-AFC3-1AE36FF84C03%40yugabyte.com
select c_name, t_name, t_schema, t_namespace, t_kind, c_kind, c_expr from
a.constraints
where c_name = 'c'
order by t_name, t_schema, t_namespace;
This is the result:
c_name | t_name | t_schema | t_namespace | t_kind | c_kind
| c_expr
--------+-------------------+----------+-------------+----------------+--------+--------------------------
c | irrelevant_name_1 | s1 | relations | ordinary-table | c
| (v = lower(v))
c | irrelevant_name_2 | s1 | types | domain | c
| (cardinality(VALUE) > 1)
The first four columns are each constraint's business unique key. And the last
three are some of its properties or of what it hangs off.
And finally, a little test to confirm that the constraint kind is not part of a
constraint's business unique key. Make sure that schema 's1' is empty when you
try it.
create table s1.t(
k int,
v int,
constraint c primary key(k),
constraint c unique(v));
It fails because (as I'd say it) it attempts to create two constrains whose
"(c_name, t_name, t_schema, t_namespace)" values are the same—or, briefly, «
constraint "c" already exists ». In fact, the error message spells it with
"relation" and not with "constraint". I think that this is an error.
