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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954