Re: Newbie Inheritance Question

Hi All,

Can anyone tell me now I might extend an existing record in a parent table
to have values in an inherited table? An example would be a table of all
people (person), and a table of users (usr) that inherits from person. Say
there's an existing record in person, and I want to use that same record
to make them a member of the usr table, as well. Is this possible, other
than by deleting the record from person and re-inserting it into usr?

Thanks,

David

--
David Wheeler AIM: dwTheory
David(at)Wheeler(dot)net ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org

Unless you have some kind of development environment that requires it, you
should avoid using inheritance. Besides the fact that I recently asked on
the list on how to get indexes to work when selecting from all the
inherited tables at once and still have not received an answer,
inheritance is just a bad way to structure your database since it makes it
a huge pain to manage which entry should be in which table. One of the
huge risks of using inheritance as a distinguishing attribute is that if
you need to move rows between tables, you have to wrap the move in a
transaction to make sure you don't delete something that didn't get moved
probably or that you don't have the same row in two different tables.

Instead of inheritance, you should have a table such as person_type, which
describes the category that they are in which you were formally
distinguishing between using inheritance. If you need to add additional
columns just for one category, this can be done easily with an extra
table, and you can always use views if you don't want to join tables
manually a lot or if you need old code to remain compatible with the new
layout. By having a column as your category attribute, you also benefit
from being able to easily select from child-1 and child-2 without
selecting rows from the parent table or child-3.

-Edwin

On Fri, 22 Jun 2001, David Wheeler wrote:

> Hi All,
>
> Can anyone tell me now I might extend an existing record in a parent table
> to have values in an inherited table? An example would be a table of all
> people (person), and a table of users (usr) that inherits from person. Say
> there's an existing record in person, and I want to use that same record
> to make them a member of the usr table, as well. Is this possible, other
> than by deleting the record from person and re-inserting it into usr?
>
> Thanks,
>
> David
>
>

--
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org

mQGiBDj905MRBACm5QP3Z4mILmXUsLN8bnhDgoG6HQa4mn/wQsaLApOwN5/802Y8
5yAIZiWH4I2iMqZdqwBrnQXQsoRBWIVHueYoVbtLcLdelt2bW2YI16JYkOMVOQ5i
EBd1gP93hsl6Te4sOPQ/X2Oms+MYjMW7u5HPQIv6lIwgjE7MIz19/b+VbwCg671x
9MXIGx9ewF2L4WtqjciaVnkD/jitamiI2bbns37xE2OFWhZSBm+WpMa+HHNGTFtq
jwCByZ/t3DT0mKexqvo3EDVha7WgQ3yfzo1GxyAw8x9/9UEVEWlACidGM3vny27I
SjqbqKG5IMD5lEU1vEZVZJOe6BFWiAHbRoaBMk/tLCHKZTZOoRywIdCOyyrKBa5E
/pKuA/9qObVrvyqiA+RkGHVKH6/R5SKo+YuRTQNmQ0eQtSsTA0Xgl/a72W2B6q7V
jeUEaW9CTRTCY+0UFNqq368tSnRBCEsQtLqYB28dnaKZ35AbPNLR+6Fsc1VaKNAS
mz9ZhnXtQk3y06skluwdsvjKcLfSLCvgVPMo/fepwRuzfuUwfLQ0RWR3aW4gR3J1
YmJzIChSYWNrc3BhY2UuY29tKSA8ZWdydWJic0ByYWNrc3BhY2UuY29tPohWBBMR
AgAWBQI4/dOTBAsKBAMDFQMCAxYCAQIXgAAKCRDXgAoO6t/5UcX7AJ9GlYhmZ+Jc
86FI2+ZouFMemn46IACdH1SclQjEDvApLWYAn6dncmRqgxi5Ag0EOP3UHRAIAMTU
Vqbau9aUHSzieDjXVWUUDXitJqtzpPAhgsQWKBqlgSqZtSPr5DN2+OeobcrWhspX
+S6VwF/U0iXPTZntng4rN2Y3oUgXjZopjLnfNdWcULvSql8W1JaX/zAc9oQ9uQ8B
cc/buFe1ayC3i0rWyTE1bE1A+Lvn8SdeCBEMyp+8Rq3En0hRBitcY4FpEt4ADssM
vB4Z71CyBWm9YYD0z9qBteaaaU82+gnoLcryXhc5FSW5HMolMKgHHWmXbMCqMGvH
ME0QNRqekewqErW5F6L0Hpf0pPVVXZlcAAmwTJDAdKsZrYB1AEojzv7y6ssf+E2Z
oJuKOjzfHBIKwDRIzX8AAwUH/1Y/jqxtJAfLdto3L9XRGXt9zCS5mYCNhEjzkgvE
OiqWGJi6g54Umiwf1oizugZcfnbN3l9NHReN37OburXMcNGE754uOatwvfbNny0o
W2WCs5GGkjslBe5Xc8a4wMyyr9Dt5NR7muZopEx99W7vHH0gxqXyzcAnkMFcbhdp
86jtot+w1BOa26wjj0keQi0EhbXr5GOTMaHk2avXhzfLYvfVknjYCtScw+4tvtfi
n2e4NGzXQxXejHV9uPdDEin7ts1OjmSwB1oXsYVjAU7ZhHQOwDlw5BoUGAorMY0i
fHtx16IPwsD+06HFoz9W36iBXuR8GmFXBOiZv8fuMHhTPlOIRgQYEQIABgUCOP3U
HQAKCRDXgAoO6t/5USJkAKDOQAywN/ZLijImMIge4ylut9xYyACggOO0sp8QS/ea
vFDsdhwocmVhGbo=
=qOGJ
-----END PGP PUBLIC KEY BLOCK-----

On Fri, 22 Jun 2001, Edwin Grubbs wrote:

> Unless you have some kind of development environment that requires it, you
> should avoid using inheritance. Besides the fact that I recently asked on
> the list on how to get indexes to work when selecting from all the
> inherited tables at once and still have not received an answer,
> inheritance is just a bad way to structure your database since it makes it
> a huge pain to manage which entry should be in which table. One of the
> huge risks of using inheritance as a distinguishing attribute is that if
> you need to move rows between tables, you have to wrap the move in a
> transaction to make sure you don't delete something that didn't get moved
> probably or that you don't have the same row in two different tables.
>
> Instead of inheritance, you should have a table such as person_type, which
> describes the category that they are in which you were formally
> distinguishing between using inheritance. If you need to add additional
> columns just for one category, this can be done easily with an extra
> table, and you can always use views if you don't want to join tables
> manually a lot or if you need old code to remain compatible with the new
> layout. By having a column as your category attribute, you also benefit
> from being able to easily select from child-1 and child-2 without
> selecting rows from the parent table or child-3.
>
> -Edwin
>

If you want to stick to inheritance (which I don't see necessarily as a bad idea) the correct think would be to remove the entry from the parent and insert it in the corresponding child table.
So either you use inheritance and let Pg transparently take care of the "relationship" between parent-children or create the tables with id's so you can connect the corresponding entries with joins to implement the relationship yourself.

I guess things get hairy with when you have multiple inheritance, which doesn't seem to be your case.

cheers,
thalis

ps what was the question about "how to get indexes to work when selecting from all the inherited tables at once"?

> On Fri, 22 Jun 2001, David Wheeler wrote:
>
> > Hi All,
> >
> > Can anyone tell me now I might extend an existing record in a parent table
> > to have values in an inherited table? An example would be a table of all
> > people (person), and a table of users (usr) that inherits from person. Say
> > there's an existing record in person, and I want to use that same record
> > to make them a member of the usr table, as well. Is this possible, other
> > than by deleting the record from person and re-inserting it into usr?
> >
> > Thanks,
> >
> > David
> >
> >
>
> --
> -----BEGIN PGP PUBLIC KEY BLOCK-----
> Version: GnuPG v1.0.4 (GNU/Linux)
> Comment: For info see http://www.gnupg.org
>
> mQGiBDj905MRBACm5QP3Z4mILmXUsLN8bnhDgoG6HQa4mn/wQsaLApOwN5/802Y8
> 5yAIZiWH4I2iMqZdqwBrnQXQsoRBWIVHueYoVbtLcLdelt2bW2YI16JYkOMVOQ5i
> EBd1gP93hsl6Te4sOPQ/X2Oms+MYjMW7u5HPQIv6lIwgjE7MIz19/b+VbwCg671x
> 9MXIGx9ewF2L4WtqjciaVnkD/jitamiI2bbns37xE2OFWhZSBm+WpMa+HHNGTFtq
> jwCByZ/t3DT0mKexqvo3EDVha7WgQ3yfzo1GxyAw8x9/9UEVEWlACidGM3vny27I
> SjqbqKG5IMD5lEU1vEZVZJOe6BFWiAHbRoaBMk/tLCHKZTZOoRywIdCOyyrKBa5E
> /pKuA/9qObVrvyqiA+RkGHVKH6/R5SKo+YuRTQNmQ0eQtSsTA0Xgl/a72W2B6q7V
> jeUEaW9CTRTCY+0UFNqq368tSnRBCEsQtLqYB28dnaKZ35AbPNLR+6Fsc1VaKNAS
> mz9ZhnXtQk3y06skluwdsvjKcLfSLCvgVPMo/fepwRuzfuUwfLQ0RWR3aW4gR3J1
> YmJzIChSYWNrc3BhY2UuY29tKSA8ZWdydWJic0ByYWNrc3BhY2UuY29tPohWBBMR
> AgAWBQI4/dOTBAsKBAMDFQMCAxYCAQIXgAAKCRDXgAoO6t/5UcX7AJ9GlYhmZ+Jc
> 86FI2+ZouFMemn46IACdH1SclQjEDvApLWYAn6dncmRqgxi5Ag0EOP3UHRAIAMTU
> Vqbau9aUHSzieDjXVWUUDXitJqtzpPAhgsQWKBqlgSqZtSPr5DN2+OeobcrWhspX
> +S6VwF/U0iXPTZntng4rN2Y3oUgXjZopjLnfNdWcULvSql8W1JaX/zAc9oQ9uQ8B
> cc/buFe1ayC3i0rWyTE1bE1A+Lvn8SdeCBEMyp+8Rq3En0hRBitcY4FpEt4ADssM
> vB4Z71CyBWm9YYD0z9qBteaaaU82+gnoLcryXhc5FSW5HMolMKgHHWmXbMCqMGvH
> ME0QNRqekewqErW5F6L0Hpf0pPVVXZlcAAmwTJDAdKsZrYB1AEojzv7y6ssf+E2Z
> oJuKOjzfHBIKwDRIzX8AAwUH/1Y/jqxtJAfLdto3L9XRGXt9zCS5mYCNhEjzkgvE
> OiqWGJi6g54Umiwf1oizugZcfnbN3l9NHReN37OburXMcNGE754uOatwvfbNny0o
> W2WCs5GGkjslBe5Xc8a4wMyyr9Dt5NR7muZopEx99W7vHH0gxqXyzcAnkMFcbhdp
> 86jtot+w1BOa26wjj0keQi0EhbXr5GOTMaHk2avXhzfLYvfVknjYCtScw+4tvtfi
> n2e4NGzXQxXejHV9uPdDEin7ts1OjmSwB1oXsYVjAU7ZhHQOwDlw5BoUGAorMY0i
> fHtx16IPwsD+06HFoz9W36iBXuR8GmFXBOiZv8fuMHhTPlOIRgQYEQIABgUCOP3U
> HQAKCRDXgAoO6t/5USJkAKDOQAywN/ZLijImMIge4ylut9xYyACggOO0sp8QS/ea
> vFDsdhwocmVhGbo=
> =qOGJ
> -----END PGP PUBLIC KEY BLOCK-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

On Fri, 22 Jun 2001, Thalis A. Kalfigopoulos wrote:

> If you want to stick to inheritance (which I don't see necessarily as a bad idea) the correct think would be to remove the entry from the parent and insert it in the corresponding child table.
> So either you use inheritance and let Pg transparently take care of the "relationship" between parent-children or create the tables with id's so you can connect the corresponding entries with joins to implement the relationship yourself.
>
> I guess things get hairy with when you have multiple inheritance, which doesn't seem to be your case.
>
>
> cheers,
> thalis
>
> ps what was the question about "how to get indexes to work when selecting from all the inherited tables at once"?
>

The problem I've had with using indexes and inherited tables is that
selecting from all the inherited tables with "parent*" does not use any
indexes even though selecting from "parent" or "child" will use the proper
indexes.

SELECT *
FROM parent*
WHERE id = 123;

Edwin Grubbs <egrubbs(at)rackspace(dot)com> writes:
> The problem I've had with using indexes and inherited tables is that
> selecting from all the inherited tables with "parent*" does not use any
> indexes even though selecting from "parent" or "child" will use the proper
> indexes.

Update to 7.1.

regards, tom lane