The following bug has been logged online:
Bug reference: 1730
Logged by: Krasimir dimitrov
Email address: kr@aiidatapro.com
PostgreSQL version: 7.4
Operating system: Debian Linux
Description: insert into x1.tbl select x2.tbl dont work
Details:
Hello there,
I use postgresql-7.4(7.4.8-9) on debian with kernel 2.6.11.2
My idea is: when delete from table cms.news trigger insert data in
archive.news I found a bug in Postgres-7.4.
before running:
delete from cms.news where
uid_news='118dd5bd-19be-416f-bf68-f9682147184c';
data in table cms.code_company is:
aiidatapro.net=# select * from cms.code_company;
id_company_coded | uid_news_coded |
fk_id_base_company_coded
------------------+--------------------------------------+------------------
--------
2 | 118dd5bd-19be-416f-bf68-f9682147184c |
2
3 | 118dd5bd-19be-416f-bf68-f9682147184c |
3
(2 rows)
Bug is appear when run
insert into archive.code_company select * from cms.code_company WHERE
uid_news_coded=uid;
in my trigger "on delete" in table cms.news
result is:
aiidatapro.net=# select * from cms.code_company;
id_company_coded | uid_news_coded |
fk_id_base_company_coded
------------------+--------------------------------------+------------------
--------
2 | 118dd5bd-19be-416f-bf68-f9682147184c |
2
3 | 118dd5bd-19be-416f-bf68-f9682147184c |
3
2 | 118dd5bd-19be-416f-bf68-f9682147184c |
2
3 | 118dd5bd-19be-416f-bf68-f9682147184c |
3
but I set unique constraint :
ALTER TABLE cms.code_company ADD CONSTRAINT
code_company_uid_news_coded_key UNIQUE(uid_news_coded,
fk_id_base_company_coded);
This couldn't be happend !!!!!
constraint works when insert:
aiidatapro.net=# insert into cms.code_company
(id_company_coded,uid_news_coded,fk_id_base_company_coded) values
(2,'118dd5bd-19be-416f-bf68-f9682147184c',2);
ERROR: duplicate key violates unique constraint "code_company_pkey"
please check query :
insert into x1.tbl select from x2.tbl
tanks
p.s. I send copy of this mail to pgsql-bugs@postgresql.org