Thread: Patch attached...
Attached is a patch for the ONLY inheritance
functionality...
*) It includes a SET compatibility mode.
*) The overhead for non-inheritance has
been cut down to 30 microseconds (on a pc).
*) It needs an initdb.
Comments welcome.
--
Chris Bitmead
mailto:chris@bitmead.com? src/backend/1
Index: src/backend/commands/creatinh.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/creatinh.c,v
retrieving revision 1.56
diff -c -r1.56 creatinh.c
*** src/backend/commands/creatinh.c 2000/01/29 16:58:34 1.56
--- src/backend/commands/creatinh.c 2000/02/05 03:13:17
***************
*** 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
***************
*** 323,328 ****
--- 326,332 ----
TupleConstr *constr;
relation = heap_openr(name, AccessShareLock);
+ setRelhassubclassInRelation(relation->rd_id, true);
tupleDesc = RelationGetDescr(relation);
constr = tupleDesc->constr;
***************
*** 655,657 ****
--- 659,698 ----
}
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 = SearchSysCacheTupleCopy(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_freetuple(tuple);
+ heap_close(relationRelation, RowExclusiveLock);
+ }
+
+
+
+
Index: src/backend/commands/variable.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/variable.c,v
retrieving revision 1.28
diff -c -r1.28 variable.c
*** src/backend/commands/variable.c 2000/01/22 23:50:10 1.28
--- src/backend/commands/variable.c 2000/02/05 03:13:18
***************
*** 48,53 ****
--- 48,56 ----
extern bool _use_keyset_query_optimizer;
+ #define examine_subclass_default true
+ bool examine_subclass = examine_subclass_default;
+
/*
*
* Get_Token
***************
*** 228,233 ****
--- 231,274 ----
geqo_rels = GEQO_RELS;
return TRUE;
}
+ /*
+ *
+ * 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;
+ }
/*
*
***************
*** 600,605 ****
--- 641,649 ----
{
"pg_options", parse_pg_options, show_pg_options, reset_pg_options
},
+ {
+ EXAMINE_SUBCLASS, parse_examine_subclass, show_examine_subclass, reset_examine_subclass
+ },
{
NULL, NULL, NULL, NULL
}
Index: src/backend/optimizer/plan/planner.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v
retrieving revision 1.74
diff -c -r1.74 planner.c
*** src/backend/optimizer/plan/planner.c 2000/01/27 18:11:31 1.74
--- src/backend/optimizer/plan/planner.c 2000/02/05 03:13:21
***************
*** 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,
AttrNumber **groupColIdx);
***************
*** 140,146 ****
* to change interface to plan_union_queries to pass that info back!
*/
}
! else if ((rt_index = first_inherit_rt_entry(rangetable)) != -1)
{
List *sub_tlist;
--- 141,147 ----
* to change interface to plan_union_queries to pass that info back!
*/
}
! else if ((rt_index = first_inherit_rt_entry(rangetable)) != -1 && has_inheritors(rt_fetch(rt_index,
parse->rtable)->relid))
{
List *sub_tlist;
Index: src/backend/optimizer/prep/prepunion.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v
retrieving revision 1.43
diff -c -r1.43 prepunion.c
*** src/backend/optimizer/prep/prepunion.c 2000/02/03 06:12:19 1.43
--- src/backend/optimizer/prep/prepunion.c 2000/02/05 03:13:23
***************
*** 25,30 ****
--- 25,33 ----
#include "parser/parse_clause.h"
#include "parser/parsetree.h"
#include "utils/lsyscache.h"
+ #include "access/heapam.h"
+ #include "catalog/catname.h"
+ #include "utils/syscache.h"
typedef struct {
Index rt_index;
***************
*** 45,50 ****
--- 48,54 ----
static Append *make_append(List *appendplans, List *unionrtables,
Index rt_index,
List *inheritrtable, List *tlist);
+ bool has_inheritors(Oid relationId);
/*
***************
*** 352,357 ****
--- 356,386 ----
*union_rtentriesPtr = union_rtentries;
return union_plans;
+ }
+
+ bool has_inheritors(Oid relationId)
+ {
+ bool rtn;
+ Relation relationRelation;
+ HeapTuple tuple;
+
+ /*
+ * 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, NoLock);
+ tuple = SearchSysCacheTupleCopy(RELOID,
+ ObjectIdGetDatum(relationId),
+ 0, 0, 0)
+ ;
+ /* Assert(HeapTupleIsValid(tuple)); */
+
+ rtn = ((Form_pg_class) GETSTRUCT(tuple))->relhassubclass;
+
+ heap_freetuple(tuple);
+ heap_close(relationRelation, NoLock);
+ return rtn;
}
/*
Index: src/backend/parser/gram.y
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.139
diff -c -r2.139 gram.y
*** src/backend/parser/gram.y 2000/02/04 18:49:33 2.139
--- src/backend/parser/gram.y 2000/02/05 03:13:39
***************
*** 3553,3562 ****
relation_expr: relation_name
{
! /* normal relations */
$$ = makeNode(RelExpr);
$$->relname = $1;
! $$->inh = FALSE;
}
| relation_name '*' %prec '='
{
--- 3553,3563 ----
relation_expr: relation_name
{
! /* default inheritance */
! extern bool examine_subclass;
$$ = makeNode(RelExpr);
$$->relname = $1;
! $$->inh = examine_subclass;
}
| relation_name '*' %prec '='
{
***************
*** 3565,3570 ****
--- 3566,3578 ----
$$->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: src/include/catalog/pg_attribute.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.53
diff -c -r1.53 pg_attribute.h
*** src/include/catalog/pg_attribute.h 2000/01/26 05:57:57 1.53
--- src/include/catalog/pg_attribute.h 2000/02/05 03:13:39
***************
*** 402,408 ****
{ 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));
--- 402,409 ----
{ 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));
***************
*** 422,428 ****
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));
--- 423,430 ----
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: 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
*** src/include/catalog/pg_class.h 2000/01/26 05:57:57 1.33
--- src/include/catalog/pg_class.h 2000/02/05 03:13:40
***************
*** 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,128 ****
#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
--- 123,130 ----
#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
***************
*** 135,141 ****
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("");
--- 137,143 ----
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 20 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("");
At 02:36 PM 2/5/00 +1100, Chris wrote: >*) The overhead for non-inheritance has >been cut down to 30 microseconds (on a pc). What kind of PC? I'm getting 4,000 microseconds doing simple selects on a classic P200 (no L2 cache) through AOLserver and Tcl scripts, which probably means more like 2,000 microseconds for PG alone. But without knowing your PC, I have no way to scale. For instance, my P500e that I just built gets between 3-6x performance over my P200. What's an acceptable level for overhead? I have no personal desire to eat any overhead, in all honesty. 2000/30 < 1% but without knowledge of the actual PC platform (which certainly you must know vary widely in performance) I have no way to scale. If your PC platform is closer to my P500e than my (classic) P200 (not pro, no L2 cache) then the overhead is more like 2-3%. That's measurable. And if SQL92 compliance is the goal, why must ANY degradation of performance be acceptable unless there are very, very strong reasons to do so (reasons that impact the target audience). - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> >*) The overhead for non-inheritance has > >been cut down to 30 microseconds (on a pc). We actually have to see how you have implemented it. I am not so interested in timings as in your method. It can be done fast, or it can be done sloppy. I will check the patch. > And if SQL92 compliance is the goal, why must ANY degradation > of performance be acceptable unless there are very, very strong > reasons to do so (reasons that impact the target audience). Agreed. -- 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
> > Attached is a patch for the ONLY inheritance > functionality... > > *) It includes a SET compatibility mode. > *) The overhead for non-inheritance has > been cut down to 30 microseconds (on a pc). > *) It needs an initdb. > > Comments welcome. > One problem is that you use SearchSysCacheTupleCopy while SearchSysCacheTuple is more appropriate. You need Copy only when you are going to be using the cache tuple for an extended period. Looks like the rest of that particular function is OK. However, I have received an objection from someone on another issue related to the patch. There are no documentation changes for that patch. That includes the SET manual page, and any other place Inheritance is mentioned. You also need to update include/catalog/catversion.h because initdb is required. I will wait for a new patch that has these changes, and any others mentioned by people. Thanks. -- 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
FYI the SearchSysCacheTupleCopy call that I was objecting to was in function has_inheritors. Looks like you clearly are on the right track with the patch. There is some tricky code in there, and it looks pretty good. > > Attached is a patch for the ONLY inheritance > functionality... > > *) It includes a SET compatibility mode. > *) The overhead for non-inheritance has > been cut down to 30 microseconds (on a pc). > *) It needs an initdb. > > Comments welcome. > -- 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
Don Baccus wrote: > > At 02:36 PM 2/5/00 +1100, Chris wrote: > > >*) The overhead for non-inheritance has > >been cut down to 30 microseconds (on a pc). > > What kind of PC? Cerelon 400, 64MB, IDE disk. -- Chris Bitmead mailto:chris@bitmead.com
Thanks Bruce! That suggestion with SearchSysCacheTuple makes a big
difference! I am no longer able to measure ANY performance difference
between inherited and non-inherited while doing one million queries.
Attached is a patch that incorporates your suggestions.? pgsql/src/config.log
? pgsql/src/config.cache
? pgsql/src/config.status
? pgsql/src/GNUmakefile
? pgsql/src/Makefile.global
? pgsql/src/backend/fmgr.h
? pgsql/src/backend/parse.h
? pgsql/src/backend/postgres
? pgsql/src/backend/global1.bki.source
? pgsql/src/backend/local1_template1.bki.source
? pgsql/src/backend/global1.description
? pgsql/src/backend/local1_template1.description
? pgsql/src/backend/1
? pgsql/src/backend/catalog/genbki.sh
? pgsql/src/backend/catalog/global1.bki.source
? pgsql/src/backend/catalog/global1.description
? pgsql/src/backend/catalog/local1_template1.bki.source
? pgsql/src/backend/catalog/local1_template1.description
? pgsql/src/backend/port/Makefile
? pgsql/src/backend/utils/Gen_fmgrtab.sh
? pgsql/src/backend/utils/fmgr.h
? pgsql/src/backend/utils/fmgrtab.c
? pgsql/src/bin/initdb/initdb
? pgsql/src/bin/initlocation/initlocation
? pgsql/src/bin/ipcclean/ipcclean
? pgsql/src/bin/pg_ctl/pg_ctl
? pgsql/src/bin/pg_dump/Makefile
? pgsql/src/bin/pg_dump/pg_dump
? pgsql/src/bin/pg_id/pg_id
? pgsql/src/bin/pg_passwd/pg_passwd
? pgsql/src/bin/pg_version/Makefile
? pgsql/src/bin/pg_version/pg_version
? pgsql/src/bin/pgtclsh/mkMakefile.tcldefs.sh
? pgsql/src/bin/pgtclsh/mkMakefile.tkdefs.sh
? pgsql/src/bin/psql/Makefile
? pgsql/src/bin/psql/psql
? pgsql/src/bin/scripts/createlang
? pgsql/src/include/version.h
? pgsql/src/include/config.h
? pgsql/src/interfaces/ecpg/lib/Makefile
? pgsql/src/interfaces/ecpg/lib/libecpg.so.3.0.10
? pgsql/src/interfaces/ecpg/preproc/ecpg
? pgsql/src/interfaces/libpgeasy/Makefile
? pgsql/src/interfaces/libpgeasy/libpgeasy.so.2.1
? pgsql/src/interfaces/libpgtcl/Makefile
? pgsql/src/interfaces/libpq/Makefile
? pgsql/src/interfaces/libpq/libpq.so.2.1
? pgsql/src/interfaces/libpq++/Makefile
? pgsql/src/interfaces/libpq++/libpq++.so.3.1
? 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/plpgsql/src/libplpgsql.so.1.0
? pgsql/src/pl/tcl/mkMakefile.tcldefs.sh
? pgsql/src/test/regress/GNUmakefile
Index: pgsql/doc/src/sgml/advanced.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/advanced.sgml,v
retrieving revision 1.7
diff -c -r1.7 advanced.sgml
*** pgsql/doc/src/sgml/advanced.sgml 1999/10/04 15:18:53 1.7
--- pgsql/doc/src/sgml/advanced.sgml 2000/02/05 08:24:35
***************
*** 56,93 ****
</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 |
+----------+----------+
|Las Vegas | 2174 |
+----------+----------+
|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 |
+----------+----------+
--- 56,97 ----
</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 |
+----------+----------+
|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;
+----------+----------+
|name | altitude |
+----------+----------+
***************
*** 95,112 ****
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! |Madison | 845 |
! +----------+----------+
! </programlisting>
! Here the <quote>*</quote> 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
! have already discussed (<command>select</command>,
! <command>and>up</command>and> and <command>delete</command>)
! support this <quote>*</quote> notation, as do others, like
! <command>alter</command>.
! </para>
</sect1>
<sect1>
--- 99,129 ----
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! </programlisting>
! </para>
!
! 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, as do others, like
! <command>ALTER TABLE</command>.
! </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>
</sect1>
<sect1>
Index: pgsql/doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.3
diff -c -r2.3 catalogs.sgml
*** pgsql/doc/src/sgml/catalogs.sgml 2000/01/22 23:50:08 2.3
--- pgsql/doc/src/sgml/catalogs.sgml 2000/02/05 08:24:37
***************
*** 192,197 ****
--- 192,199 ----
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.4
diff -c -r1.4 inherit.sgml
*** pgsql/doc/src/sgml/inherit.sgml 1999/08/08 04:21:33 1.4
--- pgsql/doc/src/sgml/inherit.sgml 2000/02/05 08:24:37
***************
*** 37,50 ****
</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 |
+----------+----------+
--- 37,56 ----
</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 |
+----------+----------+
***************
*** 52,92 ****
+----------+----------+
|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 |
+----------+----------+
|Las Vegas | 2174 |
+----------+----------+
|Mariposa | 1953 |
- +----------+----------+
- |Madison | 845 |
+----------+----------+
! </programlisting>
! Here the <quote>*</quote> 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
have already discussed -- <command>SELECT</command>,
<command>UPDATE</command> and <command>DELETE</command> --
! support this <quote>*</quote> notation, as do others, like
<command>ALTER TABLE</command>.
</para>
</chapter>
--- 58,109 ----
+----------+----------+
|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 |
+----------+----------+
|Las Vegas | 2174 |
+----------+----------+
|Mariposa | 1953 |
+----------+----------+
! </programlisting>
! </para>
!
! 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, as do others, like
<command>ALTER TABLE</command>.
+ </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>
</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.10
diff -c -r1.10 alter_table.sgml
*** pgsql/doc/src/sgml/ref/alter_table.sgml 2000/01/29 16:58:27 1.10
--- pgsql/doc/src/sgml/ref/alter_table.sgml 2000/02/05 08:24:38
***************
*** 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>
***************
*** 162,178 ****
</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
--- 162,178 ----
</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/select.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v
retrieving revision 1.24
diff -c -r1.24 select.sgml
*** pgsql/doc/src/sgml/ref/select.sgml 2000/01/27 18:11:25 1.24
--- pgsql/doc/src/sgml/ref/select.sgml 2000/02/05 08:24:40
***************
*** 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> [, ...] ]
***************
*** 198,203 ****
--- 198,210 ----
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.28
diff -c -r1.28 set.sgml
*** pgsql/doc/src/sgml/ref/set.sgml 1999/07/22 15:09:15 1.28
--- pgsql/doc/src/sgml/ref/set.sgml 2000/02/05 08:24:41
***************
*** 443,448 ****
--- 443,482 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>EXAMINE_SUBCLASS</term>
+ <listitem>
+ <para>
+ Sets the inheritance query syntax to the traditional postgres style.
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">OFF</replaceable></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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>ON</term>
+ <listitem>
+ <para>
+ Returns SELECT to the behaviour of automatically returning
+ results from sub-classes.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term>OFF</term>
<listitem>
Index: pgsql/src/backend/commands/creatinh.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/creatinh.c,v
retrieving revision 1.56
diff -c -r1.56 creatinh.c
*** pgsql/src/backend/commands/creatinh.c 2000/01/29 16:58:34 1.56
--- pgsql/src/backend/commands/creatinh.c 2000/02/05 08:24:44
***************
*** 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
***************
*** 323,328 ****
--- 326,332 ----
TupleConstr *constr;
relation = heap_openr(name, AccessShareLock);
+ setRelhassubclassInRelation(relation->rd_id, true);
tupleDesc = RelationGetDescr(relation);
constr = tupleDesc->constr;
***************
*** 655,657 ****
--- 659,698 ----
}
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_freetuple(tuple); */
+ 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.28
diff -c -r1.28 variable.c
*** pgsql/src/backend/commands/variable.c 2000/01/22 23:50:10 1.28
--- pgsql/src/backend/commands/variable.c 2000/02/05 08:24:45
***************
*** 48,53 ****
--- 48,56 ----
extern bool _use_keyset_query_optimizer;
+ #define examine_subclass_default true
+ bool examine_subclass = examine_subclass_default;
+
/*
*
* Get_Token
***************
*** 228,233 ****
--- 231,274 ----
geqo_rels = GEQO_RELS;
return TRUE;
}
+ /*
+ *
+ * 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;
+ }
/*
*
***************
*** 600,605 ****
--- 641,649 ----
{
"pg_options", parse_pg_options, show_pg_options, reset_pg_options
},
+ {
+ EXAMINE_SUBCLASS, parse_examine_subclass, show_examine_subclass, reset_examine_subclass
+ },
{
NULL, NULL, NULL, NULL
}
Index: pgsql/src/backend/optimizer/plan/planner.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v
retrieving revision 1.74
diff -c -r1.74 planner.c
*** pgsql/src/backend/optimizer/plan/planner.c 2000/01/27 18:11:31 1.74
--- pgsql/src/backend/optimizer/plan/planner.c 2000/02/05 08:24:48
***************
*** 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,
AttrNumber **groupColIdx);
***************
*** 140,146 ****
* to change interface to plan_union_queries to pass that info back!
*/
}
! else if ((rt_index = first_inherit_rt_entry(rangetable)) != -1)
{
List *sub_tlist;
--- 141,147 ----
* to change interface to plan_union_queries to pass that info back!
*/
}
! else if ((rt_index = first_inherit_rt_entry(rangetable)) != -1 && has_inheritors(rt_fetch(rt_index,
parse->rtable)->relid))
{
List *sub_tlist;
Index: pgsql/src/backend/optimizer/prep/prepunion.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v
retrieving revision 1.43
diff -c -r1.43 prepunion.c
*** pgsql/src/backend/optimizer/prep/prepunion.c 2000/02/03 06:12:19 1.43
--- pgsql/src/backend/optimizer/prep/prepunion.c 2000/02/05 08:24:49
***************
*** 25,30 ****
--- 25,33 ----
#include "parser/parse_clause.h"
#include "parser/parsetree.h"
#include "utils/lsyscache.h"
+ #include "access/heapam.h"
+ #include "catalog/catname.h"
+ #include "utils/syscache.h"
typedef struct {
Index rt_index;
***************
*** 45,50 ****
--- 48,54 ----
static Append *make_append(List *appendplans, List *unionrtables,
Index rt_index,
List *inheritrtable, List *tlist);
+ bool has_inheritors(Oid relationId);
/*
***************
*** 352,357 ****
--- 356,386 ----
*union_rtentriesPtr = union_rtentries;
return union_plans;
+ }
+
+ bool has_inheritors(Oid relationId)
+ {
+ bool rtn;
+ Relation relationRelation;
+ HeapTuple tuple;
+
+ /*
+ * 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, NoLock);
+ tuple = SearchSysCacheTuple(RELOID,
+ ObjectIdGetDatum(relationId),
+ 0, 0, 0)
+ ;
+ /* Assert(HeapTupleIsValid(tuple)); */
+
+ rtn = ((Form_pg_class) GETSTRUCT(tuple))->relhassubclass;
+
+ /* heap_freetuple(tuple); */
+ heap_close(relationRelation, NoLock);
+ return rtn;
}
/*
Index: pgsql/src/backend/parser/gram.y
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.139
diff -c -r2.139 gram.y
*** pgsql/src/backend/parser/gram.y 2000/02/04 18:49:33 2.139
--- pgsql/src/backend/parser/gram.y 2000/02/05 08:25:00
***************
*** 811,868 ****
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);
n->subtype = 'T';
n->relname = $3;
! n->inh = $4;
n->name = $7;
n->def = $8;
$$ = (Node *)n;
}
/* ALTER TABLE <name> DROP [COLUMN] <name> {RESTRICT|CASCADE} */
| 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;
n->name = $7;
n->behavior = $8;
$$ = (Node *)n;
}
/* ALTER TABLE <name> ADD CONSTRAINT ... */
| ALTER TABLE relation_name opt_inh_star ADD TableConstraint
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n->subtype = 'C';
n->relname = $3;
! n->inh = $4;
n->def = $6;
$$ = (Node *)n;
}
/* ALTER TABLE <name> DROP CONSTRAINT <name> {RESTRICT|CASCADE} */
| 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;
n->name = $7;
n->behavior = $8;
$$ = (Node *)n;
}
;
alter_column_action:
--- 811,926 ----
AlterTableStmt:
/* ALTER TABLE <name> ADD [COLUMN] <coldef> */
+ /* "*" deprecated */
ALTER TABLE relation_name opt_inh_star ADD opt_column columnDef
{
+ extern bool examine_subclass;
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
{
+ extern bool examine_subclass;
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 <name> DROP [COLUMN] <name> {RESTRICT|CASCADE} */
+ /* "*" deprecated */
| ALTER TABLE relation_name opt_inh_star DROP opt_column ColId drop_behavior
{
+ extern bool examine_subclass;
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 <name> ADD CONSTRAINT ... */
+ /* "*" deprecated */
| ALTER TABLE relation_name opt_inh_star ADD TableConstraint
{
+ extern bool examine_subclass;
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 <name> DROP CONSTRAINT <name> {RESTRICT|CASCADE} */
+ /* "*" deprecated */
| ALTER TABLE relation_name opt_inh_star DROP CONSTRAINT name drop_behavior
{
+ extern bool examine_subclass;
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_column_action:
***************
*** 2380,2390 ****
*****************************************************************************/
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;
--- 2438,2460 ----
*****************************************************************************/
RenameStmt: ALTER TABLE relation_name opt_inh_star
+ /* "*" deprecated */
RENAME opt_column opt_name TO name
{
+ extern bool examine_subclass;
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;
***************
*** 3553,3562 ****
relation_expr: relation_name
{
! /* normal relations */
$$ = makeNode(RelExpr);
$$->relname = $1;
! $$->inh = FALSE;
}
| relation_name '*' %prec '='
{
--- 3623,3633 ----
relation_expr: relation_name
{
! /* default inheritance */
! extern bool examine_subclass;
$$ = makeNode(RelExpr);
$$->relname = $1;
! $$->inh = examine_subclass;
}
| relation_name '*' %prec '='
{
***************
*** 3565,3570 ****
--- 3636,3648 ----
$$->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/include/catalog/catversion.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/catversion.h,v
retrieving revision 1.13
diff -c -r1.13 catversion.h
*** pgsql/src/include/catalog/catversion.h 2000/01/27 18:11:40 1.13
--- pgsql/src/include/catalog/catversion.h 2000/02/05 08:25:05
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200001271
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200002050
#endif
Index: pgsql/src/include/catalog/pg_attribute.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.53
diff -c -r1.53 pg_attribute.h
*** pgsql/src/include/catalog/pg_attribute.h 2000/01/26 05:57:57 1.53
--- pgsql/src/include/catalog/pg_attribute.h 2000/02/05 08:25:09
***************
*** 402,408 ****
{ 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));
--- 402,409 ----
{ 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));
***************
*** 422,428 ****
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));
--- 423,430 ----
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/02/05 08:25: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,128 ****
#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
--- 123,130 ----
#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
***************
*** 135,141 ****
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("");
--- 137,143 ----
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 20 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("");
Chris wrote: > > Don Baccus wrote: > > > > At 02:36 PM 2/5/00 +1100, Chris wrote: > > > > >*) The overhead for non-inheritance has > > >been cut down to 30 microseconds (on a pc). > > > > What kind of PC? > > Cerelon 400, 64MB, IDE disk. Btw, how did you measure that 30us overhead ? Does it involve disk accesses or is it just in-memory code that speed-concious folks could move to assembly like current spinlocking code for some architectures? ------------- Hannu
Hannu Krosing wrote:
> Btw, how did you measure that 30us overhead ?
I measured it with the test program below. With the latest patch it is
no longer 30us, but as far as I can measure 0us.
> Does it involve disk accesses or is it just
> in-memory code that
> speed-concious folks could move to assembly like current
> spinlocking code for some architectures?
For this patch it is an in-memory issue.
--
Chris Bitmead
mailto:chris@bitmead.com
#include <stdio.h>
#include <time.h>
#include "libpq-fe.h"
#define rep 1000000
main() {
int c;
PGconn *conn;
PGresult *res;
time_t t, t2;
conn = PQsetdb(NULL,NULL,NULL,NULL,"foo");
time(&t);
for (c = 0; c < rep; c++) { res = PQexec(conn, "select * from a*"); PQclear(res);
}
time(&t2);
printf("inh %d\n", t2 - t);
time(&t);
for (c = 0; c < rep; c++) { res = PQexec(conn, "select * from only a"); PQclear(res);
}
time(&t2);
printf("no inh %d\n", t2 - t);
PQfinish(conn);
}
Hi I looked at the patch and for me the name of the variable to set to get the old behaviour (SET EXAMINE_SUBCLASS TO 'on';) seems confusing. At first I thought it was a typo to set it to 'ON' for old behaviour, my internal logic would set it to 'OFF' to not select subclass by default. I think something like DONT_SELECT_INHERITED or OLD_INHERITED_SELECT_SYNTAX would be much clearer in meaning. Actually the name is not very important, as most people won't use it anyway ;) ---------------- Hannu
Hannu Krosing wrote:
>
> Hi
>
> I looked at the patch and for me the name of the variable
> to set to get the old behaviour (SET EXAMINE_SUBCLASS TO 'on';)
> seems confusing.
>
> At first I thought it was a typo to set it to 'ON' for old behaviour,
> my internal logic would set it to 'OFF' to not select subclass by default.
Umm, but that IS how it works...
$ psql
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands
\g or terminate with semicolon to execute query \q to quit
pghack=# select * from a;aa
-----aaabbb
(2 rows)
pghack=# set examine_subclass to 'off';
SET VARIABLE
pghack=# select * from a;aa
-----aaa
(1 row)
> I think something like DONT_SELECT_INHERITED or OLD_INHERITED_SELECT_SYNTAX
> would be much clearer in meaning.
I'm happy to hear alternative names, but I don't really want "SELECT" in
the name, because this might apply to UPDATE eventually too.
>
> Actually the name is not very important, as most people won't use it anyway ;)
>
> ----------------
> Hannu
--
Chris Bitmead
mailto:chris@bitmead.com
> pghack=# set examine_subclass to 'off';
> > I think something like DONT_SELECT_INHERITED or
> > OLD_INHERITED_SELECT_SYNTAX
> > would be much clearer in meaning.
> I'm happy to hear alternative names, but I don't really want "SELECT" in
> the name, because this might apply to UPDATE eventually too.
Hmm. This uncovers our clunky SET syntax. imho this should be a clean
and natural option, not something with a bunch of underscores in the
keyword and a quoted string for the option.
But this is awfully close to beta to be even considering making this
change in default behavior and syntax for 7.0. We've got a lot of
other features to babysit through beta, and this one wasn't even on
the radar until a few days ago...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Chris, This is to let you know that the core list has discussed this patch,
and we feel that it is not appropriate to apply it at this late stage
in the 7.0 development cycle. There are several reasons for this:
* It appears that making such a definitional change is still
controversial. (One thing that still needs to be looked at is whether
SQL 3 defines any comparable features, and if so whether we ought
to be following their syntax and behavior.)
* The implications of changing this behavior still need to be followed
through in the rest of the system. For example, it doesn't make much
sense to me to change SELECT to have recursive behavior by default when
UPDATE and DELETE can't yet do it at all. A user would naturally
expect "UPDATE table" to scan the same tuples that "SELECT FROM table"
does.
* It's awfully late in the 7.0 development cycle to be making such a
significant change. We have only ten days left to scheduled beta,
which is not enough time to find and work out any unexpected problems
that may be lurking.
We encourage you to continue to work on this line of development,
but with an eye to merging your code into CVS early in the 7.1 cycle,
rather than trying to squeeze it into 7.0 at the last minute.
regards, tom lane
Chris wrote: > > Hannu Krosing wrote: > > > > Hi > > > > I looked at the patch and for me the name of the variable > > to set to get the old behaviour (SET EXAMINE_SUBCLASS TO 'on';) > > seems confusing. > > > > At first I thought it was a typo to set it to 'ON' for old behaviour, > > my internal logic would set it to 'OFF' to not select subclass by default. > > Umm, but that IS how it works... I don't contest that ;) > > I think something like DONT_SELECT_INHERITED or OLD_INHERITED_SELECT_SYNTAX > > would be much clearer in meaning. > > I'm happy to hear alternative names, but I don't really want "SELECT" in > the name, because this might apply to UPDATE eventually too. Oops, I didnt think of that. Of course it should actually apply to all four (SELECT, UPDATE, SELETE, INSERT) as wellas DDL statements (ALTER TABLE ADD/DROP xxx, CREATE CONSTRAINT/INDEX/RULE/TRIGGER) ------------------ Hannu
Tom Lane wrote: > > Chris, > This is to let you know that the core list has discussed this patch, > and we feel that it is not appropriate to apply it at this late stage > in the 7.0 development cycle. Here you see Chris what happens when you try to force the default behaviour be the "wrong" way :-p But seriously, we could still warn people about current (mis)use of inheritance and that it may be soon be changed/deprecated or "made compatible with Informix" whichever seems most PC. > There are several reasons for this: > > * It appears that making such a definitional change is still > controversial. (One thing that still needs to be looked at is whether > SQL 3 defines any comparable features, It does define "comparable" features, but moves away from out nice clean SQL92 worldview quite radically. > and if so whether we ought > to be following their syntax and behavior.) I agree that some discussion about OQL vs. SQL3 would be in place. > * The implications of changing this behavior still need to be followed > through in the rest of the system. For example, it doesn't make much > sense to me to change SELECT to have recursive behavior by default when > UPDATE and DELETE can't yet do it at all. A user would naturally > expect "UPDATE table" to scan the same tuples that "SELECT FROM table" > does. That's true. I would like to see INSERT,UPDATE,DELETE and SELECT be updated together. Fixing ALTER TABLE behaviour is not so important as we are just getting most of it done for plain SQL92 by 7.0. > * It's awfully late in the 7.0 development cycle to be making such a > significant change. We have only ten days left to scheduled beta, > which is not enough time to find and work out any unexpected problems > that may be lurking. Also - fixing object DB behaviours would give us reason to move to 8.x faster ;) > We encourage you to continue to work on this line of development, > but with an eye to merging your code into CVS early in the 7.1 cycle, > rather than trying to squeeze it into 7.0 at the last minute. But could we then disable the current half-hearted OO for the time being to avoid more compatibility problems from people who might err to use it. If there is serious attempt to put the O back in ORDBMS we should not let compatibility with non-SQL postgres extensions to be a decisive fact. But then again that kind of change is best done at a major number change. -------------------- Hannu
Tom Lane wrote: >(One thing that still needs to be looked at is > whether SQL 3 defines any comparable features, and > if so whether we ought to be following their syntax > and behavior.) I just downloaded the SQL3 document from dec. I can't seem to make head or tail of it. Can anybody understand what it's saying? -- Chris Bitmead mailto:chris@bitmead.com
At 11:21 AM 2/6/00 +1100, Chris wrote: >Tom Lane wrote: >>(One thing that still needs to be looked at is >> whether SQL 3 defines any comparable features, and >> if so whether we ought to be following their syntax >> and behavior.) >I just downloaded the SQL3 document from dec. I can't seem to make head >or tail of it. Can anybody understand what it's saying? No ... a full summary of the private discussion earlier today between Jan and I regarding referential integrity would indicate that NOBODY can understand what it's saying! Be glad it was in private, it was bad enough that the two of us had to see each other so confused. Date cheated, his co-author's a ringer who was part of the standards committee and knows what they meant, rather than what they wrote :) The appendix on SQL3 in Date's book talks very briefly about it. There's a CREATE TABLE foo LIKE bar that causes foo to inherit from bar. He doesn't go into details, though. Talks briefly about sub and super tables and how the consequences aren't fully understood. Then he punts. It would still be a good place to start if you have it. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
On Sun, Feb 06, 2000 at 11:21:52AM +1100, Chris wrote: > Tom Lane wrote: > >(One thing that still needs to be looked at is > > whether SQL 3 defines any comparable features, and > > if so whether we ought to be following their syntax > > and behavior.) > > I just downloaded the SQL3 document from dec. I can't seem to make head > or tail of it. Can anybody understand what it's saying? I can occasionall twist my brian in the specific way necessary to read (and partially understand) standards. Got a URI for what you downloaded? 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