Обсуждение: Changes to not deferred FK in 8.0.3 to 7.4?

Hi,

in the release docs it says:

   "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. For example, if a function inserts a new row into a table, any
non-deferred foreign key checks occur before proceeding with the function."

I don't know if it relates to my problem:

I have lots of tables with mutli-column PK and multi-column FK. All FK are
cascading, so updating a PK should trigger through the whole database.

This worked earlier in 7.4:

UPDATE tipprunden SET tr_kurzname = 'schwarze2' where tr_kurzname =
'schwarze';

it should cacsade through lots of tables and other primary key as each table
has at least a column of "tr_kurzname".

With 8.0.3 it get error messages like:

    ERROR:  insert or update on table "spieletipps" violates foreign key
constraint "fk_tippspieltage2spiele"
DETAIL:  Key (tr_kurzname,sp_id)=(schwarze2,197619) is not present in table
"tippspieltage2spiele".
CONTEXT:  SQL statement "UPDATE ONLY "public"."spieletipps" SET "tr_kurzname"
= $1, "mg_name" = $2 WHERE "tr_kurzname" = $3 AND "mg_name" = $4"
SQL statement "UPDATE ONLY "public"."mitglieder" SET "tr_kurzname" = $1 WHERE
"tr_kurzname" = $2

What happens here to me is, that it cascades first from "tipprunden" to
"mitglieder" to "spieletipps". But "tippspieltage2spiele" relates to
"tipprunden" as well, so updating "spieletipps" fails because the FK
fk_tippspieltage2spiele fails as the table "tippspieltage2spiele" is not up
to date at this moment.

It makes sense to me when i reread the release notes. Not-deferred FK are
checked immediatley not at the end of the statement so circular references
cant' be handeled with not-deferrable FK !?

Then i tried to make all my FK constraint to be deferrable and initially
deferred like this:

$ UPDATE pg_constraint set condeferrable= 't', condeferred='t'  where conname
LIKE 'fk_%';

Is it all what needs to be done to pg_catalog? Or did i miss something. But to
me it looks ok as a table description with '\d' actually states "deferrable
initially deferred" for all my FK.

But with all FK deferred i still get the error above. If i drop a few FK
completely to avoid a circular roundtrip everything works fine (but of course
this is not an option as i need these FKs)

Any help is very appreciated.

kind regards,
janning

Janning Vygen <vygen@gmx.de> writes:
> I have lots of tables with mutli-column PK and multi-column FK. All FK are
> cascading, so updating a PK should trigger through the whole database.

> This worked earlier in 7.4:

> UPDATE tipprunden SET tr_kurzname = 'schwarze2' where tr_kurzname =
> 'schwarze';

> it should cacsade through lots of tables and other primary key as each table
> has at least a column of "tr_kurzname".

> With 8.0.3 it get error messages like:

>     ERROR:  insert or update on table "spieletipps" violates foreign key
> constraint "fk_tippspieltage2spiele"
> DETAIL:  Key (tr_kurzname,sp_id)=(schwarze2,197619) is not present in table
> "tippspieltage2spiele".
> CONTEXT:  SQL statement "UPDATE ONLY "public"."spieletipps" SET "tr_kurzname"
> = $1, "mg_name" = $2 WHERE "tr_kurzname" = $3 AND "mg_name" = $4"
> SQL statement "UPDATE ONLY "public"."mitglieder" SET "tr_kurzname" = $1 WHERE
> "tr_kurzname" = $2

> What happens here to me is, that it cascades first from "tipprunden" to
> "mitglieder" to "spieletipps". But "tippspieltage2spiele" relates to
> "tipprunden" as well, so updating "spieletipps" fails because the FK
> fk_tippspieltage2spiele fails as the table "tippspieltage2spiele" is not up
> to date at this moment.

AFAICS, if it worked for you in 7.4 it was only by pure chance.  There
was not then, and is not now, any logic that would prevent the FK checks
from being applied in an order you don't want.

My guess is that the reason for the change in behavior is that the 7.4
FK checks happened to fire in a "safe" order and the same checks in 8.0
are being fired in a different order.  This could be pg_dump's fault
--- it seems to feel that it can dump FK constraints in any order it
wants to, rather than trying to preserve the order-of-creation which is
(I believe) what determines the order of trigger firing.

Note that the order I'm thinking of is the firing order of multiple
triggers for the same event on the same table; this is quite unrelated
to the question of deferred-ness for the whole trigger set.

            regards, tom lane

On Mon, 18 Jul 2005, Tom Lane wrote:

> Janning Vygen <vygen@gmx.de> writes:
> > I have lots of tables with mutli-column PK and multi-column FK. All FK are
> > cascading, so updating a PK should trigger through the whole database.
>
> > This worked earlier in 7.4:
>
> > UPDATE tipprunden SET tr_kurzname = 'schwarze2' where tr_kurzname =
> > 'schwarze';
>
> > it should cacsade through lots of tables and other primary key as each table
> > has at least a column of "tr_kurzname".
>
> > With 8.0.3 it get error messages like:
>
> >     ERROR:  insert or update on table "spieletipps" violates foreign key
> > constraint "fk_tippspieltage2spiele"
> > DETAIL:  Key (tr_kurzname,sp_id)=(schwarze2,197619) is not present in table
> > "tippspieltage2spiele".
> > CONTEXT:  SQL statement "UPDATE ONLY "public"."spieletipps" SET "tr_kurzname"
> > = $1, "mg_name" = $2 WHERE "tr_kurzname" = $3 AND "mg_name" = $4"
> > SQL statement "UPDATE ONLY "public"."mitglieder" SET "tr_kurzname" = $1 WHERE
> > "tr_kurzname" = $2
>
> > What happens here to me is, that it cascades first from "tipprunden" to
> > "mitglieder" to "spieletipps". But "tippspieltage2spiele" relates to
> > "tipprunden" as well, so updating "spieletipps" fails because the FK
> > fk_tippspieltage2spiele fails as the table "tippspieltage2spiele" is not up
> > to date at this moment.
>
> AFAICS, if it worked for you in 7.4 it was only by pure chance.  There
> was not then, and is not now, any logic that would prevent the FK checks
> from being applied in an order you don't want.

True, although I think in 7.4 it was more likely to work since the check
triggers would be put on the trigger queue after the first level of
referential action triggers rather than be run immediately between, right?
I'm not sure when the triggered update's constraint checks are supposed to
fire (is it as part of the referential action's updating action or the
original query's constraint checks at end of statement?)

Am Montag, 18. Juli 2005 16:28 schrieb Stephan Szabo:
> On Mon, 18 Jul 2005, Tom Lane wrote:
> > Janning Vygen <vygen@gmx.de> writes:
> > > I have lots of tables with mutli-column PK and multi-column FK. All FK
> > > are cascading, so updating a PK should trigger through the whole
> > > database.
> > >
> > > This worked earlier in 7.4:
> > >
> > > UPDATE tipprunden SET tr_kurzname = 'schwarze2' where tr_kurzname =
> > > 'schwarze';
> > >
> > > it should cacsade through lots of tables and other primary key as each
> > > table has at least a column of "tr_kurzname".
> > >
> > > With 8.0.3 it get error messages like:
> > >
> > >     ERROR:  insert or update on table "spieletipps" violates foreign
> > > key constraint "fk_tippspieltage2spiele"
> > > DETAIL:  Key (tr_kurzname,sp_id)=(schwarze2,197619) is not present in
> > > table "tippspieltage2spiele".
> > > CONTEXT:  SQL statement "UPDATE ONLY "public"."spieletipps" SET
> > > "tr_kurzname" = $1, "mg_name" = $2 WHERE "tr_kurzname" = $3 AND
> > > "mg_name" = $4" SQL statement "UPDATE ONLY "public"."mitglieder" SET
> > > "tr_kurzname" = $1 WHERE "tr_kurzname" = $2
> > >
> > > What happens here to me is, that it cascades first from "tipprunden" to
> > > "mitglieder" to "spieletipps". But "tippspieltage2spiele" relates to
> > > "tipprunden" as well, so updating "spieletipps" fails because the FK
> > > fk_tippspieltage2spiele fails as the table "tippspieltage2spiele" is
> > > not up to date at this moment.
> >
> > AFAICS, if it worked for you in 7.4 it was only by pure chance.  There
> > was not then, and is not now, any logic that would prevent the FK checks
> > from being applied in an order you don't want.
>
> True, although I think in 7.4 it was more likely to work since the check
> triggers would be put on the trigger queue after the first level of
> referential action triggers rather than be run immediately between, right?
> I'm not sure when the triggered update's constraint checks are supposed to
> fire (is it as part of the referential action's updating action or the
> original query's constraint checks at end of statement?)

ok, i understand that circular references are checked in any order and it
worked by luck in 7.4.

But why doesn't it work if i make alle FK deferrable initially deferred?

IMHO the check should occur at the end of the transaction, right? So at this
time alle PK and FK should be updated and everything should work fine. But it
doesn't. Or did i just get the pg_catalog update statment wrong making all my
fk "deferrable inititally deferred"?

i am kind of helpless.

kind regards,
janning

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Mon, 18 Jul 2005, Tom Lane wrote:
>> AFAICS, if it worked for you in 7.4 it was only by pure chance.  There
>> was not then, and is not now, any logic that would prevent the FK checks
>> from being applied in an order you don't want.

> True, although I think in 7.4 it was more likely to work since the check
> triggers would be put on the trigger queue after the first level of
> referential action triggers rather than be run immediately between, right?

I don't see why.  They are all "AFTER UPDATE" triggers so the trigger
mechanism isn't going to make any distinction between them; it'll just
fire them in trigger name order (which for FK triggers will reduce to
time-of-creation order, ignoring OID wraparound issues).

> I'm not sure when the triggered update's constraint checks are supposed to
> fire (is it as part of the referential action's updating action or the
> original query's constraint checks at end of statement?)

Arguably the constraint checks should always fire later.  I think it
would actually work correctly in 8.0 if Janning made the constraints all
deferred (using the proper syntax, ie, SET CONSTRAINTS ALL DEFERRED)
because the referential action triggers are not deferred in any case,
so they fire at end-of-query, but deferred constraint triggers fire
at end-of-transaction.

Do you think that's enough, or do we need to add more mechanism to
ensure that even non-deferred constraint checks fire after all
referential actions are complete?

            regards, tom lane

Janning Vygen <vygen@gmx.de> writes:
> But why doesn't it work if i make alle FK deferrable initially deferred?

You didn't do it right --- I don't believe the code actually looks at
pg_constraint, it looks at pg_trigger.  And if you are going to hack
pg_trigger directly, be careful to only change the check-trigger rows
not the action-trigger rows.  I forget which is which but Stephen
probably remembers.

            regards, tom lane

Am Montag, 18. Juli 2005 16:56 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> writes:
> > But why doesn't it work if i make alle FK deferrable initially deferred?
>
> You didn't do it right --- I don't believe the code actually looks at
> pg_constraint, it looks at pg_trigger.  And if you are going to hack
> pg_trigger directly, be careful to only change the check-trigger rows
> not the action-trigger rows.  I forget which is which but Stephen
> probably remembers.

as always: you are absolutly right! I should have checked it properly with the
correct syntax before asking wizards. Sorry for stealing your time. damn. i
felt a little bit like a hacker by manipulating pg_catalog.

regards
Janning

On Mon, 18 Jul 2005, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > On Mon, 18 Jul 2005, Tom Lane wrote:
> >> AFAICS, if it worked for you in 7.4 it was only by pure chance.  There
> >> was not then, and is not now, any logic that would prevent the FK checks
> >> from being applied in an order you don't want.
>
> > True, although I think in 7.4 it was more likely to work since the check
> > triggers would be put on the trigger queue after the first level of
> > referential action triggers rather than be run immediately between, right?
>
> I don't see why.  They are all "AFTER UPDATE" triggers so the trigger
> mechanism isn't going to make any distinction between them; it'll just
> fire them in trigger name order (which for FK triggers will reduce to
> time-of-creation order, ignoring OID wraparound issues).

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

Right?

> > I'm not sure when the triggered update's constraint checks are supposed to
> > fire (is it as part of the referential action's updating action or the
> > original query's constraint checks at end of statement?)
>
> Arguably the constraint checks should always fire later.  I think it
> would actually work correctly in 8.0 if Janning made the constraints all
> deferred (using the proper syntax, ie, SET CONSTRAINTS ALL DEFERRED)
> because the referential action triggers are not deferred in any case,
> so they fire at end-of-query, but deferred constraint triggers fire
> at end-of-transaction.
>
> Do you think that's enough, or do we need to add more mechanism to
> ensure that even non-deferred constraint checks fire after all
> referential actions are complete?

I think that's probably enough for now.  We probably will need to do
something, but since we still haven't managed to work out all of these
timing problems, I think it needs a bunch of going over the spec before
trying to actually do any changes. I have a feeling that it'll turn out
that normal triggers happen as we do now, but constraint checks caused
by referential actions are special and should be considered as part of
their parent statement. I'll try to see if rereading 99 or 03 (if
work has one) helps any.

Stephan Szabo <sszabo@megazone.bigpanda.com> 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).

>> Do you think that's enough, or do we need to add more mechanism to
>> ensure that even non-deferred constraint checks fire after all
>> referential actions are complete?

> I think that's probably enough for now.  We probably will need to do
> something, but since we still haven't managed to work out all of these
> timing problems, I think it needs a bunch of going over the spec before
> trying to actually do any changes.

Agreed, this doesn't seem like an area for hasty solutions.

The short-term answer for Janning is definitely to make his check
constraints deferred, but we should look at whether our current ordering
of non-deferred checks is really per spec or not.

            regards, tom lane

resending it because i used the wrong mail address. sorry!

Am Montag, 18. Juli 2005 18:18 schrieb Tom Lane:
> Stephan Szabo <sszabo@megazone.bigpanda.com> 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

Janning Vygen <vygen@gmx.de> writes:
> 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_%'
> ;

No, only the triggers that are for checks should be marked
deferrable/deferred.  These are the ones using functions
 RI_FKey_check_ins
 RI_FKey_check_upd
 RI_FKey_noaction_del
 RI_FKey_noaction_upd
You want the others nondeferrable because (a) that's the standard
behavior and (b) it'll ensure that the actions happen before the
checks are made.

            regards, tom lane

Am Montag, 18. Juli 2005 18:18 schrieb Tom Lane:
> Stephan Szabo <sszabo@megazone.bigpanda.com> 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

On Mon, 18 Jul 2005, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> 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).
>
> >> Do you think that's enough, or do we need to add more mechanism to
> >> ensure that even non-deferred constraint checks fire after all
> >> referential actions are complete?
>
> > I think that's probably enough for now.  We probably will need to do
> > something, but since we still haven't managed to work out all of these
> > timing problems, I think it needs a bunch of going over the spec before
> > trying to actually do any changes.
>
> Agreed, this doesn't seem like an area for hasty solutions.
>
> The short-term answer for Janning is definitely to make his check
> constraints deferred, but we should look at whether our current ordering
> of non-deferred checks is really per spec or not.

It seems like we're off from reading SQL03 in various ways, but I don't
fully understand all of the rules and how they're set up.

It seems to me that the referential action text is now implying that
after triggers caused by referential actions become part of the
trigger context of the statement that triggered them. GR5 sets SSC to the
current trigger execution context (presumably for the statement doing the
action) and the ref action texts add state changes to SSC.

Taking the above with the fact that the "statement"  triggers for
referential action are always empty implies to me that a referential
action change isn't a statement; GR9-11 don't seem to affect this, but
give some weight, since otherwise separately triggered events would not
count for triggered data change events I think.

GR14 seems to codify the fact that referential actions that cause other
referential actions happen immediately.

I really don't understand what they've done with GR15 and 16.  15 would
seem to say that in the case of a cascade delete, the rows are marked for
deletion and then the before triggers are called, but that doesn't make
much sense to me.

-------------

So, it looks to me like the following (minus before deletion triggers
caused by ref actions) from combining the 14.* effect list and the above
for delete (update looks similar).  I'm also a little unsure of the last
two.  The notes on the rules that applies after trigger says, "All
constraints have already been checked..." which makes it sound like that
comes before the after triggrs to me.  I'm going to keep looking through
this, but figured I'd throw something out for people to rip apart.

On delete statement:
 Make a new trigger context
 Run before triggers
 Mark for deletion
begin 11.8 rules:
 If there are any referential actions,
  For each action,
   If it's a cascade,
    Mark referencing rows for deletion
    Add after row triggers for that to the context created above
   If it's a restrict,
    Error or not
   If it's a SET *,
    Run row level before triggers for the triggered update
    Identify for replacement
    Add after row triggers for triggered update
 If any state changes were done by the preceding, apply 11.8 rules
  again until there aren't any more changes
end 11.8 rules
 Delete the rows marked for deletion
 Evaluate constraints
 Run after triggers

[sorry for resending again. i am not at my usual desktop at the moment and
used the wrong sender address]

Am Dienstag, 19. Juli 2005 15:40 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> writes:
> > 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_%'
> > ;
>
> No, only the triggers that are for checks should be marked
> deferrable/deferred.  These are the ones using functions
>  RI_FKey_check_ins
>  RI_FKey_check_upd
>  RI_FKey_noaction_del
>  RI_FKey_noaction_upd
> You want the others nondeferrable because (a) that's the standard
> behavior and (b) it'll ensure that the actions happen before the
> checks are made.

ok thanks. i do it now like this:

UPDATE pg_trigger
SET
  tgdeferrable = true,
  tginitdeferred = true
WHERE tgconstrname LIKE 'fk_%'
 AND tgfoid IN (
   SELECT oid FROM pg_proc
   WHERE proname IN (
    'RI_FKey_check_ins', 'RI_FKey_check_upd',
    'RI_FKey_noaction_del', 'RI_FKey_noaction_upd')
 )
;

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

COMMIT;

This should work i hope, but i feel a little bit unsure if hacking the
pg_catalog is a good way to do it. Maybe I should have take the long, but
secure way by modifying the schema with ddl statements.

kind regards,
janning

Am Dienstag, 19. Juli 2005 15:40 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> writes:
> > 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_%'
> > ;
>
> No, only the triggers that are for checks should be marked
> deferrable/deferred.  These are the ones using functions
>  RI_FKey_check_ins
>  RI_FKey_check_upd
>  RI_FKey_noaction_del
>  RI_FKey_noaction_upd
> You want the others nondeferrable because (a) that's the standard
> behavior and (b) it'll ensure that the actions happen before the
> checks are made.

ok thanks. i do it now like this:

UPDATE pg_trigger
SET
  tgdeferrable = true,
  tginitdeferred = true
WHERE tgconstrname LIKE 'fk_%'
 AND tgfoid IN (
   SELECT oid FROM pg_proc
   WHERE proname IN (
    'RI_FKey_check_ins', 'RI_FKey_check_upd',
    'RI_FKey_noaction_del', 'RI_FKey_noaction_upd')
 )
;

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

COMMIT;

This should work i hope, but i feel a little bit unsure if hacking the
pg_catalog is a good way to do it. Maybe I should have take the long, but
secure way by modifying the schema with ddl statements.

kind regards,
janning