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
