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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
