Thread

1. alter table rename and ruminations on referential integrity

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-02-13T21:51:46Z

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

2. Re: alter table rename and ruminations on referential integrity

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-02-14T00:35:02Z

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