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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
