> t...@sss.pgh.pa.us wrote:
>
>> b...@yugabyte.com writes:
>>
>> Question 1: why does "pg_constraint" have a "connamespace" column?
>
> You appear to be assuming that every pg_constraint entry is tied to a table.
> This isn't so.
I see how this might have appeared to be the case. But I did, very much, know
about the possibility to define a constraint on a domain. (It's a very valuable
feature and I've used it a lot.)
> (1) That catalog also carries check constraints for domains, which are tied
> to types instead. Yeah, you could imagine some rule like "look in either
> pg_class or pg_type to find the schema", but it'd be really painful.
Yes, I knew that too. The documentation of the catalog tables is terse to a
fault—and I, at least, have to do empirical tests to try to discern the proper
mental model. I'm probably misunderstanding some things. Having said this, the
results from these query seem to be informative. I did it in a "play" database
where I'd created both a table with a column check constraint and a domain with
a value constraint.
select count(*) from pg_constraint where (conrelid <> 0 and contypid = 0);
-->> 107 rows
select count(*) from pg_constraint where (conrelid = 0 and contypid <> 0);
-->> 3 rows
select exists(
select 1 from pg_constraint
where not (
(conrelid = 0 and contypid <> 0) or
(conrelid <> 0 and contypid = 0)
)
)::text as "conrelid and contypid both non-zero"; -->> false
My hypothesis (of which I've been unable to find a statement in the doc) is
that this shows a reliable invariant. In other words, it seems that a
constraint has an arc-FK relationship to its parent EITHER in "pg_class" OR in
"pg_type". I think that you imply this with your "look in either pg_class or
pg_type to find the schema". However, I don't feel this lookup to be painful. I
anyway want a view that lists all the schema objects that implement my database
app.
I've copied my definition of my "schema_objects" view at the end. It unions
these (aliased) columns:
oid, name, schema_oid, owner_oid, kind
from "pg_class", "pg_type", and "pg_proc". (These, so far, are sufficient for
my present purpose.) It also adds the column "namespace" to materialize which
catalog table is the source:
pg_class → 'relations', pg_type → 'types', and pg_proc → 'subprograms'
Then it joins this union with "pg_namespace" and "pg_roles" to get the
human-readable names for these.
The upshot is that my "schema_objects" view has a surrogate PK, "oid", and the
unique business key "(name, schema, namespace)".
I don't know what better name to use for my "namespace" column. It's taken up
by the name of the view for schemas (which should, in my book, be called
"pg_schema"—in the singular or plural according to the usual coin-toss. My name
captures the meaning and accords with the fact that I can have an object whose
"name" is 'x' three times in "schema" called 's1' distinguished from each other
by the fact that each has a different "namespace" thus: 'relations', 'types'
and 'subprograms'.
I'm confused by these outcomes:
- when you create a composite type manually, you automatically get an entry
with its name and schema BOTH in "pg_class" AND in "pg_type".
- when you create a table or a view manually, you again automatically get an
entry with its name and schema BOTH in "pg_class" AND in "pg_type".
Where can I read about the mental model for this?
Anyway, with my "schema objects" in place, I can create a "constrants" view
thus:
create view a.constraints(c_name, c_kind, c_expr, t_name, t_schema,
t_namespace, t_kind) as
with c(conname, contype, expr, sch_obj_id) as (
select
conname,
contype,
pg_get_expr(conbin, conrelid),
case contypid
when 0 then conrelid
else contypid
end
from pg_constraint)
select
c.conname,
c.contype,
c.expr,
s.name,
s.schema,
s.namespace,
s.kind
from
a.schema_objects s
inner join
c
on sch_obj_id = s.oid;
Then this query:
select c_name, c_kind, c_expr, t_name, t_schema, t_namespace, t_kind from
a.constraints
where t_schema = any(array['s1', 's2', 's3'])
order by c_name, t_name, t_schema, t_namespace;
shows this result:
c_name | c_kind | c_expr | t_name | t_schema | t_namespace |
t_kind
----------+--------+----------------+--------+----------+-------------+----------------
d_chk | c | s2.fnc(VALUE) | dom | s3 | types | domain
itm_pkey | p | | itm | s1 | relations |
ordinary-table
t_chk | c | (v = lower(v)) | tab | s1 | relations |
ordinary-table
tab_pkey | p | | tab | s1 | relations |
ordinary-table
for the test objects that I created. So (from the user P.o.V.) a constraint is
uniquely identified by its name, its kind, and the identity of the schema
object that it hangs off. This seems to me to be just what I want.
> (2) The SQL standard describes "assertions", which are global check
> constraints that can affect multiple tables. We don't support those, and very
> possibly never will, but the pg_constraint catalog is set up to support them.
> Presumably they'd be stored with conrelid and contypid both zero, so there
> would be no other place to find out the assertion's schema.
Well yes, I wish that PG did support SQL assertions. I expect that you know
that there's a vote to see how popular he idea would be for Oracle Database:
https://community.oracle.com/tech/apps-infra/discussion/4390732/sql-assertions-declarative-multi-row-constraints
But I've no idea what might come of that.
> I'm not entirely convinced that putting these two (or three) sorts of objects
> in the same catalog was a great design. However, that's what we've got and
> changing it seems like more trouble than it'd be worth.
I don't know what you mean by "in that same catalog". I thought that "catalog"
was the prose name for all those tables in the "pg_catalog" schema of which
we've focused on a few here: "pg_class", "pg_type", "pg_proc", "pg_namespace",
"pg_roles", and "pg_constraint".
It would seem to me that a SQL assertion must be a (first class) schema object
(i.e. with, as the user would see it, the unique business key "(name, schema,
namespace)" and with its very own "owner"—and then all sorts of other
properties.
I can't see how it could belong in "pg_constraint" because it doesn't "hang
off" a single schema object (like existing kinds of constraint do) but, rather,
spans any number of tables which (I'd hope) could have various owners. I'd
assume there be a new "assertion on" privilege that would have to be granted to
the owner of the to-be-created assertion on each of the tables that the new
assertion would span.
So, to follow the pattern, there should be a new dedicated "pg_assertion"
table. This would have no impact on what the existing "pg_constraint" would
record because you couldn't have a constraint on an assertion.
--------------------------------------------------------------------------------
create view a.schema_objects(oid, name, schema, namespace, owner, kind) as
with o(oid, name, schema_oid, namespace, owner_oid, kind) as
(
select
oid,
relname,
relnamespace,
'relations',
relowner,
case relkind
when 'r' then 'ordinary-table'
when 'v' then 'view'
when 'i' then 'index'
when 'S' then 'sequence'
when 'c' then 'composite-type'
else 'other'
end
from pg_class
union all
select
oid,
typname,
typnamespace,
'types',
typowner,
case typtype
when 'b' then 'base type'
when 'c' then 'composite-type'
when 'd' then 'domain'
when 'e' then 'enum'
else 'other'
end
from pg_type
-- 'b' (here) is automtically gerenerated 'base type'
-- has no info. value for the present use-case
where typtype <> 'b'
union all
select
oid,
proname,
pronamespace,
'subprograms',
proowner,
case prokind
when 'f' then 'function'
when 'p' then 'procedure'
else 'other'
end
from pg_proc
)
select
o.oid,
o.name,
s.nspname,
namespace,
r.rolname,
o.kind
from
o
inner join
pg_namespace s
on o.schema_oid = s.oid
inner join pg_roles r
on o.owner_oid = r.oid;
