RI status report #2

ATTENTION: catalog changes - initdb required!

General support for deferred constraint triggers is finished
and committed to CURRENT tree.

Implemented so far:

CREATE CONSTRAINT TRIGGER <constraint_name>
AFTER <event> ON <relation_name>
[ FROM <referencing_relation_name> ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY { IMMEDIATE | DEFERRED } ]
FOR EACH ROW EXECUTE PROCEDURE <procedure_name> ( <args> )

SET CONSTRAINTS { <constraint_names> | ALL } { IMMEDIATE | DEFERRED }

Details on CREATE CONSTRAINT TRIGGER:

<constraint_name>

Can be a usual identifier or "" for unnamed
constraints. Since the same constraint can result in
multiple pg_trigger entries for different tables,
there's no check for duplicates. This is the name to
later identify constraints in SET CONSTRAINTS.

FROM <referencing_relation_name>

If given, causes that this trigger are automatically
removed when the referencing relation is dropped.
This is useful for referential action triggers (like
ON DELETE CASCADE), which are fired on changes to the
PK table. Dropping the FK table without removing the
triggers from the PK table would make it unusable.

[ NOT ] DEFERRABLE

Specifies if the trigger is deferrable or not.
Defaults to NOT DEFERRABLE if INITIALLY is IMMEDIATE.
Defaults to DEFERRABLE if INITIALLY is DEFERRED.

INITIALLY { IMMEDIATE | DEFERRED }

Specifies the deferred state of the trigger at
session start. Defaults to IMMEDIATE.

<procedure_name> ( <args> )

The usual trigger procedure definition.

The trigger itself in pg_trigger is created with a tgname
of RI_ConstraintTrigger_<newoid>, which should be unique
enough.

Details on SET CONSTRAINTS:

<constraint_names>

A comma separated list of constraint identifiers. An
attempt to set named constraints to DEFERRED where at
least one of the pg_trigger entries with this name
isn't deferrable raises an ERROR.

Using ALL with DEFERRED sets all deferrable
constraint triggers (named and unnamed) to deferred,
leaving not deferrable ones immediate.

If SET CONSTRAINTS is used outside of a transaction block
(BEGIN/COMMIT), it sets the default behaviour on session
level. All constraint triggers begin each transaction
(explicit block or implicit single statement) in these
states.

All AFTER ROW triggers (regular ones) are treated like
IMMEDIATE constraint triggers now so they are fired at
the end of the entire statement instead of during it.
This interfered with the funny_dup17 test in the
regression suite which is commented out now.

Trigger events for deferred triggers are condensed during
a transaction. That means, that executing multiple
UPDATE commands affecting the same row would finally
invoke only one trigger call which receives the original
tuple (before BEGIN) as OLD and the final tuple (after
last UPDATE) as NEW. Similar INSERT/DELETE of same row
will fire no trigger at all.

There are checks done if IMMEDIATE or BEFORE ROW triggers
have already been fired when a row is touched multiple
times in the same transaction. In that case, an error is
raised because this might violate referential integrity.

Needless to say that COMMIT causes an implicit SET
CONSTRAINTS ALL IMMEDIATE. All deferred triggers are run
then, so COMMIT could raise trigger generated errors now!

Next we need:

1. Generic trigger procs that are argument driven. I'll make
a separate thread for this topic.

2. Support in CREATE TABLE that issues the appropriate
CREATE CONSTRAINT TRIGGER statements for FOREIGN KEY in
the same manner as CREATE INDEX for PRIMARY KEY is done.
This must wait until we have an accepted call interface
for the generic trigger procs from 1..

3. Support for pg_dump to emit the correct CREATE CONSTRAINT
TRIGGER statements. Who wants to pick up this topic?

4. Add the ability to swap out huge amounts of deferred
trigger events to disk (actually I'm collecting them in
memory - so large transactions affecting millions of rows
of a table where triggers are defined are likely to blow
up the backend). This is my topic - sorry.

5. Write a regression test for the new FOREIGN KEY support.
Surely an important thing but one of the last steps after
anything else works properly.

6. Remove the "not supported yet" note for FOREIGN KEY from
the docs along with correcting to the full syntax
supported finally :-)

Hmmmm - the more I work on it the longer the TODO becomes.

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) #