xof@xxxxxxxxxxxx wrote: 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: conname | connamespace | n ---------+--------------+------ c | 2127201 | 2000 tgname | n --------+------ t | 1000 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.) -- Boring code to guarantee a fresh start and error-free re-runs. \c postgres postgres set client_min_messages = warning; drop database if exists bryn; create database bryn owner postgres; revoke all on database bryn from public; \c bryn postgres set client_min_messages = warning; do $body$ begin begin drop owned by u1 cascade; exception when undefined_object then null; end; drop schema public; create schema s1 authorization postgres; revoke usage on schema s1 from public; drop role if exists u1; create role u1 login password 'p'; grant connect on database bryn to u1; grant create on database bryn to u1; grant temporary on database bryn to u1; grant usage on schema s1 to u1; grant create on schema s1 to u1; alter user u1 set search_path = pg_catalog, pg_temp; end; $body$; -------------------------------------------------------------------------------- \c bryn u1 /* The interesting code starts here. It simply creates lots of tables and domains, all in the single schema 's1'. Each table has a trigger called 't'. And both each table and each domain has a constraint called 'c'. */; create function s1.f() returns trigger security definer language plpgsql as $body$ begin return old; end; $body$; do $body$ declare no_of_iterations constant int not null := 1000; cr_table constant text not null := ' create table s1.%I( k int primary key, v text, constraint c check(v = lower(v)))'; cr_trigger constant text not null := ' create trigger t after delete on s1.%I for each statement execute function s1.f()'; cr_domain constant text not null := ' create domain s1.%I as int[] constraint c check(cardinality(value) > 1)'; begin for j in 1..no_of_iterations loop execute format(cr_table, 'a'||j::text); execute format(cr_trigger, 'a'||j::text); execute format(cr_domain, 'b'||j::text); end loop; end; $body$; create temporary view constraints as select conname, conrelid, contypid, connamespace from pg_constraint where conname = 'c'; create temporary view triggers as select tgname, tgrelid from pg_trigger where tgname = 't'; select conname, connamespace, count(*) as n from constraints group by conname, connamespace; select tgname, count(*) as n from triggers group by tgname; |
