alter table rename bug - Mailing list pgsql-bugs

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