Thread

1. Postgres Bug (ALTER TABLE problem)

   Boulat Khakimov <boulat@inet-interactif.com> — 2001-04-26T17:31:13Z

   Hi,
   
   I've discovered a bug in Postgres. When you rename 
   a table, the corresponding triggers for that table 
   are not updated.
   
   For example:
   
   CREATE TABLE tblParent (
     ID SERIAL NOT NULL,
     Name text,
     PRIMARY KEY (ID)
   );
    
   
   CREATE TABLE tblChild (
     ID int4 NOT NULL,
     email text,
     FOREIGN KEY (ID) REFERENCES tblParent ON DELETE NO ACTION ON UPDATE
   CASCADE
   );
    
   -----------------------------------------------------------------------------
   -- Create temporary table to transfer  data from old table structure
   -- into new one.
   -- ALTER TABLE tblChild ADD COLUM is not used because it doesnt allow
   things
   -- like  check (fieldname in...) when new columns are added
   --
   CREATE TABLE tblChildTemp (
     ID int4 NOT NULL,
     email text,
     Billed char check (Billed in ('Y','N')) DEFAULT 'N' NOT NULL,
     FOREIGN KEY (ID) REFERENCES tblParent ON DELETE NO ACTION ON UPDATE
   CASCADE
   );          
   
   INSERT INTO tblChildTemp(ID,email)
   SELECT ID,email FROM tblChild;
    
   DROP table tblChild;
    
   ALTER TABLE tblChildTemp RENAME TO tblChild;
    
   ---------------------------------------------------------------------------
   -- Here is where the problem starts
   UPDATE tblParent SET name='Mary'; 
   
   
   ERROR:  RI constraint <unnamed> cannot find table tblchildtemp   
   
   
   If I do "SELECT * FROM pg_trigger";
   
    tgrelid |           tgname            | tgfoid | tgtype | tgenabled |
   tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
   tginitdeferred | tgnargs | tgattr |                               
   tgargs
   ---------+-----------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+-----------------------------------------------------------------------
       1260 | pg_sync_pg_pwd              |     12 |     29 | t         |
   f              |              |             0 | f
           | f              |       0 |        |
     349149 | RI_ConstraintTrigger_349162 |   1644 |     21 | t         |
   t              | <unnamed>    |        349105 | f
           | f              |       6 |        |
   <unnamed>\000tblchildtemp\000tblparent\000UNSPECIFIED\000id\000id\000
     349105 | RI_ConstraintTrigger_349164 |   1654 |      9 | t         |
   t              | <unnamed>    |        349149 | f
           | f              |       6 |        |
   <unnamed>\000tblchildtemp\000tblparent\000UNSPECIFIED\000id\000id\000
     349105 | RI_ConstraintTrigger_349166 |   1647 |     17 | t         |
   t              | <unnamed>    |        349149 | f
           | f              |       6 |        |
   <unnamed>\000tblchildtemp\000tblparent\000UNSPECIFIED\000id\000id\000
   (4 rows)  
   
   
   I can see that the triggers were not updated, they are still using
   tblchildtemp, 
   altho it got renamed.
   
   
   Does anyone know a way to fix that problem?
   Any Feedback would be appreciated...
   
   
   Regards,
   Boulat Khakimov
   
   -- 
   What goes around, comes around
   
   

2. Re: Postgres Bug (ALTER TABLE problem)

   Joel Burton <jburton@scw.org> — 2001-04-26T18:59:54Z

   On Thu, 26 Apr 2001, Boulat Khakimov wrote:
   
   > Hi,
   > 
   > I've discovered a bug in Postgres. When you rename 
   > a table, the corresponding triggers for that table 
   > are not updated.
   
   Yep.
   
   Use ALTER TABLE ADD CONSTRAINT to add 'em back in.
   
   More info can be found in the Ref Int tutorial I just submitted at
   techdocs.postgresql.org.
   
   HTH,
   -- 
   Joel Burton   <jburton@scw.org>
   Director of Information Systems, Support Center of Washington