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/ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
