Search Postgresql Archives

Re: Two questions about "pg_constraint"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



tgl@xxxxxxxxxxxxx wrote:

bryn@xxxxxxxxxxxx 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:


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;


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux