From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: John Lange <lists@darkcore.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query plan and Inheritance. Weird behavior
Date: Thu, 23 Jan 2003 01:07:47 -0500
Message-ID: <23641.1043302067@sss.pgh.pa.us> (raw)
In-Reply-To: <20030122183939.I5182-100000@megazone23.bigpanda.com>
References: <20030122183939.I5182-100000@megazone23.bigpanda.com>
> On 22 Jan 2003, John Lange wrote:
>> In this way the parent table would not need to know, nor would it care
>> about child tables in any way (just like inheritance in most programming
>> languages). If done this way a select on a parent table would only
>> require the retrieval of a single row and a select on a child table
>> would only require the retrieval of two rows (one in the child table and
>> one in the parent table).
No, it'd require the retrieval of N rows: you're failing to think about
multiple levels of inheritance or multi-parent inheritance, both of
which are supported reasonably effectively by the current model.
My guess is that this scheme would crash and burn just on locking
considerations. (When you want to update a child row, what locks do you
have to get in what order? With pieces of the row scattered through
many tables, it'd be pretty messy.)
You may care to look in the pghackers archives for prior discussions.
The variant scheme that's sounded most interesting to me so far is to
store *all* rows of an inheritance hierarchy in a single physical table.
This'd require giving up multiple inheritance, but few people seem to
use that, and the other benefits (like being able to enforce uniqueness
constraints over the whole hierarchy with just a standard unique index)
seem worth it. No one's stepped up to bat to do the legwork on the idea
yet, though. One bit that looks pretty tricky is ALTER TABLE ADD
COLUMN.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com, lists@darkcore.net
Subject: Re: Query plan and Inheritance. Weird behavior
In-Reply-To: <23641.1043302067@sss.pgh.pa.us>
References: <20030122183939.I5182-100000@megazone23.bigpanda.com> <23641.1043302067@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox