Re: update before drop causes OID problems in transaction?

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 DEFERRED
>
> I'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 <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954