Search Postgresql Archives

Re: Changes to not deferred FK in 8.0.3 to 7.4?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Am Montag, 18. Juli 2005 18:18 schrieb Tom Lane:
> Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx> writes:
> > On Mon, 18 Jul 2005, Tom Lane wrote:
> >> I don't see why.
> >
> > Except that before I think the order would have looked like (for 1 row)
> > Originating Action
> > Trigger A on originating table that does update
> > Trigger B on originating table that does update
> > Trigger A1 caused by A
> > Trigger B1 caused by B
> >
> > I think now it acts like:
> > Originating Action
> > Trigger A on originating table that does update
> >  Trigger A1 caused by A
> > Trigger B on originating table that does update
> >  Trigger B1 caused by B
>
> Ah, of course.  So that could explain Janning's difference in results
> without having to assume any rearrangement from pg_dump (not but what
> we shouldn't take a second look at pg_dump's behavior anyway).

a FK results in a "referential action" which updates the FK attributes and a 
"referential constraint" which checks if all FKs are ok, right?

So my understanding of what's going on is:

        table1
       /      \
    table2   table3 
       \      /
        table4

UPDATE Table1 PK = $2 WHERE PK = $1
 -> UPDATE Table2 FK = $2 WHERE FK = $1
    -> UPDATE Table4 FK1 = $2 WHERE FK1 = $1
       -> no action
       -> CHECK table4 FK1 in table2
       -> CHECK table4 FK2 in table3        (***)
    -> CHECK table2 FK in table 1     
 -> UPDATE Table3 FK = $2 WHERE FK = $1
    -> UPDATE Table4 FK2 = $2 WHERE FK2 = $1
       -> no action
       -> CHECK table4 FK1 in table2
       -> CHECK table4 FK2 in table3
    -> CHECK table3 FK in table 1     
 -> no check on table1

if fk1 and fk2 on table4 overlap in one column, i get an error at (***) 
because table3 is not updated at the moment. this error doesn't show up with 
deferrable constraints because all check clauses are moved to end of the 
transaction.

so i think my problem is the overlapping of a FK in table4. In 7.4 the 
beahaviour was like this:

UPDATE Table1 PK = $2 WHERE PK = $1
 -> UPDATE Table2 FK = $2 WHERE FK = $1
 -> UPDATE Table3 FK = $2 WHERE FK = $1
 -> no check on table1
    -> UPDATE Table4 FK1 = $2 WHERE FK1 = $1
    -> UPDATE Table4 FK2 = $2 WHERE FK2 = $1
       -> no action
       -> CHECK table4 FK1 in table2
       -> CHECK table4 FK2 in table3  (***)
       -> CHECK table4 FK1 in table2
       -> CHECK table4 FK2 in table3
 -> CHECK table2 FK in table 1     
 -> CHECK table3 FK in table 1     

I dont' got an error because table3 is already updated at (***)
In my example there are NO circular references, they just overlap on table4 
which is a common technique if you have natural primary keys. 

My "feeling" is:
If you DON'T have circular references, you should not need defferable 
constraints.

So I don't see any benefit of changes the order of execution, but anyway: two 
remarks:

from the docs (CREATE TABLE)
"A constraint that is not deferrable will be checked immediately after every 
command." What means command in this sentence. Each Update which is triggered 
by a FK or my original statement?" To me "statment" means the user statement. 
so checks should be done after statement and all fired trigger statements are 
complete. But this isn't the case. It should be
"A constraint that is not deferrable will be checked immediately after 
completion of the triggering query".

>From the realease notes:(8.0)
"Non-deferred AFTER triggers are now fired immediately after completion of the 
triggering query, rather than upon finishing the current interactive command. 
This makes a difference when the triggering query occurred within a function: 
the trigger is invoked before the function proceeds to its next operation."

it should be mentioned, that is makes a difference if you have overlapping FKs 
like i have.

I hope that all this stuff i just wrote is mostly correct and maybe it helps 
you improving postgresql. If i can help any further with a complete example, 
please let me know.

On more related question:
I updated pg_trigger and pg_constraint and changed all my FK:

UPDATE pg_trigger 
SET 
  tgdeferrable = true,
  tginitdeferred = true
WHERE tgconstrname LIKE 'fk_%'
;

UPDATE pg_constraint
SET 
  condeferrable = true,
  condeferred = true
WHERE conname LIKE 'fk_%'
;

did i make it right this time updating the pg_catalog? Or is there more to do 
in pg_catalog?

kind regards
janning


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux