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!
