Re: OO Patch - Mailing list pgsql-hackers

Peter Eisentraut wrote:
> 
> Chris Bitmead writes:
> 
> [ONLY]
> > For UPDATE and DELETE it is absolutely correct, and useful, not to
> > mention absolutely essential.
> 
> Could you explain how and why, preferably with a concrete example? I am
> still at a loss.

The simple answer is that UPDATE and DELETE should not act different to
SELECT. If SELECT returns a certain set of records with a particular
WHERE clause, then DELETE should delete the same set of records with
identical WHERE clause and UPDATE should UPDATE the same set of records.
Which part of this is tricky?

The complex answer is in your own SQL3 research. Now of course Postgres
is not implemented that way that the SQL3 model seems to imply (a good
thing IMHO). Columns that came from super tables are stored in the most
specific table. But the end result has to conform to the description in
your other posting. I'll comment a little more on your other posting.

> > > the `SELECT **' syntax (bad idea, IMO),
> >
> > Why is it a bad idea (considering that every ODBMS on the planet does
> > this)?
> 
> First of all, ODBMS and [O]RDBMS are not necessarily infinitely 
> compatible concepts. 

Why?

> An ORDBMS is an RDBMS extended with OO'ish features such as
> table inheritance and abstract data types to make data modeling easier 
> for those who like it. 

The custom data type aspect of ORDBMS is a good feature. The inheritance
feature of ORDBMS is IMHO half-baked. Take the class
shape/circle/square example...
CREATE TABLE SHAPE( ..);
CREATE TABLE SQUARE(x1, y1, x2, y2) INHERITS(shape);
CREATE TABLE CIRCLE(x, y, radius) INHERITS(shape);

I can't just go SELECT * FROM SHAPE and call some C++ method to display
the shape on the screen. If I maintain an attribute in SHAPE called
"classname" manually, then I can SELECT * FROM SHAPE, and then do a
separate query on the subclass when I know the type - very inefficient.
Or I can do 3 separate queries. But then I'm hosed when I add a TRIANGLE
type.

What I really want is..

Result r = Query<Shape>.select("SELECT ** FROM SHAPE");
foreach(r, item) {item->display();
}

Which still will work when I add a triangle. I.e. typical polymorphism
code maintenance advantage.

Which is what object databases do or an object relational mapper like
Persistance do. Without that ability I would argue there's very limited
point in having inheritance at all.

> But below it all there's still relational algebra and
> friends. An ODBMS is a paradigm shift to get rid of some restrictions 
> in relational databases, both technical and theoretical, the 
> implication of
> which is that it's no longer a relational database. Please correct me 
> if I'm wrong.

It's no longer a purely relational database true. I think it's always
been a crazy idea that everything should be squeezed into a pure
table/column model.

> Specifically, a query on a relational database always returns a table, 
> and a table is a set of rows with the same number and types of columns. 
> This is a pretty fundamental assumption, and even accounting for the
> possibility that it might be broken somehow is going to be a major 
> effort throughout the entire system.

It's a pretty fundamentally limiting assumption. If you're saying that
this might be a lot of work to fix I think I agree. If you're saying
that you can't see the relational and object models being merged into a
coherent and useful combination then I disagree. I can see no conflict
at all between them. Both models are like seeing half the world. Both
models without the other are limiting.
> Now a question in particular. I understand that this syntax might
> give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
> (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
> (b, c, e) and (b, c, h)?

I don't think I understand this question.

> Finally, it seems that the same effect can be obtained with a UNION 
> query,
> padding with NULLs where necessary and perhaps judicious use of
> CORRESPONDING. What would be wrong with that?

Several things. Firstly, what happens when you introduce TRIANGLE? You
have to rewrite every query in your system. Secondly, what if you have
20 classes in your hierarchy each with 20 different fields. Now you have
a UNION with 400 fields, most of which are NULL.