Thread: pg_constraint
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, ???)
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.
pg_class.relchecks would change to mean relconstraints, though I wont
change the column name.
A view to pg_relcheck would be created.
End result? Foreign Keys will be tracked and restricted / cascaded
through via pg_depend code (see patches archive in April). This would
knock several items off the TODO list.
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.
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?
--
Rod
> 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. Hmmm...I don't see the need at all for NOT NULL constraint tracking. The spec doesn't seem to require it and we do not have names for them anyway. Even if they were given names, it'd be pointless, as there's only one per column. Primary keys and unique keys are SQL constraints - are you going to bother tracking them as well or leave them in the current format? Maybe you could do it with a view or something. Why not just create a pg_references table and leave pg_relcheck as is? Chris
> > 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. > > Hmmm...I don't see the need at all for NOT NULL constraint tracking. The > spec doesn't seem to require it and we do not have names for them anyway. > Even if they were given names, it'd be pointless, as there's only one per > column. Correct me if I'm wrong, but aren't NOT NULL constraints a shortform of the similar CHECK constraint (according to spec which I don't have infront of me). I've been debating combining the 2 and allowing names on them, but won't do this yet. CHECK (VALUE NOT NULL) would mark the pg_attribute column and assign the name. > Primary keys and unique keys are SQL constraints - are you going to bother > tracking them as well or leave them in the current format? Maybe you could > do it with a view or something. > Why not just create a pg_references table and leave pg_relcheck as is? relcheck needs changes anyway. It needs to track the specific columns that it depends on, rather than simply the table. This is for reasons of DROP COLUMN. Last thing you want is a bad check constraint after that ;) The other reason is that they're supposed to be in the same namespace (which makes sense) and having each constraint in its own table would be silly. Of note, the above table should also have immediate, and deferrable bools attached to it. I debated about the primary / unique keys, but indicies seem to do a good enough job with those.
"Rod Taylor" <rbt@zort.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
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Hmmm...I don't see the need at all for NOT NULL constraint tracking. The
> spec doesn't seem to require it and we do not have names for them anyway.
> Even if they were given names, it'd be pointless, as there's only one per
> column.
Hmm, you're probably right. Way back when, I was thinking of naming
them as a route to allowing DROP CONSTRAINT for them --- but given the
ALTER TABLE SET/DROP NOT NULL syntax that we have now, supporting DROP
CONSTRAINT is not really necessary. So I concur that not-null isn't a
feature that pg_constraint needs to deal with.
> Why not just create a pg_references table and leave pg_relcheck as is?
One reason is that that structure wouldn't guarantee that
check-constraint names are distinct from references/unique-constraint
names, which'd make life difficult for DROP CONSTRAINT.
regards, tom lane
> 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. The only problem with this is that I don't want the rename of a constraint to have to fall over into the pg_depend table. pg_depend is currently happy with system OIDS or a Relation OID and some unique number to represent it -- much as pg_description wouldn't want to know the name of the constraint for the ability to add a comment to it. > 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 will implement the various flags required for these. conupdtyp, condeltyp (on update type and on delete type respectively) as well as immediate and deferrable bools. > > 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. Thanks for the tip. > 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. Good point.
"Rod Taylor" <rbt@zort.ca> writes:
>> 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.
> The only problem with this is that I don't want the rename of a
> constraint to have to fall over into the pg_depend table. pg_depend
> is currently happy with system OIDS or a Relation OID and some unique
> number to represent it -- much as pg_description wouldn't want to know
> the name of the constraint for the ability to add a comment to it.
Good points, but I think those argue for assigning OIDs to constraints
after all. If that is what you want connum for then I have a *big*
problem with it: aren't you assuming that connum will be distinct from
any attribute number that the relation might have? What's going to
enforce that? Besides, the approach doesn't scale to allow other
kinds of objects associated with a relation (just try keeping attnum,
connum, foonum, and barnum from overlapping...).
I had once thought that we could avoid assigning OIDs to rules and
triggers, but learned differently as I got into the implementation.
I'm thinking that constraints will be the same kind of thing; it'll
be a lot easier if you give them OIDs.
regards, tom lane
> > The only problem with this is that I don't want the rename of a > > constraint to have to fall over into the pg_depend table. pg_depend > > is currently happy with system OIDS or a Relation OID and some unique > > number to represent it -- much as pg_description wouldn't want to know > > the name of the constraint for the ability to add a comment to it. > > Good points, but I think those argue for assigning OIDs to constraints > after all. If that is what you want connum for then I have a *big* Yes, OIDs are probably the right way to go. > problem with it: aren't you assuming that connum will be distinct from > any attribute number that the relation might have? What's going to As far as pg_depend goes, it doesn't care whether they overlap or not as it knows the source (class) relation is pg_constraint. Comment on stuff would need to be changed though. > I had once thought that we could avoid assigning OIDs to rules and > triggers, but learned differently as I got into the implementation. > I'm thinking that constraints will be the same kind of thing; it'll > be a lot easier if you give them OIDs. Sounds like a plan. I'll