Search Postgresql Archives

Re: AfterTriggerSaveEvent() called outside of query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Vick Khera <vivek@xxxxxxxxx> writes:
> On Tue, Nov 16, 2010 at 12:40 PM, Tom Lane <tgl@xxxxxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux