Re: Are circular REFERENCES possible ?

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 immediately at the time a PK/FK is
touched. Inside of your function (as well as inside a
transaction from the app-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 for non-
existence of references on destruction of primary key are
delayed, at max until COMMIT. Setting them back to IMMEDIATE
runs the checks "for these constraint" immediately, without
waiting for the COMMIT, and arranges for 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(at)Yahoo(dot)com #

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