Foreign Key work for 7.3+ - Mailing list pgsql-hackers

Here are the things I'd like to get feedback on doing to the foreign
key constraint triggers and support code. It's relatively high level
since I'm at the beginning stages of looking at the code that'd be
involved. This might therefore be a bit premature, but I figured I'd see
if anyone saw anything obviously wrong and there were a few places where
I'm uncertain what behavior is better or correct so I'm looking for
advice.

This isn't necessarily a list of things for 7.3 since I don't expect that
I'd be able to finish all of them unless 7.3's devel goes on like 7.2's.

* Rewrite foreign key triggers to remove dependencies on SPIUse the index and heap scanning functions to check the
table.Thisshould let us remove the ReferentialIntegritySnapshotOverride globaland give us more control over what the
triggersare doing by doingthe checks we need directly. My current test code duplicates a bunchof work (finding an index
toscan if possible, etc) that other areasof the code have to be doing, but that's mostly a matter of findingwhat I can
usefrom other sections. Hopefully this will also eventuallyallow us to move away from the current locking method
(althoughI haven'tlooked at it yet)
 

* Fix remaining visibility problemsThere are a couple of places where we have problems with this.For pk updates and
deleteswith no action we need to make surethat there wasn't a row updated or inserted with the old key valuesthat would
satisfythe constraint. For fk inserts/updates we needto make sure that we're not seeing an intermediate state of therow
(ieit was changed again before the check was done). Deferredcascades have a few possible broken cases (I'm not sure if
theseareactual errors or not, but they seem like it -- see questions below).Simply checking that the row we're checking
isstill valid on the fkinserts/updates causes some similar behavior.
 

* Switch over to oids and attnos for new constraintsMake the real functions for the constraints take oids and
attnos.We'llneed to either hack around when we see the create constrainttrigger to use the new form or keep functions
bythe old names/oids whichtake names, but make those effectively wrappers around the oid versions(look up the numbers
andpass those in to the new ones). In any casenew constraints will use the oids and attnos version.
 

* Make checking an entire table faster for Alter TableRight now alter table runs the insert/update trigger on the fk
tableonceper existing fk row on alter table. If we don't care about thepossible disk space usage, we can make this
fasterby going through bothtables in sorted key order (the disk space would be if we had to goto an explicit sort).
Theother option is to only do this form if wehad an index on each table we could use to scan in order.
 

* Change dumps to dump fk constraints as alter table statements by defaultMake dumps write out alter table statements
atthe end rather thancreate constraint triggers. We may wish to provide an option to allowthe older style create
constrainttrigger dumps for speed.
 

* Extend fk constraints to work with inheritanceMake fk constraints inherit properly with both fk and pk base
tables.Thiswill probably mean making the appropriate triggers on the childtables involved as well as scanning the
additionaltables when checks andchanges are needed. For right now, I'd say we'd want to require that thechild tables at
leastalso have unique constraints across the key.
 

* Fixes on unique index checks for fk constraint checkingRight now I believe unique partial and functional indexes are
consideredsufficientto be targets of an fk constraint.  I think this is usuallywrong for partial indexes and that this
maybe wrong for functionalindexes. Any thoughts?
 

* Make a central place for storing fk constraint info?Keep track of fk constraint info in a centralized place keeping a
moreuseableform of the tables and columns involved as well as the misc. data(match type, deferrability).  We'd probably
alsowant to keep referencesto the triggers involved in the constraint. This would make some of thethings (dumping fk
constraintsas alter table, inheritance) easier Ibelieve since we wouldn't have to match up constraint triggers
todeterminethe details of the constraint, but it would probably require ahack for checking create constraint trigger
statementsto see if they'refk constraints.
 

* Match partialMatch partial is rather complicated and could be even more so dependingon the answers to the behavioral
questionsin the notes below, but it'dbe good to do it if we can.
 

Behavioral questions:
* Is this current behavior correct or should fk contain a row with thekey value 1 at the end of the sequence?
create table pk(a int unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'pk_a_key' for
table

'pk'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'pk_a_key' for
table

'pk'
CREATE

create table fk(a int references pk(a) on delete cascade initially
deferred);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE

insert into pk values (1);
INSERT 16645 1

begin;
BEGIN
delete from pk where a=1;
DELETE 1
insert into pk values (1);
INSERT 16646 1
insert into fk values (1);
INSERT 16647 1

end;
COMMIT

select * from pk;a
---1
(1 row)

select * from fk;a
---
(0 rows)

* Using the tables from above.  Should the following have no rows in fkor a single row containing 2?
delete from fk;
DELETE 1

delete from pk;
DELETE 1

insert into pk values (1);
INSERT 16668 1

insert into fk values (1);
INSERT 16669 1

begin;
BEGIN
delete from pk where a=1;
DELETE 1
insert into pk values (2);
INSERT 16670 1
update fk set a=2;
UPDATE 1

end;
COMMIT

select * from pk;a
---2
(1 row)

select * from fk;a
---2
(1 row)