pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <JanWieck@Yahoo.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: Denis Bucher <dbucher@niftycom.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Are circular REFERENCES possible ?
Date: Tue, 7 Aug 2001 14:51:21 -0400 (EDT)
Message-ID: <200108071851.f77IpLv02450@jupiter.us.greatbridge.com> (raw)
In-Reply-To: <web-98111@davinci.ethosmedia.com>

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@Yahoo.com #



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




view thread (15+ messages)  latest in thread

Message-ID: <200108071851.f77IpLv02450@jupiter.us.greatbridge.com>
Permalink:  ../200108071851.f77IpLv02450@jupiter.us.greatbridge.com/
Also on:    postgresql.org/message-id/200108071851.f77IpLv02450@jupiter.us.greatbridge.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: JanWieck@Yahoo.com, josh@agliodbs.com, dbucher@niftycom.com
  Subject: Re: Are circular REFERENCES possible ?
  In-Reply-To: <200108071851.f77IpLv02450@jupiter.us.greatbridge.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox