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