Re: update before drop causes OID problems in transaction?

Jeff Frost wrote:
> On Sat, 18 Mar 2006, Tom Lane wrote:
>
>> IIRC you'd have to drop the underlying plpgsql function, not only
>> the trigger object that connects the function to a table. We cache
>> stuff with respect to the function.
>
> Tom, sorry it took me a little while to make a test case. The test case
> is attached. If the attachments don't get through to the mailing list,
> you can grab the files here:
>
> http://www.frostconsultingllc.com/testcase/transaction-test-case-setup.sql
> http://www.frostconsultingllc.com/testcase/transaction-test-case.sql
>
> transaction-test-case-setup.sql will create the appropriate tables and
> transaction-test-case.sql will demonstrate the error.
>
> You can reproduce the problem like so:
>
> createdb testcase
> createlang plpgsql testcase
> psql -f doc/perpetual/transaction-test-case-setup.sql testcase
> psql -f doc/perpetual/transaction-test-case.sql testcase
>
> psql:transaction-test-case.sql:10: ERROR: could not open relation with
> OID 2038878
>
> 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.

> Now that I've got a test case for you guys to look at, I'm off to
> rewrite our standard procedure to use TRUNCATE instead of DROP.

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.

--
Richard Huxton
Archonet Ltd