Re: [HACKERS] RI status report #2

Man, that's a heap of additions.

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

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026