Re: Are circular REFERENCES possible ?
От | Jan Wieck |
---|---|
Тема | Re: Are circular REFERENCES possible ? |
Дата | |
Msg-id | 200108071851.f77IpLv02450@jupiter.us.greatbridge.com обсуждение исходный текст |
Ответ на | Re: Are circular REFERENCES possible ? ("Josh Berkus" <josh@agliodbs.com>) |
Список | 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
В списке pgsql-sql по дате отправления: