Re: AfterTriggerSaveEvent() called outside of query - Mailing list pgsql-general

Vick Khera <vivek@khera.org> writes:
> On Tue, Nov 16, 2010 at 12:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The system will not normally allow cascade actions to be deferred
>> ... did you manually munge the pg_trigger entries? �If you managed
>> to provoke this purely through DDL commands, that would be a bug,
>> and I'd like to see how you did it.

> Based on advice gleaned from here, earlier this year we did the
> following to make the constraints deferrable.  There were "warrantee
> breaking" warnings with that advice, though :(  I guess I broke it.

> UPDATE pg_trigger SET tgdeferrable='t' WHERE oid IN (SELECT objid FROM
> pg_depend WHERE refobjid IN (SELECT oid FROM pg_constraint WHERE
> condeferrable='f' AND contype='f' AND connamespace=2200));
> UPDATE pg_constraint SET condeferrable='t' WHERE condeferrable='f' AND
> contype='f' AND connamespace=2200;

Yeah, that was overambitious.  You should have set just the check
triggers, not the cascade triggers, to be deferrable.  Try making a
deferrable constraint the regular way and have a look at the pg_trigger
entries it creates.  For example,

create table m (f1 int primary key);
create table s (f2 int references m ON DELETE CASCADE DEFERRABLE);
select tgfoid::regproc, tgrelid::regclass, * from pg_trigger order by oid desc limit 4;

On HEAD I get this:

         tgfoid         | tgrelid | tgrelid |           tgname           | tgfoid | tgtype | tgenabled | tgisinternal |
tgconstrrelid| tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual  

------------------------+---------+---------+----------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------
 "RI_FKey_noaction_upd" | m       |   41310 | RI_ConstraintTrigger_41322 |   1655 |     17 | O         | t            |
       41315 |         41313 |        41318 | t            | f              |       0 |        | \x     |  
 "RI_FKey_cascade_del"  | m       |   41310 | RI_ConstraintTrigger_41321 |   1646 |      9 | O         | t            |
       41315 |         41313 |        41318 | f            | f              |       0 |        | \x     |  
 "RI_FKey_check_upd"    | s       |   41315 | RI_ConstraintTrigger_41320 |   1645 |     17 | O         | t            |
       41310 |         41313 |        41318 | t            | f              |       0 |        | \x     |  
 "RI_FKey_check_ins"    | s       |   41315 | RI_ConstraintTrigger_41319 |   1644 |      5 | O         | t            |
       41310 |         41313 |        41318 | t            | f              |       0 |        | \x     |  
(4 rows)

Notice the RI_FKey_cascade_del trigger is not deferrable.

> Is there a way to better limit that to avoid the FK constraints?

I think the code in the backend that does this just has a hard-wired
list of which trigger function OIDs to exclude from deferrability.

            regards, tom lane