Re: Postgresql OO Patch
Re: Postgresql OO Patch
От:
"Robert B. Easter" <reaster@comptechnews.com>
Дата:
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
Re: [GENERAL] Re: Postgresql OO Patch
От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
> 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 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 IS A . > > 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
Re: [GENERAL] Re: Postgresql OO Patch
От:
Marten Feldtmann <marten@feki.toppoint.de>
Дата:
> 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
Re: [GENERAL] Re: Postgresql OO Patch
От:
Marten Feldtmann <marten@feki.toppoint.de>
Дата:
> > > > Therefore: for object system you need complete other types of object > > identification numbers. > > I agree, that's why I have suggested an implied super-class "Object" for > all postgresql objects. Then you could do "SELECT ** FROM object WHERE > oid=?". The ability to place an index over sub-class hierarchies (in > this case oid for all objects) would get the good performance. I can not believe, that this will result in a good performance. This column (object identifier) would need an index to cover ALL objects ... and this index will be growing and now image a system with about 1.000.000 objects and now try to insert a new object. Indices on such large mount of value maybe a problem. On the other hand: the solution you mentioned can be done without an implied table - which would be a special solution. The application can create the "super"-table and should be responsible for it. > > Actually, postgres can have arrays of oids which is the ODBMS way of > handling associations. Last I looked there are some contrib functions > for doing things like ... > > CREATE TABLE foo( bar [] ); > CREATE TABLE bar( ... etc); > SELECT bar.** from bar, foo where array_in(bar.oid, foo.bar) and > foo.oid=?". In other words, to retrieve all the objects in a list. > (forget the actual function name). Have you ever create a 1:n association with about 800 entries ? Actually I do not know, how many entries such an array may have. Unlimited ? How do I remove an entry, how do I delete an entry. I may have a closer look at that. > > - no support to inform the client about changes inthe database ! > > Havn't even looked at that. > But here again an active system may be build on top of the system we already have: - update, insert, deletes are catched via triggers (on commit) these trigger functions do retrieve the object-id of the objects changed and write the result into a special table. - another software has notification on this special table and managed the ip-commuication to the clients. Marten
OO Patch
От:
Chris <chris@bitmead.com>
Дата:
I'm resubmitting this patch from a while ago, now that 7.0 is out. If
you cast your minds back, this patch allows update and delete to work on
inheritance hierarchies just like it now works on select. It also uses
the Informix/Illustra model for subclasses - i.e. "ONLY", as was
discussed at length before.
Please point out anything I've screwed up so I can post a final version.
In particular I forgot where you change the initdb db version thingy,
but I don't want to do that anyway till everything else is correct.? pgsql/src/ID
? pgsql/src/config.log
? pgsql/src/config.cache
? pgsql/src/config.status
? pgsql/src/nohup.out
? pgsql/src/GNUmakefile
? pgsql/src/Makefile.global
? pgsql/src/backend/1
? pgsql/src/backend/catalog/genbki.sh
? pgsql/src/backend/port/Makefile
? pgsql/src/backend/utils/Gen_fmgrtab.sh
? pgsql/src/bin/pg_dump/Makefile
? pgsql/src/bin/pg_version/Makefile
? pgsql/src/bin/pgtclsh/mkMakefile.tcldefs.sh
? pgsql/src/bin/pgtclsh/mkMakefile.tkdefs.sh
? pgsql/src/bin/psql/Makefile
? pgsql/src/include/version.h
? pgsql/src/include/config.h
? pgsql/src/interfaces/ecpg/lib/Makefile
? pgsql/src/interfaces/ecpg/preproc/Makefile
? pgsql/src/interfaces/jdbc/postgresql.jar
? pgsql/src/interfaces/jdbc/example/psql.class
? pgsql/src/interfaces/jdbc/postgresql/DriverClass.java
? pgsql/src/interfaces/jdbc/postgresql/DriverClass.class
? pgsql/src/interfaces/jdbc/postgresql/Connection.class
? pgsql/src/interfaces/jdbc/postgresql/Field.class
? pgsql/src/interfaces/jdbc/postgresql/PG_Stream.class
? pgsql/src/interfaces/jdbc/postgresql/Driver.class
? pgsql/src/interfaces/jdbc/postgresql/ResultSet.class
? pgsql/src/interfaces/jdbc/postgresql/fastpath/Fastpath.class
? pgsql/src/interfaces/jdbc/postgresql/fastpath/FastpathArg.class
? pgsql/src/interfaces/jdbc/postgresql/geometric/PGbox.class
? pgsql/src/interfaces/jdbc/postgresql/geometric/PGpoint.class
? pgsql/src/interfaces/jdbc/postgresql/geometric/PGcircle.class
? pgsql/src/interfaces/jdbc/postgresql/geometric/PGline.class
? pgsql/src/interfaces/jdbc/postgresql/geometric/PGlseg.class
? pgsql/src/interfaces/jdbc/postgresql/geometric/PGpath.class
? pgsql/src/interfaces/jdbc/postgresql/geometric/PGpolygon.class
? pgsql/src/interfaces/jdbc/postgresql/jdbc2/ResultSet.class
? pgsql/src/interfaces/jdbc/postgresql/jdbc2/Connection.class
? pgsql/src/interfaces/jdbc/postgresql/jdbc2/ResultSetMetaData.class
? pgsql/src/interfaces/jdbc/postgresql/jdbc2/DatabaseMetaData.class
? pgsql/src/interfaces/jdbc/postgresql/jdbc2/Statement.class
? pgsql/src/interfaces/jdbc/postgresql/jdbc2/PreparedStatement.class
? pgsql/src/interfaces/jdbc/postgresql/jdbc2/CallableStatement.class
? pgsql/src/interfaces/jdbc/postgresql/largeobject/LargeObjectManager.class
? pgsql/src/interfaces/jdbc/postgresql/largeobject/LargeObject.class
? pgsql/src/interfaces/jdbc/postgresql/util/PSQLException.class
? pgsql/src/interfaces/jdbc/postgresql/util/UnixCrypt.class
? pgsql/src/interfaces/jdbc/postgresql/util/Serialize.class
? pgsql/src/interfaces/jdbc/postgresql/util/PGobject.class
? pgsql/src/interfaces/jdbc/postgresql/util/PGtokenizer.class
? pgsql/src/interfaces/jdbc/postgresql/util/PGmoney.class
? pgsql/src/interfaces/libpgeasy/Makefile
? pgsql/src/interfaces/libpgtcl/Makefile
? pgsql/src/interfaces/libpq/Makefile
? pgsql/src/interfaces/libpq++/Makefile
? pgsql/src/interfaces/odbc/GNUmakefile
? pgsql/src/interfaces/odbc/Makefile.global
? pgsql/src/pl/plpgsql/src/Makefile
? pgsql/src/pl/plpgsql/src/mklang.sql
? pgsql/src/pl/tcl/mkMakefile.tcldefs.sh
? pgsql/src/test/regress/GNUmakefile
? pgsql/src/test/regress/x.x
? pgsql/src/test/regress/nohup.out
? pgsql/src/test/regress/sql/inherit.sql
Index: pgsql/doc/FAQ_DEV
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/FAQ_DEV,v
retrieving revision 1.7
diff -c -r1.7 FAQ_DEV
*** pgsql/doc/FAQ_DEV 1999/12/24 16:46:11 1.7
--- pgsql/doc/FAQ_DEV 2000/05/17 15:05:24
***************
*** 90,105 ****
M-x set-variable tab-width
or
; Cmd to set tab stops &etc for working with PostgreSQL code
! (defun pgsql-mode ()
! "Set PostgreSQL C indenting conventions in current buffer."
! (interactive)
! (c-mode) ; necessary to make c-set
! -offset local!
! (setq tab-width 4) ; already buffer-local
! ; (setq comment-column 48) ; already buffer-local
! (c-set-style "bsd")
! (c-set-offset 'case-label '+)
! )
and add this to your autoload list (modify file path in macro):
--- 90,103 ----
M-x set-variable tab-width
or
; Cmd to set tab stops &etc for working with PostgreSQL code
! (c-add-style "pgsql"
! '("bsd"
! (indent-tabs-mode . t)
! (c-basic-offset . 4)
! (tab-width . 4)
! (c-offsets-alist .
! ((case-label . +))))
! t) ; t = set this mode on
and add this to your autoload list (modify file path in macro):
Index: pgsql/doc/src/sgml/advanced.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/advanced.sgml,v
retrieving revision 1.12
diff -c -r1.12 advanced.sgml
*** pgsql/doc/src/sgml/advanced.sgml 2000/05/02 20:01:51 1.12
--- pgsql/doc/src/sgml/advanced.sgml 2000/05/17 15:05:25
***************
*** 60,73 ****
! For example, the following query finds
! all the cities that are situated at an attitude of 500ft or higher:
!
!
! SELECT name, altitude
! FROM cities
! WHERE altitude > 500;
+----------+----------+
|name | altitude |
+----------+----------+
--- 60,79 ----
!
! For example, the following query finds the names of all cities,
! including state capitals, that are located at an altitude
! over 500ft, the query is:
!
!
! SELECT c.name, c.altitude
! FROM cities c
! WHERE c.altitude > 500;
!
!
! which returns:
+
+----------+----------+
|name | altitude |
+----------+----------+
***************
*** 75,97 ****
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
!
!
!
! On the other hand, to find the names of all cities,
! including state capitals, that are located at an altitude
! over 500ft, the query is:
!
!
! SELECT c.name, c.altitude
! FROM cities* c
! WHERE c.altitude > 500;
!
- which returns:
-
-
+----------+----------+
|name | altitude |
+----------+----------+
--- 81,101 ----
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! |Madison | 845 |
! +----------+----------+
!
!
!
! On the other hand, the following query finds
! all the cities, but not capital cities
! that are situated at an attitude of 500ft or higher:
!
!
! SELECT name, altitude
! FROM ONLY cities
! WHERE altitude > 500;
+----------+----------+
|name | altitude |
+----------+----------+
***************
*** 99,108 ****
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! |Madison | 845 |
! +----------+----------+
!
Here the "*" after cities indicates that the query should
be run over cities and all classes below cities in the
inheritance hierarchy. Many of the commands that we
--- 103,134 ----
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
!
!
!
+ <<<<<<< advanced.sgml
+ Here the ONLY before cities indicates that the query should
+ be run over only cities and not classes below cities in the
+ inheritance hierarchy. Many of the commands that we
+ have already discussed -- SELECT,
+ UPDATE and DELETE --
+ support this ONLY notation.
+
+
+ Deprecated: In previous versions of postgres, the default was not to
+ get access to child classes. By experience this was found to be error
+ prone. Under the old syntax, to get the sub-classes you append "*"
+ to the table name. For example
+
+ SELECT * from cities*;
+
+ This old behaviour is still available by using a SET command...
+
+ SET EXAMINE_SUBCLASS TO 'on';
+
+
+ =======
Here the "*" after cities indicates that the query should
be run over cities and all classes below cities in the
inheritance hierarchy. Many of the commands that we
***************
*** 111,116 ****
--- 137,143 ----
support this inheritance notation using "*" as do other commands like
ALTER.
+ >>>>>>> 1.12
Index: pgsql/doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.5
diff -c -r2.5 catalogs.sgml
*** pgsql/doc/src/sgml/catalogs.sgml 2000/02/17 03:39:39 2.5
--- pgsql/doc/src/sgml/catalogs.sgml 2000/05/17 15:05:27
***************
*** 191,196 ****
--- 191,198 ----
2=main memory */
int2vector relkey /* - unused */
oidvector relkeyop /* - unused */
+ bool relhassubclass /* does the class have a subclass?
+ */
aclitem relacl[1] /* access control lists */
.fi
.nf M
Index: pgsql/doc/src/sgml/inherit.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/inherit.sgml,v
retrieving revision 1.7
diff -c -r1.7 inherit.sgml
*** pgsql/doc/src/sgml/inherit.sgml 2000/05/02 20:01:51 1.7
--- pgsql/doc/src/sgml/inherit.sgml 2000/05/17 15:05:27
***************
*** 41,46 ****
--- 41,48 ----
+ <<<<<<< inherit.sgml
+ =======
For example, the following query finds
all the cities that are situated at an attitude of 500ft or higher:
***************
*** 57,75 ****
! On the other hand, to find the names of all cities,
including state capitals, that are located at an altitude
over 500ft, the query is:
SELECT c.name, c.altitude
FROM cities* c
WHERE c.altitude > 500;
which returns:
name | altitude
-----------+----------
--- 59,119 ----
+ >>>>>>> 1.7
! For example, the following query finds the names of all cities,
including state capitals, that are located at an altitude
over 500ft, the query is:
+ <<<<<<< inherit.sgml
+
+ SELECT c.name, c.altitude
+ FROM cities c
+ =======
SELECT c.name, c.altitude
FROM cities* c
+ >>>>>>> 1.7
WHERE c.altitude > 500;
which returns:
+
+ +----------+----------+
+ |name | altitude |
+ +----------+----------+
+ |Las Vegas | 2174 |
+ +----------+----------+
+ |Mariposa | 1953 |
+ +----------+----------+
+ |Madison | 845 |
+ +----------+----------+
+
+
+
+
+ On the other hand, the following query finds
+ all the cities, but not capital cities
+ that are situated at an attitude of 500ft or higher:
+
+
+ SELECT name, altitude
+ FROM ONLY cities
+ WHERE altitude > 500;
+
+ <<<<<<< inherit.sgml
+ +----------+----------+
+ |name | altitude |
+ +----------+----------+
+ |Las Vegas | 2174 |
+ +----------+----------+
+ |Mariposa | 1953 |
+ +----------+----------+
+
+
+
+ =======
name | altitude
-----------+----------
***************
*** 77,90 ****
--- 121,157 ----
Mariposa | 1953
Madison | 845
+ >>>>>>> 1.7
+ <<<<<<< inherit.sgml
+ Here the ONLY before cities indicates that the query should
+ be run over only cities and not classes below cities in the
+ =======
Here the "*" after cities indicates that the query should
be run over cities and all classes below cities in the
+ >>>>>>> 1.7
inheritance hierarchy. Many of the commands that we
have already discussed -- SELECT,
UPDATE and DELETE --
+ <<<<<<< inherit.sgml
+ support this ONLY notation.
+
+
+ Deprecated: In previous versions of postgres, the default was not to
+ get access to child classes. By experience this was found to be error
+ prone. Under the old syntax, to get the sub-classes you append "*"
+ to the table name. For example
+
+ SELECT * from cities*;
+
+ This old behaviour is still available by using a SET command...
+
+ SET EXAMINE_SUBCLASS TO 'on';
+
+ =======
support this "*" notation, as do others, like
ALTER TABLE.
+ >>>>>>> 1.7
Index: pgsql/doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.12
diff -c -r1.12 alter_table.sgml
*** pgsql/doc/src/sgml/ref/alter_table.sgml 2000/04/11 14:43:54 1.12
--- pgsql/doc/src/sgml/ref/alter_table.sgml 2000/05/17 15:05:28
***************
*** 23,35 ****
1999-07-20
! ALTER TABLE table [ * ]
ADD [ COLUMN ] column type
! ALTER TABLE table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
! ALTER TABLE table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
--- 23,35 ----
1999-07-20
! ALTER TABLE [ ONLY ]table [ * ]
ADD [ COLUMN ] column type
! ALTER TABLE [ ONLY ]table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
! ALTER TABLE [ ONLY ]table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
***************
*** 176,192 ****
! * following a name of a table indicates that the statement
! should be run over that table and all tables below it in the
inheritance hierarchy;
! by default, the attribute will not be added to or renamed in any of the subclasses.
! This should always be done when adding or modifying an attribute in a
! superclass. If it is not, queries on the inheritance hierarchy
such as
! SELECT NewColumn FROM SuperClass*
will not work because the subclasses will be missing an attribute
--- 176,192 ----
! ONLY preceeding the name of a table indicates that the statement
! should be run over only that table and not tables below it in the
inheritance hierarchy;
! by default, the attribute will be added to or renamed in any of the subclasses.
! It is recommended to never use the ONLY feature however.
! If it is, queries on the inheritance hierarchy
such as
! SELECT NewColumn FROM SuperClass
will not work because the subclasses will be missing an attribute
Index: pgsql/doc/src/sgml/ref/delete.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/delete.sgml,v
retrieving revision 1.10
diff -c -r1.10 delete.sgml
*** pgsql/doc/src/sgml/ref/delete.sgml 2000/03/26 18:32:27 1.10
--- pgsql/doc/src/sgml/ref/delete.sgml 2000/05/17 15:05:29
***************
*** 24,30 ****
1999-07-20
! DELETE FROM table [ WHERE condition ]
--- 24,30 ----
1999-07-20
! DELETE FROM [ ONLY ] table [ WHERE condition ]
***************
*** 116,121 ****
--- 116,127 ----
faster mechanism to remove all rows from a table.
+
+
+
+ By default DELETE will delete tuples in the table specified
+ and all its sub-classes. If you wish to only update the
+ specific table mentioned, you should use the ONLY clause.
Index: pgsql/doc/src/sgml/ref/select.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v
retrieving revision 1.28
diff -c -r1.28 select.sgml
*** pgsql/doc/src/sgml/ref/select.sgml 2000/03/27 17:14:43 1.28
--- pgsql/doc/src/sgml/ref/select.sgml 2000/05/17 15:05:31
***************
*** 25,31 ****
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
expression [ AS name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
! [ FROM table [ alias ] [, ...] ]
[ WHERE condition ]
[ GROUP BY column [, ...] ]
[ HAVING condition [, ...] ]
--- 25,31 ----
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
expression [ AS name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
! [ FROM [ ONLY ]table [ alias ] [, ...] ]
[ WHERE condition ]
[ GROUP BY column [, ...] ]
[ HAVING condition [, ...] ]
***************
*** 202,207 ****
--- 202,214 ----
Candidates for selection are rows which satisfy the WHERE condition;
if WHERE is omitted, all rows are candidates.
(See .)
+
+
+ ONLY will eliminate rows from subclasses of the table.
+ This was previously the default result, and getting subclasses was
+ obtained by appending * to the table name.
+ The old behaviour is available via the command
+ SET EXAMINE_SUBCLASS TO 'on';
Index: pgsql/doc/src/sgml/ref/set.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/set.sgml,v
retrieving revision 1.42
diff -c -r1.42 set.sgml
*** pgsql/doc/src/sgml/ref/set.sgml 2000/04/18 15:23:34 1.42
--- pgsql/doc/src/sgml/ref/set.sgml 2000/05/17 15:05:33
***************
*** 554,559 ****
--- 554,592 ----
+ EXAMINE_SUBCLASS
+
+
+ Changes the behaviour of SELECT so that it no longer automatically
+ examines sub-classes. (See SELECT). By default a SELECT on a table
+ will also return subclass tuples unless specifying ONLY tablename.
+ Setting this returns postgres to the traditional behaviour of
+ only returning subclasses when appending "*" to the tablename.
+
+
+ ON
+
+
+ Returns SELECT to the behaviour of automatically returning
+ results from sub-classes.
+
+
+
+
+
+ OFF
+
+
+ Prevents SELECT from returning sub-classes unless the "*" follows the table name
+
+
+
+
+
+
+
+
+
ENABLE_SEQSCAN
Index: pgsql/doc/src/sgml/ref/update.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/update.sgml,v
retrieving revision 1.9
diff -c -r1.9 update.sgml
*** pgsql/doc/src/sgml/ref/update.sgml 2000/04/11 05:39:15 1.9
--- pgsql/doc/src/sgml/ref/update.sgml 2000/05/17 15:05:33
***************
*** 23,29 ****
1999-07-20
! UPDATE table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]
--- 23,29 ----
1999-07-20
! UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]
***************
*** 139,144 ****
--- 139,150 ----
You must have write access to the table in order to modify
it, as well as read access to any table whose values are
mentioned in the WHERE condition.
+
+
+
+ By default UPDATE will update tuples in the table specified
+ and all its sub-classes. If you wish to only update the
+ specific table mentioned, you should use the ONLY clause.
Index: pgsql/src/backend/commands/creatinh.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/creatinh.c,v
retrieving revision 1.57
diff -c -r1.57 creatinh.c
*** pgsql/src/backend/commands/creatinh.c 2000/04/12 17:14:58 1.57
--- pgsql/src/backend/commands/creatinh.c 2000/05/17 15:05:35
***************
*** 35,40 ****
--- 35,43 ----
const char *attributeType, List *schema);
static List *MergeAttributes(List *schema, List *supers, List **supconstr);
static void StoreCatalogInheritance(Oid relationId, List *supers);
+ static void
+ setRelhassubclassInRelation(Oid relationId, bool relhassubclass);
+
/* ----------------------------------------------------------------
* DefineRelation
***************
*** 327,332 ****
--- 330,336 ----
TupleConstr *constr;
relation = heap_openr(name, AccessShareLock);
+ setRelhassubclassInRelation(relation->rd_id, true);
tupleDesc = RelationGetDescr(relation);
constr = tupleDesc->constr;
***************
*** 661,663 ****
--- 665,703 ----
}
return false;
}
+
+
+ static void
+ setRelhassubclassInRelation(Oid relationId, bool relhassubclass)
+ {
+ Relation relationRelation;
+ HeapTuple tuple;
+ Relation idescs[Num_pg_class_indices];
+
+ /*
+ * Lock a relation given its Oid. Go to the RelationRelation (i.e.
+ * pg_relation), find the appropriate tuple, and add the specified
+ * lock to it.
+ */
+ relationRelation = heap_openr(RelationRelationName, RowExclusiveLock);
+ tuple = SearchSysCacheTuple(RELOID,
+ ObjectIdGetDatum(relationId),
+ 0, 0, 0)
+ ;
+ Assert(HeapTupleIsValid(tuple));
+
+ ((Form_pg_class) GETSTRUCT(tuple))->relhassubclass = relhassubclass;
+ heap_update(relationRelation, &tuple->t_self, tuple, NULL);
+
+ /* keep the catalog indices up to date */
+ CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, idescs);
+ CatalogIndexInsert(idescs, Num_pg_class_indices, relationRelation, tuple
+ );
+ CatalogCloseIndices(Num_pg_class_indices, idescs);
+
+ heap_close(relationRelation, RowExclusiveLock);
+ }
+
+
+
+
Index: pgsql/src/backend/commands/variable.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/variable.c,v
retrieving revision 1.34
diff -c -r1.34 variable.c
*** pgsql/src/backend/commands/variable.c 2000/04/12 17:15:00 1.34
--- pgsql/src/backend/commands/variable.c 2000/05/17 15:05:37
***************
*** 97,102 ****
--- 97,105 ----
static bool show_random_seed(void);
static bool reset_random_seed(void);
+ #define examine_subclass_default true
+ bool examine_subclass = examine_subclass_default;
+
/*
* get_token
* Obtain the next item in a comma-separated list of items,
***************
*** 234,239 ****
--- 237,281 ----
}
/*
+ *
+ * EXAMINE_SUBCLASS
+ *
+ */
+ #define EXAMINE_SUBCLASS "EXAMINE_SUBCLASS"
+
+ static bool
+ parse_examine_subclass(const char *value)
+ {
+ if (strcasecmp(value, "on") == 0)
+ examine_subclass = true;
+ else if (strcasecmp(value, "off") == 0)
+ examine_subclass = false;
+ else if (strcasecmp(value, "default") == 0)
+ examine_subclass = examine_subclass_default;
+ else
+ elog(ERROR, "Bad value for %s (%s)", EXAMINE_SUBCLASS, value);
+ return TRUE;
+ }
+
+ static bool
+ show_examine_subclass()
+ {
+
+ if (examine_subclass)
+ elog(NOTICE, "%s is ON", EXAMINE_SUBCLASS);
+ else
+ elog(NOTICE, "%s is OFF", EXAMINE_SUBCLASS);
+ return TRUE;
+ }
+
+ static bool
+ reset_examine_subclass(void)
+ {
+ examine_subclass = examine_subclass_default;
+ return TRUE;
+ }
+
+ /*
* ENABLE_SEQSCAN
*/
static bool
***************
*** 431,436 ****
--- 473,479 ----
/* expect one and only one item */
if (tok == NULL)
elog(ERROR, "Value undefined");
+
if (rest && *rest != '\0')
elog(ERROR, "Unable to parse '%s'", rest);
***************
*** 1194,1199 ****
--- 1237,1245 ----
{
"pg_options", parse_pg_options, show_pg_options, reset_pg_options
},
+ {
+ EXAMINE_SUBCLASS, parse_examine_subclass, show_examine_subclass, reset_examine_subclass
+ },
{
"seed", parse_random_seed, show_random_seed, reset_random_seed
},
Index: pgsql/src/backend/executor/nodeAppend.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/executor/nodeAppend.c,v
retrieving revision 1.30
diff -c -r1.30 nodeAppend.c
*** pgsql/src/backend/executor/nodeAppend.c 2000/04/12 17:15:09 1.30
--- pgsql/src/backend/executor/nodeAppend.c 2000/05/17 15:05:39
***************
*** 268,274 ****
resultList = lcons(rri, resultList);
}
! appendstate->as_result_relation_info_list = resultList;
}
/* ----------------
* call ExecInitNode on each of the plans in our list
--- 268,279 ----
resultList = lcons(rri, resultList);
}
! /*
! The as_result_relation_info_list must be in the same
! order as the rtentry list otherwise update or delete on
! inheritance hierarchies won't work.
! */
! appendstate->as_result_relation_info_list = lreverse(resultList);
}
/* ----------------
* call ExecInitNode on each of the plans in our list
Index: pgsql/src/backend/nodes/list.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/nodes/list.c,v
retrieving revision 1.31
diff -c -r1.31 list.c
*** pgsql/src/backend/nodes/list.c 2000/04/12 17:15:16 1.31
--- pgsql/src/backend/nodes/list.c 2000/05/17 15:05:40
***************
*** 523,528 ****
--- 523,543 ----
}
/*
+ * Reverse a list, non-destructively
+ */
+ List *
+ lreverse(List *l)
+ {
+ List *result = NIL;
+ List *i;
+ foreach(i, l)
+ {
+ result = lcons(lfirst(i), result);
+ }
+ return result;
+ }
+
+ /*
* Return t if two integer lists have no members in common.
*/
bool
Index: pgsql/src/backend/optimizer/plan/planner.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v
retrieving revision 1.79
diff -c -r1.79 planner.c
*** pgsql/src/backend/optimizer/plan/planner.c 2000/04/12 17:15:22 1.79
--- pgsql/src/backend/optimizer/plan/planner.c 2000/05/17 15:05:43
***************
*** 35,40 ****
--- 35,41 ----
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
+ #include "parser/parsetree.h"
static List *make_subplanTargetList(Query *parse, List *tlist,
***************
*** 110,115 ****
--- 111,119 ----
Plan *
subquery_planner(Query *parse, double tuple_fraction)
{
+ List *l;
+ List *rangetable = parse->rtable;
+ RangeTblEntry *rangeTblEntry;
/*
* A HAVING clause without aggregates is equivalent to a WHERE clause
***************
*** 141,146 ****
--- 145,162 ----
eval_const_expressions((Node *) parse->targetList);
parse->qual = eval_const_expressions(parse->qual);
parse->havingQual = eval_const_expressions(parse->havingQual);
+
+ /*
+ * If the query is going to look for subclasses, but no subclasses
+ * actually exist, then we can optimise away the union that would
+ * otherwise happen and thus save some time.
+ */
+ foreach(l, rangetable)
+ {
+ rangeTblEntry = (RangeTblEntry *)lfirst(l);
+ if (rangeTblEntry->inh && !has_subclass(rangeTblEntry->relid))
+ rangeTblEntry->inh = FALSE;
+ }
/*
* Canonicalize the qual, and convert it to implicit-AND format.
Index: pgsql/src/backend/optimizer/util/plancat.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/util/plancat.c,v
retrieving revision 1.50
diff -c -r1.50 plancat.c
*** pgsql/src/backend/optimizer/util/plancat.c 2000/04/12 17:15:24 1.50
--- pgsql/src/backend/optimizer/util/plancat.c 2000/05/17 15:05:44
***************
*** 285,290 ****
--- 285,309 ----
return list;
}
+ /*
+ * has_subclass -
+ * In the current implementation, has_subclass returns whether a
+ * particular class *might* have a subclass. It will not return the
+ * correct result if a class had a subclass which was later dropped.
+ * This is because relhassubclass in pg_class is not updated,
+ * possibly because of efficiency and/or concurrency concerns.
+ * Currently has_subclass is only used as an efficiency hack, so this
+ * is ok.
+ */
+ bool has_subclass(Oid relationId)
+ {
+ HeapTuple tuple =
+ SearchSysCacheTuple(RELOID,
+ ObjectIdGetDatum(relationId),
+ 0, 0, 0);
+ return ((Form_pg_class) GETSTRUCT(tuple))->relhassubclass;
+ }
+
#ifdef NOT_USED
/*
* VersionGetParents
Index: pgsql/src/backend/parser/analyze.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.142
diff -c -r1.142 analyze.c
*** pgsql/src/backend/parser/analyze.c 2000/04/12 17:15:26 1.142
--- pgsql/src/backend/parser/analyze.c 2000/05/17 15:05:49
***************
*** 270,276 ****
/* set up a range table */
makeRangeTable(pstate, NULL);
! setTargetTable(pstate, stmt->relname);
qry->distinctClause = NIL;
--- 270,276 ----
/* set up a range table */
makeRangeTable(pstate, NULL);
! setTargetTable(pstate, stmt->relname, stmt->inh);
qry->distinctClause = NIL;
***************
*** 368,374 ****
* (We didn't want it there until now since it shouldn't be visible in
* the SELECT part.)
*/
! setTargetTable(pstate, stmt->relname);
/* now the range table will not change */
qry->rtable = pstate->p_rtable;
--- 368,374 ----
* (We didn't want it there until now since it shouldn't be visible in
* the SELECT part.)
*/
! setTargetTable(pstate, stmt->relname, FALSE);
/* now the range table will not change */
qry->rtable = pstate->p_rtable;
***************
*** 1489,1495 ****
* do this with REPLACE in POSTQUEL so we keep the feature.
*/
makeRangeTable(pstate, stmt->fromClause);
! setTargetTable(pstate, stmt->relname);
qry->targetList = transformTargetList(pstate, stmt->targetList);
--- 1489,1495 ----
* do this with REPLACE in POSTQUEL so we keep the feature.
*/
makeRangeTable(pstate, stmt->fromClause);
! setTargetTable(pstate, stmt->relname, stmt->inh);
qry->targetList = transformTargetList(pstate, stmt->targetList);
Index: pgsql/src/backend/parser/gram.y
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.167
diff -c -r2.167 gram.y
*** pgsql/src/backend/parser/gram.y 2000/04/07 13:39:34 2.167
--- pgsql/src/backend/parser/gram.y 2000/05/17 15:06:01
***************
*** 49,54 ****
--- 49,55 ----
#include "storage/lmgr.h"
#include "utils/acl.h"
#include "utils/numeric.h"
+ #include "commands/variable.h"
#ifdef MULTIBYTE
#include "miscadmin.h"
***************
*** 207,213 ****
%type substr_list, substr_from, substr_for, trim_list
%type opt_interval
! %type opt_inh_star, opt_binary, opt_using, opt_instead,
opt_with_copy, index_opt_unique, opt_verbose, opt_analyze
%type opt_cursor
--- 208,214 ----
%type substr_list, substr_from, substr_for, trim_list
%type opt_interval
! %type opt_inh_star, opt_binary, opt_using, opt_instead, opt_only
opt_with_copy, index_opt_unique, opt_verbose, opt_analyze
%type opt_cursor
***************
*** 880,895 ****
AlterTableStmt:
/* ALTER TABLE ADD [COLUMN] */
! ALTER TABLE relation_name opt_inh_star ADD opt_column columnDef
! {
! AlterTableStmt *n = makeNode(AlterTableStmt);
! n->subtype = 'A';
! n->relname = $3;
! n->inh = $4;
! n->def = $7;
! $$ = (Node *)n;
! }
/* ALTER TABLE ALTER [COLUMN] {SET DEFAULT |DROP DEFAULT} */
| ALTER TABLE relation_name opt_inh_star ALTER opt_column ColId alter_column_action
{
AlterTableStmt *n = makeNode(AlterTableStmt);
--- 881,927 ----
AlterTableStmt:
/* ALTER TABLE ADD [COLUMN] */
! /* "*" deprecated */
! ALTER TABLE relation_name opt_inh_star ADD opt_column columnDef
! {
! AlterTableStmt *n = makeNode(AlterTableStmt);
! n->subtype = 'A';
! n->relname = $3;
! n->inh = $4 || examine_subclass;
! n->def = $7;
! $$ = (Node *)n;
! }
! | ALTER TABLE ONLY relation_name ADD opt_column columnDef
! {
! AlterTableStmt *n = makeNode(AlterTableStmt);
! n->subtype = 'A';
! n->relname = $4;
! n->inh = FALSE;
! n->def = $7;
! $$ = (Node *)n;
! }
/* ALTER TABLE ALTER [COLUMN] {SET DEFAULT |DROP DEFAULT} */
+ /* "*" deprecated */
+ | ALTER TABLE relation_name opt_inh_star ALTER opt_column ColId alter_column_action
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'T';
+ n->relname = $3;
+ n->inh = $4 || examine_subclass;
+ n->name = $7;
+ n->def = $8;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE ONLY relation_name ALTER opt_column ColId alter_column_action
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'T';
+ n->relname = $4;
+ n->inh = FALSE;
+ n->name = $7;
+ n->def = $8;
+ $$ = (Node *)n;
+ }
| ALTER TABLE relation_name opt_inh_star ALTER opt_column ColId alter_column_action
{
AlterTableStmt *n = makeNode(AlterTableStmt);
***************
*** 901,906 ****
--- 933,959 ----
$$ = (Node *)n;
}
/* ALTER TABLE DROP [COLUMN] {RESTRICT|CASCADE} */
+ /* "*" deprecated */
+ | ALTER TABLE relation_name opt_inh_star DROP opt_column ColId drop_behavior
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'D';
+ n->relname = $3;
+ n->inh = $4 || examine_subclass;
+ n->name = $7;
+ n->behavior = $8;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE ONLY relation_name DROP opt_column ColId drop_behavior
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'D';
+ n->relname = $4;
+ n->inh = FALSE;
+ n->name = $7;
+ n->behavior = $8;
+ $$ = (Node *)n;
+ }
| ALTER TABLE relation_name opt_inh_star DROP opt_column ColId drop_behavior
{
AlterTableStmt *n = makeNode(AlterTableStmt);
***************
*** 912,917 ****
--- 965,989 ----
$$ = (Node *)n;
}
/* ALTER TABLE ADD CONSTRAINT ... */
+ /* "*" deprecated */
+ | ALTER TABLE relation_name opt_inh_star ADD TableConstraint
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'C';
+ n->relname = $3;
+ n->inh = $4 || examine_subclass;
+ n->def = $6;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE ONLY relation_name ADD TableConstraint
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'C';
+ n->relname = $4;
+ n->inh = FALSE;
+ n->def = $6;
+ $$ = (Node *)n;
+ }
| ALTER TABLE relation_name opt_inh_star ADD TableConstraint
{
AlterTableStmt *n = makeNode(AlterTableStmt);
***************
*** 922,927 ****
--- 994,1021 ----
$$ = (Node *)n;
}
/* ALTER TABLE DROP CONSTRAINT {RESTRICT|CASCADE} */
+ /* "*" deprecated */
+ | ALTER TABLE relation_name opt_inh_star DROP CONSTRAINT name drop_behavior
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'X';
+ n->relname = $3;
+ n->inh = $4 || examine_subclass;
+ n->name = $7;
+ n->behavior = $8;
+ $$ = (Node *)n;
+ }
+ | ALTER TABLE ONLY relation_name DROP CONSTRAINT name drop_behavior
+ {
+ AlterTableStmt *n = makeNode(AlterTableStmt);
+ n->subtype = 'X';
+ n->relname = $4;
+ n->inh = FALSE;
+ n->name = $7;
+ n->behavior = $8;
+ $$ = (Node *)n;
+ }
+ ;
| ALTER TABLE relation_name opt_inh_star DROP CONSTRAINT name drop_behavior
{
AlterTableStmt *n = makeNode(AlterTableStmt);
***************
*** 2539,2549 ****
*****************************************************************************/
RenameStmt: ALTER TABLE relation_name opt_inh_star
RENAME opt_column opt_name TO name
{
RenameStmt *n = makeNode(RenameStmt);
n->relname = $3;
! n->inh = $4;
n->column = $7;
n->newname = $9;
$$ = (Node *)n;
--- 2633,2654 ----
*****************************************************************************/
RenameStmt: ALTER TABLE relation_name opt_inh_star
+ /* "*" deprecated */
RENAME opt_column opt_name TO name
{
RenameStmt *n = makeNode(RenameStmt);
n->relname = $3;
! n->inh = $4 || examine_subclass;
! n->column = $7;
! n->newname = $9;
! $$ = (Node *)n;
! }
! | ALTER TABLE ONLY relation_name
! RENAME opt_column opt_name TO name
! {
! RenameStmt *n = makeNode(RenameStmt);
! n->relname = $4;
! n->inh = FALSE;
n->column = $7;
n->newname = $9;
$$ = (Node *)n;
***************
*** 3097,3108 ****
*
*****************************************************************************/
! DeleteStmt: DELETE FROM relation_name
where_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->relname = $3;
! n->whereClause = $4;
$$ = (Node *)n;
}
;
--- 3202,3214 ----
*
*****************************************************************************/
! DeleteStmt: DELETE FROM opt_only relation_name
where_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->inh = ! $3;
! n->relname = $4;
! n->whereClause = $5;
$$ = (Node *)n;
}
;
***************
*** 3139,3154 ****
*
*****************************************************************************/
! UpdateStmt: UPDATE relation_name
SET update_target_list
from_clause
where_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->relname = $2;
! n->targetList = $4;
! n->fromClause = $5;
! n->whereClause = $6;
$$ = (Node *)n;
}
;
--- 3245,3261 ----
*
*****************************************************************************/
! UpdateStmt: UPDATE opt_only relation_name
SET update_target_list
from_clause
where_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->inh = ! $2;
! n->relname = $3;
! n->targetList = $5;
! n->fromClause = $6;
! n->whereClause = $7;
$$ = (Node *)n;
}
;
***************
*** 3533,3538 ****
--- 3640,3649 ----
| /*EMPTY*/ { $$ = FALSE; }
;
+ opt_only: ONLY { $$ = TRUE; }
+ | /*EMPTY*/ { $$ = FALSE; }
+ ;
+
relation_name_list: name_list;
name_list: name
***************
*** 3783,3792 ****
relation_expr: relation_name
{
! /* normal relations */
$$ = makeNode(RelExpr);
$$->relname = $1;
! $$->inh = FALSE;
}
| relation_name '*' %prec '='
{
--- 3894,3903 ----
relation_expr: relation_name
{
! /* default inheritance */
$$ = makeNode(RelExpr);
$$->relname = $1;
! $$->inh = examine_subclass;
}
| relation_name '*' %prec '='
{
***************
*** 3795,3800 ****
--- 3906,3918 ----
$$->relname = $1;
$$->inh = TRUE;
}
+ | ONLY relation_name
+ {
+ /* no inheritance */
+ $$ = makeNode(RelExpr);
+ $$->relname = $2;
+ $$->inh = FALSE;
+ }
opt_array_bounds: '[' ']' opt_array_bounds
{ $$ = lcons(makeInteger(-1), $3); }
Index: pgsql/src/backend/parser/parse_clause.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/parse_clause.c,v
retrieving revision 1.60
diff -c -r1.60 parse_clause.c
*** pgsql/src/backend/parser/parse_clause.c 2000/05/12 01:33:54 1.60
--- pgsql/src/backend/parser/parse_clause.c 2000/05/17 15:06:04
***************
*** 72,78 ****
* there is no other use of any of its attributes. Tricky, eh?
*/
void
! setTargetTable(ParseState *pstate, char *relname)
{
RangeTblEntry *rte;
--- 72,78 ----
* there is no other use of any of its attributes. Tricky, eh?
*/
void
! setTargetTable(ParseState *pstate, char *relname, bool inh)
{
RangeTblEntry *rte;
***************
*** 80,86 ****
if (refnameRangeTablePosn(pstate, relname, NULL) == 0)
rte = addRangeTableEntry(pstate, relname,
makeAttr(relname, NULL),
! FALSE, FALSE, FALSE);
else
rte = refnameRangeTableEntry(pstate, relname);
--- 80,86 ----
if (refnameRangeTablePosn(pstate, relname, NULL) == 0)
rte = addRangeTableEntry(pstate, relname,
makeAttr(relname, NULL),
! inh, FALSE, FALSE);
else
rte = refnameRangeTableEntry(pstate, relname);
Index: pgsql/src/include/catalog/pg_attribute.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.55
diff -c -r1.55 pg_attribute.h
*** pgsql/src/include/catalog/pg_attribute.h 2000/04/12 17:16:28 1.55
--- pgsql/src/include/catalog/pg_attribute.h 2000/05/17 15:06:08
***************
*** 427,433 ****
{ 1259, {"relrefs"}, 21, 0, 2, 16, 0, -1, -1, '\001', 'p', '\0', 's', '\0', '\0' }, \
{ 1259, {"relhaspkey"}, 16, 0, 1, 17, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
{ 1259, {"relhasrules"}, 16, 0, 1, 18, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
! { 1259, {"relacl"}, 1034, 0, -1, 19, 0, -1, -1, '\0', 'p', '\0', 'i', '\0', '\0' }
DATA(insert OID = 0 ( 1259 relname 19 0 NAMEDATALEN 1 0 -1 -1 f p f i f f));
DATA(insert OID = 0 ( 1259 reltype 26 0 4 2 0 -1 -1 t p f i f f));
--- 427,434 ----
{ 1259, {"relrefs"}, 21, 0, 2, 16, 0, -1, -1, '\001', 'p', '\0', 's', '\0', '\0' }, \
{ 1259, {"relhaspkey"}, 16, 0, 1, 17, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
{ 1259, {"relhasrules"}, 16, 0, 1, 18, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
! { 1259, {"relhassubclass"},16, 0, 1, 19, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
! { 1259, {"relacl"}, 1034, 0, -1, 20, 0, -1, -1, '\0', 'p', '\0', 'i', '\0', '\0' }
DATA(insert OID = 0 ( 1259 relname 19 0 NAMEDATALEN 1 0 -1 -1 f p f i f f));
DATA(insert OID = 0 ( 1259 reltype 26 0 4 2 0 -1 -1 t p f i f f));
***************
*** 447,453 ****
DATA(insert OID = 0 ( 1259 relrefs 21 0 2 16 0 -1 -1 t p f s f f));
DATA(insert OID = 0 ( 1259 relhaspkey 16 0 1 17 0 -1 -1 t p f c f f));
DATA(insert OID = 0 ( 1259 relhasrules 16 0 1 18 0 -1 -1 t p f c f f));
! DATA(insert OID = 0 ( 1259 relacl 1034 0 -1 19 0 -1 -1 f p f i f f));
DATA(insert OID = 0 ( 1259 ctid 27 0 6 -1 0 -1 -1 f p f i f f));
DATA(insert OID = 0 ( 1259 oid 26 0 4 -2 0 -1 -1 t p f i f f));
DATA(insert OID = 0 ( 1259 xmin 28 0 4 -3 0 -1 -1 t p f i f f));
--- 448,455 ----
DATA(insert OID = 0 ( 1259 relrefs 21 0 2 16 0 -1 -1 t p f s f f));
DATA(insert OID = 0 ( 1259 relhaspkey 16 0 1 17 0 -1 -1 t p f c f f));
DATA(insert OID = 0 ( 1259 relhasrules 16 0 1 18 0 -1 -1 t p f c f f));
! DATA(insert OID = 0 ( 1259 relhassubclass 16 0 1 19 0 -1 -1 t p f c f f));
! DATA(insert OID = 0 ( 1259 relacl 1034 0 -1 20 0 -1 -1 f p f i f f));
DATA(insert OID = 0 ( 1259 ctid 27 0 6 -1 0 -1 -1 f p f i f f));
DATA(insert OID = 0 ( 1259 oid 26 0 4 -2 0 -1 -1 t p f i f f));
DATA(insert OID = 0 ( 1259 xmin 28 0 4 -3 0 -1 -1 t p f i f f));
Index: pgsql/src/include/catalog/pg_class.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/pg_class.h,v
retrieving revision 1.33
diff -c -r1.33 pg_class.h
*** pgsql/src/include/catalog/pg_class.h 2000/01/26 05:57:57 1.33
--- pgsql/src/include/catalog/pg_class.h 2000/05/17 15:06:09
***************
*** 78,88 ****
int2 relrefs; /* # of references to this relation */
bool relhaspkey; /* has PRIMARY KEY */
bool relhasrules;
aclitem relacl[1]; /* this is here for the catalog */
} FormData_pg_class;
#define CLASS_TUPLE_SIZE \
! (offsetof(FormData_pg_class,relhasrules) + sizeof(bool))
/* ----------------
* Form_pg_class corresponds to a pointer to a tuple with
--- 78,89 ----
int2 relrefs; /* # of references to this relation */
bool relhaspkey; /* has PRIMARY KEY */
bool relhasrules;
+ bool relhassubclass;
aclitem relacl[1]; /* this is here for the catalog */
} FormData_pg_class;
#define CLASS_TUPLE_SIZE \
! (offsetof(FormData_pg_class,relhassubclass) + sizeof(bool))
/* ----------------
* Form_pg_class corresponds to a pointer to a tuple with
***************
*** 102,109 ****
* relacl field.
* ----------------
*/
! #define Natts_pg_class_fixed 18
! #define Natts_pg_class 19
#define Anum_pg_class_relname 1
#define Anum_pg_class_reltype 2
#define Anum_pg_class_relowner 3
--- 103,110 ----
* relacl field.
* ----------------
*/
! #define Natts_pg_class_fixed 19
! #define Natts_pg_class 20
#define Anum_pg_class_relname 1
#define Anum_pg_class_reltype 2
#define Anum_pg_class_relowner 3
***************
*** 122,159 ****
#define Anum_pg_class_relrefs 16
#define Anum_pg_class_relhaspkey 17
#define Anum_pg_class_relhasrules 18
! #define Anum_pg_class_relacl 19
/* ----------------
* initial contents of pg_class
* ----------------
*/
! DATA(insert OID = 1247 ( pg_type 71 PGUID 0 0 0 0 f f r 16 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1249 ( pg_attribute 75 PGUID 0 0 0 0 f f r 15 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1255 ( pg_proc 81 PGUID 0 0 0 0 f f r 16 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1259 ( pg_class 83 PGUID 0 0 0 0 f f r 19 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1260 ( pg_shadow 86 PGUID 0 0 0 0 f t r 8 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1261 ( pg_group 87 PGUID 0 0 0 0 f t r 3 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1262 ( pg_database 88 PGUID 0 0 0 0 f t r 4 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1264 ( pg_variable 90 PGUID 0 0 0 0 f t s 1 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1269 ( pg_log 99 PGUID 0 0 0 0 f t s 1 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 376 ( pg_xactlock 0 PGUID 0 0 0 0 f t s 1 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1215 ( pg_attrdef 109 PGUID 0 0 0 0 t t r 4 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1216 ( pg_relcheck 110 PGUID 0 0 0 0 t t r 4 0 0 0 0 0 f f _null_ ));
DESCR("");
! DATA(insert OID = 1219 ( pg_trigger 111 PGUID 0 0 0 0 t t r 13 0 0 0 0 0 f f _null_ ));
DESCR("");
#define RelOid_pg_type 1247
--- 123,161 ----
#define Anum_pg_class_relrefs 16
#define Anum_pg_class_relhaspkey 17
#define Anum_pg_class_relhasrules 18
! #define Anum_pg_class_relhassubclass 19
! #define Anum_pg_class_relacl 20
/* ----------------
* initial contents of pg_class
* ----------------
*/
! DATA(insert OID = 1247 ( pg_type 71 PGUID 0 0 0 0 f f r 16 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1249 ( pg_attribute 75 PGUID 0 0 0 0 f f r 15 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1255 ( pg_proc 81 PGUID 0 0 0 0 f f r 16 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1259 ( pg_class 83 PGUID 0 0 0 0 f f r 20 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1260 ( pg_shadow 86 PGUID 0 0 0 0 f t r 8 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1261 ( pg_group 87 PGUID 0 0 0 0 f t r 3 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1262 ( pg_database 88 PGUID 0 0 0 0 f t r 4 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1264 ( pg_variable 90 PGUID 0 0 0 0 f t s 1 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1269 ( pg_log 99 PGUID 0 0 0 0 f t s 1 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 376 ( pg_xactlock 0 PGUID 0 0 0 0 f t s 1 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1215 ( pg_attrdef 109 PGUID 0 0 0 0 t t r 4 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1216 ( pg_relcheck 110 PGUID 0 0 0 0 t t r 4 0 0 0 0 0 f f f _null_ ));
DESCR("");
! DATA(insert OID = 1219 ( pg_trigger 111 PGUID 0 0 0 0 t t r 13 0 0 0 0 0 f f f _null_ ));
DESCR("");
#define RelOid_pg_type 1247
Index: pgsql/src/include/commands/variable.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/commands/variable.h,v
retrieving revision 1.9
diff -c -r1.9 variable.h
*** pgsql/src/include/commands/variable.h 2000/02/19 22:10:43 1.9
--- pgsql/src/include/commands/variable.h 2000/05/17 15:06:09
***************
*** 13,17 ****
--- 13,18 ----
extern bool ResetPGVariable(const char *name);
extern void set_default_datestyle(void);
+ extern bool examine_subclass;
#endif /* VARIABLE_H */
Index: pgsql/src/include/nodes/parsenodes.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.104
diff -c -r1.104 parsenodes.h
*** pgsql/src/include/nodes/parsenodes.h 2000/04/12 17:16:40 1.104
--- pgsql/src/include/nodes/parsenodes.h 2000/05/17 15:06:12
***************
*** 795,800 ****
--- 795,801 ----
NodeTag type;
char *relname; /* relation to delete from */
Node *whereClause; /* qualifications */
+ bool inh; /* delete from subclasses */
} DeleteStmt;
/* ----------------------
***************
*** 808,813 ****
--- 809,815 ----
List *targetList; /* the target list (of ResTarget) */
Node *whereClause; /* qualifications */
List *fromClause; /* the from clause */
+ bool inh; /* update subclasses */
} UpdateStmt;
/* ----------------------
Index: pgsql/src/include/nodes/pg_list.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/nodes/pg_list.h,v
retrieving revision 1.17
diff -c -r1.17 pg_list.h
*** pgsql/src/include/nodes/pg_list.h 2000/04/12 17:16:40 1.17
--- pgsql/src/include/nodes/pg_list.h 2000/05/17 15:06:13
***************
*** 118,123 ****
--- 118,124 ----
extern List *set_difference(List *list1, List *list2);
extern List *set_differencei(List *list1, List *list2);
+ extern List *lreverse(List *l);
extern List *LispUnion(List *list1, List *list2);
extern List *LispUnioni(List *list1, List *list2);
Index: pgsql/src/include/parser/parse_clause.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/parser/parse_clause.h,v
retrieving revision 1.17
diff -c -r1.17 parse_clause.h
*** pgsql/src/include/parser/parse_clause.h 2000/04/12 17:16:45 1.17
--- pgsql/src/include/parser/parse_clause.h 2000/05/17 15:06:13
***************
*** 17,23 ****
#include "parser/parse_node.h"
extern void makeRangeTable(ParseState *pstate, List *frmList);
! extern void setTargetTable(ParseState *pstate, char *relname);
extern Node *transformWhereClause(ParseState *pstate, Node *where);
extern List *transformGroupClause(ParseState *pstate, List *grouplist,
List *targetlist);
--- 17,23 ----
#include "parser/parse_node.h"
extern void makeRangeTable(ParseState *pstate, List *frmList);
! extern void setTargetTable(ParseState *pstate, char *relname, bool inh);
extern Node *transformWhereClause(ParseState *pstate, Node *where);
extern List *transformGroupClause(ParseState *pstate, List *grouplist,
List *targetlist);
Index: pgsql/src/test/regress/sql/run_check.tests
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/test/regress/sql/run_check.tests,v
retrieving revision 1.5
diff -c -r1.5 run_check.tests
*** pgsql/src/test/regress/sql/run_check.tests 2000/02/22 20:58:23 1.5
--- pgsql/src/test/regress/sql/run_check.tests 2000/05/17 15:06:19
***************
*** 70,75 ****
--- 70,76 ----
test create_aggregate
test create_operator
test create_index
+ test inherit
endparallel
test create_view # Depends on the above
Re: Postgresql OO Patch
От:
Chris <chris@bitmead.com>
Дата:
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 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 IS A . Disadvantage: *) You need to make a one line change to any programs that use inheritance to include the back-compatibility SET mode.
Re: Postgresql OO Patch
От:
Hannu Krosing <hannu@tm.ee>
Дата:
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
Re: [GENERAL] Re: Postgresql OO Patch
От:
Mike Mascari <mascarm@mascari.com>
Дата:
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
Re: [GENERAL] Re: Postgresql OO Patch
От:
Chris Bitmead <chris@bitmead.com>
Дата:
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?
Re: Postgresql OO Patch
От:
Chris Bitmead <chris@bitmead.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
Re: [GENERAL] Re: Postgresql OO Patch
От:
Hannu Krosing <hannu@tm.ee>
Дата:
Marten Feldtmann wrote: > > But here again an active system may be build on top of the system we > already have: > > - update, insert, deletes are catched via triggers (on commit) > these trigger functions do retrieve the object-id of the objects > changed and write the result into a special table. > > - another software has notification on this special table and managed > the ip-commuication to the clients. Extending NOTIFY to take at least ONE string argument or OID would go a long long way. Even better would be for it to take an "Object", in the one-supertable sense. So triggers or whatever can just notify interested parties about changes. This has been on my personal todo for severeal years already ;) -------------- Hannu
Re: [GENERAL] Re: Postgresql OO Patch
От:
Hannu Krosing <hannu@tm.ee>
Дата:
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
Re: Postgresql OO Patch
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
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
