Thread: AfterTriggerSaveEvent() called outside of query

I'm running a script I have for cleaning out some old data.  It
iterates over a handful of tables and deletes any data referencing a
row in an "owners" table.  Basically, it enforces the equivalent of
the cascade delete of the data without actually deleting the master
row.

The code looks basically like this, for each owner_id ($oid) targeted
for the purge:

BEGIN;
SET LOCAL synchronous_commit TO OFF;
SET CONSTRAINTS ALL DEFERRED;

foreach table (owner_log, ... user_list, invoices, ... )
 DELETE FROM $table WHERE owner_id=$oid

UPDATE owner SET status='terminated' WHERE owner_id=$oid
INSERT INTO admin_log (.... record of account being purged ... );
COMMIT;

This worked just dandy without the two SET commands above I added
yesterday to try to speed things up.  (Yes, the constraints are marked
as deferrable...) What happens is now I get the following error:


 ERROR:  AfterTriggerSaveEvent() called outside of query
 CONTEXT:  SQL statement "DELETE FROM ONLY "public"."user_event_log"
WHERE $1 OPERATOR(pg_catalog.=) "user_id"" at ./purgeoldownerinfo line
77.


The context is showing a row being deleted via FK reference to the
user_list table user_id primary key field, which had an explicit
delete done by the loop.

I'm running this via Perl DBD::Pg connecting to a postgres 8.3.11
server on FreeBSD 8.1.  The tables in this database are replicated
using slony1.

Vick Khera <vivek@khera.org> writes:
> The code looks basically like this, for each owner_id ($oid) targeted
> for the purge:

> BEGIN;
> SET LOCAL synchronous_commit TO OFF;
> SET CONSTRAINTS ALL DEFERRED;

> foreach table (owner_log, ... user_list, invoices, ... )
>  DELETE FROM $table WHERE owner_id=$oid

> UPDATE owner SET status='terminated' WHERE owner_id=$oid
> INSERT INTO admin_log (.... record of account being purged ... );
> COMMIT;

> This worked just dandy without the two SET commands above I added
> yesterday to try to speed things up.  (Yes, the constraints are marked
> as deferrable...) What happens is now I get the following error:

>  ERROR:  AfterTriggerSaveEvent() called outside of query

The code comment associated with that error message says:

     * Check state.  We use normal tests not Asserts because it is possible
     * to reach here in the wrong state given misconfigured RI triggers,
     * in particular deferring a cascade action trigger.

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.

            regards, tom lane

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;

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

When we do a pg_dump for the schema, the FK constraints do show
DEFERRABLE like this:

ALTER TABLE ONLY user_event_log
    ADD CONSTRAINT user_event_log_user_id_fkey FOREIGN KEY (user_id)
REFERENCES user_list(user_id) ON DELETE CASCADE DEFERRABLE;

The above also is how it looks when I load my current schema into a Pg
9.0 instance and run pg_dump to get it back.  I'm guessing that the
deferrable here only applies to the existence test, not the cascade,
and when I hacked the pg_trigger entries it made the cascade bits also
deferrable.

It should all be fixed up when we do the migration to 9.0 since I will
load the schema freshly from the pg_dump then have slony copy the
data.

Thanks!

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