Thread: alter table rename and ruminations on referential integrity

Dave E Martin (xxiii@cyberdude.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
alter table rename and ruminations on referential integrity

Long Description
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2

I suspect symptoms of this have been reported in various other bugs. It appears that alter table rename does not
properlytake care of triggers. 
I had done the following:

alter table current_usage rename to old

create table current_usage (...);

update table current_usage ....

ERROR:  constraint <unnamed>: table current_usage does not have an attribute ...

drop table old;

lots of messages about implicitly dropping triggers from other tables

update table current_usage ... (same update as above)

UPDATE 1

It appears that the triggers were never updated to now refer to 'old' instead of to current_usage when it was renamed,
butwere still associated with 'old' for purposes of dropping them. (i presume this is because the triggers were
attachedto 'old' by oid, which didnt change when it was renamed, but the triggers themselves referred to current_usage
byname, 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
tablesout of the way, and creating new ones (when the change required wasn't within the capabilities of alter table),
andcopying the data from the old table to the new table, then dropping the old table. 

In these cases, no other tables refer referentially to the table in question, except by the triggers the table in
questioncreated itself. i'm not sure how i'll manage if I ever need to alter a non-leaf table. 

Is there a way to temporarily disable triggers for a session, other than deleting them, then putting them back when
done?

can drop/create table occur inside transactions, and thus defer referential checking until we're done (although this
wouldprobably be infeasable for large tables)? 

(certain database maintenance operations become very interesting in the presence of referential integrity, perhaps
thereshould be a section in the documentation on this). In extreme cases, I suppose one could just dump the entire
databaseas insert statements with attributes, recreate the database, massage the insert statements as necessary, and
executethem in proper order. 

Sample Code


No file was uploaded with this report

> 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.