Обсуждение: 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/