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 SPI
 Use the index and heap scanning functions to check the table. This
 should let us remove the ReferentialIntegritySnapshotOverride global
 and give us more control over what the triggers are doing by doing
 the checks we need directly. My current test code duplicates a bunch
 of work (finding an index to scan if possible, etc) that other areas
 of the code have to be doing, but that's mostly a matter of finding
 what I can use from other sections. Hopefully this will also eventually
 allow us to move away from the current locking method (although I haven't
 looked at it yet)

* Fix remaining visibility problems
 There are a couple of places where we have problems with this.
 For pk updates and deletes with no action we need to make sure
 that there wasn't a row updated or inserted with the old key values
 that would satisfy the constraint. For fk inserts/updates we need
 to make sure that we're not seeing an intermediate state of the
 row (ie it was changed again before the check was done). Deferred
 cascades have a few possible broken cases (I'm not sure if these are
 actual errors or not, but they seem like it -- see questions below).
 Simply checking that the row we're checking is still valid on the fk
 inserts/updates causes some similar behavior.

* Switch over to oids and attnos for new constraints
 Make the real functions for the constraints take oids and attnos.
 We'll need to either hack around when we see the create constraint
 trigger to use the new form or keep functions by the old names/oids which
 take names, but make those effectively wrappers around the oid versions
 (look up the numbers and pass those in to the new ones). In any case
 new constraints will use the oids and attnos version.

* Make checking an entire table faster for Alter Table
 Right now alter table runs the insert/update trigger on the fk table
 once per existing fk row on alter table. If we don't care about the
 possible disk space usage, we can make this faster by going through both
 tables in sorted key order (the disk space would be if we had to go
 to an explicit sort).  The other option is to only do this form if we
 had an index on each table we could use to scan in order.

* Change dumps to dump fk constraints as alter table statements by default
 Make dumps write out alter table statements at the end rather than
 create constraint triggers. We may wish to provide an option to allow
 the older style create constraint trigger dumps for speed.

* Extend fk constraints to work with inheritance
 Make fk constraints inherit properly with both fk and pk base tables.
 This will probably mean making the appropriate triggers on the child
 tables involved as well as scanning the additional tables when checks and
 changes are needed. For right now, I'd say we'd want to require that the
 child tables at least also have unique constraints across the key.

* Fixes on unique index checks for fk constraint checking
 Right now I believe unique partial and functional indexes are considered
 sufficient to be targets of an fk constraint.  I think this is usually
 wrong for partial indexes and that this may be wrong for functional
 indexes. Any thoughts?

* Make a central place for storing fk constraint info?
 Keep track of fk constraint info in a centralized place keeping a more
 useable form of the tables and columns involved as well as the misc. data
 (match type, deferrability).  We'd probably also want to keep references
 to the triggers involved in the constraint. This would make some of the
 things (dumping fk constraints as alter table, inheritance) easier I
 believe since we wouldn't have to match up constraint triggers to
 determine the details of the constraint, but it would probably require a
 hack for checking create constraint trigger statements to see if they're
 fk constraints.

* Match partial
 Match partial is rather complicated and could be even more so depending
 on the answers to the behavioral questions in the notes below, but it'd
 be good to do it if we can.

Behavioral questions:
* Is this current behavior correct or should fk contain a row with the
 key 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 fk
 or 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)



Wed, 20 Feb 2002 09:28:03 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Foreign Key work for 7.3+
On Wed, 2002-02-20 at 17:28, Stephan Szabo wrote:
> Here are the things I'd like to get feedback on doing to the foreign
> key constraint triggers and support code. ...
> 
> * Extend fk constraints to work with inheritance
>  Make fk constraints inherit properly with both fk and pk base tables.
>  This will probably mean making the appropriate triggers on the child
>  tables involved as well as scanning the additional tables when checks and
>  changes are needed. For right now, I'd say we'd want to require that the
>  child tables at least also have unique constraints across the key.

This means that we need a solution for unique indexes and primary keys
under inheritance.  I wrote about this in a mail which is preserved in
doc/TODO.detail/inheritance.

    3. Inheritance of a table implies inheriting all its constraints
    unless ONLY is used or the constraints are subsequently dropped;
    again, dropping operates through all descendant tables.  A primary
    key, foreign key or unique constraint cannot be dropped or modified
    for a descendant.  A unique index on a column is shared by all
    tables below the table for which it is declared.  It cannot be
    dropped for any descendant.
    
    In other words, only NOT NULL and CHECK constraints can be dropped
    in descendants.

[On reconsidering this, I'm not sure it is right to let these be
modified either.]

    In multiple inheritance, a column may inherit multiple unique
    indices from its several ancestors.  All inherited constraints must
    be satisfied together (though check constraints may be dropped).
    
    4. RI to a table implies the inclusion of all its descendants in the
    check.  Since a referenced column may be uniquely indexed further up
    the hierarchy than in the table named, the check must ensure that
    the referenced value occurs in the right segment of the hierarchy. 
    RI to one particular level of the hierarchy, excluding descendants,
    requires the use of ONLY in the constraint.

So an index must somehow be made to serve more than one table, and then
an index lookup must also discover whether the key is in the right
segment of the inheritance hierarchy:


          table_a (id INTEGER PRIMARY KEY, ...)
          table_b (..., bfld CHAR UNIQUE) INHERITS (table_a)
          table_c1 (...) INHERITS (table_b)
          table_c2 (...) INHERITS (table_b)
          table_d (...) INHERITS (table_c1, table_c2)

          table_x (a INTEGER REFERENCES table_a(id))  -- looks at whole
                                                      -- hierarchy
          table_y (a INTEGER REFERENCES table_b(id),  -- looks at whole
                                                      -- hierarchy from
                                                      -- b down,
                                                      -- excluding a
                   b CHAR REFERENCES table_b(bfld),   -- looks at whole
                                                      -- hierarchy from
                                                      -- b down for bfld
                   c CHAR REFERENCES ONLY table_b(bfld) -- excludes b's
                   )                                  -- descendants
          table_z (a INTEGER REFERENCES table_c2(id)) -- looks at c2 and
                                                      -- d

Perhaps the answer to this is to create a separate key table for each
unique field, which would act as an indirect index:

      table_a_keys_id (id INTEGER PRIMARY KEY,
                             table  OID REFERENCES pg_class(oid)
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE)
      table_b_keys_bfld (id CHAR PRIMARY KEY,
                             table  OID REFERENCES pg_class(oid)
                                        ON UPDATE CASCADE
                                        ON DELETE CASCADE)

So the key table would record which table contained each key, and it
would need triggers to update it whenever any rows in the hierarchy
changed.  There would not be a unique index on any of the tables
directly, but instead there would be unique indexes on the keys tables.

On the creation of the first descendant table in a hierarchy, the key
tables would be created and filled with any existing keys from the
ancestor table; the existing unique indexes of the ancestor would be
dropped. On dropping the last descendant in a hierarchy, the reverse
would have to be done.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "For the Lord himself shall descend from heaven with a 
      shout, with the voice of the archangel, and with the 
      trump of God; and the dead in Christ shall rise first;
      Then we which are alive and remain shall be caught up 
      together with them in the clouds, to meet the Lord in 
      the air; and so shall we ever be with the Lord."      
                          I Thessalonians 4:16,17 


20 Feb 2002 23:15:53 +0000
Oliver Elphick <olly@lfix.co.uk>
Re: Foreign Key work for 7.3+

On 20 Feb 2002, Oliver Elphick wrote:

> On Wed, 2002-02-20 at 17:28, Stephan Szabo wrote:
> > Here are the things I'd like to get feedback on doing to the foreign
> > key constraint triggers and support code. ...
> >
> > * Extend fk constraints to work with inheritance
> >  Make fk constraints inherit properly with both fk and pk base tables.
> >  This will probably mean making the appropriate triggers on the child
> >  tables involved as well as scanning the additional tables when checks and
> >  changes are needed. For right now, I'd say we'd want to require that the
> >  child tables at least also have unique constraints across the key.
>
> This means that we need a solution for unique indexes and primary keys
> under inheritance.  I wrote about this in a mail which is preserved in

Yeah, I'm sort of hoping that someone who works more with inheritance
will jump on the primary keys thing so that I can utilize it from the
foreign key constraints.  This one's one of the parts likely to be put
off since as much as I'd like to get it done it sounds like it's going
to take a lot of work.

> doc/TODO.detail/inheritance.
>
>     3. Inheritance of a table implies inheriting all its constraints
>     unless ONLY is used or the constraints are subsequently dropped;
>     again, dropping operates through all descendant tables.  A primary
>     key, foreign key or unique constraint cannot be dropped or modified
>     for a descendant.  A unique index on a column is shared by all
>     tables below the table for which it is declared.  It cannot be
>     dropped for any descendant.
>
>     In other words, only NOT NULL and CHECK constraints can be dropped
>     in descendants.
>
> [On reconsidering this, I'm not sure it is right to let these be
> modified either.]

I'm not sure either since a non-ONLY select on the parent will show the
rows that wouldn't meet the parent's constraints.

>     4. RI to a table implies the inclusion of all its descendants in the
>     check.  Since a referenced column may be uniquely indexed further up
>     the hierarchy than in the table named, the check must ensure that
>     the referenced value occurs in the right segment of the hierarchy.
>     RI to one particular level of the hierarchy, excluding descendants,
>     requires the use of ONLY in the constraint.
>
> So an index must somehow be made to serve more than one table, and then
> an index lookup must also discover whether the key is in the right
> segment of the inheritance hierarchy:

I'm sort of assuming that a cross table index will contain some reference
to the table it came from if only to help provide a way to reference the
associate heap tuple row.



Wed, 20 Feb 2002 16:37:38 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Foreign Key work for 7.3+