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
