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/