Re: Imperfect solutions - Mailing list pgsql-hackers

On Thu, 31 May 2001, Tom Lane wrote:

> Indeed.  You're looking at the aftermath of an "imperfect fix" to add
> foreign keys.  With all due respect to Jan and Stephan, who did a great
> job adding the feature at all, there are still a lot of things that need
> to be fixed in that area.  The trouble with imperfect fixes is that they

Ugh yes.  Actually all of the constraints seem to have this problem to
some degree.  Unique doesn't quite work right for updates where rows 
may "temporarily" be of the same value, check constraints using user
functions can be violated if those functions do sql statements and column
renames cause dump/restore to fail.  Fk has at least the following 
(in no order and probably incomplete due to just waking up):

Temp tables can shadow pk/fk tables- If we have schemas and temp tables are in their own, we can probably   fix this
justwith fully qualifying.- Otherwise, we'd probably want to refer to the table by oid, but that   would require having
someway to do that in SPI or to replace the   SPI calls.  (Getting the name from the oid isn't sufficient,
obviously)

Inheritance- Plenty of discussion about this already- An additional wrinkle comes in if we allow/are going to allow
users  to rename base table columns in inherited tables.
 

Alter Table Rename- Either we need to store oids or follow name changes.  I'd actually   prefer the latter if possible,
butthat requires a dependency system.   (Especially if we were to go with only storing the text of check
constraints.)

General- For update locks are too strong?  Do we need a self conflicting lock   on the pk table rows?  Is there some
generallybetter way to handle   this?  How does this tie into the problem Jan noted before?- We probably need a way to
checkthe entire table at once rather than   per row checks.  This would make alter table more reasonable for
dump/restore(right now on large tables it would try each row's   check separately - ugh)- Deferred constraints are
brokenin a few cases.  Update/insert trigger  on fk needs to make sure the row is still there at check time, no
actiontrigger needs to make sure there hasn't been another row with  the key values inserted.  Other actions are
questionable,has anyone  actually figured out what the correct behavior is?  I think that  running actual actions
immediatelymay be the correct thing, but in  any case, they'd probably need checks like the no action trigger  (what
happensif the delete/insert is done within one statement  due to triggers or whatever)- Match partial - Complicated.
Todo this completely means almost   a separate implementation since stuff like the above checks wouldn't   work in this
caseand means that we have to recognize things where   the user has updated two pk rows referenced by a single fk row
to  distinct key values, since that's an error condition.
 

Storage/Reporting- We really need something that stores the fk information better than   what we have (we may want to
seeif we can generalize more constraints   into the system as well, but we'd have to see)- We'll want to make
dump/restoresshow the constraint in a better   fashion.  This may need the above, and we'd still need to have
backwardcompatibility (one of the reasons switching to storing   oids would be interesting)