Proposed Changes to PostgreSQL - Mailing list pgsql-general

Hi,

I've been spending a lot of time lately with gdb and tracing the
back-end seeing if I can understand it enough to make some changes.
I'm starting to actually understand a lot of stuff, so in order
to have some possibility of having my changes accepted, I want to
discuss
them here first. Based on that, I'm going to hopefully make an attempt
at implementation. I have a patch for one of these changes already
if I get the go ahead.

THESE CHANGES DON'T AFFECT YOU IF YOU DON'T USE INHERITANCE.

Speak now about these changes or please, forever hold your peace. Of
course you can comment later if I screw up implementation.

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;
person   | Fred
student  | Bill
employee | Jim
person   | Chris

If you want to know the exact behaviour it is as if every table in the
database had done to it...
ALTER TABLE foo ADD COLUMN classname TEXT;
UPDATE foo SET classname='foo';

Of course this is not how it would be implemented. It is just
reference for how it will appear to work. BTW, this idea was also
in the original berkeley design notes.

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. If a politician
IS a person and I say SELECT * from person, then logically I should
see all the politicians because they are people too (so they claim
:). 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. It seems ashame to have to resort to non-standard SQL
with the "*" syntax in this case when it is really your only
choice. Basicly, wanting ONLY a classname is a far more unusual
choice, and leaving off the "*" is a common error. Fourthly, it seems
out of character for the SQL language to have this single character
operator. The SQL style is to use wordy descriptions of the operators
meaning. "ONLY" fits well here because it describes its own meaning
perfectly whereas to the unitiated, "*" is harder to guess at. While
this change is an incompatibility I hope for those few people using
inheritance they can accept the need to move forward without
over-burden of backwards compatibility.

3) The ability to return different types of rows from a SELECT. This
is to allow implementation of ODBMS functionality where a query could
be required to instantiate objects of differing types with differing
attributes.

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. Since the
current PQ interface which doesn't support this notion would remain
unchanged this wouldn't affect current users.

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.

The reason I think the "*" syntax should take on the new functionality
is because it would be more consistent with what the OQL (object query
language) standard specifies, and also because it seems the more
useful default. Also there is no compatibility reason not to do it.

In addition it would be legal to specify columns that only exist in
sub-classes. For example, if we had

CREATE TABLE person (name TEXT);
CREATE TABLE student (studentid TEXT, faculty TEXT) INHERITS (person);

it would be legal to say...
> SELECT * FROM person;
NAME
----
Fred
Bill

NAME | STUDENTID | FACULTY
--------------------------
Jim  | 23455     | Science
Chris| 45666     | Arts

> SELECT *, studentid FROM person;
NAME
----
Fred
Bill

NAME | STUDENTID
----------------
Jim  | 23455
Chris| 45666

> SELECT *, studentid FROM ONLY person;
ERROR: person does not contain studentid.

> SELECT % FROM person;
NAME
----
Fred
Bill
Jim
Chris

As you can see, it is desirable that psql be modified to be able to
print these differing tuple types. Presumably new column headings will
be printed when a tuple is differing to the previous one. Likely it
will be often desirable to do a
SELECT * FROM person p ORDER BY p.classname;
in order to have all the tuples of a particular type grouped together.

In addition some extenions will be done to the PQ interface to support
these differing return types. The current PQ interface will be left
unchanged and backwards compatible for retrieving rows of a single
type.

Also there should be an settable option that specifies that "*" should
also return the normally ignored columns of oid and classname. This is
so that OO programs that embed SQL into them also get back the oid and
classname which are required for the behind the scenes implementation
of an ODMG client. Something like...

SET SHOW_OID TRUE;
SHOW_CLASSNAME TRUE;

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