Thread

alter table rename bug

Boulat Khakimov <boulat@inet-interactif.com> — 2001-04-26T18:43:24Z

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.



Regards,
Boulat Khakimov

-- 
What goes around, comes around