Re: [SQL] Proposed Changes to PostgreSQL - Mailing list pgsql-sql

Tom Lane wrote:

> > 1) An imaginary field in every tuple that tells you the class it came
> This is a good idea, but it seems to me that it'd fit into the system
> traditions better if the pseudo-field gave the OID of the source
> relation.  

This was my initial thought too, but then it occured to me that SQL
doesn't normally deal in oids. For example you don't do a DROP TABLE
oid;

OTOH, oids are probably programmatically useful for things like ODBMSs.

What do you think about having both? I know you can go from one to the 
other by joining with pg_class, but that's too inconvenient, and I can't
make up my mind which is the better "system tradition" either.

I'm not overly fussed on this point though.

> Well, mumble.  That would be the cleanest choice if we were designing
> in a green field, but we aren't.  You're talking about breaking every
> single extant Postgres application that uses inheritance, and possibly
> some that don't use it except as a shorthand for making their schemas
> more compact.  (That's not a hypothetical case; I have DBs that use
> schema inheritance but never do SELECT FROM table*.)  I think that's
> a mighty high price to pay for achieving a little more logical
> cleanliness.

Ok, well compatibility is always a contentious thing. But in your case
you are mis-using the inheritance feature.

The question is, are you willing to do the (simple) changes to your
code to cater for the common good? I'm wanting to make postgresql into a
REAL odbms, and this is a stumbling point that will eventually affect
100x
as many users as it does now (I hope :).

We can also leave the old gram.y for people who want to retain
compatibility
for longer.

> There is also a nontrivial performance penalty that would be paid
> for reversing this default, because then every ordinary SQL query
> would suffer the overhead of looking to see whether there are
> child tables for each table named in the query.  That *really*
> doesn't strike me as a good idea.

I can't comment on what the current performance penalty would be, but 
I'm sure this can be optimised to be a completely trivial overhead.
> If Illustra were popular enough to have defined an industry standard
> about inheritance, I might think we should follow their lead --- but
> who else has followed their lead?

Well Informix of course, which is not small potatoes.
> > I would propose that that anytime you do a SELECT * from a base table
> > that you would get back the full rows from those sub tables.
> 
> Frankly: ugh.  This doesn't square with *my* ideas of object
> inheritance.  When you are dealing with something that ISA person,
> you do not really want to hear about any additional properties it may
> have; you are dealing with it as a person and not at any finer grain of
> detail.  That goes double for dealing with whole collections of persons.
> If you want to examine a particular member of the collection and
> dynamically downcast it to some more-specific type, the proposed
> classname/classoid feature will give you the ability to do that;
> but I think it's a mistake to assume that this should happen by default.

This would be the case if the database were the whole world. But it is
not,
it is a repository for applications written in other languages. How can
you
"dynamically downcast to a more specific type" if the database hasn't
returned
the columns of the more specific type? How can I instantiate a C++
object of
type "Student" if the database has only returned to me the data members
of type
"Person"?

> > Since the current PQ interface which doesn't support this notion would
> > remain unchanged this wouldn't affect current users.
> 
> How would you implement this without actually breaking the current
> PQ interface?

By adding new functions for use when you need to access the extra
columns.

> > It's probably also desirable to have a syntax for getting just the
> > columns of the base table when this is desired. Say perhaps SELECT %
> > from table. This would be a performance hack for users of libpq and a
> > functionality difference for users of psql.
> 
> Again, I think you've got the default backwards.  I remind you also
> of something we've been beating on Peter about: psql is an application
> scripting tool, so you don't get to redefine its behavior at whim,
> anymore than you can change libpq's API at whim.

I am less adamant about the default in this scenario than in the "ONLY
table"
scenario. I'm a bit concerned about the fact that this would break
compatibility with OQL standards, but I can live with this.
> > In addition it would be legal to specify columns that only exist in
> > sub-classes. For example,
> > it would be legal to say...
> >> SELECT *, studentid FROM person;
> 
> Yipes.  I really, really, really DON'T like that one.  At the level
> of table person, studentid is unequivocally an invalid column name.

The reason for this is you need some kind of compromise between seeing
every single column (which overwhelms you in psql) and not seeing any
sub-type columns at all.

> If you do this, you couldn't even guarantee that different subtables
> that had studentid columns would have compatible datatypes for those
> columns.

I think you can because postgres won't let you create sub-types with
column of the same name with incompatible data types. In fact it is
this very fact about postgres that makes this feature feasible. 

> > SELECT * FROM person;
> 
> > OID  CLASSNAME NAME
> > -------------------
> > 2344 person    Fred
> > 3445 person    Bill
> 
> > OID  CLASSNAME NAME | STUDENTID | FACULTY
> > -----------------------------------------
> > 2355 student   Jim  | 23455     | Science
> > 5655 student   Chris| 45666     | Arts
> 
> This is not too hard for a person to make sense of, but I think that
> it'd be mighty unwieldy for a program to deal with.  What would the
> libpq-like interface look like, and what would a typical client
> routine look like?

The PQ interface would have a new function something like
PQnfieldsv(PQresult,tuplenum),
so it returns a different number for each tuple.

But the real benefit is not writing "unwieldy" code in C, but ODBMS
style code where you 
can go...
List<Shape> l = query("SELECT * FROM shape");
Shape *s;
for (l.begin(); s = l.get(); l.next())     s.display();

Because if the dbms returns ALL the columns, a C++ runtime system can
properly instantiate subtypes and use polymorphism.