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/