Re: BUG #1730: insert into x1.tbl select x2.tbl dont work

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/