On Sat, 18 Mar 2006, Tom Lane wrote:
No, I think it's that you've got a plpgsql trigger function that contains queries referring to credit_card_audit. Dropping and recreating that table invalidates plpgsql's cached plans for those queries.
Is that the case whether the triggers are executed or not? There aren't any triggers on credit_card_audit, but credit_card has the audit_credit_card trigger which calls a plpgsql function. However, we drop that trigger before dropping credit_card_audit, so I'd think that would be ok. Also, we aren't modifying data in credit_card, so I wouldn't think that trigger would fire anyway. Of course, I probably am missing something here.
We do have in mind to fix this (Neil Conway was poking at it, last I heard) but it won't happen before 8.2 at the earliest. In the meantime I'm wondering why you are insistent on dropping and recreating credit_card_audit, as opposed to something less invasive like TRUNCATE.
I inherited this procedure from the previous DBA and hadn't looked at streamlining until now. I would guess it's because we have a script which generates the SQL responsible for setting up the audit table and associated trigger, constraints and functions..thus making it easier to just drop and recreate the table with the automatically generated SQL.
The procedure has worked well in the past, but this is the first time I needed to incorporate an update due to changing a NOT NULL constraint. I didn't think this to be the expected behavior for this query, so I thought I'd post and see whether I was thinking along the wrong lines. If this is the expected behavior, then TRUNCATE...ALTER TABLE appears like the way to go in the future.
Thanks, as always, for the info! -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
