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