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.
