Re: BUG #6268: multiple update with on cascade

"Roman Lytovchenko" <roman(dot)lytovchenko(at)gmail(dot)com> writes:
> Description: multiple update with on cascade

Yeah, this is a bug all right, see my analysis at
http://archives.postgresql.org/pgsql-hackers/2011-10/msg01361.php

As a stopgap workaround, you could manually rename the triggers
generated by the FK constraint so that they execute in the opposite
order. Try a command like this to see the triggers on t2:

regression=# select tgname, tgfoid::regproc from pg_trigger where tgrelid = 't2'::regclass order by 1;
tgname | tgfoid
----------------------------+-----------------------
RI_ConstraintTrigger_53586 | "RI_FKey_check_ins"
RI_ConstraintTrigger_53587 | "RI_FKey_check_upd"
RI_ConstraintTrigger_53588 | "RI_FKey_cascade_del"
RI_ConstraintTrigger_53589 | "RI_FKey_cascade_upd"
(4 rows)

(The numbers at the ends of the names will almost certainly be different
for you.)

You need to adjust the names so that the RI_FKey_check_upd trigger sorts
after the one that calls RI_FKey_cascade_upd. For example, on my copy
of your test case,

update pg_trigger set tgname = 'RI_ConstraintTrigger_x53587'
where tgname = 'RI_ConstraintTrigger_53587' and tgrelid = 't2'::regclass;

followed by starting a fresh database session fixes it. (You'll need to
be superuser to mess with the system catalogs directly like that.)

Keep in mind that until we fix this in the source code, any
freshly-created self-referential FK constraint is vulnerable to the same
problem; in particular the problem would come back if you did a dump and
reload.

regards, tom lane