Re: alter table rename and ruminations on referential integrity

> It appears that the triggers were never updated to now refer to 'old'
> instead of to current_usage when it was renamed, but were still
> associated with 'old' for purposes of dropping them. (i presume this
> is because the triggers were attached to 'old' by oid, which didnt
> change when it was renamed, but the triggers themselves referred to
> current_usage by name, and this wasn't altered?) IF this is the case,
> i'll feel better, as just dropping the 'old' tables will fix the
> problem, otherwise i'm worried about my database state as i've applied
> several upgrades by renaming older versions of tables out of the way,
> and creating new ones (when the change required wasn't within the
> capabilities of alter table), and copying the data from the old table
> to the new table, then dropping the old table.
You basically have this right. The trigger uses the tablename to
make a SPI query to the table while the drop test uses the value in
pg_trigger.tgconstrrelid to do the other drop. Eventually we'll be
using the OID in the trigger as well, but there are a few side steps
to that.

> In these cases, no other tables refer referentially to the table in
> question, except by the triggers the table in question created itself.
> i'm not sure how i'll manage if I ever need to alter a non-leaf table.
You can probably get away with dropping the table you've renamed and using
alter table to re-add the constraint to tables that refer to the new
table of that name.

> Is there a way to temporarily disable triggers for a session, other
> than deleting them, then putting them back when done?
I haven't tried, but you may be able to twiddle pg_trigger.tgenabled.