Обсуждение: BUG #1730: insert into x1.tbl select x2.tbl dont work
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
On Fri, Jun 24, 2005 at 04:08:58PM +0100, Krasimir dimitrov wrote: > > 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; The above statement inserts into archive.code_company... > in my trigger "on delete" in table cms.news > result is: > > aiidatapro.net=# select * from cms.code_company; ...and the above query selects from cms.code_company. Why are you looking at cms.code_company when you inserted into archive.code_company? Are you using inheritance? The following query results seem to imply that. > 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 !!!!! Does archive.code_company inherit cms.code_company? That would explain why inserting into archive.code_company causes records to appear in cms.code_company, and it would explain why those records don't cause a unique constraint violation, since constraint support in inherited tables is documented as being deficient. See the last paragraph of the following page: http://www.postgresql.org/docs/7.4/static/ddl-inherit.html The 8.0 documentation is a bit more descriptive: http://www.postgresql.org/docs/8.0/static/ddl-inherit.html If you don't think inheritance is the problem, then please post a complete example. That is, all SQL statements that somebody could load into an empty database to duplicate the problem. -- Michael Fuhr http://www.fuhr.org/~mfuhr/