Re: Are circular REFERENCES possible ? - Mailing list pgsql-sql

Josh Berkus wrote:
> Jan,
>
> >     All of this is wrong. If the constraints are  defined  to  be
> >     INITIALLY  DEFERRED,  all  you  have to do is to wrap all the
> >     changes that put the database into a  temporary  inconsistent
> >     state  into  a  transaction. What is a good idea and strongly
> >     advised anyway.
> >
> >     DEFERRED means, that the  consistency  WRT  the  foreign  key
> >     constratins  will  be  checked  at COMMIT time instead of the
> >     actual statement. So if you
>
> Hmmm... sounds interesting.  Can this be done through functions?  I.E.,
> if I put the INSERT/INSERT/UPDATE operation inside a function, does it
> automatically wait until the function completes before checking
> constraints?
   Acutally  you  have  fine  control  over  it  if you name the   constraints explicitly. You  can  define  a
constraint just   beeing  DEFERRABLE but INITIALLY IMMEDIATE. Such a constraint   will by default be checked
immediatelyat the time a PK/FK is   touched.  Inside  of  your  function  (as  well  as  inside a   transaction from
theapp-level) you can
 
       SET CONSTRAINTS namelist DEFERRED;
       do all your inserts/updates;
       SET CONSTRAINTS namelist IMMEDIATE;
   Setting them to DEFERRED means, that the checks  for  primary   key  existence  on  make  of references or the check
fornon-   existence of references on destruction  of  primary  key  are   delayed,  at max until COMMIT. Setting them
backto IMMEDIATE   runs the checks "for these constraint"  immediately,  without   waiting  for the COMMIT, and
arrangesfor all further actions   to get checked immediately.
 
   Whatever  you  do  and  in  whatever  state  you  leave   the   constraints, everything not yet checked will be at
COMMIT.
   Well,  the  SET  CONSTRAINTS has to be put into an EXECUTE in   PL/pgSQL, but I think that's not too big of a
problem.

> >     Josh, maybe you should buy a newer SQL-bo... :-)
> >
> >     Got ya (LOL)!
>
> Zap!  Ouch.  ;-)
   Couldn't resist ;-P

> >     The point is that we based our implementation of foreign keys
> >     on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK.
> >
>
> Know a good SQL3 book?  I bought O'Reilly's SQL In A Nutshell for that,
> but the book has numerous omissions and a few mistakes.
   Unfortunately no - others?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com