Re: update before drop causes OID problems in transaction?

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