Re: Two questions about "pg_constraint"
| От | Bryn Llewellyn |
|---|---|
| Тема | Re: Two questions about "pg_constraint" |
| Дата | |
| Msg-id | 470B384D-5F76-449B-AFC3-1AE36FF84C03@yugabyte.com обсуждение исходный текст |
| Ответ на | Re: Two questions about "pg_constraint" (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-general |
tgl@sss.pgh.pa.us wrote:bryn@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
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;
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;
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
----------+--------+----------------+--------+----------+-------------+----------------
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:
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;
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;
В списке pgsql-general по дате отправления: