Re: pg_constraint

"Rod Taylor" <rbt(at)zort(dot)ca> writes:
> For tracking of Foreign Keys, Check constraints, and maybe NULL / NOT
> NULL (specific type of check constraint) I intend to create (as per
> suggestion) pg_constraint.

> conrelid
> conname
> contype ('c'heck, 'f'oreign key, ???)

'u'unique, 'p'rimary key, 'n'ot null seem to cover it

> conkey (int2vector of columns of relid, like pg_index.indkey)
> connum int4 -- unique identifying constraint number for the relation
> id.
> consrc
> conbin

> Dependencies would be on conrelid, and connum in pg_depend. If each
> constraint has a unique number for the relation OIDs aren't required
> here. Much like pg_attribute.

Could we instead insist on a unique name per-table, and make this table's
key be (conrelid, conname)? Assigning a number seems quite artificial.

consrc/conbin seem to only cover the check-constraint case. Need some
thought about what to store for foreign keys (ideally, enough info for
pg_dump to reconstruct the REFERENCES spec without looking at the
triggers) and unique/primary keys (a link to the implementing index
seems like a good idea here).

> I'm not exactly sure how to find out what columns a check constraint
> depends on, but I'm sure I'll figure that out sooner or later.

pull_var_clause() on the nodetree representation is your friend.
I see a difficulty in the above representation though: what if a check
constraint refers to > INDEX_MAX_KEY columns? Maybe conkey had better
be an int2[] variable-length array.

> Any thoughts or suggestions? Is there any reason to allow a check in
> a namespace other than the relation it's tied to? Spec seems to allow
> that, but is it actually useful?

For constraints tied to tables, namespaces are irrelevant.

There is something in the spec about stand-alone assertions that can
specify cross-table constraints, but I think that's a task for some
future year.

regards, tom lane