Re: update before drop causes OID problems in transaction?

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

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.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954