
I think my last message to Tom (and the list)
about the foreign key stuff and oids ended up 
in /dev/null due to a problem on the local 
mailer.

Tom had suggested storing a more 
understandable form of the foreign key constraint
to make dumping more reasonable in its own table.
I'd guess like the src stored for check constraints.
However, I noticed a few problems with this and
while thinking about it I had a few germs of
ideas which aren't any kind of proposal yet, but
I thought someone might be interested in them.

The problem with storing source is that it doesn't
get changed when things change.  Try altering
a column name that has a check constraint, then
dump the database.  I don't think this is the
responsibility of the dumper. If we store source
we should be guaranteeing it's correct.  
Plus, right now for FK constraints we do something
specific to keep track of the other table referenced
so we can remove the constraints if the table goes 
away.  But, what happens when we allow subqueries
in check constraints, etc...

So, what I was thinking is, that if we have another
table to store this kind of constraint info, it
should probably store information for all constraints.
I was thinking two tables, one (say pg_constraint)
which stores basic information about the constraint
(what type, the constraint name, primarily constraintd
table, maybe owner if constraints have owners in SQL)
and a source form (see more below).
The second table stores references from this constraint.
So any table, column, index, etc is stored here.
Probably something of the form constraintoid, 
type of thing being referenced (the oid of the table?),
the oid of the referenced thing and a number.

The number comes in to the source form thats stored.
Anywhere that we're referencing something that a name
is insufficient for (like a column name or table name)
we put something into the source for that says 
referncing column n of the referenced thing m.

Then we create something like 
format_constraint(constraintoid) which gives out
an SQL compliant version of the cconstraint.

And it means that if we deleted something, we know fairly 
easily whether or not it is being referenced by some
constraint somewhere without writing separate code for
fk constraints and check constraints, etc.. And
renaming wouldn't be a problem.

- There are some problems I see right off both conceptually
and implementation, but I thought someone might be able 
to come up with a better idea once it was presented (even 
if it's just a "not worth the effort" :) )

One of the problems I see is that if taken to its end,
would you store function oids here?  If so, that might
make it harder to allow a drop function/create function
to ever work transparently in the future.
Plus, I'm not even really sure if it would be reasonable
to get a source form like I was thinking of for check
constraints really.



Sun, 6 Aug 2000 10:29:19 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Constraint stuff
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Tom had suggested storing a more 
> understandable form of the foreign key constraint
> to make dumping more reasonable in its own table.
> I'd guess like the src stored for check constraints.

I wasn't actually thinking of storing source, but rather precompiled
expressions (as I remarked awhile ago, I think pg_relcheck's rcsrc
column is dead weight; we could and should generate the value on demand
by reverse-listing rcbin instead).  This gets you away from
rename-induced problems since everything is table OIDs, attribute column
numbers, operator and function OIDs, etc.

However, digging those references out of the expression tree is a little
bit painful; you're right that we shouldn't expect applications to do
that for themselves.  We could store an additional list of referenced
items.  We wouldn't necessarily have to store that explicitly either,
though --- functions to say "is this OID referenced in this stored
expression" or perhaps "give me an array of all function OIDs in this
expression" would get the job done AFAICS.

> One of the problems I see is that if taken to its end,
> would you store function oids here?  If so, that might
> make it harder to allow a drop function/create function
> to ever work transparently in the future.

I don't think we should worry about that.  What's actually needed IMHO
is an "ALTER FUNCTION" command that allows you to replace the body of
an existing function, and perhaps change its name, but NOT its type
signature (result type and number/types of arguments).  Changing the
signature is inherently not a transparent operation because it'd
invalidate stored expressions that use the function.  ALTER would let
you make safe changes to a function without changing its OID and thus
without invalidating references-by-OID.

			regards, tom lane

Mon, 07 Aug 2000 12:32:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraint stuff 
Tom Lane wrote:
> 
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Tom had suggested storing a more
> > understandable form of the foreign key constraint
> > to make dumping more reasonable in its own table.
> > I'd guess like the src stored for check constraints.

...

> I don't think we should worry about that.  What's actually needed IMHO
> is an "ALTER FUNCTION" command that allows you to replace the body of
> an existing function, and perhaps change its name, but NOT its type
> signature (result type and number/types of arguments). 

IIRC Oracle allows the syntax CREATE OR REPLACE in many places, for 
example for changing VIEWS and PROCEDURES without affecting the things 
dependent on them.

CREATE OR REPLACE works also for not-yet-existing function which ALTER 
probably would not.

> Changing the
> signature is inherently not a transparent operation because it'd
> invalidate stored expressions that use the function.  ALTER would let
> you make safe changes to a function without changing its OID and thus
> without invalidating references-by-OID.
> 

----------
Hannu

Mon, 07 Aug 2000 20:23:44 +0300
Hannu Krosing <hannu@tm.ee>
Re: Constraint stuff
On Mon, 7 Aug 2000, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > Tom had suggested storing a more 
> > understandable form of the foreign key constraint
> > to make dumping more reasonable in its own table.
> > I'd guess like the src stored for check constraints.
> 
> I wasn't actually thinking of storing source, but rather precompiled
> expressions (as I remarked awhile ago, I think pg_relcheck's rcsrc

I guess you could store the fk_constraint node that is generated for fk
constraints, but that's not really an expression... I think I must
be missing something, because I can't quite see what the precompiled
expression for an fk constraint would be...

> However, digging those references out of the expression tree is a little
> bit painful; you're right that we shouldn't expect applications to do
> that for themselves.  We could store an additional list of referenced
> items.  We wouldn't necessarily have to store that explicitly either,
> though --- functions to say "is this OID referenced in this stored
> expression" or perhaps "give me an array of all function OIDs in this
> expression" would get the job done AFAICS.

The reason I was thinking of storing things was also so you could do
things like: is this oid stored in any constraint.  For example,
I'm removing a column, is there any constraint that references this
column, etc, rather than having to code stuff for all of the special
cases in all places that might need it.


Mon, 7 Aug 2000 11:13:08 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Constraint stuff 
At 08:23 PM 8/7/00 +0300, Hannu Krosing wrote:

>IIRC Oracle allows the syntax CREATE OR REPLACE in many places

Yes, Oracle does allow this.  




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Mon, 07 Aug 2000 11:33:18 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Constraint stuff