Обсуждение: inherited tables failure

Laszlo Csite (ozy@tiszanet.hu) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
inherited tables failure

Long Description
We have two tables inherited one from the other one. If you try to insert from the parent into the child by an "INSERT
INTO"statement then the record is inserted into the child but into the parent too! Therefore in the parent duplicated
rowsappear.  

The other bug is if you delete a row from the parent then it erases from the child too. see the illustration below.


Sample Code

create table try (col1 int4);
create table try1 () inherits (try);
insert into try (col1) values (15);
select * from try1;   --> you get 0 row
select * from try;   --->you get 1 row
insert into try1 select * from try;  --> the answer is 1 row is inserted into try1

select * from try1;   --> you get 1 row
select * from try;   --->you get 2 row !!!!!!!!!!! <-That's wrong

delete from try;
select * from try1;   --> you get 0 row !!!!!!!!!!! <-That's wrong
select * from try;   --->you get 0 row



No file was uploaded with this report

On Thu, 16 Aug 2001 pgsql-bugs@postgresql.org wrote:

> Laszlo Csite (ozy@tiszanet.hu) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> inherited tables failure
>
> Long Description

> We have two tables inherited one from the other one. If you try to
> insert from the parent into the child by an "INSERT INTO" statement
> then the record is inserted into the child but into the parent too!
> Therefore in the parent duplicated rows appear.
>
> The other bug is if you delete a row from the parent then it erases
> from the child too. see the illustration below.

> create table try (col1 int4);
> create table try1 () inherits (try);
> insert into try (col1) values (15);
> select * from try1;   --> you get 0 row
> select * from try;   --->you get 1 row
> insert into try1 select * from try;  --> the answer is 1 row is inserted into try1
>
> select * from try1;   --> you get 1 row
> select * from try;   --->you get 2 row !!!!!!!!!!! <-That's wrong

No, that's the intended behavior.  The default behavior as of 7.1 (I
believe) is that sql queries occur across inheritance trees. So the latter
is select from try and any subtables.  If you only want try, use ONLY
(select * from ONLY try).  There's only one copy of the row, however.

> delete from try;
> select * from try1;   --> you get 0 row !!!!!!!!!!! <-That's wrong
> select * from try;   --->you get 0 row

No, that's also intended. As above, delete from try means delete from
try and any subtables and only means delete from only that table.

pgsql-bugs@postgresql.org writes:
> Long Description
> We have two tables inherited one from the other one. If you try to insert from the parent into the child by an
"INSERTINTO" statement then the record is inserted into the child but into the parent too! Therefore in the parent
duplicatedrows appear.  
> The other bug is if you delete a row from the parent then it erases from the child too. see the illustration below.

This is not a bug; it's the way things are supposed to work in 7.1.
"*" is now the default behavior.  Use ONLY if you want to restrict
scans or updates to the parent table.

            regards, tom lane