RI status report #1

Hi all,

just to give anyone a chance to complain, I'd like to
describe what I plan to implement for v6.6 in the referential
integrity (RI) corner.

1. What will be supported

I'm concentrating on FOREIGN KEY support. From the general
definition (thanks to Vadim for the SQL3 draft):

[ CONSTRAINT constraint-name ] FOREIGN KEY ( column [, ...] )
REFERENCES [ PENDANT ] table-name [ ( column [, ...] ) ]
[ MATCH { FULL | PARTIAL } ]
[ ON DELETE <referential-action> ]
[ ON UPDATE <referential-action> ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY { IMMEDIATE | DEFERRED } ]

<referential-action> ::=
CASCADE
| SET NULL
| SET DEFAULT
| RESTRICT
| NO ACTION

I'll omit the following parts on the first go:

PENDANT
MATCH (match type is allways FULL)

The implementation of referential-actions will require
that the columns in the referenced table build a unique
key. It will not be guaranteed, that an appropriate unique
index exists.

The support for the SET DEFAULT action depends on the
smartness of the generic trigger procedure described later
- so that detail might be left unsupported too in v6.6.

2. Implementation

As previous discussions turned out, the rule system isn't
adequate for implementing deferred constraints with
respect to all their side effects. Thus, RI constraints
will be implemented by specialized trigger procedures.

Therefore, a bunch of new attributes and some indices are
added to the pg_trigger system catalog. These are required
to tell constraints from real triggers, automatically drop
referential-action constraints from referenced tables if
the referencing table is dropped and to hold information
about deferrability and initially deferred states for the
constraints.

The procedures will finally get implemented as builtin, C
language, generic functions.

3. What I have so far

I've added the following attributes to pg_trigger:

tgenabled A bool that is designed to switch off
a regular trigger with the ALTER
TRIGGER command. This is not related
to RI and I'm not actually planning on
implementing the parser/utility stuff.

tgisconstraint A bool that tells a constraint from a
trigger.

tgconstrname The NAME of the constraint. RI
constraint triggers will be
automatically inserted during CREATE
TABLE with trigger names
_RI_Fkey_Constraint_<n> so that they
are unique "triggers" per table. This
attribute (indexed) holds the real
constraint name for SET CONSTRAINT.

tgconstrrelid The OID of the opposite table
(indexed). In the constraints that
check foreign key existance, it's the
Oid of the referenced table. In the
constraints that do the referential-
actions, it's the Oid of the
referenced table. This Oid is used to
quickly drop triggers from the
opposite table in the case of DROP
TABLE.

tgdeferrable A bool telling if the constraint can
be set to DEFERRED checking.

tginitdeferred A bool telling if the constraint is in
DEFERRED state by default.

To commands/trigger.c I've added a few hundred lines of
code. All AFTER ROW IMMEDIATE triggers are executed after
the entire query. DEFERRED triggers are executed at SET
CONSTRAINTS ... IMMEDIATE or at COMMIT.

To the time qualification code I've added SnapshotAny.
Since I know the exact CTID of the tuple WHICH IS OLD/NEW
for the event in question, this new snapshot completely
ignores any time qualification and fetches it.

What I see so far from the tests, anything (except for the
damned funny_dup17 reported earlier) still works. And
setting triggers to deferred execution solves the cyclic
integrity problems which are the reason for deferred
execution. So I assume I'm on the right track.

4. Next steps

First I need to implement the SET CONSTRAINTS command in
the parser and utility stuff now.

Second I'll write the generic trigger procs in PL/Tcl that
don't use prepared SPI plans (it's easiest to do it this
way). When they work as needed by the implementation, I'll
write down the specs and ask the co-developers to
implement their high quality, plan saving C-language
equivalents. Sorry, but all co-developers therefore should
at least compile in PL/Tcl support.

All the parser/utility stuff must be written that handles
constraint trigger creation/dropping during CREATE/DROP
table. And all the new features/definitions must be
adapted to pg_dump and psql.

Finally the deferred trigger manager must buffer huge
amounts of trigger events (actually collected in memory)
out onto disk.

Most of the activities after "Second" next step can be
done parallel. I'll commit my changes after that, because
then I'm able to run a full test of deferred constraints
to be sure I'm really on the right track. All co-
developers can join then using the CURRENT tree.

Any comments?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #