Thread: BUG #6268: multiple update with on cascade

The following bug has been logged online:

Bug reference:      6268
Logged by:          Roman Lytovchenko
Email address:      roman.lytovchenko@gmail.com
PostgreSQL version: 9.1.1
Operating system:   Mac OS X 10.6.8
Description:        multiple update with on cascade
Details:

create table t2 (

    a text not null,
    b text null,

    constraint t2_pk primary key (a),

    constraint t2_t2_fk foreign key (b)
       references t2 (a) match simple
           on update cascade            --  !
           on delete cascade
               deferrable
               initially deferred       --  !
);

insert into t2 (a, b)
values
    ('www', 'www'),
    ('asd', 'asd');


--  run this transaction in another connection!
start transaction;
    set constraints all immediate;

    update t2
    set a = '123'
    where a = 'www';

    select
        a, b
    from t2;

    --  this update failed with
    --  ERROR: insert or update on table "t2" violates foreign key
constraint "t2_t2_fk"
    --  Detail: Key (b)=(123) is not present in table "t2".

    update t2
    set a = 'kkk'
    where a = '123';

commit;

"Roman Lytovchenko" <roman.lytovchenko@gmail.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