Re: Two questions about "pg_constraint"

Thanks for your interest in this, Christophe. I’ll start again from scratch. I’m copying David because he said something relevant to the present discussion in an earlier separate thread.

But, first, a caveat. I write, here, as if I'm 100% confident of the correctness of everything I say. I've found that this is an effective way to provoke correction when I'm wrong. And I look forward to correction from you (all) here.

I’ll be as brief as I can. But I’m afraid that I cannot condense the problem statement beyond my points #1 and #2 below. And then I need a preamble before the problem statement and a discussion after it.

Notice that I don't think that there's any wrong behavior here. Rather, there's just that annoying transitive dependency and, worse for me, what appears to be some wrong ways to talk about stuff from the user's PoV where only business unique keys matter and the surrogate oid values vanish from the picture.

For example, Christophe used the phrase "a constraint is always in the same schema as..." (and didn't mention domains). And David said "the trigger owner is the context in which the trigger function is executed". Neither a constraint nor a trigger is "in" a schema or "has" an owner. In both cases, these are derived (a.k.a. transitive) properties.

Here, now, is my problem statement:

«

1. The "pg_constraint" column, "connamespace", is a transitive dependency. Therefore, tautologically, it need not be there. (So I’m not convinced by what Tom said here.) Given that "pg_constraint" does have its "connamespace" column, it may just as well have a "conowner"column as a second transitive dependency. On the other hand, "pg_trigger" has neither a "tgnamespace" column nor a "tgowner" column. But, if you (all) think that transitive dependencies are a nice usability feature, and you (all) can guarantee that the derived values are always in step with their source, then I suppose that no harm is done—beyond possibly leading the novice to assume a faulty mental. Whatever is decided, the documentation should make it clear.

2. The larger point is that I want to draw a distinction between a (primary) schema object, like a table, a domain, or a function and a secondary object like a constraint or a trigger. The business unique key of a schema object is its schema-qualified name together with the catalog table ("pg_class", "pg_type", "pg_proc",...) that defines the uniqueness scope for its name. A schema object also has an owner—but its name is not part of its business unique key. In contrast, the business unique key of a secondary object is its own name together with the business unique key of the object off which it hangs. And a secondary object cannot exist without hanging off a schema object.

»

I have to use more words now to illustrate, and dramatize, my point. First, run my demo. Just copy-and-paste the code at the end and paste it into a ".sql" script. Then execute it in psql. You can run it time and again. It finishes by producing this output:

So I've created 2K constraints, all called 'c', and all "in" (as Christophe and Adrian would have it) the same schema. And I've created 1K triggers, all called 't', and all "having" (as David would have it) the same owner.

Look at the definitions of the temporary views whose results sets I showed above. Each constraint has a unique value for the tuple "(conname, conrelid, contypid)"—as it must have because my "constrains" view selects from just the single table "pg_constraint" and this places a uniqueness requirement on this tuple. It's only to be expected that I can have any number of constraints, all with the same "conname" and "connamespace" because the definition of "pg_constraint" places no uniqueness requirement on either of these, neither separately nor jointly.

Similarly, each trigger has a unique value for the tuple "(tgname, tgrelid)"—as it must have because my "triggers" view selects from just the single table "pg_trigger" and this places a uniqueness requirement on this tuple. It's only to be expected that I can have any number of constraints, all with the same "tgname" because the definition of "pg_trigger" places no uniqueness requirement on this.

With a bit more typing, I could materialize both a schema and an owner for each constraint and each trigger. (I showed the SQL for doing this for a constraint earlier in this thread. And I've done it for myself for triggers.) The situation is a little harder for constraints than for triggers because a constraint can hang off either a table or a domain while a DML trigger can hang off just a table. This leads to the arc-FK (implied, if not declared) from "pg_constraint" either to "pg_class" or to "pg_type". The documentation for "pg_constraint" on the topic:

«
conrelid (oid references pg_class.oid): The table this constraint is on; 0 if not a table constraint
contypid (oid references pg_type.oid): The domain this constraint is on; 0 if not a domain constraint
»

together with the requirement that "(conname, conrelid, contypid)" is unique (and some empirical testing) makes it clear. Thanks, Tom, for asking me to think about this. It implies more elaborate SQL to get the facts for the schema object that a constraint hangs off than it does to get those facts for a trigger. But it's not (as Tom claimed it would be) daunting. I expect that my SQL could be improved. But it wasn't hard to write what I did.

Anyway... the structure of the "pg_constraint" and "pg_trigger" tables, their unique indexes, and where their implied oid-FKs point is fully consistent with my mental model, thus:

- A schema object is uniquely identified by its qualified name and its namespace ('relations', types', 'subprograms',...).

- A constraint (as a secondary object) is uniquely identified by its own name and the identity of the schema object off which it hangs

- A trigger, too, (as a secondary object) is also uniquely identified by its own name and the identity of the schema object off which it hangs

- Because a schema object lives in a schema and has an owner, you might like to think that a constraint, or a trigger, each lives in a schema and has an owner. But I think that it's both unnecessary and sometimes unhelpful to elide the transitive step here. (This elision led Adrian to say that a constraint name must be unique within a schema. And it led David to say that the owner of a trigger plays a part in determining the "current_role" that a "security invoker" trigger function sees when the trigger fires.)