Received: from smtp016.mail.yahoo.com (smtp016.mail.yahoo.com [216.136.174.113]) by postgresql.org (8.11.3/8.11.4) with SMTP id f77Ige668459 for ; Tue, 7 Aug 2001 14:42:41 -0400 (EDT) (envelope-from janwieck@yahoo.com) Received: from jupiter.us.greatbridge.com (65.196.69.55) by smtp.mail.vip.sc5.yahoo.com with SMTP; 7 Aug 2001 18:42:39 -0000 X-Apparently-From: Received: (from wieck@localhost) by jupiter.us.greatbridge.com (8.11.2/8.11.2) id f77IpLv02450; Tue, 7 Aug 2001 14:51:21 -0400 From: Jan Wieck Message-Id: <200108071851.f77IpLv02450@jupiter.us.greatbridge.com> Subject: Re: Are circular REFERENCES possible ? In-Reply-To: from Josh Berkus at "Aug 7, 2001 10:52:38 am" To: Josh Berkus Date: Tue, 7 Aug 2001 14:51:21 -0400 (EDT) CC: Jan Wieck , Denis Bucher , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200108/110 X-Sequence-Number: 4331 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