Re: [SQL] Proposed Changes to PostgreSQL

[ I trimmed the cc list a bit ]

Chris Bitmead <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)au> writes:
> The proposed changes are....

> 1) An imaginary field in every tuple that tells you the class it came
> from.
> This is useful when you select from table* and want to know which
> relation the object actually came from. It wouldn't be stored on disk,
> and like oid it wouldn't be displayed when you do SELECT *. The field
> would be called classname. So you could have...
> SELECT p.classname, p.name FROM person p;

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. If you wanted the actual name of the relation, you'd need
to join against pg_class. You could argue it either way I suppose;
a name would be more convenient for simple interactive uses, but an
OID would probably be more convenient and efficient for applications
using this feature. I tend to lean towards the programmatic convenience
side --- far more SQL queries are issued by programs than humans.

> 2) Changing the sense of the default for getting inherited tuples.
> Currently you only get inherited tuples if you specify "tablename*".
> This would be changed so that you get all sub-class tuples too by
> default unless you specify "ONLY tablename". There are several
> rationale for this. Firstly this is what Illustra/Informix have
> implemented. Secondly, I believe it is more logical from an OO
> perspective as well as giving a more useful default.

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.

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.

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?

In short, I vote for leaving well enough alone. It's not so badly
wrong as to be intolerable, and the pain of changing looks high.

> Thirdly, there are a whole range of SQL statements that should
> probably be disallowed without including sub-classes. e.g. an ALTER
> TABLE ADD COLUMN that does not include sub-classes is almost certainly
> undesirable.

This is true. We could either silently add *, or reject it ("hey bozo,
have you forgotten that this table has subclasses?"). The reject
option would be more conservative, just in case the admin *has*
forgotten that the table has subclasses --- as a crude analogy,
Unix "rm" doesn't assume "-r" by default ;-). I agree that allowing
an ALTER to make a parent table inconsistent with its children is
very bad news and should be prevented. (Dropping an inherited column
is another example of something we shouldn't allow.)

> 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.

> 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?

> 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.

> 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.
If you do this, you couldn't even guarantee that different subtables
that had studentid columns would have compatible datatypes for those
columns.

> 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?

regards, tom lane