Re: Two questions about "pg_constraint" - Mailing list pgsql-general

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:

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.

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:

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:

This was my result. (Your oid values will vary.)

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.

Now use it to look at our two constraints called 'c':

This is the result:

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:

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:

This is the result:

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.