Re: Inheritance - Mailing list pgsql-hackers

On Thu, 2002-09-05 at 11:34, Curt Sampson wrote:
> On 5 Sep 2002, Hannu Krosing wrote:
> 
> > > Oh, I see. Not that this is that big a deal, I think. Given that
> > > it doesn't work correctly at the moment, making it work fast is a
> > > definite second priority, I would think.
> >
> > But choosing an implementation that _can_be_ made to work fast is not.
> 
> I would say it definitely is. A correctly working implementation
> can be replaced. An incorrectly working implementation destroys
> data integrety.
> 
> Which is more important for PostgreSQL? Speed or maintaining data
> integrity?

Both of course. The lack of one often makes the other unusable.

But as MySQL experience suggest, often people select speed over data
integrity. OTOH sometimes you happily accept a 10sec delay in updates to
have data integrity (like when doing a money transfer over internet;)

> > > Not to mention,
> > > there's always the question of what happens to triggers and suchlike
> > > when handed a tuple with extra columns from what it expects, and having
> > > it modify the insert into a different table.
> >
> > IMHO that the trigger should not be aware of underlying implementation -
> > so it needs not worry about modifying the insert into a different table.
> 
> I agree.
> 
> > > The beauty of storing all supertable columns in the supertable itself is
> > > that the behaviour is automatically correct.
> >
> > But "automatically correct" may not be what you want ;)
> >
> > What about trigger that generates a cached printname using function
> > printname(row) that is different for each table - here you definitely do
> > not want to run the function defined for base table for anything
> > inherited.
> 
> Right. But that will be "automatically correct" when you store all
> base data in the base table. It's when you start storing those data
> in other tables that the trigger can get confused.
> 
> Or are you saying that when I insert a row into "just" a child
> table, the trigger shouldn't be invoked on the "parent table"
> portion of that insert? If so, I'd strongly disagree.

Conceptually there are no "portions" of table - the trigger is invoked
on one _tuple_ exactly (pg has only row-level triggers), and each tuple
belongs to only one table regardless how it is implemented internally.

> If that
> trigger is acting as an integrety constraint on the base table,
> you might destroy the table's integrity.

What I try to say is that you should have the same freedom with triggers
that you have with select/insert/update/delete - you must be able to
choose if the trigger is on the parent table ONLY or on parent and all
children. 

And you should be able to override a trigger for child table even if it
is defined on parent as applying to all children - I guess that
overriding by trigger _name_ would be what most people expect.

Suppose you have a table CITIZEN with table-level constraint IS_GOOD
which is defined as kills_not_others(CITIZEN). and there is table
CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY
(...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD so
you must either be able to override the trigger for that table (and its
children) or make sure that the functions used are dynamically mached to
the actual tuple type (header in Relational Model parlance) so that
kills_not_others(MILITARY) will be used, which presents the system
MILITARYs view of the being good ;)

What I'm after here is dynamic (and automatic) row level dispach of the
right function based on row type - so that for rows in CITIZEN or
CIVIL_SERVANT the function kills_not_others(CITIZEN) will be used but
for rows in MILITAY the kills_not_others(MILITARY) is used.

--------- Hannu