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


Thu, 25 Apr 2002 22:30:45 -0400
"Rod Taylor" <rbt@zort.ca>
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.

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


Fri, 26 Apr 2002 15:34:12 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: 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.
>
> 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.





Fri, 26 Apr 2002 07:49:42 -0400
"Rod Taylor" <rbt@zort.ca>
Re: pg_constraint
"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

Fri, 26 Apr 2002 10:25:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_constraint 
"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

Fri, 26 Apr 2002 10:50:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_constraint 
> 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.


Fri, 26 Apr 2002 10:58:53 -0400
"Rod Taylor" <rbt@zort.ca>
Re: pg_constraint 
"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

Fri, 26 Apr 2002 11:44:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_constraint 
> > 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



Fri, 26 Apr 2002 13:28:53 -0400
"Rod Taylor" <rbt@zort.ca>
Re: pg_constraint 