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@debis.com (Jan Wieck) #
>
>
>
> ************
>


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