Thread: Re: [HACKERS] Postgresql OO Patch
Tom Lane wrote: > It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers > and maybe pgsql-general. One more time for the <general> mailing list... Hands up if you have objections to the patch I recently submitted for postgresql. It fixes the long standing bit-rot / bug that DELETE and UPDATE don't work on inheritance hierarchies, and it adds the ONLY syntax as mentioned in SQL3 and as implemented by Informix. The downside is it breaks compatibility with the old inheritance syntax. But there is a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT * FROM ONLY foobar". Benefits: *) SQL3 says it. *) Informix does it. *) If you never used inheritance it doesn't affect you. *) Performance is unaffected. *) There is a backwards compatibility mode via SET. *) My own experience says strongly that this will greatly reduce programmer bugs because the default is much more common (laziness usually leads us to discard the "*" to the detriment of future inheritance data model changes.) *) It is more OO since by default a <subclass> IS A <baseclass>. Disadvantage: *) You need to make a one line change to any programs that use inheritance to include the back-compatibility SET mode.
> Tom Lane wrote: > > > It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers > > and maybe pgsql-general. > > One more time for the <general> mailing list... > > Hands up if you have objections to the patch I recently submitted for > postgresql. It fixes the long standing bit-rot / bug that DELETE and > UPDATE don't work on inheritance hierarchies, and it adds the ONLY > syntax as mentioned in SQL3 and as implemented by Informix. The downside > is it breaks compatibility with the old inheritance syntax. But there is > a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes > "SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT * > FROM ONLY foobar". > > Benefits: > *) SQL3 says it. > *) Informix does it. > *) If you never used inheritance it doesn't affect you. > *) Performance is unaffected. > *) There is a backwards compatibility mode via SET. > *) My own experience says strongly that this will greatly reduce > programmer bugs because the default is much more common (laziness > usually leads us to discard the "*" to the detriment of future > inheritance data model changes.) > *) It is more OO since by default a <subclass> IS A <baseclass>. > > Disadvantage: > *) You need to make a one line change to any programs that use > inheritance to include the back-compatibility SET mode. Well, it seems many of us forgot the valid arguments for the change. Matching SQL3 and Informix's behavior is a good thing. Considering how broken our current inheritance implementation is, backward compatibility is not a must, and you have a SET option for that too. Great. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Chris wrote: > > Tom Lane wrote: > > > It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers > > and maybe pgsql-general. --snip-- So it's not just me, I was using examples from Oracal 8 and was have trouble. I started thinking, I was just missing something or maybe just to new to SQL. Ricahrd
Chris writes: > I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and > "SELECT * from foobar" becomes "SELECT * FROM ONLY foobar". This aspect of the patch I wholeheartedly agree on. The rest I'm not sure about -- yet. :) > Benefits: > *) SQL3 says it. That is unfortunately false for the patch in general. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Sun, 21 May 2000, Chris Bitmead wrote: > Peter Eisentraut wrote: > > > > Chris writes: > > > > > I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and > > > "SELECT * from foobar" becomes "SELECT * FROM ONLY foobar". > > > > This aspect of the patch I wholeheartedly agree on. The rest I'm not sure > > about -- yet. :) > > > > > Benefits: > > > *) SQL3 says it. > > I also agree about the usage of ONLY, as long as it follows the official standardized SQL3 spec. About returning multiple types of rows again: I don't see that in SQL3 so far (difficult and time consuming to read). If it were allowed, you might have to specify the level to dig to in the tree. The rows are shared among supertable and subtables. One row in a leaf table has subrows in all its supertables up the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to redefine table* to mean select heterogeneous rows), what row will you get for a row that exists in a leaf? The same row is in all tables between supertable and the leaf. I suppose it would be necessary to have the query check each row and see how far down the tree it goes, or the system keeps track of that and returns the row-type from the table that inserted it. OR, there could be some extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this case, it would only look down into the tree to 3 levels below supertable and you'd never get row-types that are down lower than level 3. Anyhow, I still don't think returning multple row-types is going to happen, not that I have any authority one way or the other! :-) -- Robert B. Easter reaster@comptechnews.com
While SQL3 talks about trees and leaf rows, it's not implemented like that, so all this worrying about digging down trees and leafs is all a bit mute. "Robert B. Easter" wrote: > If it were allowed, you might have to > specify the level to dig to in the tree. The rows are shared among supertable > and subtables. One row in a leaf table has subrows in all its supertables up > the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to > redefine table* to mean select heterogeneous rows), what row will you get for a > row that exists in a leaf? The same row is in all tables between supertable > and the leaf. I suppose it would be necessary to have the query check each row > and see how far down the tree it goes, or the system keeps track of that and > returns the row-type from the table that inserted it. OR, there could be some > extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this > case, it would only look down into the tree to 3 levels below supertable and > you'd never get row-types that are down lower than level 3. Anyhow, I still > don't think returning multple row-types is going to happen, not that I have any > authority one way or the other! :-) > > -- > Robert B. Easter > reaster@comptechnews.com -- Chris Bitmead mailto:chris@bitmead.com http://www.techphoto.org - Photography News, Stuff that Matters
Chris Bitmead wrote: > > > In this > > case, it would only look down into the tree to 3 levels below supertable and > > you'd never get row-types that are down lower than level 3. Anyhow, I still > > don't think returning multple row-types is going to happen, OTOH, I'm pretty sure that original Postgres did allow for it. > > not that I have any authority one way or the other! :-) > > ------------- Hannu
Chris Bitmead wrote: > > While SQL3 talks about trees and leaf rows, it's not implemented like > that, so all this worrying about digging down trees and leafs is all a > bit mute. Moot. ;-) At a minimum, it seems to me, the backend must support the concept of multiple tuples with different attributes at the relation level since concurrency and rollback-ability of ALTER TABLE ADD COLUMN will cause two concurrent transactions to see a single relation with different attributes. It doesn't seem a large leap to support this concept for OO purposes from "leaf" to "base". For "base" to "leaf" type queries, wouldn't it be acceptable to return the base attributes only, as long as the equivalent of run-time type information could be had from the OID? Just curious, Mike Mascari
Mike Mascari wrote: > At a minimum, it seems to me, the backend must support the > concept of multiple tuples with different attributes at the > relation level since concurrency and rollback-ability of ALTER > TABLE ADD COLUMN will cause two concurrent transactions to see a > single relation with different attributes. It doesn't seem a > large leap to support this concept for OO purposes from "leaf" to > "base". For "base" to "leaf" type queries, wouldn't it be > acceptable to return the base attributes only, as long as the > equivalent of run-time type information could be had from the > OID? How are you going to be able to go shape.display() and have it work for a triangle, if the triangle's apex's weren't retrieved?
Chris wrote: > I.e. "SELECT * FROM foobar*" becomes > "SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT * > FROM ONLY foobar". As a user, is this all I need to know? I'd just ask that the documentation be updated simultaneously. I don't know SQL3 or any other vendor's implementation. I'm pretty dependant on the docs to know what I can & can't do, and how to do it. I'm easily confused. -Ron-
> the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to > redefine table* to mean select heterogeneous rows), what row will you get for a > row that exists in a leaf? The same row is in all tables between supertable > and the leaf. I suppose it would be necessary to have the query check each row > and see how far down the tree it goes, or the system keeps track of that and > returns the row-type from the table that inserted it. OR, there could be some > extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this > case, it would only look down into the tree to 3 levels below supertable and > you'd never get row-types that are down lower than level 3. Anyhow, I still > don't think returning multple row-types is going to happen, not that I have any > authority one way or the other! :-) > > -- > Robert B. Easter > reaster@comptechnews.com > Your example is a very good example, that shows, why multiple result sets are needed to get a very good object-oriented system ! Everyone here on this lists should think about: "What do we expect from on object-oriented extension and how can it help me to improve my system". As an example: My software background is Smalltalk and relational- and object-oriented databases. Now I use relational databases and from this technology I use only a small part to do my mapping. After reading all the postings here on the lists I looked at my wrapper and asked myself: how would it benefit from an oo-extension. And the result was pretty much frustrated: - the OID (SEQUENCE's) are useless (ok, I say it again and again). Give PostgreSQL the OID and ask PostgreSQL to return the attributes of this object. Perhaps even with class informations ! PostgreSQL is not able to do that ! Think about this and you see the usage of the OID in perhaps a different way :-) Therefore: for object system you need complete other types of object identification numbers. - query over a hierarchy of classes ! See the example above ! Until you're not able to return multiple sets you get too much garbage or you need to many queries or you need much more disc-space, depending of the way you wrap classes to tables. This feature is a CRITICAL one ! This may push the performance, depending how it is done. - for associations (m:n) I still need additional help tables, but that is ok :-) - no support for tree structures ! - more powerful statements DDL to change the structure of a database ! - no support to inform the client about changes inthe database ! And that's it ! All the other stuff mentioned here are syntactical sugar for people doing object-oriented database queries over pgsql or hoping to structure their work - but I do not see, that it's a real win. Very frustrating ! Marten Feldtmann
Chris Bitmead wrote:
>
>
> > - no support for tree structures !
>
> AGAIN AGREE! Original postgres had a syntax "SELECT* from foo" to get a
> transitive closure on a tree! Why this was removed (argh!) I can only
> guess.
>
This is what I got sneaked into TODO (or at least I think it must be it ;):
EXOTIC FEATURES
* Add sql3 recursive unions
From my reading of SQL3 draft a few years ago I concluded that this was wat it
described
Now they seem to have RECURSIVE VIEWs that are used as follows:
CREATE RECURSIVE VIEW APPLICABLE_ROLES ( GRANTEE, ROLE_NAME, IS_GRANTABLE ) AS
( ( SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS
WHERE GRANTEE IN ( CURRENT_USER, 'PUBLIC' ) )
UNION
( SELECT RAD.GRANTEE, RAD.ROLE_NAME, RAD.IS_GRANTABLE
FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS RAD
JOIN
APPLICABLE_ROLES R
ON
RAD.GRANTEE = R.ROLE_NAME ) );
The definition of the meaning of RECURSIVE is something I should read in the
morning ;~]
---------------------
Hannu