Re: Imperfect solutions

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 just with fully qualifying.
- Otherwise, we'd probably want to refer to the table by oid, but that
would require having some way 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, but that 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 generally better way to handle
this? How does this tie into the problem Jan noted before?
- We probably need a way to check the 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 broken in a few cases. Update/insert trigger
on fk needs to make sure the row is still there at check time, no
action trigger 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 immediately may be the correct thing, but in
any case, they'd probably need checks like the no action trigger
(what happens if the delete/insert is done within one statement
due to triggers or whatever)
- Match partial - Complicated. To do this completely means almost
a separate implementation since stuff like the above checks wouldn't
work in this case and 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 see if we can generalize more constraints
into the system as well, but we'd have to see)
- We'll want to make dump/restores show the constraint in a better
fashion. This may need the above, and we'd still need to have
backward compatibility (one of the reasons switching to storing
oids would be interesting)