Discussion about inheritance

[Marcelo Soares]

Hello,

I'm using inheritance in my DB with PostgreSQL 7.3.4, and a friend of my
told me that there is a bug in that type of table.

To resume, the problem is:
- a table A have a primary key
- a table B references the primary key of A
- a table C inherits the A fields
- when I insert a tuple in table B that references something inserted at
A, everything is OK.
- when I insert a tuple in table B that references something inserted at
C, a integrity violation occurs.

To me, this looks not like a bug, because the tuple is not at A phisically.

Taking the example of a VIEW: its not possible to reference a column in a
view because the data is not phisically in it.

Here, i put an example where i try to show the "problem" (it is in
portuguese, but I think the names don't seem to be a problem for all):

PostgreSQL 7.3.4

everton=# create table pessoa (id_pessoa serial NOT NULL PRIMARY KEY,
nome character varying(30));

everton=# create table pessoafisica(cpf integer) INHERITS(pessoa);

everton=# create table pessoajuridica(cnpj integer) INHERITS(pessoa);

everton=# create table telefone(id_pessoa integer, telefone integer,
foreign key (id_pessoa) references pessoa (id_pessoa));

everton=# insert into pessoa(nome) values ('Everton');

everton=# insert into pessoafisica(nome, cpf) values ('Alencar', 123);

everton=# insert into pessoajuridica(nome, cnpj) values ('Philler', 999);

everton=# select * from pessoafisica;
id_pessoa | nome | cpf
-----------+---------+-----
3 | Alencar | 123

everton=# select * from pessoajuridica;
id_pessoa | nome | cnpj
-----------+---------+------
4 | Philler | 999

everton=# select * from pessoa;
id_pessoa | nome
-----------+---------
2 | Everton
3 | Alencar
4 | Philler

everton=# insert into telefone(id_pessoa, telefone) values (2, 5410000);
INSERT 22234 1
everton=# insert into telefone(id_pessoa, telefone) values (4, 5413443); ERROR: $1 referential integrity violation - key referenced from
telefone not found in pessoa

Like I said, this is not a problem for me, but I want to know if someone
thinks this can be considered a real problem, or if something will be done
to try to correct (or modify) this operation.

Thanks to all,

Marcelo Soares
Informática - Master Hotéis
ICQ Externo: 19398317
ICQ Interno: 1002
Linux user#: 288006

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Marcelo Soares" <ms*****@masterhoteis.com.br> writes:

To me, this looks not like a bug, because the tuple is not at A phisically.
A lot of people call it a bug because it makes it impossible for them to
use inheritance the way they want to.
Like I said, this is not a problem for me, but I want to know if someone
thinks this can be considered a real problem, or if something will be done
to try to correct (or modify) this operation.

It is likely that it will get changed at some point in the future,
because the consensus seems to be that the other behavior would be more
useful. However, fixing it seems to require building indexes across
multiple tables --- or else a drastic change in the storage
representation of inherited tables --- so it won't happen soon.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend