From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Vick Khera <vivek(at)khera(dot)org> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: AfterTriggerSaveEvent() called outside of query |
Date: | 2010-11-16 23:11:14 |
Message-ID: | 8559.1289949074@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Vick Khera <vivek(at)khera(dot)org> writes:
> On Tue, Nov 16, 2010 at 12:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-11-16 23:14:36 | Re: Programming error: Out of Memory |
Previous Message | Jorge Arévalo | 2010-11-16 23:02:08 | Re: Programming error: Out of Memory |