Skip site navigation (1) Skip section navigation (2)

Constraint stuff

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Constraint stuff
Date: 2000-08-06 17:29:19
Message-ID: Pine.BSF.4.10.10008060953340.47908-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.



Responses

pgsql-hackers by date

Next:From: The Hermit HackerDate: 2000-08-06 18:10:16
Subject: Re: OK to remove operators for exp() and ln()
Previous:From: Thomas LockhartDate: 2000-08-06 05:38:06
Subject: Re: LIKE/ESCAPE implementation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group