From: | Vick Khera <vivek(at)khera(dot)org> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: AfterTriggerSaveEvent() called outside of query |
Date: | 2010-11-16 20:36:41 |
Message-ID: | AANLkTinoja8pLF7rnTBRzLfTT+YGf64HMLOO_DniHyRB@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-11-16 20:38:51 | Re: ipv4 data type does not allow to use % assubnet mask delimiter |
Previous Message | Dann Corbit | 2010-11-16 20:35:59 | Re: How to select a postgresql table inside Oracle |