Dave E Martin ([EMAIL PROTECTED]) 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 properly take 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, 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. 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. 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 would probably be infeasable for large tables)? (certain database maintenance operations become very interesting in the presence of referential integrity, perhaps there should be a section in the documentation on this). In extreme cases, I suppose one could just dump the entire database as insert statements with attributes, recreate the database, massage the insert statements as necessary, and execute them in proper order. Sample Code No file was uploaded with this report
