On Mon, 20 Mar 2006, Richard Huxton wrote:
I stripped the tables and queries down to the minimum that demonstrated the error. Interestingly, the problem was not reproducible until I added the credit_card_audit_account_id constraint below:CONSTRAINT credit_card_audit_account_id_fkey FOREIGN KEY (account_id) REFERENCES accounts_basics (id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERREDI'm not sure a deferred constraint makes sense if you're dropping the table before the end of the transaction. I'm not sure whether the DROP should be prevented or what other error should be provided, but I can't see how both the constraint and the drop can occur.
Indeed much of this transaction might not make sense as it is really all done just for schema change and not part of normal operation. And in fact, you're correct that removing the DEFERRABLE property of the constraint allows the transaction to commit, so the workaround for my update as part of the transaction problem would be to set constraints immediate as part of that transaction like so:
SET CONSTRAINTS credit_card_audit_account_id_fkey IMMEDIATE; And indeed this does work.
Another problem might well be with your plpgsql trigger function. If you're dropping/re-creating credit_card_audit then that'll give you the error you're seeing.
The trigger shouldn't be firing at all in this scenario as it is on credit_card and not credit_card_audit. Are you saying that it could cause this sort of problem even though it doesn't fire?
-- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
