Re: AfterTriggerSaveEvent() called outside of query

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;

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!