Thread: OO Patch
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 ****
</para>
</note>
! For example, the following query finds
! all the cities that are situated at an attitude of 500ft or higher:
!
! <programlisting>
! SELECT name, altitude
! FROM cities
! WHERE altitude > 500;
+----------+----------+
|name | altitude |
+----------+----------+
--- 60,79 ----
</para>
</note>
! <para>
! 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:
!
! <programlisting>
! SELECT c.name, c.altitude
! FROM cities c
! WHERE c.altitude > 500;
! </programlisting>
!
! which returns:
+ <programlisting>
+----------+----------+
|name | altitude |
+----------+----------+
***************
*** 75,97 ****
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! </programlisting>
! </para>
! <para>
! 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:
!
! <programlisting>
! SELECT c.name, c.altitude
! FROM cities* c
! WHERE c.altitude > 500;
! </programlisting>
- which returns:
-
- <programlisting>
+----------+----------+
|name | altitude |
+----------+----------+
--- 81,101 ----
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! |Madison | 845 |
! +----------+----------+
! </programlisting>
! </para>
! <para>
! 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:
!
! <programlisting>
! SELECT name, altitude
! FROM ONLY cities
! WHERE altitude > 500;
+----------+----------+
|name | altitude |
+----------+----------+
***************
*** 99,108 ****
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! |Madison | 845 |
! +----------+----------+
! </programlisting>
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 |
+----------+----------+
! </programlisting>
! </para>
!
+ <<<<<<< advanced.sgml
+ Here the <quote>ONLY</quote> 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 -- <command>SELECT</command>,
+ <command>UPDATE</command> and <command>DELETE</command> --
+ support this <quote>ONLY</quote> notation.
+ </para>
+ <para>
+ 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
+ <programlisting>
+ SELECT * from cities*;
+ </programlisting>
+ This old behaviour is still available by using a SET command...
+ <programlisting>
+ SET EXAMINE_SUBCLASS TO 'on';
+ </programlisting>
+ </para>
+ =======
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
<command>ALTER</command>.
</para>
+ >>>>>>> 1.12
</sect1>
<sect1>
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 ----
</para>
</note>
+ <<<<<<< inherit.sgml
+ =======
For example, the following query finds
all the cities that are situated at an attitude of 500ft or higher:
***************
*** 57,75 ****
</programlisting>
</para>
<para>
! 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:
<programlisting>
SELECT c.name, c.altitude
FROM cities* c
WHERE c.altitude > 500;
</programlisting>
which returns:
<programlisting>
name | altitude
-----------+----------
--- 59,119 ----
</programlisting>
</para>
+ >>>>>>> 1.7
<para>
! 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
+ <programlisting>
+ SELECT c.name, c.altitude
+ FROM cities c
+ =======
<programlisting>
SELECT c.name, c.altitude
FROM cities* c
+ >>>>>>> 1.7
WHERE c.altitude > 500;
</programlisting>
which returns:
+ <programlisting>
+ +----------+----------+
+ |name | altitude |
+ +----------+----------+
+ |Las Vegas | 2174 |
+ +----------+----------+
+ |Mariposa | 1953 |
+ +----------+----------+
+ |Madison | 845 |
+ +----------+----------+
+ </programlisting>
+ </para>
+
+ <para>
+ 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:
+
+ <programlisting>
+ SELECT name, altitude
+ FROM ONLY cities
+ WHERE altitude > 500;
+
+ <<<<<<< inherit.sgml
+ +----------+----------+
+ |name | altitude |
+ +----------+----------+
+ |Las Vegas | 2174 |
+ +----------+----------+
+ |Mariposa | 1953 |
+ +----------+----------+
+ </programlisting>
+ </para>
+
+ =======
<programlisting>
name | altitude
-----------+----------
***************
*** 77,90 ****
--- 121,157 ----
Mariposa | 1953
Madison | 845
</programlisting>
+ >>>>>>> 1.7
+ <<<<<<< inherit.sgml
+ Here the <quote>ONLY</quote> 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 -- <command>SELECT</command>,
<command>UPDATE</command> and <command>DELETE</command> --
+ <<<<<<< inherit.sgml
+ support this <quote>ONLY</quote> notation.
+ </para>
+ <para>
+ 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
+ <programlisting>
+ SELECT * from cities*;
+ </programlisting>
+ This old behaviour is still available by using a SET command...
+ <programlisting>
+ SET EXAMINE_SUBCLASS TO 'on';
+ </programlisting>
+ =======
support this "*" notation, as do others, like
<command>ALTER TABLE</command>.
+ >>>>>>> 1.7
</para>
</chapter>
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 ****
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
! ALTER TABLE <replaceable class="PARAMETER">table</replaceable> [ * ]
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable
class="PARAMETER">type</replaceable>
! ALTER TABLE <replaceable class="PARAMETER">table</replaceable> [ * ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
class="PARAMETER">value</replaceable> | DROP DEFAULT }
! ALTER TABLE <replaceable class="PARAMETER">table</replaceable> [ * ]
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
class="PARAMETER">newcolumn</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
--- 23,35 ----
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
! ALTER TABLE [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable
class="PARAMETER">type</replaceable>
! ALTER TABLE [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
class="PARAMETER">value</replaceable> | DROP DEFAULT }
! ALTER TABLE [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
class="PARAMETER">newcolumn</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
***************
*** 176,192 ****
</para>
<para>
! <quote>*</quote> 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
<programlisting>
! SELECT <replaceable>NewColumn</replaceable> FROM <replaceable>SuperClass</replaceable>*
</programlisting>
will not work because the subclasses will be missing an attribute
--- 176,192 ----
</para>
<para>
! <quote>ONLY</quote> 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
<programlisting>
! SELECT <replaceable>NewColumn</replaceable> FROM <replaceable>SuperClass</replaceable>
</programlisting>
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 ****
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
! DELETE FROM <replaceable class="PARAMETER">table</replaceable> [ WHERE <replaceable
class="PARAMETER">condition</replaceable>]
</synopsis>
<refsect2 id="R2-SQL-DELETE-1">
--- 24,30 ----
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
! DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ WHERE <replaceable
class="PARAMETER">condition</replaceable>]
</synopsis>
<refsect2 id="R2-SQL-DELETE-1">
***************
*** 116,121 ****
--- 116,127 ----
faster mechanism to remove all rows from a table.
</para>
</tip>
+ </para>
+
+ <para>
+ 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.
</para>
<para>
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 ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
<replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">name</replaceable> ]
[,...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
! [ FROM <replaceable class="PARAMETER">table</replaceable> [ <replaceable class="PARAMETER">alias</replaceable> ]
[,...] ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
--- 25,31 ----
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
<replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">name</replaceable> ]
[,...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
! [ FROM [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ <replaceable
class="PARAMETER">alias</replaceable>] [, ...] ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
[ GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...] ]
[ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
***************
*** 202,207 ****
--- 202,214 ----
Candidates for selection are rows which satisfy the WHERE condition;
if WHERE is omitted, all rows are candidates.
(See <xref linkend="sql-where" endterm="sql-where-title">.)
+ </para>
+ <para>
+ <command>ONLY</command> will eliminate rows from subclasses of the table.
+ This was previously the default result, and getting subclasses was
+ obtained by appending <command>*</command> to the table name.
+ The old behaviour is available via the command
+ <command>SET EXAMINE_SUBCLASS TO 'on';</command>
</para>
<para>
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 ----
</varlistentry>
<varlistentry>
+ <term>EXAMINE_SUBCLASS</term>
+ <listitem>
+ <para>
+ 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.
+ <variablelist>
+ <varlistentry>
+ <term>ON</term>
+ <listitem>
+ <para>
+ Returns SELECT to the behaviour of automatically returning
+ results from sub-classes.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>OFF</term>
+ <listitem>
+ <para>
+ Prevents SELECT from returning sub-classes unless the "*" follows the table name
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term>ENABLE_SEQSCAN</term>
<listitem>
<para>
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 ****
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
! UPDATE <replaceable class="PARAMETER">table</replaceable> SET <replaceable class="PARAMETER">col</replaceable> =
<replaceableclass="PARAMETER">expression</replaceable> [, ...]
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
</synopsis>
--- 23,29 ----
<date>1999-07-20</date>
</refsynopsisdivinfo>
<synopsis>
! UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> SET <replaceable
class="PARAMETER">col</replaceable>= <replaceable class="PARAMETER">expression</replaceable> [, ...]
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
</synopsis>
***************
*** 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.
+ </para>
+
+ <para>
+ 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.
</para>
</refsect1>
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 <list> substr_list, substr_from, substr_for, trim_list
%type <list> opt_interval
! %type <boolean> opt_inh_star, opt_binary, opt_using, opt_instead,
opt_with_copy, index_opt_unique, opt_verbose, opt_analyze
%type <boolean> opt_cursor
--- 208,214 ----
%type <list> substr_list, substr_from, substr_for, trim_list
%type <list> opt_interval
! %type <boolean> opt_inh_star, opt_binary, opt_using, opt_instead, opt_only
opt_with_copy, index_opt_unique, opt_verbose, opt_analyze
%type <boolean> opt_cursor
***************
*** 880,895 ****
AlterTableStmt:
/* ALTER TABLE <name> ADD [COLUMN] <coldef> */
! 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 <name> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|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 <name> ADD [COLUMN] <coldef> */
! /* "*" 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 <name> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|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 <name> DROP [COLUMN] <name> {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 <name> 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 <name> DROP CONSTRAINT <name> {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
I can't wait to apply this... > > 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. -- 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, Pennsylvania19026
Chris writes: > I'm resubmitting this patch from a while ago, now that 7.0 is out. I don't recall that discussion ever ending with a definite design document. Could you at least point out which of the many proposals this refers to? What I do remember in fact is that the core decided that the various proposals need to be checked against SQL3 before anything can happen and as far as I can see you definitely didn't do that. > If you cast your minds back, this patch allows update and delete to > work on inheritance hierarchies just like it now works on select. I don't think that's a good idea. If I have an inheritance hierarchy A < B < C and I update a row in A "only" then I break the hierarchy. (I'm also wondering how you would do that, since you would have to make a copy of the row for A and then keep the old copies around for B and C.) SQL3 violation right there. That also goes for the various ALTER TABLE [ONLY] syntax additions. If I add a row to A only then B is no longer a subtable of A. One thing I see you didn't change is the CREATE TABLE syntax, although that could posibly have used it. > It also uses the Informix/Illustra model for subclasses - i.e. "ONLY", > as was discussed at length before. SELECT ONLY is cool, that's the SQL3 way. All in all I think it's great that you're tackling this but this patch looks very suspect to me. Rolling your own inheritance model when there's decades of scientific research out there that some presumably smart guys wrote down in a (now official) standards document doesn't seem like a wise thing to do. PS: Could you elaborate on that FAQ_DEV change? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Chris Bitmead writes: > > That also goes for the various ALTER TABLE [ONLY] > > syntax additions. If I add a row to A only then B is no longer a subtable > > of A. > > I agree that the alter table only is crazy, but the functionality was > there before and I didn't want to be the one to take it out. But if > someone does I can't imagine I'd object. Okay, I think I see what you're getting at. The "ONLY" syntax on DELETE, UPDATE, and ALTER TABLE would provide an entry point for the current, broken behaviour, for those who need it (though it's not really backwards compatibility per se). We might want to flag these with warnings "don't do that" and reserve the option to remove them at a later date, to save people from attempting stupid things. I guess what I might have alluded to with "design document" is that you would have explained that connection, because I did look at the old thread(s) and didn't have any clue what was decided upon. What I was also wondering about were these things such as the "virtual" IDENTITY field that was proposed, the `SELECT **' syntax (bad idea, IMO), and the notion that a query could return different types of rows when reading from an inheritance structure (a worse idea, IMO). I didn't know whether the patch touched that. (I think now that it doesn't.) I'll tell you what, I have some time next week, and I'll read up on SQL3. Perhaps I'll survive it. ;-) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> writes:
> I guess what I might have alluded to with "design document" is that you
> would have explained that connection, because I did look at the old
> thread(s) and didn't have any clue what was decided upon.
AFAIR, nothing was decided on ;-) ... the list has gone 'round on this
topic a few times without achieving anything you could call consensus.
I think Robert Easter might have his hands on the right idea: there
is more than one concept here, and more than one set of applications
to be addressed. We need to break things down into component concepts
rather than trying for a one-size-fits-all solution.
> I'll tell you what, I have some time next week, and I'll read up on SQL3.
> Perhaps I'll survive it. ;-)
Daniel enters the lions' den ... good luck ;-)
regards, tom lane
Tom Lane wrote: > > Peter Eisentraut <peter_e@gmx.net> writes: > > I guess what I might have alluded to with "design document" is that you > > would have explained that connection, because I did look at the old > > thread(s) and didn't have any clue what was decided upon. > > AFAIR, nothing was decided on ;-) ... the list has gone 'round on this > topic a few times without achieving anything you could call consensus. Oh dear. I thought we had progressed further than that. I hope we're not back to square one here. > I think Robert Easter might have his hands on the right idea: there > is more than one concept here, and more than one set of applications > to be addressed. We need to break things down into component concepts > rather than trying for a one-size-fits-all solution. I can't see that anything I've proposed could be construed as one-size-fits-all. 1) DELETE and UPDATE on inheritance hierarchies. You actually suggested it Tom, it used to work in postgres (if you look at the V7.0 doco very carefully, it still says it works!! though it probably hasn't since the V4.2 days). It's really a rather obvious inclusion. 2) Imaginary classoid field. This is a very stand-alone feature, that I didn't hear any objections to. 3) Returning of sub-class fields. Any ODBMS *must* do this by definition. If it doesn't, it isn't an ODBMS. The only question is what syntax to activate it, and I'm not much fussed about that.
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes:
> 3) Returning of sub-class fields. Any ODBMS *must* do this by
> definition. If it doesn't, it isn't an ODBMS.
Chris, you have a bad habit of defining away the problem. Not
everyone is convinced upon this point, and your assertions that
there was consensus don't help your cause.
Possibly more to the point: your patch doesn't implement the
above behavior AFAICS. (Certainly libpq is unprepared to support
multiple tuple types returned in one SELECT --- and there are no
frontend changes in your patch.) So it might help if you'd clarify
exactly what the proposed patch does and doesn't do.
regards, tom lane
Tom Lane wrote: > > Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes: > > 3) Returning of sub-class fields. Any ODBMS *must* do this by > > definition. If it doesn't, it isn't an ODBMS. > > Chris, you have a bad habit of defining away the problem. Not > everyone is convinced upon this point, You claimed to be convinced in the previous discussions. Who exactly wasn't? > and your assertions that > there was consensus don't help your cause. I must admit to frustration here. Will I be issued with a certificate or something when an arbitrator declares "consensus". I can't fathom how decisions are made around here, but you seem to be as close to a leader as I'll find. On the sub-class returning issue you declared that you understood that it was "good for a certain class of problems" or some such. My take on the previous discussions were that a great number of objections were resolved. Am I supposed to just sit on my bum waiting for people who havn't even used an ODBMS to argue for a few years? I'm quite willing to talk this all through again but it needs to reach closure at some point. > Possibly more to the point: your patch doesn't implement the > above behavior AFAICS. I know, it only implements the first point. But this is useful in itself. > (Certainly libpq is unprepared to support > multiple tuple types returned in one SELECT --- and there are no > frontend changes in your patch.) So it might help if you'd clarify > exactly what the proposed patch does and doesn't do. This is the third time I've submitted the patch and you examined it in detail last two times. This is just a post-7.0 merge and I was expecting it put in CVS now that 7.0 is done. To repeat - it implements DELETE and UPDATE on inheritance hierarchies to correct old bit-rot, and it implements ONLY as relates inheritance hierarchies to exclude sub-classes. Oh, and the emacs pgsql code style lisp implementation is done right in the FAQ.
Tom Lane wrote: > > Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes: > > 3) Returning of sub-class fields. Any ODBMS *must* do this by > > definition. If it doesn't, it isn't an ODBMS. > > Chris, you have a bad habit of defining away the problem. Not > everyone is convinced upon this point. Or to put things another way, my goal is to implement the ODMG (http://www.odmg.org/) interface on postgresql. Nobody has said *anything* like that this is a bad goal to aim for, or that there is a better way of doing it.
Tom Lane wrote: > > Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes: > > 3) Returning of sub-class fields. Any ODBMS *must* do this by > > definition. If it doesn't, it isn't an ODBMS. > > Chris, you have a bad habit of defining away the problem. Not > everyone is convinced upon this point, and your assertions that > there was consensus don't help your cause. I am convinced ;). There should be no consensus that "there should be no way to retrieve sub-fields" ;) I agree that the default may well be to retrieve only fuelds of base class. > > Possibly more to the point: your patch doesn't implement the > above behavior AFAICS. (Certainly libpq is unprepared to support > multiple tuple types returned in one SELECT IIRC Bruce removed that feature in Pg95 days claiming that it would not be needed. If backend starts to support it again it would be relatively easy to put back in. > --- and there are no > frontend changes in your patch.) So it might help if you'd clarify > exactly what the proposed patch does and doesn't do. > > regards, tom lane
Good Morning. From user (somebody like me) point of view it is important that documentation reflects relaity. I mentioned about the following discrepancy a couple of months ago. The remark was thorougly ignored. It is great that it will be corrected in the best possible way, it means reality will be upgraded to documentation| Regards, Andrzej Mazurkiewicz. > -----Original Message----- > From: Chris Bitmead [SMTP:chrisb@nimrod.itg.telstra.com.au] > Sent: 19 maja 2000 06:39 > To: Tom Lane > Cc: Peter Eisentraut; Chris; Postgres Hackers List > Subject: Re: [HACKERS] OO Patch > > > 1) DELETE and UPDATE on inheritance hierarchies. You actually suggested > it Tom, it used to work in postgres (if you look at the V7.0 doco very > carefully, it still says it works!! though it probably hasn't since the > V4.2 days). It's really a rather obvious inclusion. >
On Fri, 19 May 2000, Chris Bitmead wrote: > Tom Lane wrote: > > > > Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes: > > > 3) Returning of sub-class fields. Any ODBMS *must* do this by > > > definition. If it doesn't, it isn't an ODBMS. > > > > Chris, you have a bad habit of defining away the problem. Not > > everyone is convinced upon this point, > > You claimed to be convinced in the previous discussions. Who exactly > wasn't? > > > and your assertions that > > there was consensus don't help your cause. > > I must admit to frustration here. Will I be issued with a certificate or > something when an arbitrator declares "consensus". I can't fathom how > decisions are made around here, but you seem to be as close to a leader > as I'll find. On the sub-class returning issue you declared that you > understood that it was "good for a certain class of problems" or some > such. We have a list archive ... just to try and help out here, you might want to try posting URLs to show quotes ... to back things up ... > My take on the previous discussions were that a great number of > objections were resolved. Am I supposed to just sit on my bum waiting > for people who havn't even used an ODBMS to argue for a few years? I'm > quite willing to talk this all through again but it needs to reach > closure at some point. Nope, my take on things is that your patch does things that would break existing functionality, which won't be permitted without one helluva good explanation ... > This is the third time I've submitted the patch and you examined it in > detail last two times. This is just a post-7.0 merge and I was expecting > it put in CVS now that 7.0 is done. That won't happen ... v7.1, if you can get agreement, but not in the current CVS tree ...
The Hermit Hacker wrote: > We have a list archive ... just to try and help out here, you > might want to try posting URLs to show quotes ... to back things up ... I don't have much success with the archive. (Search for "Proposed Changes" - the name of the thread. It yields zero results). The links to the result urls are coloured the same whether you have visited them or not (not a bright idea), and in general I'm skeptical the searching works properly. I certainly can't lay my hands on quite a few important postings. > Nope, my take on things is that your patch does things that would break > existing functionality, which won't be permitted without one helluva > good explanation ... That is true that the ONLY aspect had controversy up front, but it seemed to me to peter out as it was discussed and the patch was submitted. The arguments in favour of ONLY seemed to be (a) It's what SQL3 says, (b) It's what Informix does (c) Experience in usage suggests that it significantly reduced programming errors. (d) The other important point being that the patch includes a SET compatibility mode so that old code needs only a 1 line change. > This is just a post-7.0 merge and I was expecting > > it put in CVS now that 7.0 is done. > > That won't happen ... v7.1, if you can get agreement, but not in the > current CVS tree ... We're post v7.0 now, so presumably we are in pre-7.1 land right? Surely any minor patches now can be done in a branch? I can understand reluctance to branch with heavy development in progress pre-7.0 but once you've released it's time to move on.
The Hermit Hacker wrote: > > On Fri, 19 May 2000, Chris Bitmead wrote: > > > > My take on the previous discussions were that a great number of > > objections were resolved. Am I supposed to just sit on my bum waiting > > for people who havn't even used an ODBMS to argue for a few years? I'm > > quite willing to talk this all through again but it needs to reach > > closure at some point. > > Nope, my take on things is that your patch does things that would break > existing functionality, IMHO it actually _fixes_ existing broken functionality . > which won't be permitted without one helluva good explanation ... Yes, that was The Hermit Hacker I fearfully referred to as misusing even the current "OO" functionality when I warned people not to promote using any half-baked OO features developers have forgot into PostgreSQL when they converted a cool ORDBMS into a generlly usable (non-O)RDBMS. It may be time to fork the tree into OO and beancounting editions ? Especially so if the main tree will migrate to BDB ;-p OOPostgreSQL sounds quite nice ;) > > This is the third time I've submitted the patch and you examined it in > > detail last two times. This is just a post-7.0 merge and I was expecting > > it put in CVS now that 7.0 is done. > > That won't happen ... v7.1, if you can get agreement, but not in the > current CVS tree ... From where must he get that agreement ? --------------- Hannu
Hannu Krosing wrote: > It may be time to fork the tree into OO and beancounting editions ? > Especially so if the main tree will migrate to BDB ;-p > > OOPostgreSQL sounds quite nice ;) I hope we don't have to go there. A better relational engine and a proper OO engine are completely complementry. That was the whole premise of the Stonebraker research. I should also remind people again I guess of my original design proposal I wrote a few years ago. You can find it here http://www.tech.com.au/postgres/ These issues have been on my mind ever since Berkeley released R4.2.
On Fri, 19 May 2000, Hannu Krosing wrote: > The Hermit Hacker wrote: > > > > On Fri, 19 May 2000, Chris Bitmead wrote: > > > > > > > My take on the previous discussions were that a great number of > > > objections were resolved. Am I supposed to just sit on my bum waiting > > > for people who havn't even used an ODBMS to argue for a few years? I'm > > > quite willing to talk this all through again but it needs to reach > > > closure at some point. > > > > Nope, my take on things is that your patch does things that would break > > existing functionality, > > IMHO it actually _fixes_ existing broken functionality . Oops, sorry, mis-spell ... would should be could ... > > > which won't be permitted without one helluva good explanation ... > > Yes, that was The Hermit Hacker I fearfully referred to as misusing even > the current "OO" functionality when I warned people not to promote using > any half-baked OO features developers have forgot into PostgreSQL when they > converted a cool ORDBMS into a generlly usable (non-O)RDBMS. > > It may be time to fork the tree into OO and beancounting editions ? > Especially so if the main tree will migrate to BDB ;-p > > OOPostgreSQL sounds quite nice ;) > > > > This is the third time I've submitted the patch and you examined it in > > > detail last two times. This is just a post-7.0 merge and I was expecting > > > it put in CVS now that 7.0 is done. > > > > That won't happen ... v7.1, if you can get agreement, but not in the > > current CVS tree ... > > From where must he get that agreement ? From more then two ppl? Actually, IMHO, it looks like alot of the problem is not that we should improve our OO, but how to go about it. It appears to me that the past thread that Chris started ended in a fashion that bred misunderstanding ... Chris thought it was resolved, others thought it got left hanging ... What *I'd* like to see is that past thread re-picked up again ... I'm going to take some time tonight to go through the archives and see if I can pull out "the start of the thread", will post it, and see if we can get some discussions going ... v7.0 hasn't been BRANCHED yet, so it can't go into the tree yet, but if we can take the next bit of time before it is BRANCHED to discuss it out and reach some sort of consensus here ... Chris, one quick question ... the last email I read from you stated a bunch of things that you wanted to accomplish, but your patch only addressed the first one. Can we focus on that and ignore the others? Do it through step'ng stones? Or does each step only make sense in view of the whole picture?
The Hermit Hacker wrote: > Chris, one quick question ... the last email I read from you stated a > bunch of things that you wanted to accomplish, but your patch only > addressed the first one. Can we focus on that and ignore the others? > Do it through step'ng stones? Or does each step only make sense in > view of the whole picture? Each of the 3 is independant and useful in and of itself, although all 3 are needed to achieve the goal - an ODMG interface. We can discuss one by one. It might be useful to start off with a meta-discussion. Does everyone understand the significance of ODMG, the the benefits of supporting it?
The Hermit Hacker wrote: > > On Fri, 19 May 2000, Hannu Krosing wrote: > > > The Hermit Hacker wrote: > > > > > > On Fri, 19 May 2000, Chris Bitmead wrote: > > > > > > > > > > My take on the previous discussions were that a great number of > > > > objections were resolved. Am I supposed to just sit on my bum waiting > > > > for people who havn't even used an ODBMS to argue for a few years? I'm > > > > quite willing to talk this all through again but it needs to reach > > > > closure at some point. > > > > > > Nope, my take on things is that your patch does things that would break > > > existing functionality, > > > > IMHO it actually _fixes_ existing broken functionality . > > Oops, sorry, mis-spell ... would should be could ... ;) > > > > From where must he get that agreement ? > > >From more then two ppl? Actually, IMHO, it looks like alot of the problem > is not that we should improve our OO, but how to go about it. It appears > to me that the past thread that Chris started ended in a fashion that bred > misunderstanding ... Chris thought it was resolved, others thought it got > left hanging ... > > What *I'd* like to see is that past thread re-picked up again ... I'm > going to take some time tonight to go through the archives and see if I > can pull out "the start of the thread", will post it, and see if we can > get some discussions going ... > > v7.0 hasn't been BRANCHED yet, so it can't go into the tree yet, but if we > can take the next bit of time before it is BRANCHED to discuss it out and > reach some sort of consensus here ... Some sort of mission statement - what we want to accomplish and steps to get there ? > Chris, one quick question ... the last email I read from you stated a > bunch of things that you wanted to accomplish, but your patch only > addressed the first one. Can we focus on that and ignore the others? Do > it through step'ng stones? Or does each step only make sense in view of > the whole picture? I guess the first step implemented in the patch is a useful fix in its own right. Alter table ONLY should be discouraged (maybe even forbidden in future) Making Alter table to work efficiently on subtables would need some redesign of tuple storage anyway, but this can probably postponed to when other things are working. The same redesign would also give us efficient ALTER TABLE DROP COLUMN. Future things like having a unique index over all inherited tables require more technical discussion as there are several vays to implement them, each efficient for different use pattern. btw. I'll be away from computer from now to monday, but I'm very much interested in this topic and will surely followup then - it's a pain to do all the OO in the frontend. ------------- Hannu
On Sat, May 20, 2000 at 09:42:45AM +1000, Chris wrote: > The Hermit Hacker wrote: > > > We have a list archive ... just to try and help out here, you > > might want to try posting URLs to show quotes ... to back things up ... > > I don't have much success with the archive. (Search for "Proposed > Changes" - the name of the thread. It yields zero results). The links > to the result urls are coloured the same whether you have visited them > or not (not a bright idea), and in general I'm skeptical the searching > works properly. I certainly can't lay my hands on quite a few important > postings. http://www.postgresql.org/mhonarc/pgsql-hackers/2000-02/msg00050.html Seems to be the start of it. The web server had an unfortunate hard drive crash, from what I understand, and they've been rebuilding the indices for the search engine. (I found this by greping my local 'all postgresql list I subscribe to' archive, to find the date, then going to that page on postgresql.org. One problem is that the 'by month' links in the mailing list archives only give you _part_ of the month: you have to hit the 'next page' link at the top) > > We're post v7.0 now, so presumably we are in pre-7.1 land right? Surely > any minor patches now can be done in a branch? I can understand > reluctance to branch with heavy development in progress pre-7.0 but once > you've released it's time to move on. Nope - the standard release process for postgresql is tag at release date, branch after the inital flurry of bug reports/patches settles down. This avoids a lot of double patching for the bugs that the beta testers don't find, but the general user community does. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes:
> I must admit to frustration here. Will I be issued with a certificate or
> something when an arbitrator declares "consensus". I can't fathom how
> decisions are made around here,
If necessary, hard decisions are made by agreement of the core committee
--- but core prefers not to impose answers on the community. If
possible we wait until we think we see a consensus on the mailing list.
(I say "we" since I was recently appointed to core, but being the junior
member of core I'm hardly the man in charge ;-). Perhaps I should also
point out that in sitting here and debating the technical issues with
you, I'm not speaking for core; I'm just speaking as another member of
the community. My opinion doesn't count any more than yours does,
unless it comes to a point of having to be settled by a core vote ...
which we'd rather avoid.)
> On the sub-class returning issue you declared that you understood that
> it was "good for a certain class of problems" or some such.
So I did, and I think there wasn't too much debate about that once you'd
exhibited some sample problems. As I recall it, the remaining debate
was mostly about whether we wanted to change the system's default
behavior (ie the results of SQL92-compatible syntax) to cater to that
class of problems. There was also concern about whether we shouldn't
look first at SQL3 and try to follow its lead. If I recall correctly,
you are pursuing some other document than SQL3?
> To repeat - it implements DELETE and UPDATE on inheritance hierarchies
> to correct old bit-rot, and it implements ONLY as relates inheritance
> hierarchies to exclude sub-classes. Oh, and the emacs pgsql code style
> lisp implementation is done right in the FAQ.
Fixing DELETE* and UPDATE* is clearly not going to raise any hackles,
since that won't hurt any working applications. Swapping the behavior
of SELECT and SELECT* (which is what you really mean by "ONLY", no?)
*will* break some extant applications, so the threshold for deciding
that that's a good thing to do is a lot higher. That's the point at
which we start wanting to be convinced that there's a community
consensus in favor of the idea, and also that we're not choosing the
wrong standard to follow. If we do break existing apps, we want to
break them once, not several times until we get it right...
regards, tom lane
Hannu Krosing <hannu@tm.ee> writes:
>> Certainly libpq is unprepared to support
>> multiple tuple types returned in one SELECT
> IIRC Bruce removed that feature in Pg95 days claiming that it would
> not be needed. If backend starts to support it again it would be
> relatively easy to put back in.
Would it? libpq's internals might not care much, but it seems to me
that a rather significant API change would be needed, thus risking
breaking client applications. I'd want to see how the libpq API
changes before deciding how easy or hard this is ...
regards, tom lane
Tom Lane wrote: > > Hannu Krosing <hannu@tm.ee> writes: > >> Certainly libpq is unprepared to support > >> multiple tuple types returned in one SELECT > > > IIRC Bruce removed that feature in Pg95 days claiming that it would > > not be needed. If backend starts to support it again it would be > > relatively easy to put back in. > > Would it? libpq's internals might not care much, but it seems to me > that a rather significant API change would be needed, thus risking > breaking client applications. I'd want to see how the libpq API > changes before deciding how easy or hard this is ... The current API would not change. New APIs would be added. One option is just add PQnfieldsv(result, tuple_number) to find the number of fields in a particular tuple. But then we started discussing postgres' lack of streaming result sets and how we might rectify that at the same time. And then it was discussed that PQ will be thrown out in favour of Corba anyway. And then I couldn't figure out where the project is heading, so I didn't know what to work on, so I didn't. I want to know up front if PQ is disappearing in favour of Corba or not.
Tom Lane wrote: > --- but core prefers not to impose answers on the community. If > possible we wait until we think we see a consensus on the mailing list. So is the "community" the hacking community? Ok then, hands up now anyone with concerns about the compatibility aspect of this patch (taking into account the backwards compatibly SET mode), and let's talk about it.
> So is the "community" the hacking community?
It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers
and maybe pgsql-general.
regards, tom lane
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.
> Hannu Krosing <hannu@tm.ee> writes: > >> Certainly libpq is unprepared to support > >> multiple tuple types returned in one SELECT > > > IIRC Bruce removed that feature in Pg95 days claiming that it would > > not be needed. If backend starts to support it again it would be > > relatively easy to put back in. > > Would it? libpq's internals might not care much, but it seems to me > that a rather significant API change would be needed, thus risking > breaking client applications. I'd want to see how the libpq API > changes before deciding how easy or hard this is ... Since this came up, I don't remember removing any of this. I may have given the OK to do it, though. -- 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, Pennsylvania19026
Chris <chris@bitmead.com> writes:
> And then I couldn't figure out where the project is heading, so I didn't
> know what to work on, so I didn't. I want to know up front if PQ is
> disappearing in favour of Corba or not.
At this point, I'd say no one knows that (although if Alex's opinion
of Corba is correct, I'd bet we won't be going to Corba after all...)
You can wait and see, or you can make a guess and expend effort on the
basis of a guess.
My guess is that libpq won't be going away for a very long time. Even
if we adopted Corba or some other new protocol, we'd have a lot of
legacy clients that we'd want to support for the foreseeable future.
So it's probably worth improving libpq even if you think we will/should
adopt something else in the long run.
regards, tom lane
> The current API would not change. New APIs would be added. One option is > just add PQnfieldsv(result, tuple_number) to find the number of fields > in a particular tuple. > > But then we started discussing postgres' lack of streaming result sets > and how we might rectify that at the same time. > > And then it was discussed that PQ will be thrown out in favour of Corba > anyway. > > And then I couldn't figure out where the project is heading, so I didn't > know what to work on, so I didn't. I want to know up front if PQ is > disappearing in favour of Corba or not. OK, there are no plans to change PQ anytime soon. What someone may do is to implement a CORBA network service that interacts with PostgreSQL. -- 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, Pennsylvania19026
Tom Lane wrote: > > Chris <chris@bitmead.com> writes: > > And then I couldn't figure out where the project is heading, so I didn't > > know what to work on, so I didn't. I want to know up front if PQ is > > disappearing in favour of Corba or not. > > At this point, I'd say no one knows that (although if Alex's opinion > of Corba is correct, I'd bet we won't be going to Corba after all...) What is Alex's opinion?
Alex Pilosov wrote: > Corba IS a performance dog compared to everything else in existance. > Almost every ORB in existance is dog-slow. There are some opensource ORBs > which are getting better, but its still a ways off. Have you tried ORBit? Supposedly those guys found other ORBs slow and they wrote their own to be fast.
> 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 Bitmead writes: [ONLY] > For UPDATE and DELETE it is absolutely correct, and useful, not to > mention absolutely essential. Could you explain how and why, preferably with a concrete example? I am still at a loss. > > the `SELECT **' syntax (bad idea, IMO), > > Why is it a bad idea (considering that every ODBMS on the planet does > this)? First of all, ODBMS and [O]RDBMS are not necessarily infinitely compatible concepts. An ORDBMS is an RDBMS extended with OO'ish features such as table inheritance and abstract data types to make data modeling easier for those who like it. But below it all there's still relational algebra and friends. An ODBMS is a paradigm shift to get rid of some restrictions in relational databases, both technical and theoretical, the implication of which is that it's no longer a relational database. Please correct me if I'm wrong. Specifically, a query on a relational database always returns a table, and a table is a set of rows with the same number and types of columns. This is a pretty fundamental assumption, and even accounting for the possibility that it might be broken somehow is going to be a major effort throughout the entire system. Now a question in particular. I understand that this syntax might give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c), (b, c, e) and (b, c, h)? Finally, it seems that the same effect can be obtained with a UNION query, padding with NULLs where necessary and perhaps judicious use of CORRESPONDING. What would be wrong with that? -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut wrote:
>
> Chris Bitmead writes:
>
> [ONLY]
> > For UPDATE and DELETE it is absolutely correct, and useful, not to
> > mention absolutely essential.
>
> Could you explain how and why, preferably with a concrete example? I am
> still at a loss.
The simple answer is that UPDATE and DELETE should not act different to
SELECT. If SELECT returns a certain set of records with a particular
WHERE clause, then DELETE should delete the same set of records with
identical WHERE clause and UPDATE should UPDATE the same set of records.
Which part of this is tricky?
The complex answer is in your own SQL3 research. Now of course Postgres
is not implemented that way that the SQL3 model seems to imply (a good
thing IMHO). Columns that came from super tables are stored in the most
specific table. But the end result has to conform to the description in
your other posting. I'll comment a little more on your other posting.
> > > the `SELECT **' syntax (bad idea, IMO),
> >
> > Why is it a bad idea (considering that every ODBMS on the planet does
> > this)?
>
> First of all, ODBMS and [O]RDBMS are not necessarily infinitely
> compatible concepts.
Why?
> An ORDBMS is an RDBMS extended with OO'ish features such as
> table inheritance and abstract data types to make data modeling easier
> for those who like it.
The custom data type aspect of ORDBMS is a good feature. The inheritance
feature of ORDBMS is IMHO half-baked. Take the class
shape/circle/square example...
CREATE TABLE SHAPE( ..);
CREATE TABLE SQUARE(x1, y1, x2, y2) INHERITS(shape);
CREATE TABLE CIRCLE(x, y, radius) INHERITS(shape);
I can't just go SELECT * FROM SHAPE and call some C++ method to display
the shape on the screen. If I maintain an attribute in SHAPE called
"classname" manually, then I can SELECT * FROM SHAPE, and then do a
separate query on the subclass when I know the type - very inefficient.
Or I can do 3 separate queries. But then I'm hosed when I add a TRIANGLE
type.
What I really want is..
Result r = Query<Shape>.select("SELECT ** FROM SHAPE");
foreach(r, item) {item->display();
}
Which still will work when I add a triangle. I.e. typical polymorphism
code maintenance advantage.
Which is what object databases do or an object relational mapper like
Persistance do. Without that ability I would argue there's very limited
point in having inheritance at all.
> But below it all there's still relational algebra and
> friends. An ODBMS is a paradigm shift to get rid of some restrictions
> in relational databases, both technical and theoretical, the
> implication of
> which is that it's no longer a relational database. Please correct me
> if I'm wrong.
It's no longer a purely relational database true. I think it's always
been a crazy idea that everything should be squeezed into a pure
table/column model.
> Specifically, a query on a relational database always returns a table,
> and a table is a set of rows with the same number and types of columns.
> This is a pretty fundamental assumption, and even accounting for the
> possibility that it might be broken somehow is going to be a major
> effort throughout the entire system.
It's a pretty fundamentally limiting assumption. If you're saying that
this might be a lot of work to fix I think I agree. If you're saying
that you can't see the relational and object models being merged into a
coherent and useful combination then I disagree. I can see no conflict
at all between them. Both models are like seeing half the world. Both
models without the other are limiting.
> Now a question in particular. I understand that this syntax might
> give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
> (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
> (b, c, e) and (b, c, h)?
I don't think I understand this question.
> Finally, it seems that the same effect can be obtained with a UNION
> query,
> padding with NULLs where necessary and perhaps judicious use of
> CORRESPONDING. What would be wrong with that?
Several things. Firstly, what happens when you introduce TRIANGLE? You
have to rewrite every query in your system. Secondly, what if you have
20 classes in your hierarchy each with 20 different fields. Now you have
a UNION with 400 fields, most of which are NULL.
> CREATE TABLE SHAPE( ..);
> CREATE TABLE SQUARE(x1, y1, x2, y2) INHERITS(shape);
> CREATE TABLE CIRCLE(x, y, radius) INHERITS(shape);
>
> I can't just go SELECT * FROM SHAPE and call some C++ method to display
> the shape on the screen. If I maintain an attribute in SHAPE called
> "classname" manually, then I can SELECT * FROM SHAPE, and then do a
> separate query on the subclass when I know the type - very inefficient.
> Or I can do 3 separate queries. But then I'm hosed when I add a TRIANGLE
> type.
>
> What I really want is..
>
> Result r = Query<Shape>.select("SELECT ** FROM SHAPE");
> foreach(r, item) {
> item->display();
> }
>
> Which still will work when I add a triangle. I.e. typical polymorphism
> code maintenance advantage.
>
> Which is what object databases do or an object relational mapper like
> Persistance do. Without that ability I would argue there's very limited
> point in having inheritance at all.
>
I can agree with that. As I wrote a relational mapper for Smalltalk/X
based on the libpq API I noticed the same problems, when doing mapping on
tables.
But some questions/comments about that:
a) How are the indices handled ? If I define an index on an attribute defined in TABLE SHAPE all subclasses are also
handledby this index or do we have an index for the base table and each sub table on this attribute ?
b) Please do not make the libpq API too compilcated ! It's a charm how small the API is and how easy the initial
connectionto a psqgl database is -- compare it against the ODBC API ....
b.1) Despite the ODBC API I rather would like to see to enhance the idea of result sets supported by the
libpq-API.I do not need to query each tuple what it delivers to me. I would like to open the result, query the
structureand then handle the data. If the database returns multiple different sets (results from different tables):
ok:do it the same way for each result set.
b.2) There were some postings about other delivering methods to retrieve the information from each tuple. Today we
getan ASCII-representation of the result tuple and the client has to convert it.
Some were not very happy about it, but I like it. Some were concerned about the fact, that they have to copy the
resultto the information structure within their software. When you use software systems, which are based on
garbagecollection systems, then one ALMOST EVER has to do it.
c) I would like to see more ideas about the extension of pgsql to become an active database. The notification
systenis not enough, because it does not return the most interesting informations.
I myself would like to see something like the VERSANT event system.
d) Please only add basic, language independent, support for inheritance - special features can very often better
simulatedby software on the client side. The best example is the introduction of sequences.
Marten
Marten Feldtmann wrote: > I can agree with that. As I wrote a relational mapper for Smalltalk/X > based on the libpq API I noticed the same problems, when doing mapping on > tables. > > But some questions/comments about that: > > a) How are the indices handled ? If I define an index on an attribute > defined in TABLE SHAPE all subclasses are also handled by this index > or do we have an index for the base table and each sub table on > this attribute ? At the moment there is a separate index for each subclass, but this should probably not be the default. > b) Please do not make the libpq API too compilcated ! It's a charm how > small the API is and how easy the initial connection to a psqgl > database is -- compare it against the ODBC API .... There will be 2 levels of API. An important part of an object database is the existance of the client side cache. The lowest level API would be something like libpq - that is uncached. Next there would be ODMG interfaces for various languages that incorporate the client side cache. I would say the only people who would continue to use libpq level would be people writing higher level interfaces. ODMG is just too convenient. > b.1) > Despite the ODBC API I rather would like to see to enhance the idea > of result > sets supported by the libpq-API. I do not need to query each tuple > what it delivers to me. I would like to open the result, query > the structure and then handle the data. If the database returns > multiple different sets (results from different tables): ok: do it the > same way for each result set. I'm a bit vague on what you mean here. But if you go the full OO way, the language polymorphism will do all the handling of different result sets. The ODMG layer will do the hard work. > b.2) > There were some postings about other delivering methods to retrieve > the information from each tuple. Today we get an ASCII-representation > of the result tuple and the client has to convert it. > > Some were not very happy about it, but I like it. Some were concerned > about the fact, that they have to copy the result to the information > structure within their software. When you use software systems, which > are based on garbage collection systems, then one ALMOST EVER has > to do it. Again, using ODMG takes the hard work out of it. You stop caring about what format the information is delivered in. > c) > I would like to see more ideas about the extension of pgsql to become > an active database. The notification systen is not enough, because > it does not return the most interesting informations. > > I myself would like to see something like the VERSANT event system. Oh, a Versant fan. Good, I like Versant. The only flaw in the Versant event system is events can be lost when the receiver is dead, which may or may not matter depending on the application. > d) > Please only add basic, language independent, support for > inheritance - special features can very often better simulated by > software on the client side. The best example is the introduction > of sequences.
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
Peter Eisentraut wrote: > > > > the `SELECT **' syntax (bad idea, IMO), > > > > Why is it a bad idea (considering that every ODBMS on the planet does > > this)? > > First of all, ODBMS and [O]RDBMS are not necessarily infinitely compatible > concepts. An ORDBMS is an RDBMS extended with OO'ish features such as > table inheritance and abstract data types to make data modeling easier for > those who like it. And which may be ignored by those who don't, just like SELECT ** . > But below it all there's still relational algebra and > friends. An ODBMS is a paradigm shift to get rid of some restrictions in > relational databases, both technical and theoretical, the implication of > which is that it's no longer a relational database. Please correct me if > I'm wrong. Adding DATE and TIME datatypes or functions to SQL may have also seemed a paradigm shift but seems quite essential once it is done. > > Specifically, a query on a relational database always returns a table, and > a table is a set of rows with the same number and types of columns. Says who ? ;) > This is a pretty fundamental assumption, and even accounting for the > possibility that it might be broken somehow is going to be a major effort > throughout the entire system. In first round ** could we disallowed in subselects and other tricky parts. > Now a question in particular. I understand that this syntax might > give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others > (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c), > (b, c, e) and (b, c, h)? What would you need that for ? If its really needed we could implement something like SELECT B,C,E?,H? FROM BASECLASS. but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in other it would perhaps be better to do SELECT B,C,SUB1.E?,SUB3.H? FROM BASECLASS. which means the attribute E defined in subclass SUB1 (an inherited by its descendants) or perhaps SELECT B,C,E OF SUB1,H OF SUB3 FROM BASECLASS. to be style-compatible vith general verbosity and english-likeness of SQL ;) > Finally, it seems that the same effect can be obtained with a UNION query, > padding with NULLs where necessary and perhaps judicious use of > CORRESPONDING. What would be wrong with that? It would be overly complex and error-prone and need a rewrite each time a new sub-class is added. ------------ Hannu
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. > > That is unfortunately false for the patch in general. Huh?
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
Hannu Krosing wrote: >Peter Eisentraut wrote: >> Now a question in particular. I understand that this syntax might >> giveme some rows (a, b, c) and others (a, b, c, d, e) and perhaps others >> (a, b, c, f, g, h). Now what would be the syntaxfor getting only (b, c), >> (b, c, e) and (b, c, h)? > >What would you need that for ? In OO terms it should be illegal. In terms of any one class, there is a defined set of columns that can be seen. What Peter is asking for is a union of selects on different classes. The ordinary union rules should apply. >If its really needed we could implement something like > >SELECT B,C,E?,H? FROM BASECLASS. > >but as E can be an INT inone subclass and TIMESTAMP or VARBINARY in >other I don't think that should be allowed. It violates inheritance principles, since the types are not compatible. There is quite a lot right with inheritance as it is. We support multiple inheritance, and columns with the same name are merged in the child. What we immediately lack are features to make inheritance properly useful: * shared index - an index that should point to the correct child class for quick recovery of rows from inheritance hierarchies. * inheritance of constraints, including Primary/Foreign keys (does this imply the necessity of turning inheritance off incertain cases?) * handling of some write operations on a hierarchy: DELETE and UPDATE (INSERT must require the exact class to be specified) * automatic use of inheritance hierarchies (use ONLY to avoid it) * ALTER ... ADD COLUMN inserting columns in the correct positions in child tables; alternatively, have column numbering independentof the physical representation, so that columns can be added at the end but shown in the correct place by SELECT. There are further complexities in OO which might be desirable, but would require a lot of design work. One fundamental feature of pure OO is that classes carry their own methods, whereas SELECT (for example) imposes a global operation on the various classes of the inheritance tree. This makes the following problematic: * renaming columns in multiple inheritance (to avoid column merging, or to allow a child's column to be of a different type)- what would SELECT do with them? * deferred classes - tables that are used only for inheritance rather than for storing data rows - how could these be specifiedand implemented? No doubt further research would bring up many more examples. I'm not sure it is feasible to make PostgreSQL into a proper OO database, but getting those first five features would really be useful. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "We are troubled on every side, yet not distressed;we are perplexed, but not in despair; persecuted, but not forsaken; cast down, but not destroyed; Alwaysbearing about in the body the dying of the Lord Jesus, that the life also of Jesus might be made manifestin our body." II Corinthians 4:8-10
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?
Peter Eisentraut wrote: > > On Sun, 21 May 2000, Hannu Krosing wrote: > > > > Now a question in particular. I understand that this syntax might > > > give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others > > > (a, b, c, f, g, h). Now what would be the syntax for getting only (b, c), > > > (b, c, e) and (b, c, h)? > > > > What would you need that for ? > > Gee, lemme think. Why do we have SELECT a, b, c at all? Why doesn't > everyone just use SELECT * and filter the stuff themselves? What if I want > to apply a function on `h' but not on the others? Don't tell me there's no > syntax for that, only for getting all columns. (And the fact that your > proposed syntaxes seem completely ad hoc and home-brewed doesn't make me > feel better.) Oh, now I understand what you asking. Yes I did suggest that you be allowed to specify sub-class attributes that don't occur in the super-class. The syntax would be the obvious - either attrname, or class.attrname. As far as syntax is concerned I don't think I'm welded to anything in particular, so suggestions are welcome.
> Hannu Krosing wrote: > > > >but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in > >other > > I don't think that should be allowed. It violates inheritance principles, > since the types are not compatible. I see ... here's a person who has always programmed with typed languages and now thinks, that this is the right definition .... it's much more out there in the world. Open your mind and think about the following: An attribute named "a" of "type" TIMESTAMP of an instance of a class can be seen as a relation from this class to the class TIMESTAMP and this relation is named "a". And if you're on the way to relations you're not far away to see, that a relation is of course not limited to show to one specific class ... but perhaps to all subclasses also ... and this is not a violation. I know, that for many people these are only theoretical questions and they may even be true with that, but "violation of inheritance principles" is simply wrong. But I also know, that we deal with a relational database and I do not expect, that it will be as good as a pure object-oriented database - but all those great wrapper software in the market work with relational databases and they work pretty well - but I also see, that they only use the basic technology to do their work. The reason seems to be, that all those nice oo-features within all those databases do not scale very well ... and they're good for a single implementation. There're other problems out there: - caching at the client side - more powerful db desing evolution features. Change the type, the length of a typed attribute Marten
Marten Feldtmann wrote: >> Hannu Krosing wrote: >> > >> >but as E can be an INT in one subclass and TIMESTAMP or VARBINARYin >> >other >> >> I don't think that should be allowed. It violates inheritance principles, >> since the typesare not compatible. > > I see ... here's a person who has always programmed with typed >languages and now thinks, thatthis is the right definition .... it's >much more out there in the world. Open your mind and think about the >following:> > An attribute named "a" of "type" TIMESTAMP of an instance of a class >can be seen as a relation from thisclass to the class TIMESTAMP and >this relation is named "a". > > And if you're on the way to relations you're not faraway to see, >that a relation is of course not limited to show to one specific class >... but perhaps to all subclassesalso ... and this is not a >violation. However the example I was referring to talked of INT4, TIMESTAMP or VARBINARY. These are not subclasses but totally unrelated. Suppose you had parent (id char(2)) child1 (a int4) child2 (a timestamp) and someone asks for select sum(a) from parent* since the types are incompatible, the answer would be nonsense. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "We are troubled on every side, yet not distressed;we are perplexed, but not in despair; persecuted, but not forsaken; cast down, but not destroyed; Alwaysbearing about in the body the dying of the Lord Jesus, that the life also of Jesus might be made manifestin our body." II Corinthians 4:8-10
Oliver Elphick wrote: > > Marten Feldtmann wrote: > >> Hannu Krosing wrote: > >> > > >> >but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in > >> >other > >> > >> I don't think that should be allowed. It violates inheritance principles, > >> since the types are not compatible. > > > > I see ... here's a person who has always programmed with typed > >languages and now thinks, that this is the right definition .... it's > >much more out there in the world. Open your mind and think about the > >following: > > > > An attribute named "a" of "type" TIMESTAMP of an instance of a class > >can be seen as a relation from this class to the class TIMESTAMP and > >this relation is named "a". > > > > And if you're on the way to relations you're not far away to see, > >that a relation is of course not limited to show to one specific class > >... but perhaps to all subclasses also ... and this is not a > >violation. > > However the example I was referring to talked of INT4, TIMESTAMP or VARBINARY. > > These are not subclasses but totally unrelated. Suppose you had > > parent (id char(2)) > child1 (a int4) > child2 (a timestamp) > > and someone asks for > > select sum(a) from parent* > > since the types are incompatible, the answer would be nonsense. MS Excel for example SUMs only things summable, by which logic in this case the sum would/could/should be sum of int4 colums. In real world not all things are summable. OTOH for schema parent (id char(2)) child1 (a orange) child2 (a apple) select sum(a) from parent* could yield "N apples and M oranges" or possibly "X fruits" if orange and apple were subtypes of fruit. Yes, really ;) Or depending on how the sum() function is defined it could even be "Y kg" . --------- Hannu
Oliver Elphick wrote: > These are not subclasses but totally unrelated. Suppose you had > > parent (id char(2)) > child1 (a int4) > child2 (a timestamp) > > and someone asks for > > select sum(a) from parent* > > since the types are incompatible, the answer would be nonsense. That query would be disallowed, for the reason you note. Ambigous coloumns would need to be specified by class.attribute.
On Sat, 20 May 2000, Chris wrote:
> And then I couldn't figure out where the project is heading, so I didn't
> know what to work on, so I didn't. I want to know up front if PQ is
> disappearing in favour of Corba or not.
Eventually ... maybe. But, I agree with Tom on this, it will be awhile
before libpq can/will disappear, as there is too much code out there that
relies on it. Figuring our release cycles being 4-6mos, and figuring that
it would be *at least* 2 full releases after Corba was fully implemented
before we could phase out libpq, figure, oh, 2 years at least before libpq
*could* disappear :)
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
The Hermit Hacker wrote: > > And then I couldn't figure out where the project is heading, so I didn't > > know what to work on, so I didn't. I want to know up front if PQ is > > disappearing in favour of Corba or not. > > Eventually ... maybe. But, I agree with Tom on this, it will be awhile > before libpq can/will disappear, as there is too much code out there that > relies on it. Figuring our release cycles being 4-6mos, and figuring that > it would be *at least* 2 full releases after Corba was fully implemented > before we could phase out libpq, figure, oh, 2 years at least before libpq > *could* disappear :) When you say "libpq", do you mean the API or the protocol? The API can stay forever if it is implemented in terms of a Corba API. I've been looking into it. The thing I've come up against now is postgres' advanced types. Does every postgres type, user-defined or not now need a Corba IDL definition if we go to Corba? If so, how do people feel about it? If we go to a binary representation protocol (which I believe is the right thing BTW), there has to be something which can marshal etc, and using IDL to achieve it may as well be it. But when I started to realise this aspect and the amount of work, Corba started to get pushed down my TODO list in favour of a quick fix to the current protocol to do my OO stuff.
> 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
> - 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. 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. > - 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. Yep. > - for associations (m:n) I still need additional help tables, but > that is ok :-) 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). > - 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. > - more powerful statements DDL to change the structure of a database ! Yep, important. > - no support to inform the client about changes inthe database ! Havn't even looked at that.
> > > > 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
Marten Feldtmann wrote: > > > > > > > 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. The implied table doesn't do anything to performance. Having an index on that table obviously needs to be maintained and the decision to create such an index would be by the user. So the user can make use of such an implied super-table or not as they please. But having such a global index is necessary for an ODBMS, and I can tell you that for the Versant ODBMS it is lightning fast even with gigabytes of data (I have seen Versant grown to 100 Gig). Versant does use an indexing mechanism. > Have you ever create a 1:n association with about 800 entries ? In postgres, no. In other ODBMS, yes easily. > Actually I do not know, how many entries such an array may > have. Unlimited ? To work properly we do need TOAST so that tuples can grow bigger. > How do I remove an entry, how do I delete an > entry. I may have a closer look at that. Adding and deleting entries would be done in memory and then the attribute updated in one go. Of course with an ODBMS you can create more sophisticated data structures if you need really huge arrays, like roll your own btree, or whatever thing you can find in Knuth.
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
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