diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2001-08-21 16:36:06 +0000 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2001-08-21 16:36:06 +0000 |
| commit | f933766ba7c5446a28d714904ae0c46d8b21b86a (patch) | |
| tree | 81c8ecd2a2f8161d91670f5325331ba1704c2ab7 | |
| parent | c2d156691292d7be998eacf5b99dce3ea3c29ab2 (diff) | |
| download | postgresql-f933766ba7c5446a28d714904ae0c46d8b21b86a.tar.gz | |
Restructure pg_opclass, pg_amop, and pg_amproc per previous discussions in
pgsql-hackers. pg_opclass now has a row for each opclass supported by each
index AM, not a row for each opclass name. This allows pg_opclass to show
directly whether an AM supports an opclass, and furthermore makes it possible
to store additional information about an opclass that might be AM-dependent.
pg_opclass and pg_amop now store "lossy" and "haskeytype" information that we
previously expected the user to remember to provide in CREATE INDEX commands.
Lossiness is no longer an index-level property, but is associated with the
use of a particular operator in a particular index opclass.
Along the way, IndexSupportInitialize now uses the syscaches to retrieve
pg_amop and pg_amproc entries. I find this reduces backend launch time by
about ten percent, at the cost of a couple more special cases in catcache.c's
IndexScanOK.
Initial work by Oleg Bartunov and Teodor Sigaev, further hacking by Tom Lane.
initdb forced.
60 files changed, 1913 insertions, 1924 deletions
diff --git a/contrib/cube/cube.sql.in b/contrib/cube/cube.sql.in index ba616b5722..00994d39a0 100644 --- a/contrib/cube/cube.sql.in +++ b/contrib/cube/cube.sql.in @@ -212,15 +212,18 @@ CREATE FUNCTION g_cube_same(cube, cube, opaque) RETURNS opaque -- register the default opclass for indexing -INSERT INTO pg_opclass (opcname, opcdeftype) - SELECT 'gist_cube_ops', oid - FROM pg_type - WHERE typname = 'cube'; +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'gist'), + 'gist_cube_ops', + (SELECT oid FROM pg_type WHERE typname = 'cube'), + true, + 0); -- get the comparators for boxes and store them in a tmp table SELECT o.oid AS opoid, o.oprname -INTO TABLE gist_cube_ops_tmp +INTO TEMP TABLE gist_cube_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'cube'; @@ -231,59 +234,75 @@ WHERE o.oprleft = t.oid and o.oprright = t.oid -- using the tmp table, generate the amop entries -- cube_left -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 1, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '<<'; -- cube_over_left -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 2 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 2, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '&<'; -- cube_overlap -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '&&'; -- cube_over_right -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 4 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 4, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '&>'; -- cube_right -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 5 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 5, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '>>'; -- cube_same -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 6 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 6, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '='; -- cube_contains -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 7 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 7, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '@'; -- cube_contained -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 8 - FROM pg_am am, pg_opclass opcl, gist_cube_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 8, false, c.opoid + FROM pg_opclass opcl, gist_cube_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and c.oprname = '~'; DROP TABLE gist_cube_ops_tmp; @@ -292,46 +311,60 @@ DROP TABLE gist_cube_ops_tmp; -- add the entries to amproc for the support methods -- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and proname = 'g_cube_consistent'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 2 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and proname = 'g_cube_union'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 3 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and proname = 'g_cube_compress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 4 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and proname = 'g_cube_decompress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 5 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and proname = 'g_cube_penalty'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 6 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and proname = 'g_cube_picksplit'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 7 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_cube_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_cube_ops' and proname = 'g_cube_same'; END TRANSACTION; diff --git a/contrib/findoidjoins/README.findoidjoins b/contrib/findoidjoins/README.findoidjoins index 712fc1373e..557fd04158 100644 --- a/contrib/findoidjoins/README.findoidjoins +++ b/contrib/findoidjoins/README.findoidjoins @@ -48,10 +48,8 @@ Join pg_am.amrestrpos => pg_proc.oid Join pg_am.ambuild => pg_proc.oid Join pg_am.ambulkdelete => pg_proc.oid Join pg_am.amcostestimate => pg_proc.oid -Join pg_amop.amopid => pg_am.oid Join pg_amop.amopclaid => pg_opclass.oid Join pg_amop.amopopr => pg_operator.oid -Join pg_amproc.amid => pg_am.oid Join pg_amproc.amopclaid => pg_opclass.oid Join pg_amproc.amproc => pg_proc.oid Join pg_attribute.attrelid => pg_class.oid @@ -63,7 +61,8 @@ Join pg_class.reltoastidxid => pg_class.oid Join pg_description.classoid => pg_class.oid Join pg_index.indexrelid => pg_class.oid Join pg_index.indrelid => pg_class.oid -Join pg_opclass.opcdeftype => pg_type.oid +Join pg_opclass.opcamid => pg_am.oid +Join pg_opclass.opcintype => pg_type.oid Join pg_operator.oprleft => pg_type.oid Join pg_operator.oprright => pg_type.oid Join pg_operator.oprresult => pg_type.oid diff --git a/contrib/findoidjoins/findoidjoins.c b/contrib/findoidjoins/findoidjoins.c index 87d794a9da..c559b7a362 100644 --- a/contrib/findoidjoins/findoidjoins.c +++ b/contrib/findoidjoins/findoidjoins.c @@ -68,15 +68,17 @@ main(int argc, char **argv) if (strcmp(typname, "oid") == 0) sprintf(query, "\ DECLARE c_matches BINARY CURSOR FOR \ - SELECT count(*) \ + SELECT count(*)::int4 \ FROM \"%s\" t1, \"%s\" t2 \ - WHERE t1.\"%s\" = t2.oid ", relname, relname2, attname); + WHERE t1.\"%s\" = t2.oid ", + relname, relname2, attname); else sprintf(query, "\ DECLARE c_matches BINARY CURSOR FOR \ - SELECT count(*) \ - FROM \"%s\" t1, \"%s\" t2 \ - WHERE RegprocToOid(t1.\"%s\") = t2.oid ", relname, relname2, attname); + SELECT count(*)::int4 \ + FROM \"%s\" t1, \"%s\" t2 \ + WHERE RegprocToOid(t1.\"%s\") = t2.oid ", + relname, relname2, attname); doquery(query); doquery("FETCH ALL IN c_matches"); diff --git a/contrib/intarray/README.intarray b/contrib/intarray/README.intarray index 26cb082e3a..8e292126c0 100644 --- a/contrib/intarray/README.intarray +++ b/contrib/intarray/README.intarray @@ -1,10 +1,10 @@ This is an implementation of RD-tree data structure using GiST interface -of PostgreSQL. It has built-in lossy compression - must be declared -in index creation - with (islossy). Current implementation provides index -support for one-dimensional array of int4's - gist__int_ops, suitable for -small and medium size of arrays (used on default), and gist__intbig_ops for -indexing large arrays (we use superimposed signature with length of 4096 -bits to represent sets). +of PostgreSQL. It has built-in lossy compression. + +Current implementation provides index support for one-dimensional array of +int4's - gist__int_ops, suitable for small and medium size of arrays (used on +default), and gist__intbig_ops for indexing large arrays (we use superimposed +signature with length of 4096 bits to represent sets). All work was done by Teodor Sigaev (teodor@stack.net) and Oleg Bartunov (oleg@sai.msu.su). See http://www.sai.msu.su/~megera/postgres/gist @@ -35,7 +35,7 @@ EXAMPLE USAGE: -- create indices CREATE unique index message_key on message ( mid ); CREATE unique index message_section_map_key2 on message_section_map (sid, mid ); -CREATE INDEX message_rdtree_idx on message using gist ( sections gist__int_ops) with ( islossy ); +CREATE INDEX message_rdtree_idx on message using gist ( sections gist__int_ops); -- select some messages with section in 1 OR 2 - OVERLAP operator select message.mid from message where message.sections && '{1,2}'; diff --git a/contrib/intarray/_int.sql.in b/contrib/intarray/_int.sql.in index 697be30bde..75ead93600 100644 --- a/contrib/intarray/_int.sql.in +++ b/contrib/intarray/_int.sql.in @@ -99,15 +99,18 @@ CREATE FUNCTION g_int_same(_int4, _int4, opaque) RETURNS opaque -- register the default opclass for indexing -INSERT INTO pg_opclass (opcname, opcdeftype) - SELECT 'gist__int_ops', oid - FROM pg_type - WHERE typname = '_int4'; +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'gist'), + 'gist__int_ops', + (SELECT oid FROM pg_type WHERE typname = '_int4'), + true, + (SELECT oid FROM pg_type WHERE typname = '_int4')); -- get the comparators for _intments and store them in a tmp table SELECT o.oid AS opoid, o.oprname -INTO TABLE _int_ops_tmp +INTO TEMP TABLE _int_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oprright = t.oid and t.typname = '_int4'; @@ -116,33 +119,42 @@ WHERE o.oprleft = t.oid and o.oprright = t.oid -- SELECT * from _int_ops_tmp; -- using the tmp table, generate the amop entries +-- note: these operators are all lossy -- _int_overlap -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, _int_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, true, c.opoid + FROM pg_opclass opcl, _int_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and c.oprname = '&&'; -- _int_same -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 6 - FROM pg_am am, pg_opclass opcl, _int_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 6, true, c.opoid + FROM pg_opclass opcl, _int_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and c.oprname = '='; -- _int_contains -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 7 - FROM pg_am am, pg_opclass opcl, _int_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 7, true, c.opoid + FROM pg_opclass opcl, _int_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and c.oprname = '@'; -- _int_contained -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 8 - FROM pg_am am, pg_opclass opcl, _int_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 8, true, c.opoid + FROM pg_opclass opcl, _int_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and c.oprname = '~'; DROP TABLE _int_ops_tmp; @@ -151,46 +163,60 @@ DROP TABLE _int_ops_tmp; -- add the entries to amproc for the support methods -- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and proname = 'g_int_consistent'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 2 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and proname = 'g_int_union'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 3 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and proname = 'g_int_compress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 4 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and proname = 'g_int_decompress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 5 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and proname = 'g_int_penalty'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 6 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and proname = 'g_int_picksplit'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 7 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__int_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__int_ops' and proname = 'g_int_same'; @@ -219,14 +245,19 @@ CREATE FUNCTION g_intbig_union(bytea, opaque) RETURNS _int4 CREATE FUNCTION g_intbig_same(_int4, _int4, opaque) RETURNS opaque AS 'MODULE_PATHNAME' LANGUAGE 'c'; --- register the default opclass for indexing -INSERT INTO pg_opclass (opcname, opcdeftype) - values ( 'gist__intbig_ops', 0 ); +-- register the opclass for indexing (not as default) +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'gist'), + 'gist__intbig_ops', + (SELECT oid FROM pg_type WHERE typname = '_int4'), + false, + (SELECT oid FROM pg_type WHERE typname = '_int4')); -- get the comparators for _intments and store them in a tmp table SELECT o.oid AS opoid, o.oprname -INTO TABLE _int_ops_tmp +INTO TEMP TABLE _int_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oprright = t.oid and t.typname = '_int4'; @@ -235,26 +266,33 @@ WHERE o.oprleft = t.oid and o.oprright = t.oid -- SELECT * from _int_ops_tmp; -- using the tmp table, generate the amop entries +-- note: these operators are all lossy -- _int_overlap -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, _int_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, true, c.opoid + FROM pg_opclass opcl, _int_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and c.oprname = '&&'; -- _int_contains -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 7 - FROM pg_am am, pg_opclass opcl, _int_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 7, true, c.opoid + FROM pg_opclass opcl, _int_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and c.oprname = '@'; -- _int_contained -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 8 - FROM pg_am am, pg_opclass opcl, _int_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 8, true, c.opoid + FROM pg_opclass opcl, _int_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and c.oprname = '~'; DROP TABLE _int_ops_tmp; @@ -263,46 +301,60 @@ DROP TABLE _int_ops_tmp; -- add the entries to amproc for the support methods -- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and proname = 'g_intbig_consistent'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 2 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and proname = 'g_intbig_union'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 3 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and proname = 'g_intbig_compress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 4 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and proname = 'g_intbig_decompress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 5 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and proname = 'g_intbig_penalty'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 6 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and proname = 'g_intbig_picksplit'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 7 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist__intbig_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__intbig_ops' and proname = 'g_intbig_same'; END TRANSACTION; diff --git a/contrib/intarray/bench/create_test.pl b/contrib/intarray/bench/create_test.pl index ada064a270..b39d1fb473 100755 --- a/contrib/intarray/bench/create_test.pl +++ b/contrib/intarray/bench/create_test.pl @@ -49,7 +49,7 @@ print <<EOT; CREATE unique index message_key on message ( mid ); --CREATE unique index message_section_map_key1 on message_section_map ( mid, sid ); CREATE unique index message_section_map_key2 on message_section_map ( sid, mid ); -CREATE INDEX message_rdtree_idx on message using gist ( sections gist__int_ops ) with ( islossy ); +CREATE INDEX message_rdtree_idx on message using gist ( sections gist__int_ops ); VACUUM ANALYZE; select count(*) from message; diff --git a/contrib/intarray/expected/_int.out b/contrib/intarray/expected/_int.out index 4cde92ce56..edcccf54bb 100644 --- a/contrib/intarray/expected/_int.out +++ b/contrib/intarray/expected/_int.out @@ -17,7 +17,7 @@ SELECT count(*) from test__int WHERE a @ '{23,50}'; 12 (1 row) -CREATE INDEX text_idx on test__int using gist ( a gist__int_ops ) with ( islossy ); +CREATE INDEX text_idx on test__int using gist ( a gist__int_ops ); SELECT count(*) from test__int WHERE a && '{23,50}'; count ------- @@ -31,7 +31,7 @@ SELECT count(*) from test__int WHERE a @ '{23,50}'; (1 row) drop index text_idx; -CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops ) with ( islossy ); +CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops ); SELECT count(*) from test__int WHERE a && '{23,50}'; count ------- diff --git a/contrib/intarray/sql/_int.sql b/contrib/intarray/sql/_int.sql index b23d406a6a..9635c5f26f 100644 --- a/contrib/intarray/sql/_int.sql +++ b/contrib/intarray/sql/_int.sql @@ -13,13 +13,13 @@ CREATE TABLE test__int( a int[] ); SELECT count(*) from test__int WHERE a && '{23,50}'; SELECT count(*) from test__int WHERE a @ '{23,50}'; -CREATE INDEX text_idx on test__int using gist ( a gist__int_ops ) with ( islossy ); +CREATE INDEX text_idx on test__int using gist ( a gist__int_ops ); SELECT count(*) from test__int WHERE a && '{23,50}'; SELECT count(*) from test__int WHERE a @ '{23,50}'; drop index text_idx; -CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops ) with ( islossy ); +CREATE INDEX text_idx on test__int using gist ( a gist__intbig_ops ); SELECT count(*) from test__int WHERE a && '{23,50}'; SELECT count(*) from test__int WHERE a @ '{23,50}'; diff --git a/contrib/rtree_gist/expected/rtree_gist.out b/contrib/rtree_gist/expected/rtree_gist.out index 86404e40d9..27f88a859a 100644 --- a/contrib/rtree_gist/expected/rtree_gist.out +++ b/contrib/rtree_gist/expected/rtree_gist.out @@ -36,7 +36,7 @@ select count(*) from polytmp where p && '(1000,1000),(0,0)'::polygon; (1 row) drop index pix; -create index pix on polytmp using gist (p gist_poly_ops) with(islossy); +create index pix on polytmp using gist (p gist_poly_ops); select count(*) from polytmp where p && '(1000,1000),(0,0)'::polygon; count ------- diff --git a/contrib/rtree_gist/rtree_gist.sql.in b/contrib/rtree_gist/rtree_gist.sql.in index 68fe8b5edb..c030fe471b 100644 --- a/contrib/rtree_gist/rtree_gist.sql.in +++ b/contrib/rtree_gist/rtree_gist.sql.in @@ -21,123 +21,154 @@ create function gbox_union(bytea, opaque) returns box as 'MODULE_PATHNAME' langu create function gbox_same(box, box, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; --- add a new opclass ---INSERT INTO pg_opclass (opcname, opcdeftype) --- SELECT 'gist_box_ops', oid --- FROM pg_type --- WHERE typname = 'box'; -INSERT INTO pg_opclass (opcname, opcdeftype) values ( 'gist_box_ops', 0 ); - ---SELECT oid, opcname FROM pg_opclass WHERE opcname = 'gist_box_ops'; +-- add a new opclass (non-default) +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'gist'), + 'gist_box_ops', + (SELECT oid FROM pg_type WHERE typname = 'box'), + false, + 0); -- get the comparators for boxes and store them in a tmp table SELECT o.oid AS opoid, o.oprname -INTO TABLE rt_ops_tmp +INTO TEMP TABLE rt_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and t.typname = 'box'; -- using the tmp table, generate the amop entries -- box_left -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 1, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '<<'; -- box_overleft -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 2 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 2, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '&<'; -- box_overlap -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '&&'; -- box_overright -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 4 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 4, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '&>'; -- box_right -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 5 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 5, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '>>'; -- box_same -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 6 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 6, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '~='; -- box_contains -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 7 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 7, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '~'; -- box_contained -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 8 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 8, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and c.oprname = '@'; DROP table rt_ops_tmp; -- add the entries to amproc for the support methods -- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_box_ops' + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and proname = 'gbox_consistent'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 2 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and proname = 'gbox_union'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 3 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and proname = 'gbox_compress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 4 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and proname = 'rtree_decompress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 5 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and proname = 'gbox_penalty'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 6 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and proname = 'gbox_picksplit'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 7 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_box_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_box_ops' and proname = 'gbox_same'; -- @@ -160,124 +191,155 @@ create function gpoly_union(bytea, opaque) returns opaque as 'MODULE_PATHNAME' l create function gpoly_same(opaque, opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; --- add a new opclass ---INSERT INTO pg_opclass (opcname, opcdeftype) --- SELECT 'gist_poly_ops', oid --- FROM pg_type --- WHERE typname = 'polygon'; -INSERT INTO pg_opclass (opcname, opcdeftype) values ( 'gist_poly_ops', 0 ); - ---SELECT oid, opcname FROM pg_opclass WHERE opcname = 'gist_poly_ops'; +-- add a new opclass (non-default) +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'gist'), + 'gist_poly_ops', + (SELECT oid FROM pg_type WHERE typname = 'polygon'), + false, + 0); -- get the comparators for polygons and store them in a tmp table -- hack for 757 (poly_contain_pt) Teodor SELECT o.oid AS opoid, o.oprname -INTO TABLE rt_ops_tmp +INTO TEMP TABLE rt_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oid <> 757 and t.typname = 'polygon'; -- using the tmp table, generate the amop entries -- poly_left -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 1, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '<<'; -- poly_overleft -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 2 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 2, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '&<'; -- poly_overlap -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '&&'; -- poly_overright -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 4 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 4, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '&>'; -- poly_right -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 5 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 5, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '>>'; -- poly_same -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 6 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 6, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '~='; -- poly_contains -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 7 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 7, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '~'; -- poly_contained -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 8 - FROM pg_am am, pg_opclass opcl, rt_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 8, false, c.opoid + FROM pg_opclass opcl, rt_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and c.oprname = '@'; DROP table rt_ops_tmp; -- add the entries to amproc for the support methods -- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_poly_ops' + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and proname = 'gpoly_consistent'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 2 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and proname = 'gpoly_union'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 3 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and proname = 'gpoly_compress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 4 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and proname = 'rtree_decompress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 5 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and proname = 'gpoly_penalty'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 6 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and proname = 'gpoly_picksplit'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 7 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_poly_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_poly_ops' and proname = 'gpoly_same'; end transaction; diff --git a/contrib/rtree_gist/sql/rtree_gist.sql b/contrib/rtree_gist/sql/rtree_gist.sql index a050621322..592d4c3e78 100644 --- a/contrib/rtree_gist/sql/rtree_gist.sql +++ b/contrib/rtree_gist/sql/rtree_gist.sql @@ -32,7 +32,7 @@ select count(*) from polytmp where p && '(1000,1000),(0,0)'::polygon; drop index pix; -create index pix on polytmp using gist (p gist_poly_ops) with(islossy); +create index pix on polytmp using gist (p gist_poly_ops); select count(*) from polytmp where p && '(1000,1000),(0,0)'::polygon; diff --git a/contrib/seg/seg.sql.in b/contrib/seg/seg.sql.in index f2c3f834c8..74739dfc5c 100644 --- a/contrib/seg/seg.sql.in +++ b/contrib/seg/seg.sql.in @@ -236,15 +236,18 @@ CREATE FUNCTION gseg_same(seg, seg, opaque) RETURNS opaque -- register the default opclass for indexing -INSERT INTO pg_opclass (opcname, opcdeftype) - SELECT 'gist_seg_ops', oid - FROM pg_type - WHERE typname = 'seg'; +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'gist'), + 'gist_seg_ops', + (SELECT oid FROM pg_type WHERE typname = 'seg'), + true, + 0); -- get the comparators for segments and store them in a tmp table SELECT o.oid AS opoid, o.oprname -INTO TABLE seg_ops_tmp +INTO TEMP TABLE seg_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'seg'; @@ -255,59 +258,75 @@ WHERE o.oprleft = t.oid and o.oprright = t.oid -- using the tmp table, generate the amop entries -- seg_left -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 1, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '<<'; -- seg_overleft -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 2 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 2, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '&<'; -- seg_overlap -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '&&'; -- seg_overright -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 4 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 4, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '&>'; -- seg_right -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 5 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 5, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '>>'; -- seg_same -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 6 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 6, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '='; -- seg_contains -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 7 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 7, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '@'; -- seg_contained -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 8 - FROM pg_am am, pg_opclass opcl, seg_ops_tmp c - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 8, false, c.opoid + FROM pg_opclass opcl, seg_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and c.oprname = '~'; DROP TABLE seg_ops_tmp; @@ -316,46 +335,60 @@ DROP TABLE seg_ops_tmp; -- add the entries to amproc for the support methods -- note the amprocnum numbers associated with each are specific! -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and proname = 'gseg_consistent'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 2 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and proname = 'gseg_union'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 3 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and proname = 'gseg_compress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 4 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and proname = 'gseg_decompress'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 5 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and proname = 'gseg_penalty'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 6 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and proname = 'gseg_picksplit'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 7 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'gist' and opcname = 'gist_seg_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_seg_ops' and proname = 'gseg_same'; END TRANSACTION; diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index bc5bab0a47..e2c3139d85 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ <!-- Documentation of the system catalogs, directed toward PostgreSQL developers - $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.20 2001/08/10 18:57:32 tgl Exp $ + $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.21 2001/08/21 16:35:58 tgl Exp $ --> <chapter id="catalogs"> @@ -1051,13 +1051,6 @@ </row> <row> - <entry>indislossy</entry> - <entry><type>bool</type></entry> - <entry></entry> - <entry>???</entry> - </row> - - <row> <entry>indisunique</entry> <entry><type>bool</type></entry> <entry></entry> diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index d81ce77517..9c5b805bb2 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1,4 +1,4 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v 1.20 2001/07/16 05:06:57 tgl Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/indices.sgml,v 1.21 2001/08/21 16:35:59 tgl Exp $ --> <chapter id="indexes"> <title id="indexes-title">Indexes</title> @@ -401,15 +401,14 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> <para> The following query shows all defined operator classes: <programlisting> -SELECT am.amname AS acc_name, +SELECT am.amname AS acc_method, opc.opcname AS ops_name, opr.oprname AS ops_comp - FROM pg_am am, pg_amop amop, - pg_opclass opc, pg_operator opr - WHERE amop.amopid = am.oid AND + FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr + WHERE opc.opcamid = am.oid AND amop.amopclaid = opc.oid AND amop.amopopr = opr.oid - ORDER BY acc_name, ops_name, ops_comp; + ORDER BY acc_method, ops_name, ops_comp </programlisting> </para> </sect1> diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index efb5c24408..f2b042d771 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.21 2001/08/06 18:09:45 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v 1.22 2001/08/21 16:35:59 tgl Exp $ Postgres documentation --> @@ -374,9 +374,8 @@ ERROR: Cannot create index: 'index_name' already exists. SELECT am.amname AS acc_method, opc.opcname AS ops_name, opr.oprname AS ops_comp - FROM pg_am am, pg_amop amop, - pg_opclass opc, pg_operator opr - WHERE amop.amopid = am.oid AND + FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr + WHERE opc.opcamid = am.oid AND amop.amopclaid = opc.oid AND amop.amopopr = opr.oid ORDER BY acc_method, ops_name, ops_comp diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index 5b747fe603..b391caebb8 100644 --- a/doc/src/sgml/xindex.sgml +++ b/doc/src/sgml/xindex.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/xindex.sgml,v 1.15 2001/05/17 21:50:17 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/xindex.sgml,v 1.16 2001/08/21 16:35:59 tgl Exp $ Postgres documentation --> @@ -27,13 +27,13 @@ Postgres documentation </para> <para> - The <filename>pg_am</filename> table contains one row for every user - defined access method. Support for the heap access method is built into + The <filename>pg_am</filename> table contains one row for every index + access method. Support for the heap access method is built into <productname>Postgres</productname>, but every other access method is - described here. The schema is + described in <filename>pg_am</filename>. The schema is <table tocentry="1"> - <title>Index Schema</title> + <title>Index Access Method Schema</title> <tgroup cols="2"> <thead> @@ -65,6 +65,22 @@ Postgres documentation number of the strategy operator that describes the sort order</entry> </row> <row> + <entry>amcanunique</entry> + <entry>does AM support UNIQUE indexes?</entry> + </row> + <row> + <entry>amcanmulticol</entry> + <entry>does AM support multi-column indexes?</entry> + </row> + <row> + <entry>amindexnulls</entry> + <entry>does AM support NULL index entries?</entry> + </row> + <row> + <entry>amconcurrent</entry> + <entry>does AM support concurrent updates?</entry> + </row> + <row> <entry>amgettuple</entry> </row> <row> @@ -73,8 +89,8 @@ Postgres documentation <row> <entry>...</entry> <entry>procedure identifiers for interface routines to the access - method. For example, regproc ids for opening, closing, and - getting rows from the access method appear here.</entry> + method. For example, regproc ids for opening, closing, and + getting rows from the access method appear here.</entry> </row> </tbody> </tgroup> @@ -84,9 +100,9 @@ Postgres documentation <para> The <acronym>object ID</acronym> of the row in <filename>pg_am</filename> is used as a foreign key in a lot of other - tables. You do not need to add a new rows to this table; all that + tables. You do not need to add a new row to this table; all that you are interested in is the <acronym>object ID</acronym> of the access - method row you want to extend: + method you want to extend: <programlisting> SELECT oid FROM pg_am WHERE amname = 'btree'; @@ -124,7 +140,7 @@ SELECT oid FROM pg_am WHERE amname = 'btree'; <para> Defining a new set of strategies is beyond the scope of this discussion, but we'll explain how <acronym>B-tree</acronym> strategies work because - you'll need to know that to add a new operator class. In the + you'll need to know that to add a new B-tree operator class. In the <filename>pg_am</filename> table, the amstrategies column is the number of strategies defined for this access method. For <acronym>B-tree</acronym>s, this number is 5. These strategies @@ -167,25 +183,25 @@ SELECT oid FROM pg_am WHERE amname = 'btree'; </para> <para> - The idea is that you'll need to add procedures corresponding to the + The idea is that you'll need to add operators corresponding to the comparisons above to the <filename>pg_amop</filename> relation (see below). The access method code can use these strategy numbers, regardless of data type, to figure out how to partition the <acronym>B-tree</acronym>, compute selectivity, and so on. Don't worry about the details of adding - procedures yet; just understand that there must be a set of these - procedures for <filename>int2, int4, oid,</filename> and every other + operators yet; just understand that there must be a set of these + operators for <filename>int2, int4, oid,</filename> and every other data type on which a <acronym>B-tree</acronym> can operate. </para> <para> Sometimes, strategies aren't enough information for the system to figure - out how to use an index. Some access methods require other support + out how to use an index. Some access methods require additional support routines in order to work. For example, the <acronym>B-tree</acronym> access method must be able to compare two keys and determine whether one is greater than, equal to, or less than the other. Similarly, the <acronym>R-tree</acronym> access method must be able to compute intersections, unions, and sizes of rectangles. These - operations do not correspond to user qualifications in + operations do not correspond to operators used in qualifications in SQL queries; they are administrative routines used by the access methods, internally. </para> @@ -203,7 +219,7 @@ SELECT oid FROM pg_am WHERE amname = 'btree'; <note> <para> Strictly speaking, this routine can return a negative - number (< 0), 0, or a non-zero positive number (> 0). + number (< 0), zero, or a non-zero positive number (> 0). </para> </note> </para> @@ -211,7 +227,7 @@ SELECT oid FROM pg_am WHERE amname = 'btree'; <para> The <filename>amstrategies</filename> entry in <filename>pg_am</filename> is just the number - of strategies defined for the access method in question. The procedures + of strategies defined for the access method in question. The operators for less than, less equal, and so on don't appear in <filename>pg_am</filename>. Similarly, <filename>amsupport</filename> is just the number of support routines required by the access @@ -222,33 +238,46 @@ SELECT oid FROM pg_am WHERE amname = 'btree'; By the way, the <filename>amorderstrategy</filename> entry tells whether the access method supports ordered scan. Zero means it doesn't; if it does, <filename>amorderstrategy</filename> is the number of the strategy - routine that corresponds to the ordering operator. For example, btree + routine that corresponds to the ordering operator. For example, B-tree has <filename>amorderstrategy</filename> = 1 which is its "less than" strategy number. </para> <para> The next table of interest is <filename>pg_opclass</filename>. This table - exists only to associate an operator class name and perhaps a default type - with an operator class oid. Some existing opclasses are <filename>int2_ops, - int4_ops,</filename> and <filename>oid_ops</filename>. You need to add a - row with your opclass name (for example, + defines operator class names and input data types for each of the operator + classes supported by a given index access method. The same class name + can be used for several different access methods (for example, both B-tree + and hash access methods have operator classes named + <filename>oid_ops</filename>), but a separate + <filename>pg_opclass</filename> row must appear for each access method. + The <filename>oid</filename> of the <filename>pg_opclass</filename> row is + used as a foreign + key in other tables to associate specific operators and support routines + with the operator class. + </para> + + <para> + You need to add a row with your opclass name (for example, <filename>complex_abs_ops</filename>) to - <filename>pg_opclass</filename>. The <filename>oid</filename> of - this row will be a foreign key in other tables, notably - <filename>pg_amop</filename>. + <filename>pg_opclass</filename>: <programlisting> -INSERT INTO pg_opclass (opcname, opcdeftype) - SELECT 'complex_abs_ops', oid FROM pg_type WHERE typname = 'complex'; - -SELECT oid, opcname, opcdeftype +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'btree'), + 'complex_abs_ops', + (SELECT oid FROM pg_type WHERE typname = 'complex'), + true, + 0); + +SELECT oid, * FROM pg_opclass WHERE opcname = 'complex_abs_ops'; - oid | opcname | opcdeftype ---------+-----------------+------------ - 277975 | complex_abs_ops | 277946 + oid | opcamid | opcname | opcintype | opcdefault | opckeytype +--------+---------+-----------------+-----------+------------+------------ + 277975 | 403 | complex_abs_ops | 277946 | t | 0 (1 row) </programlisting> @@ -259,21 +288,17 @@ SELECT oid, opcname, opcdeftype <para> The above example assumes that you want to make this new opclass the - default index opclass for the <filename>complex</filename> datatype. - If you don't, just insert zero into <filename>opcdeftype</filename>, - rather than inserting the datatype's oid: - - <programlisting> -INSERT INTO pg_opclass (opcname, opcdeftype) VALUES ('complex_abs_ops', 0); - </programlisting> - + default B-tree opclass for the <filename>complex</filename> datatype. + If you don't, just set <filename>opcdefault</filename> to false instead. + <filename>opckeytype</filename> is not described here; it should always + be zero for B-tree opclasses. </para> <para> So now we have an access method and an operator class. We still need a set of operators. The procedure for defining operators was discussed earlier in this manual. - For the <filename>complex_abs_ops</filename> operator class on Btrees, + For the <filename>complex_abs_ops</filename> operator class on B-trees, the operators we require are: <programlisting> @@ -286,9 +311,9 @@ INSERT INTO pg_opclass (opcname, opcdeftype) VALUES ('complex_abs_ops', 0); </para> <para> - Suppose the code that implements the functions defined + Suppose the code that implements these functions is stored in the file - <filename>PGROOT/src/tutorial/complex.c</filename> + <filename>PGROOT/src/tutorial/complex.c</filename>. </para> <para> @@ -353,9 +378,16 @@ CREATE FUNCTION complex_abs_eq(complex, complex) <para> Finally, note that these operator functions return Boolean values. - The access methods rely on this fact. (On the other + In practice, all operators defined as index access method strategies + must return Boolean, since they must appear at the top level of a WHERE + clause to be used with an index. + (On the other hand, the support function returns whatever the particular access method - expects -- in this case, a signed integer.) The final routine in the + expects -- in this case, a signed integer.) + </para> + + <para> + The final routine in the file is the "support routine" mentioned when we discussed the amsupport column of the <filename>pg_am</filename> table. We will use this later on. For now, ignore it. @@ -392,7 +424,7 @@ CREATE OPERATOR = ( <programlisting> SELECT o.oid AS opoid, o.oprname - INTO TABLE complex_ops_tmp + INTO TEMP TABLE complex_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'complex'; @@ -416,32 +448,38 @@ CREATE OPERATOR = ( </para> <para> - Now we are ready to update <filename>pg_amop</filename> with our new - operator class. The most important thing in this entire discussion - is that the operators are ordered, from less than through greater - than, in <filename>pg_amop</filename>. We add the rows we need: + Now we are ready to insert entries into <filename>pg_amop</filename> for + our new operator class. These entries must associate the correct + B-tree strategy numbers with each of the operators we need. + The command to insert the less-than operator looks like: <programlisting> - INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' AND + INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 1, false, c.opoid + FROM pg_opclass opcl, complex_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND opcname = 'complex_abs_ops' AND c.oprname = '<'; </programlisting> Now do this for the other operators substituting for the "1" in the - third line above and the "<" in the last line. Note the order: + second line above and the "<" in the last line. Note the order: "less than" is 1, "less than or equal" is 2, "equal" is 3, "greater than or equal" is 4, and "greater than" is 5. </para> <para> - The next step is registration of the "support routine" previously + The field <filename>amopreqcheck</filename> is not discussed here; it + should always be false for B-tree operators. + </para> + + <para> + The final step is registration of the "support routine" previously described in our discussion of <filename>pg_am</filename>. The <filename>oid</filename> of this support routine is stored in the - <filename>pg_amproc</filename> table, keyed by the access method - <filename>oid</filename> and the operator class <filename>oid</filename>. + <filename>pg_amproc</filename> table, keyed by the operator class + <filename>oid</filename> and the support routine number. First, we need to register the function in <productname>Postgres</productname> (recall that we put the <acronym>C</acronym> code that implements this routine in the bottom of @@ -466,18 +504,19 @@ CREATE OPERATOR = ( We can add the new row as follows: <programlisting> - INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT a.oid, b.oid, c.oid, 1 - FROM pg_am a, pg_opclass b, pg_proc c - WHERE a.amname = 'btree' AND - b.opcname = 'complex_abs_ops' AND - c.proname = 'complex_abs_cmp'; + INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, p.oid + FROM pg_opclass opcl, pg_proc p + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND + opcname = 'complex_abs_ops' AND + p.proname = 'complex_abs_cmp'; </programlisting> </para> <para> And we're done! (Whew.) It should now be possible to create - and use btree indexes on <filename>complex</filename> columns. + and use B-tree indexes on <filename>complex</filename> columns. </para> </chapter> diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index 4d0cbb6d9c..779a0aa875 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/access/gist/gist.c,v 1.81 2001/08/10 14:34:28 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/access/gist/gist.c,v 1.82 2001/08/21 16:35:59 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -20,8 +20,10 @@ #include "access/heapam.h" #include "catalog/index.h" #include "catalog/pg_index.h" +#include "catalog/pg_opclass.h" #include "executor/executor.h" #include "miscadmin.h" +#include "utils/lsyscache.h" #include "utils/syscache.h" #include "access/xlogutils.h" @@ -1377,10 +1379,9 @@ gistchoose(Relation r, Page p, IndexTuple it, /* it has compressed entry */ for (i = FirstOffsetNumber; i <= maxoff && sum_grow; i = OffsetNumberNext(i)) { + IndexTuple itup = (IndexTuple) PageGetItem(p, PageGetItemId(p, i)); sum_grow=0; for (j=0; j<r->rd_att->natts; j++) { - IndexTuple itup = (IndexTuple) PageGetItem(p, PageGetItemId(p, i)); - datum = index_getattr(itup, j+1, r->rd_att, &IsNull); gistdentryinit(giststate, j, &entry, datum, r, p, i, ATTSIZE( datum, r, j+1, IsNull ), FALSE, IsNull); gistpenalty( giststate, j, &entry, IsNull, &identry[j], isnull[j], &usize); @@ -1548,20 +1549,32 @@ initGISTstate(GISTSTATE *giststate, Relation index) RegProcedure consistent_proc, union_proc, compress_proc, - decompress_proc; - RegProcedure penalty_proc, + decompress_proc, + penalty_proc, picksplit_proc, equal_proc; - HeapTuple htup; + HeapTuple itup; + HeapTuple ctup; Form_pg_index itupform; - Oid indexrelid; + Form_pg_opclass opclassform; + Oid inputtype; + Oid keytype; int i; - if (index->rd_att->natts >= INDEX_MAX_KEYS) - elog(ERROR, "initGISTstate: numberOfAttributes %d > %d", - index->rd_att->natts, INDEX_MAX_KEYS); + if (index->rd_att->natts > INDEX_MAX_KEYS) + elog(ERROR, "initGISTstate: numberOfAttributes %d > %d", + index->rd_att->natts, INDEX_MAX_KEYS); + + itup = SearchSysCache(INDEXRELID, + ObjectIdGetDatum(RelationGetRelid(index)), + 0, 0, 0); + if (!HeapTupleIsValid(itup)) + elog(ERROR, "initGISTstate: index %u not found", + RelationGetRelid(index)); + itupform = (Form_pg_index) GETSTRUCT(itup); - for(i=0; i<index->rd_att->natts; i++) { + for (i = 0; i < index->rd_att->natts; i++) + { consistent_proc = index_getprocid(index, i+1, GIST_CONSISTENT_PROC ); union_proc = index_getprocid(index, i+1, GIST_UNION_PROC ); compress_proc = index_getprocid(index, i+1, GIST_COMPRESS_PROC ); @@ -1577,37 +1590,35 @@ initGISTstate(GISTSTATE *giststate, Relation index) fmgr_info(picksplit_proc, &((giststate->picksplitFn)[i]) ); fmgr_info(equal_proc, &((giststate->equalFn)[i]) ); - giststate->attbyval[i] = - index->rd_att->attrs[i]->attbyval; + /* Check opclass entry to see if there is a keytype */ + ctup = SearchSysCache(CLAOID, + ObjectIdGetDatum(itupform->indclass[i]), + 0, 0, 0); + if (!HeapTupleIsValid(ctup)) + elog(ERROR, "cache lookup failed for opclass %u", + itupform->indclass[i]); + opclassform = (Form_pg_opclass) GETSTRUCT(ctup); + inputtype = opclassform->opcintype; + keytype = opclassform->opckeytype; + ReleaseSysCache(ctup); + + if (OidIsValid(keytype)) + { + /* index column type is (possibly) different from input data */ + giststate->haskeytype[i] = true; + giststate->attbyval[i] = get_typbyval(inputtype); + giststate->keytypbyval[i] = index->rd_att->attrs[i]->attbyval; + } + else + { + /* Normal case where index column type is same as input data */ + giststate->haskeytype[i] = false; + giststate->attbyval[i] = index->rd_att->attrs[i]->attbyval; + giststate->keytypbyval[i] = false; /* not actually used */ + } } - /* see if key type is different from type of attribute being indexed */ - htup = SearchSysCache(INDEXRELID, - ObjectIdGetDatum(RelationGetRelid(index)), - 0, 0, 0); - if (!HeapTupleIsValid(htup)) - elog(ERROR, "initGISTstate: index %u not found", - RelationGetRelid(index)); - itupform = (Form_pg_index) GETSTRUCT(htup); - giststate->haskeytype = itupform->indhaskeytype; - indexrelid = itupform->indexrelid; - ReleaseSysCache(htup); - - if (giststate->haskeytype) - { - /* key type is different -- is it byval? */ - htup = SearchSysCache(ATTNUM, - ObjectIdGetDatum(indexrelid), - UInt16GetDatum(FirstOffsetNumber), - 0, 0); - if (!HeapTupleIsValid(htup)) - elog(ERROR, "initGISTstate: no attribute tuple %u %d", - indexrelid, FirstOffsetNumber); - giststate->keytypbyval = (((Form_pg_attribute) htup)->attbyval); - ReleaseSysCache(htup); - } - else - giststate->keytypbyval = FALSE; + ReleaseSysCache(itup); } #ifdef GIST_PAGEADDITEM @@ -1670,7 +1681,7 @@ gistdentryinit(GISTSTATE *giststate, int nkey, GISTENTRY *e, GISTENTRY *dep; gistentryinit(*e, k, r, pg, o, b, l); - if (giststate->haskeytype) + if (giststate->haskeytype[nkey]) { if ( b && ! isNull ) { dep = (GISTENTRY *) @@ -1698,7 +1709,7 @@ gistcentryinit(GISTSTATE *giststate, int nkey, GISTENTRY *cep; gistentryinit(*e, k, r, pg, o, b, l); - if (giststate->haskeytype) + if (giststate->haskeytype[nkey]) { if ( ! isNull ) { cep = (GISTENTRY *) @@ -1792,7 +1803,7 @@ gistpenalty( GISTSTATE *giststate, int attno, FunctionCall3(&giststate->penaltyFn[attno], PointerGetDatum(key1), PointerGetDatum(key2), - PointerGetDatum(&penalty)); + PointerGetDatum(penalty)); } #ifdef GISTDEBUG diff --git a/src/backend/access/index/istrat.c b/src/backend/access/index/istrat.c index 188f69b571..35df06aeac 100644 --- a/src/backend/access/index/istrat.c +++ b/src/backend/access/index/istrat.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/access/index/Attic/istrat.c,v 1.51 2001/06/01 02:41:35 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/access/index/Attic/istrat.c,v 1.52 2001/08/21 16:36:00 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -457,61 +457,32 @@ RelationInvokeStrategy(Relation relation, #endif /* ---------------- - * OperatorRelationFillScanKeyEntry + * FillScanKeyEntry * - * Initialize a ScanKey entry given already-opened pg_operator relation. + * Initialize a ScanKey entry for the given operator OID. * ---------------- */ static void -OperatorRelationFillScanKeyEntry(Relation operatorRelation, - Oid operatorObjectId, - ScanKey entry) +FillScanKeyEntry(Oid operatorObjectId, ScanKey entry) { HeapTuple tuple; - HeapScanDesc scan = NULL; - bool cachesearch = (!IsBootstrapProcessingMode()) && IsCacheInitialized(); - if (cachesearch) - { - tuple = SearchSysCache(OPEROID, - ObjectIdGetDatum(operatorObjectId), - 0, 0, 0); - } - else - { - ScanKeyData scanKeyData; - - ScanKeyEntryInitialize(&scanKeyData, 0, - ObjectIdAttributeNumber, - F_OIDEQ, - ObjectIdGetDatum(operatorObjectId)); - - scan = heap_beginscan(operatorRelation, false, SnapshotNow, - 1, &scanKeyData); - - tuple = heap_getnext(scan, 0); - } + tuple = SearchSysCache(OPEROID, + ObjectIdGetDatum(operatorObjectId), + 0, 0, 0); if (!HeapTupleIsValid(tuple)) - { - if (!cachesearch) - heap_endscan(scan); - elog(ERROR, "OperatorRelationFillScanKeyEntry: unknown operator %u", + elog(ERROR, "FillScanKeyEntry: unknown operator %u", operatorObjectId); - } MemSet(entry, 0, sizeof(*entry)); entry->sk_flags = 0; entry->sk_procedure = ((Form_pg_operator) GETSTRUCT(tuple))->oprcode; - if (cachesearch) - ReleaseSysCache(tuple); - else - heap_endscan(scan); + ReleaseSysCache(tuple); if (!RegProcedureIsValid(entry->sk_procedure)) - elog(ERROR, - "OperatorRelationFillScanKeyEntry: no procedure for operator %u", + elog(ERROR, "FillScanKeyEntry: no procedure for operator %u", operatorObjectId); /* @@ -548,160 +519,99 @@ IndexSupportInitialize(IndexStrategy indexStrategy, StrategyNumber maxSupportNumber, AttrNumber maxAttributeNumber) { - Relation relation = NULL; - HeapScanDesc scan = NULL; - ScanKeyData entry[2]; - Relation operatorRelation; HeapTuple tuple; Form_pg_index iform; - StrategyMap map; - AttrNumber attNumber; int attIndex; Oid operatorClassObjectId[INDEX_MAX_KEYS]; - bool cachesearch = (!IsBootstrapProcessingMode()) && IsCacheInitialized(); - - if (cachesearch) - { - tuple = SearchSysCache(INDEXRELID, - ObjectIdGetDatum(indexObjectId), - 0, 0, 0); - } - else - { - ScanKeyEntryInitialize(&entry[0], 0, Anum_pg_index_indexrelid, - F_OIDEQ, - ObjectIdGetDatum(indexObjectId)); - relation = heap_openr(IndexRelationName, AccessShareLock); - scan = heap_beginscan(relation, false, SnapshotNow, 1, entry); - tuple = heap_getnext(scan, 0); - } + maxStrategyNumber = AMStrategies(maxStrategyNumber); + tuple = SearchSysCache(INDEXRELID, + ObjectIdGetDatum(indexObjectId), + 0, 0, 0); if (!HeapTupleIsValid(tuple)) elog(ERROR, "IndexSupportInitialize: no pg_index entry for index %u", indexObjectId); - iform = (Form_pg_index) GETSTRUCT(tuple); *isUnique = iform->indisunique; - maxStrategyNumber = AMStrategies(maxStrategyNumber); - /* * XXX note that the following assumes the INDEX tuple is well formed * and that the *key and *class are 0 terminated. */ for (attIndex = 0; attIndex < maxAttributeNumber; attIndex++) { - if (!OidIsValid(iform->indkey[attIndex])) - { - if (attIndex == InvalidAttrNumber) - elog(ERROR, "IndexSupportInitialize: bogus pg_index tuple"); - break; - } - + if (iform->indkey[attIndex] == InvalidAttrNumber || + !OidIsValid(iform->indclass[attIndex])) + elog(ERROR, "IndexSupportInitialize: bogus pg_index tuple"); operatorClassObjectId[attIndex] = iform->indclass[attIndex]; } - if (cachesearch) - ReleaseSysCache(tuple); - else - { - heap_endscan(scan); - heap_close(relation, AccessShareLock); - } + ReleaseSysCache(tuple); /* if support routines exist for this access method, load them */ if (maxSupportNumber > 0) { - ScanKeyEntryInitialize(&entry[0], 0, Anum_pg_amproc_amid, - F_OIDEQ, - ObjectIdGetDatum(accessMethodObjectId)); - - ScanKeyEntryInitialize(&entry[1], 0, Anum_pg_amproc_amopclaid, - F_OIDEQ, - InvalidOid); /* will set below */ - - relation = heap_openr(AccessMethodProcedureRelationName, - AccessShareLock); - - for (attNumber = 1; attNumber <= maxAttributeNumber; attNumber++) + for (attIndex = 0; attIndex < maxAttributeNumber; attIndex++) { + Oid opclass = operatorClassObjectId[attIndex]; RegProcedure *loc; StrategyNumber support; - loc = &indexSupport[((attNumber - 1) * maxSupportNumber)]; + loc = &indexSupport[attIndex * maxSupportNumber]; for (support = 0; support < maxSupportNumber; ++support) - loc[support] = InvalidOid; - - entry[1].sk_argument = - ObjectIdGetDatum(operatorClassObjectId[attNumber - 1]); - - scan = heap_beginscan(relation, false, SnapshotNow, 2, entry); - - while (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) { - Form_pg_amproc aform; - - aform = (Form_pg_amproc) GETSTRUCT(tuple); - support = aform->amprocnum; - Assert(support > 0 && support <= maxSupportNumber); - loc[support - 1] = aform->amproc; + tuple = SearchSysCache(AMPROCNUM, + ObjectIdGetDatum(opclass), + Int16GetDatum(support+1), + 0, 0); + if (HeapTupleIsValid(tuple)) + { + Form_pg_amproc amprocform; + + amprocform = (Form_pg_amproc) GETSTRUCT(tuple); + loc[support] = amprocform->amproc; + ReleaseSysCache(tuple); + } + else + loc[support] = InvalidOid; } - - heap_endscan(scan); } - heap_close(relation, AccessShareLock); } /* Now load the strategy information for the index operators */ - ScanKeyEntryInitialize(&entry[0], 0, - Anum_pg_amop_amopid, - F_OIDEQ, - ObjectIdGetDatum(accessMethodObjectId)); - - ScanKeyEntryInitialize(&entry[1], 0, - Anum_pg_amop_amopclaid, - F_OIDEQ, - 0); /* will fill below */ - - relation = heap_openr(AccessMethodOperatorRelationName, AccessShareLock); - operatorRelation = heap_openr(OperatorRelationName, AccessShareLock); - - for (attNumber = maxAttributeNumber; attNumber > 0; attNumber--) + for (attIndex = 0; attIndex < maxAttributeNumber; attIndex++) { + Oid opclass = operatorClassObjectId[attIndex]; + StrategyMap map; StrategyNumber strategy; - entry[1].sk_argument = - ObjectIdGetDatum(operatorClassObjectId[attNumber - 1]); - map = IndexStrategyGetStrategyMap(indexStrategy, maxStrategyNumber, - attNumber); + attIndex + 1); for (strategy = 1; strategy <= maxStrategyNumber; strategy++) - ScanKeyEntrySetIllegal(StrategyMapGetScanKeyEntry(map, strategy)); + { + ScanKey mapentry = StrategyMapGetScanKeyEntry(map, strategy); - scan = heap_beginscan(relation, false, SnapshotNow, 2, entry); + tuple = SearchSysCache(AMOPSTRATEGY, + ObjectIdGetDatum(opclass), + Int16GetDatum(strategy), + 0, 0); + if (HeapTupleIsValid(tuple)) + { + Form_pg_amop amopform; - while (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) - { - Form_pg_amop aform; - - aform = (Form_pg_amop) GETSTRUCT(tuple); - strategy = aform->amopstrategy; - Assert(strategy > 0 && strategy <= maxStrategyNumber); - OperatorRelationFillScanKeyEntry(operatorRelation, - aform->amopopr, - StrategyMapGetScanKeyEntry(map, strategy)); + amopform = (Form_pg_amop) GETSTRUCT(tuple); + FillScanKeyEntry(amopform->amopopr, mapentry); + ReleaseSysCache(tuple); + } + else + ScanKeyEntrySetIllegal(mapentry); } - - heap_endscan(scan); } - - heap_close(operatorRelation, AccessShareLock); - heap_close(relation, AccessShareLock); } /* ---------------- diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 28da7541d5..0265561856 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/bootstrap/bootparse.y,v 1.37 2001/08/10 18:57:33 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/bootstrap/bootparse.y,v 1.38 2001/08/21 16:36:00 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -246,7 +246,7 @@ Boot_DeclareIndexStmt: DefineIndex(LexIDStr($5), LexIDStr($3), LexIDStr($7), - $9, NIL, 0, 0, 0, NIL); + $9, false, false, NULL, NIL); do_end(); } ; @@ -259,7 +259,7 @@ Boot_DeclareUniqueIndexStmt: DefineIndex(LexIDStr($6), LexIDStr($4), LexIDStr($8), - $10, NIL, 1, 0, 0, NIL); + $10, true, false, NULL, NIL); do_end(); } ; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 4dbc1648c1..9ea03697b4 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/catalog/index.c,v 1.160 2001/08/17 23:50:00 inoue Exp $ + * $Header: /cvsroot/pgsql/src/backend/catalog/index.c,v 1.161 2001/08/21 16:36:00 tgl Exp $ * * * INTERFACE ROUTINES @@ -71,9 +71,9 @@ static void InitializeAttributeOids(Relation indexRelation, int numatts, Oid indexoid); static void AppendAttributeTuples(Relation indexRelation, int numatts); static void UpdateIndexRelation(Oid indexoid, Oid heapoid, - IndexInfo *indexInfo, - Oid *classOids, - bool islossy, bool primary); + IndexInfo *indexInfo, + Oid *classOids, + bool primary); static Oid IndexGetRelation(Oid indexId); static bool activate_index(Oid indexId, bool activate, bool inplace); @@ -495,7 +495,6 @@ UpdateIndexRelation(Oid indexoid, Oid heapoid, IndexInfo *indexInfo, Oid *classOids, - bool islossy, bool primary) { Form_pg_index indexForm; @@ -535,8 +534,6 @@ UpdateIndexRelation(Oid indexoid, indexForm->indrelid = heapoid; indexForm->indproc = indexInfo->ii_FuncOid; indexForm->indisclustered = false; /* not used */ - indexForm->indislossy = islossy; - indexForm->indhaskeytype = true; /* used by GIST */ indexForm->indisunique = indexInfo->ii_Unique; indexForm->indisprimary = primary; memcpy((char *) &indexForm->indpred, (char *) predText, predLen); @@ -671,7 +668,6 @@ index_create(char *heapRelationName, IndexInfo *indexInfo, Oid accessMethodObjectId, Oid *classObjectId, - bool islossy, bool primary, bool allow_system_table_mods) { @@ -779,7 +775,7 @@ index_create(char *heapRelationName, * ---------------- */ UpdateIndexRelation(indexoid, heapoid, indexInfo, - classObjectId, islossy, primary); + classObjectId, primary); /* * initialize the index strategy diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c index 361295fe8b..28b8516947 100644 --- a/src/backend/catalog/indexing.c +++ b/src/backend/catalog/indexing.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/catalog/indexing.c,v 1.81 2001/08/10 18:57:34 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/catalog/indexing.c,v 1.82 2001/08/21 16:36:01 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -36,9 +36,9 @@ char *Name_pg_aggregate_indices[Num_pg_aggregate_indices] = char *Name_pg_am_indices[Num_pg_am_indices] = {AmNameIndex, AmOidIndex}; char *Name_pg_amop_indices[Num_pg_amop_indices] = -{AccessMethodOpidIndex, AccessMethodStrategyIndex}; +{AccessMethodOperatorIndex, AccessMethodStrategyIndex}; char *Name_pg_amproc_indices[Num_pg_amproc_indices] = -{AccessProcedureIndex}; +{AccessMethodProcedureIndex}; char *Name_pg_attr_indices[Num_pg_attr_indices] = {AttributeRelidNameIndex, AttributeRelidNumIndex}; char *Name_pg_attrdef_indices[Num_pg_attrdef_indices] = @@ -58,7 +58,7 @@ char *Name_pg_language_indices[Num_pg_language_indices] = char *Name_pg_largeobject_indices[Num_pg_largeobject_indices] = {LargeObjectLOidPNIndex}; char *Name_pg_opclass_indices[Num_pg_opclass_indices] = -{OpclassDeftypeIndex, OpclassNameIndex, OpclassOidIndex}; +{OpclassAmNameIndex, OpclassOidIndex}; char *Name_pg_operator_indices[Num_pg_operator_indices] = {OperatorOidIndex, OperatorNameIndex}; char *Name_pg_proc_indices[Num_pg_proc_indices] = diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c index 0d76c6e8d3..7d5d3e6cbd 100644 --- a/src/backend/commands/cluster.c +++ b/src/backend/commands/cluster.c @@ -15,7 +15,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/cluster.c,v 1.68 2001/08/10 18:57:34 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/cluster.c,v 1.69 2001/08/21 16:36:01 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -225,7 +225,6 @@ copy_index(Oid OIDOldIndex, Oid OIDNewHeap, char *NewIndexName) indexInfo, Old_pg_index_relation_Form->relam, Old_pg_index_Form->indclass, - Old_pg_index_Form->indislossy, Old_pg_index_Form->indisprimary, allowSystemTableMods); diff --git a/src/backend/commands/command.c b/src/backend/commands/command.c index 1e8b7c5699..81ce6e1185 100644 --- a/src/backend/commands/command.c +++ b/src/backend/commands/command.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.140 2001/08/10 18:57:34 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.141 2001/08/21 16:36:01 tgl Exp $ * * NOTES * The PerformAddAttribute() code, like most of the relation @@ -1902,12 +1902,12 @@ AlterTableCreateToastTable(const char *relationName, bool silent) indexInfo->ii_FuncOid = InvalidOid; indexInfo->ii_Unique = true; - classObjectId[0] = OID_OPS_OID; - classObjectId[1] = INT4_OPS_OID; + classObjectId[0] = OID_BTREE_OPS_OID; + classObjectId[1] = INT4_BTREE_OPS_OID; toast_idxid = index_create(toast_relname, toast_idxname, indexInfo, BTREE_AM_OID, classObjectId, - false, true, true); + true, true); /* * Update toast rel's pg_class entry to show that it has an index. diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 3b556da3ed..2304dd0b0c 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -8,26 +8,19 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/indexcmds.c,v 1.56 2001/08/10 18:57:34 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/indexcmds.c,v 1.57 2001/08/21 16:36:02 tgl Exp $ * *------------------------------------------------------------------------- */ #include "postgres.h" -#include "access/genam.h" #include "access/heapam.h" #include "catalog/catalog.h" #include "catalog/catname.h" #include "catalog/heap.h" #include "catalog/index.h" -#include "catalog/pg_am.h" -#include "catalog/pg_amop.h" -#include "catalog/pg_database.h" -#include "catalog/pg_index.h" #include "catalog/pg_opclass.h" -#include "catalog/pg_operator.h" -#include "catalog/pg_proc.h" #include "commands/defrem.h" #include "miscadmin.h" #include "optimizer/clauses.h" @@ -36,7 +29,6 @@ #include "parser/parsetree.h" #include "parser/parse_coerce.h" #include "parser/parse_func.h" -#include "parser/parse_type.h" #include "utils/builtins.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" @@ -57,7 +49,7 @@ static void NormIndexAttrs(IndexInfo *indexInfo, Oid *classOidP, char *accessMethodName, Oid accessMethodId); static Oid GetAttrOpClass(IndexElem *attribute, Oid attrType, char *accessMethodName, Oid accessMethodId); -static char *GetDefaultOpClass(Oid atttypid); +static Oid GetDefaultOpClass(Oid attrType, Oid accessMethodId); /* * DefineIndex @@ -65,16 +57,14 @@ static char *GetDefaultOpClass(Oid atttypid); * * 'attributeList' is a list of IndexElem specifying either a functional * index or a list of attributes to index on. - * 'parameterList' is a list of DefElem specified in the with clause. * 'predicate' is the qual specified in the where clause. - * 'rangetable' is needed to interpret the predicate + * 'rangetable' is needed to interpret the predicate. */ void DefineIndex(char *heapRelationName, char *indexRelationName, char *accessMethodName, List *attributeList, - List *parameterList, bool unique, bool primary, Expr *predicate, @@ -88,8 +78,6 @@ DefineIndex(char *heapRelationName, IndexInfo *indexInfo; int numberOfAttributes; List *cnfPred = NIL; - bool lossy = false; - List *pl; /* * count attributes in index @@ -130,20 +118,6 @@ DefineIndex(char *heapRelationName, ReleaseSysCache(tuple); /* - * WITH clause reinstated to handle lossy indices. -- JMH, 7/22/96 - */ - foreach(pl, parameterList) - { - DefElem *param = (DefElem *) lfirst(pl); - - if (!strcasecmp(param->defname, "islossy")) - lossy = true; - else - elog(NOTICE, "Unrecognized index attribute \"%s\" ignored", - param->defname); - } - - /* * Convert the partial-index predicate from parsetree form to * an implicit-AND qual expression, for easier evaluation at runtime. * While we are at it, we reduce it to a canonical (CNF or DNF) form @@ -203,7 +177,7 @@ DefineIndex(char *heapRelationName, index_create(heapRelationName, indexRelationName, indexInfo, accessMethodId, classObjectId, - lossy, primary, allowSystemTableMods); + primary, allowSystemTableMods); /* * We update the relation's pg_class tuple even if it already has @@ -390,111 +364,109 @@ static Oid GetAttrOpClass(IndexElem *attribute, Oid attrType, char *accessMethodName, Oid accessMethodId) { - Relation relation; - HeapScanDesc scan; - ScanKeyData entry[2]; HeapTuple tuple; Oid opClassId, - oprId; - bool doTypeCheck = true; + opInputType; if (attribute->class == NULL) { /* no operator class specified, so find the default */ - attribute->class = GetDefaultOpClass(attrType); - if (attribute->class == NULL) - elog(ERROR, "data type %s has no default operator class" + opClassId = GetDefaultOpClass(attrType, accessMethodId); + if (!OidIsValid(opClassId)) + elog(ERROR, "data type %s has no default operator class for access method \"%s\"" "\n\tYou must specify an operator class for the index or define a" "\n\tdefault operator class for the data type", - format_type_be(attrType)); - /* assume we need not check type compatibility */ - doTypeCheck = false; + format_type_be(attrType), accessMethodName); + return opClassId; } - opClassId = GetSysCacheOid(CLANAME, - PointerGetDatum(attribute->class), - 0, 0, 0); - if (!OidIsValid(opClassId)) - elog(ERROR, "DefineIndex: opclass \"%s\" not found", - attribute->class); - /* - * Assume the opclass is supported by this index access method if we - * can find at least one relevant entry in pg_amop. + * Find the index operator class and verify that it accepts this + * datatype. Note we will accept binary compatibility. */ - ScanKeyEntryInitialize(&entry[0], 0, - Anum_pg_amop_amopid, - F_OIDEQ, - ObjectIdGetDatum(accessMethodId)); - ScanKeyEntryInitialize(&entry[1], 0, - Anum_pg_amop_amopclaid, - F_OIDEQ, - ObjectIdGetDatum(opClassId)); - - relation = heap_openr(AccessMethodOperatorRelationName, AccessShareLock); - scan = heap_beginscan(relation, false, SnapshotNow, 2, entry); - - if (!HeapTupleIsValid(tuple = heap_getnext(scan, 0))) - elog(ERROR, "DefineIndex: opclass \"%s\" not supported by access method \"%s\"", + tuple = SearchSysCache(CLAAMNAME, + ObjectIdGetDatum(accessMethodId), + PointerGetDatum(attribute->class), + 0, 0); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "DefineIndex: operator class \"%s\" not supported by access method \"%s\"", attribute->class, accessMethodName); + opClassId = tuple->t_data->t_oid; + opInputType = ((Form_pg_opclass) GETSTRUCT(tuple))->opcintype; + ReleaseSysCache(tuple); - oprId = ((Form_pg_amop) GETSTRUCT(tuple))->amopopr; + if (attrType != opInputType && + !IS_BINARY_COMPATIBLE(attrType, opInputType)) + elog(ERROR, "operator class \"%s\" does not accept data type %s", + attribute->class, format_type_be(attrType)); - heap_endscan(scan); - heap_close(relation, AccessShareLock); + return opClassId; +} + +static Oid +GetDefaultOpClass(Oid attrType, Oid accessMethodId) +{ + Relation relation; + ScanKeyData entry[1]; + HeapScanDesc scan; + HeapTuple tuple; + int nexact = 0; + int ncompatible = 0; + Oid exactOid = InvalidOid; + Oid compatibleOid = InvalidOid; /* - * Make sure the operators associated with this opclass actually - * accept the column data type. This prevents possible coredumps - * caused by user errors like applying text_ops to an int4 column. We - * will accept an opclass as OK if the operator's input datatype is - * binary-compatible with the actual column datatype. Note we assume - * that all the operators associated with an opclass accept the same - * datatypes, so checking the first one we happened to find in the - * table is sufficient. + * We scan through all the opclasses available for the access method, + * looking for one that is marked default and matches the target type + * (either exactly or binary-compatibly, but prefer an exact match). + * + * We could find more than one binary-compatible match, in which case we + * require the user to specify which one he wants. If we find more than + * one exact match, then someone put bogus entries in pg_opclass. * - * If the opclass was the default for the datatype, assume we can skip - * this check --- that saves a few cycles in the most common case. If - * pg_opclass is wrong then we're probably screwed anyway... + * We could use an indexscan here, but since pg_opclass is small + * and a scan on opcamid won't be very selective, the indexscan would + * probably actually be slower than heapscan. */ - if (doTypeCheck) + ScanKeyEntryInitialize(&entry[0], 0x0, + Anum_pg_opclass_opcamid, + F_OIDEQ, + ObjectIdGetDatum(accessMethodId)); + + relation = heap_openr(OperatorClassRelationName, AccessShareLock); + scan = heap_beginscan(relation, false, SnapshotNow, 1, entry); + + while (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) { - tuple = SearchSysCache(OPEROID, - ObjectIdGetDatum(oprId), - 0, 0, 0); - if (HeapTupleIsValid(tuple)) + Form_pg_opclass opclass = (Form_pg_opclass) GETSTRUCT(tuple); + + if (opclass->opcdefault) { - Form_pg_operator optup = (Form_pg_operator) GETSTRUCT(tuple); - Oid opInputType = (optup->oprkind == 'l') ? - optup->oprright : optup->oprleft; - - if (attrType != opInputType && - !IS_BINARY_COMPATIBLE(attrType, opInputType)) - elog(ERROR, "operator class \"%s\" does not accept data type %s", - attribute->class, format_type_be(attrType)); - ReleaseSysCache(tuple); + if (opclass->opcintype == attrType) + { + nexact++; + exactOid = tuple->t_data->t_oid; + } + else if (IS_BINARY_COMPATIBLE(opclass->opcintype, attrType)) + { + ncompatible++; + compatibleOid = tuple->t_data->t_oid; + } } } - return opClassId; -} - -static char * -GetDefaultOpClass(Oid atttypid) -{ - HeapTuple tuple; - char *result; - - tuple = SearchSysCache(CLADEFTYPE, - ObjectIdGetDatum(atttypid), - 0, 0, 0); - if (!HeapTupleIsValid(tuple)) - return NULL; + heap_endscan(scan); + heap_close(relation, AccessShareLock); - result = pstrdup(NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname)); + if (nexact == 1) + return exactOid; + if (nexact != 0) + elog(ERROR, "pg_opclass contains multiple default opclasses for data tyype %s", + format_type_be(attrType)); + if (ncompatible == 1) + return compatibleOid; - ReleaseSysCache(tuple); - return result; + return InvalidOid; } /* diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 68ad14119f..26da8d4793 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.153 2001/08/16 20:38:53 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.154 2001/08/21 16:36:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1166,7 +1166,6 @@ _copyIndexOptInfo(IndexOptInfo *from) newnode->indproc = from->indproc; Node_Copy(from, newnode, indpred); newnode->unique = from->unique; - newnode->lossy = from->lossy; return newnode; } @@ -2059,7 +2058,6 @@ _copyIndexStmt(IndexStmt *from) newnode->relname = pstrdup(from->relname); newnode->accessMethod = pstrdup(from->accessMethod); Node_Copy(from, newnode, indexParams); - Node_Copy(from, newnode, withClause); Node_Copy(from, newnode, whereClause); Node_Copy(from, newnode, rangetable); newnode->unique = from->unique; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 80ffc01dc1..c2cd2109fe 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -20,7 +20,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.101 2001/08/16 20:38:53 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.102 2001/08/21 16:36:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -926,8 +926,6 @@ _equalIndexStmt(IndexStmt *a, IndexStmt *b) return false; if (!equal(a->indexParams, b->indexParams)) return false; - if (!equal(a->withClause, b->withClause)) - return false; if (!equal(a->whereClause, b->whereClause)) return false; if (!equal(a->rangetable, b->rangetable)) diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 335a1eb606..5b30fd2a21 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -5,7 +5,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Header: /cvsroot/pgsql/src/backend/nodes/outfuncs.c,v 1.144 2001/08/16 20:38:53 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/outfuncs.c,v 1.145 2001/08/21 16:36:02 tgl Exp $ * * NOTES * Every (plan) node in POSTGRES has an associated "out" routine which @@ -133,16 +133,10 @@ _outIndexStmt(StringInfo str, IndexStmt *node) _outToken(str, node->accessMethod); appendStringInfo(str, " :indexParams "); _outNode(str, node->indexParams); - - appendStringInfo(str, " :withClause "); - _outNode(str, node->withClause); - appendStringInfo(str, " :whereClause "); _outNode(str, node->whereClause); - appendStringInfo(str, " :rangetable "); _outNode(str, node->rangetable); - appendStringInfo(str, " :unique %s :primary %s ", booltostr(node->unique), booltostr(node->primary)); diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 2099adc664..73d72a03cb 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -42,7 +42,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.77 2001/06/11 00:17:08 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v 1.78 2001/08/21 16:36:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -339,16 +339,16 @@ cost_index(Path *path, Query *root, * * Normally the indexquals will be removed from the list of * restriction clauses that we have to evaluate as qpquals, so we - * should subtract their costs from baserestrictcost. For a lossy - * index, however, we will have to recheck all the quals and so - * mustn't subtract anything. Also, if we are doing a join then some - * of the indexquals are join clauses and shouldn't be subtracted. - * Rather than work out exactly how much to subtract, we don't - * subtract anything in that case either. + * should subtract their costs from baserestrictcost. XXX For a lossy + * index, not all the quals will be removed and so we really shouldn't + * subtract their costs; but detecting that seems more expensive than + * it's worth. Also, if we are doing a join then some of the indexquals + * are join clauses and shouldn't be subtracted. Rather than work out + * exactly how much to subtract, we don't subtract anything. */ cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost; - if (!index->lossy && !is_injoin) + if (!is_injoin) cpu_per_tuple -= cost_qual_eval(indexQuals); run_cost += cpu_per_tuple * tuples_fetched; diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 8c0b894834..f30097ec28 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.110 2001/08/06 18:09:45 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v 1.111 2001/08/21 16:36:02 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -46,16 +46,16 @@ * * Determine whether we should continue matching index keys in a clause. * Depends on if there are more to match or if this is a functional index. - * In the latter case we stop after the first match since the there can - * be only key (i.e. the function's return value) and the attributes in + * In the latter case we stop after the first match since there can + * be only 1 key (i.e. the function's return value) and the attributes in * keys list represent the arguments to the function. -mer 3 Oct. 1991 */ #define DoneMatchingIndexKeys(indexkeys, index) \ (indexkeys[0] == 0 || \ (index->indproc != InvalidOid)) -#define is_indexable_operator(clause,opclass,relam,indexkey_on_left) \ - (indexable_operator(clause,opclass,relam,indexkey_on_left) != InvalidOid) +#define is_indexable_operator(clause,opclass,indexkey_on_left) \ + (indexable_operator(clause,opclass,indexkey_on_left) != InvalidOid) static void match_index_orclauses(RelOptInfo *rel, IndexOptInfo *index, @@ -92,7 +92,7 @@ static bool match_index_to_operand(int indexkey, Var *operand, RelOptInfo *rel, IndexOptInfo *index); static bool function_index_operand(Expr *funcOpnd, RelOptInfo *rel, IndexOptInfo *index); -static bool match_special_index_operator(Expr *clause, Oid opclass, Oid relam, +static bool match_special_index_operator(Expr *clause, Oid opclass, bool indexkey_on_left); static List *prefix_quals(Var *leftop, Oid expr_op, char *prefix, Pattern_Prefix_Status pstatus); @@ -754,30 +754,28 @@ match_clause_to_indexkey(RelOptInfo *rel, if (match_index_to_operand(indexkey, leftop, rel, index) && is_pseudo_constant_clause((Node *) rightop)) { - if (is_indexable_operator(clause, opclass, index->relam, true)) + if (is_indexable_operator(clause, opclass, true)) return true; /* * If we didn't find a member of the index's opclass, see * whether it is a "special" indexable operator. */ - if (match_special_index_operator(clause, opclass, index->relam, - true)) + if (match_special_index_operator(clause, opclass, true)) return true; return false; } if (match_index_to_operand(indexkey, rightop, rel, index) && is_pseudo_constant_clause((Node *) leftop)) { - if (is_indexable_operator(clause, opclass, index->relam, false)) + if (is_indexable_operator(clause, opclass, false)) return true; /* * If we didn't find a member of the index's opclass, see * whether it is a "special" indexable operator. */ - if (match_special_index_operator(clause, opclass, index->relam, - false)) + if (match_special_index_operator(clause, opclass, false)) return true; return false; } @@ -799,7 +797,7 @@ match_clause_to_indexkey(RelOptInfo *rel, isIndexable = !intMember(lfirsti(rel->relids), othervarnos) && !contain_noncachable_functions((Node *) rightop) && - is_indexable_operator(clause, opclass, index->relam, true); + is_indexable_operator(clause, opclass, true); freeList(othervarnos); return isIndexable; } @@ -811,7 +809,7 @@ match_clause_to_indexkey(RelOptInfo *rel, isIndexable = !intMember(lfirsti(rel->relids), othervarnos) && !contain_noncachable_functions((Node *) leftop) && - is_indexable_operator(clause, opclass, index->relam, false); + is_indexable_operator(clause, opclass, false); freeList(othervarnos); return isIndexable; } @@ -822,12 +820,11 @@ match_clause_to_indexkey(RelOptInfo *rel, /* * indexable_operator - * Does a binary opclause contain an operator matching the index's - * access method? + * Does a binary opclause contain an operator matching the index opclass? * * If the indexkey is on the right, what we actually want to know * is whether the operator has a commutator operator that matches - * the index's access method. + * the index's opclass. * * We try both the straightforward match and matches that rely on * recognizing binary-compatible datatypes. For example, if we have @@ -844,8 +841,7 @@ match_clause_to_indexkey(RelOptInfo *rel, * OID is *not* commuted; it can be plugged directly into the given clause. */ Oid -indexable_operator(Expr *clause, Oid opclass, Oid relam, - bool indexkey_on_left) +indexable_operator(Expr *clause, Oid opclass, bool indexkey_on_left) { Oid expr_op = ((Oper *) clause->oper)->opno; Oid commuted_op, @@ -865,8 +861,8 @@ indexable_operator(Expr *clause, Oid opclass, Oid relam, if (commuted_op == InvalidOid) return InvalidOid; - /* Done if the (commuted) operator is a member of the index's AM */ - if (op_class(commuted_op, opclass, relam)) + /* Done if the (commuted) operator is a member of the index's opclass */ + if (op_in_opclass(commuted_op, opclass)) return expr_op; /* @@ -937,7 +933,7 @@ indexable_operator(Expr *clause, Oid opclass, Oid relam, if (commuted_op == InvalidOid) return InvalidOid; - if (op_class(commuted_op, opclass, relam)) + if (op_in_opclass(commuted_op, opclass)) return new_op; } } @@ -1171,8 +1167,8 @@ pred_test_simple_clause(Expr *predicate, Node *clause) Oid pred_op, clause_op, test_op; - Oid opclass_id; - StrategyNumber pred_strategy, + Oid opclass_id = InvalidOid; + StrategyNumber pred_strategy = 0, clause_strategy, test_strategy; Oper *test_oper; @@ -1182,7 +1178,7 @@ pred_test_simple_clause(Expr *predicate, Node *clause) Relation relation; HeapScanDesc scan; HeapTuple tuple; - ScanKeyData entry[3]; + ScanKeyData entry[1]; Form_pg_amop aform; ExprContext *econtext; @@ -1227,23 +1223,6 @@ pred_test_simple_clause(Expr *predicate, Node *clause) /* * 1. Find a "btree" strategy number for the pred_op * - * XXX consider using syscache lookups for these searches. Right - * now we don't have caches that match all of the search conditions, - * but reconsider it after upcoming restructuring of pg_opclass. - */ - relation = heap_openr(AccessMethodOperatorRelationName, AccessShareLock); - - ScanKeyEntryInitialize(&entry[0], 0, - Anum_pg_amop_amopid, - F_OIDEQ, - ObjectIdGetDatum(BTREE_AM_OID)); - - ScanKeyEntryInitialize(&entry[1], 0, - Anum_pg_amop_amopopr, - F_OIDEQ, - ObjectIdGetDatum(pred_op)); - - /* * The following assumes that any given operator will only be in a * single btree operator class. This is true at least for all the * pre-defined operator classes. If it isn't true, then whichever @@ -1251,46 +1230,47 @@ pred_test_simple_clause(Expr *predicate, Node *clause) * will be used to find the associated strategy numbers for the test. * --Nels, Jan '93 */ - scan = heap_beginscan(relation, false, SnapshotNow, 2, entry); - tuple = heap_getnext(scan, 0); - if (!HeapTupleIsValid(tuple)) + ScanKeyEntryInitialize(&entry[0], 0x0, + Anum_pg_amop_amopopr, + F_OIDEQ, + ObjectIdGetDatum(pred_op)); + + relation = heap_openr(AccessMethodOperatorRelationName, AccessShareLock); + scan = heap_beginscan(relation, false, SnapshotNow, 1, entry); + + while (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) { - /* predicate operator isn't btree-indexable */ - heap_endscan(scan); - heap_close(relation, AccessShareLock); - return false; + aform = (Form_pg_amop) GETSTRUCT(tuple); + if (opclass_is_btree(aform->amopclaid)) + { + /* Get the predicate operator's btree strategy number (1 to 5) */ + pred_strategy = (StrategyNumber) aform->amopstrategy; + Assert(pred_strategy >= 1 && pred_strategy <= 5); + /* Remember which operator class this strategy number came from */ + opclass_id = aform->amopclaid; + break; + } } - aform = (Form_pg_amop) GETSTRUCT(tuple); - - /* Get the predicate operator's btree strategy number (1 to 5) */ - pred_strategy = (StrategyNumber) aform->amopstrategy; - Assert(pred_strategy >= 1 && pred_strategy <= 5); - - /* Remember which operator class this strategy number came from */ - opclass_id = aform->amopclaid; heap_endscan(scan); + heap_close(relation, AccessShareLock); + + if (!OidIsValid(opclass_id)) + { + /* predicate operator isn't btree-indexable */ + return false; + } /* * 2. From the same opclass, find a strategy num for the clause_op */ - ScanKeyEntryInitialize(&entry[1], 0, - Anum_pg_amop_amopclaid, - F_OIDEQ, - ObjectIdGetDatum(opclass_id)); - - ScanKeyEntryInitialize(&entry[2], 0, - Anum_pg_amop_amopopr, - F_OIDEQ, - ObjectIdGetDatum(clause_op)); - - scan = heap_beginscan(relation, false, SnapshotNow, 3, entry); - tuple = heap_getnext(scan, 0); + tuple = SearchSysCache(AMOPOPID, + ObjectIdGetDatum(opclass_id), + ObjectIdGetDatum(clause_op), + 0, 0); if (!HeapTupleIsValid(tuple)) { /* clause operator isn't btree-indexable, or isn't in this opclass */ - heap_endscan(scan); - heap_close(relation, AccessShareLock); return false; } aform = (Form_pg_amop) GETSTRUCT(tuple); @@ -1299,7 +1279,7 @@ pred_test_simple_clause(Expr *predicate, Node *clause) clause_strategy = (StrategyNumber) aform->amopstrategy; Assert(clause_strategy >= 1 && clause_strategy <= 5); - heap_endscan(scan); + ReleaseSysCache(tuple); /* * 3. Look up the "test" strategy number in the implication table @@ -1307,26 +1287,20 @@ pred_test_simple_clause(Expr *predicate, Node *clause) test_strategy = BT_implic_table[clause_strategy - 1][pred_strategy - 1]; if (test_strategy == 0) { - heap_close(relation, AccessShareLock); return false; /* the implication cannot be determined */ } /* * 4. From the same opclass, find the operator for the test strategy */ - ScanKeyEntryInitialize(&entry[2], 0, - Anum_pg_amop_amopstrategy, - F_INT2EQ, - Int16GetDatum(test_strategy)); - - scan = heap_beginscan(relation, false, SnapshotNow, 3, entry); - tuple = heap_getnext(scan, 0); + tuple = SearchSysCache(AMOPSTRATEGY, + ObjectIdGetDatum(opclass_id), + Int16GetDatum(test_strategy), + 0, 0); if (!HeapTupleIsValid(tuple)) { /* this probably shouldn't fail? */ elog(DEBUG, "pred_test_simple_clause: unknown test_op"); - heap_endscan(scan); - heap_close(relation, AccessShareLock); return false; } aform = (Form_pg_amop) GETSTRUCT(tuple); @@ -1334,9 +1308,7 @@ pred_test_simple_clause(Expr *predicate, Node *clause) /* Get the test operator */ test_op = aform->amopopr; - heap_endscan(scan); - - heap_close(relation, AccessShareLock); + ReleaseSysCache(tuple); /* * 5. Evaluate the test @@ -1681,7 +1653,7 @@ function_index_operand(Expr *funcOpnd, RelOptInfo *rel, IndexOptInfo *index) * Return 'true' if we can do something with it anyway. */ static bool -match_special_index_operator(Expr *clause, Oid opclass, Oid relam, +match_special_index_operator(Expr *clause, Oid opclass, bool indexkey_on_left) { bool isIndexable = false; @@ -1806,8 +1778,8 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam, case OID_TEXT_ICLIKE_OP: case OID_TEXT_REGEXEQ_OP: case OID_TEXT_ICREGEXEQ_OP: - if (!op_class(find_operator(">=", TEXTOID), opclass, relam) || - !op_class(find_operator("<", TEXTOID), opclass, relam)) + if (!op_in_opclass(find_operator(">=", TEXTOID), opclass) || + !op_in_opclass(find_operator("<", TEXTOID), opclass)) isIndexable = false; break; @@ -1815,8 +1787,8 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam, case OID_BPCHAR_ICLIKE_OP: case OID_BPCHAR_REGEXEQ_OP: case OID_BPCHAR_ICREGEXEQ_OP: - if (!op_class(find_operator(">=", BPCHAROID), opclass, relam) || - !op_class(find_operator("<", BPCHAROID), opclass, relam)) + if (!op_in_opclass(find_operator(">=", BPCHAROID), opclass) || + !op_in_opclass(find_operator("<", BPCHAROID), opclass)) isIndexable = false; break; @@ -1824,8 +1796,8 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam, case OID_VARCHAR_ICLIKE_OP: case OID_VARCHAR_REGEXEQ_OP: case OID_VARCHAR_ICREGEXEQ_OP: - if (!op_class(find_operator(">=", VARCHAROID), opclass, relam) || - !op_class(find_operator("<", VARCHAROID), opclass, relam)) + if (!op_in_opclass(find_operator(">=", VARCHAROID), opclass) || + !op_in_opclass(find_operator("<", VARCHAROID), opclass)) isIndexable = false; break; @@ -1833,24 +1805,24 @@ match_special_index_operator(Expr *clause, Oid opclass, Oid relam, case OID_NAME_ICLIKE_OP: case OID_NAME_REGEXEQ_OP: case OID_NAME_ICREGEXEQ_OP: - if (!op_class(find_operator(">=", NAMEOID), opclass, relam) || - !op_class(find_operator("<", NAMEOID), opclass, relam)) + if (!op_in_opclass(find_operator(">=", NAMEOID), opclass) || + !op_in_opclass(find_operator("<", NAMEOID), opclass)) isIndexable = false; break; case OID_INET_SUB_OP: case OID_INET_SUBEQ_OP: /* for SUB we actually need ">" not ">=", but this should do */ - if (!op_class(find_operator(">=", INETOID), opclass, relam) || - !op_class(find_operator("<=", INETOID), opclass, relam)) + if (!op_in_opclass(find_operator(">=", INETOID), opclass) || + !op_in_opclass(find_operator("<=", INETOID), opclass)) isIndexable = false; break; case OID_CIDR_SUB_OP: case OID_CIDR_SUBEQ_OP: /* for SUB we actually need ">" not ">=", but this should do */ - if (!op_class(find_operator(">=", CIDROID), opclass, relam) || - !op_class(find_operator("<=", CIDROID), opclass, relam)) + if (!op_in_opclass(find_operator(">=", CIDROID), opclass) || + !op_in_opclass(find_operator("<=", CIDROID), opclass)) isIndexable = false; break; } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 78e22c7b9e..9a32ae7a1b 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.107 2001/06/05 05:26:04 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v 1.108 2001/08/21 16:36:03 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -56,9 +56,12 @@ static HashJoin *create_hashjoin_plan(HashPath *best_path, List *tlist, List *joinclauses, List *otherclauses, Plan *outer_plan, List *outer_tlist, Plan *inner_plan, List *inner_tlist); -static List *fix_indxqual_references(List *indexquals, IndexPath *index_path); -static List *fix_indxqual_sublist(List *indexqual, int baserelid, - IndexOptInfo *index); +static void fix_indxqual_references(List *indexquals, IndexPath *index_path, + List **fixed_indexquals, + List **recheck_indexquals); +static void fix_indxqual_sublist(List *indexqual, int baserelid, + IndexOptInfo *index, + List **fixed_quals, List **recheck_quals); static Node *fix_indxqual_operand(Node *node, int baserelid, IndexOptInfo *index, Oid *opclass); @@ -381,11 +384,12 @@ create_indexscan_plan(Query *root, List *indxqual = best_path->indexqual; Index baserelid; List *qpqual; + Expr *indxqual_or_expr = NULL; List *fixed_indxqual; + List *recheck_indxqual; List *indexids; List *ixinfo; IndexScan *scan_plan; - bool lossy; /* there should be exactly one base rel involved... */ Assert(length(best_path->path.parent->relids) == 1); @@ -394,25 +398,23 @@ create_indexscan_plan(Query *root, baserelid = lfirsti(best_path->path.parent->relids); /* - * Build list of index OIDs, and check to see if any of the indices - * are lossy. + * Build list of index OIDs. */ indexids = NIL; - lossy = false; foreach(ixinfo, best_path->indexinfo) { IndexOptInfo *index = (IndexOptInfo *) lfirst(ixinfo); indexids = lappendi(indexids, index->indexoid); - lossy |= index->lossy; } /* * The qpqual list must contain all restrictions not automatically - * handled by the index. Note that for non-lossy indices, the - * predicates in the indxqual are checked fully by the index, while - * for lossy indices the indxqual predicates need to be double-checked - * after the index fetches the best-guess tuples. + * handled by the index. Normally the predicates in the indxqual + * are checked fully by the index, but if the index is "lossy" for + * a particular operator (as signaled by the amopreqcheck flag in + * pg_amop), then we need to double-check that predicate in qpqual, + * because the index may return more tuples than match the predicate. * * Since the indexquals were generated from the restriction clauses given * by scan_clauses, there will normally be some duplications between @@ -420,7 +422,6 @@ create_indexscan_plan(Query *root, */ if (length(indxqual) > 1) { - /* * Build an expression representation of the indexqual, expanding * the implicit OR and AND semantics of the first- and @@ -428,32 +429,24 @@ create_indexscan_plan(Query *root, */ List *orclauses = NIL; List *orclause; - Expr *indxqual_expr; foreach(orclause, indxqual) + { orclauses = lappend(orclauses, make_ands_explicit(lfirst(orclause))); - indxqual_expr = make_orclause(orclauses); - - qpqual = set_difference(scan_clauses, makeList1(indxqual_expr)); + } + indxqual_or_expr = make_orclause(orclauses); - if (lossy) - qpqual = lappend(qpqual, copyObject(indxqual_expr)); + qpqual = set_difference(scan_clauses, makeList1(indxqual_or_expr)); } else if (indxqual != NIL) { - /* * Here, we can simply treat the first sublist as an independent * set of qual expressions, since there is no top-level OR * behavior. */ - List *indxqual_list = lfirst(indxqual); - - qpqual = set_difference(scan_clauses, indxqual_list); - - if (lossy) - qpqual = nconc(qpqual, (List *) copyObject(indxqual_list)); + qpqual = set_difference(scan_clauses, lfirst(indxqual)); } else qpqual = scan_clauses; @@ -461,9 +454,35 @@ create_indexscan_plan(Query *root, /* * The executor needs a copy with the indexkey on the left of each * clause and with index attr numbers substituted for table ones. + * This pass also looks for "lossy" operators. */ - fixed_indxqual = fix_indxqual_references(indxqual, best_path); + fix_indxqual_references(indxqual, best_path, + &fixed_indxqual, &recheck_indxqual); + /* + * If there were any "lossy" operators, need to add back the appropriate + * qual clauses to the qpqual. When there is just one indexscan being + * performed (ie, we have simple AND semantics), we can just add the + * lossy clauses themselves to qpqual. If we have OR-of-ANDs, we'd + * better add the entire original indexqual to make sure that the + * semantics are correct. + */ + if (recheck_indxqual != NIL) + { + if (indxqual_or_expr) + { + /* Better do a deep copy of the original scanclauses */ + qpqual = lappend(qpqual, copyObject(indxqual_or_expr)); + } + else + { + /* Subroutine already copied quals, so just append to list */ + Assert(length(recheck_indxqual) == 1); + qpqual = nconc(qpqual, (List *) lfirst(recheck_indxqual)); + } + } + + /* Finally ready to build the plan node */ scan_plan = make_indexscan(tlist, qpqual, baserelid, @@ -868,9 +887,9 @@ create_hashjoin_plan(HashPath *best_path, /* * fix_indxqual_references * Adjust indexqual clauses to the form the executor's indexqual - * machinery needs. + * machinery needs, and check for recheckable (lossy) index conditions. * - * We have three tasks here: + * We have four tasks here: * * Index keys must be represented by Var nodes with varattno set to the * index's attribute number, not the attribute number in the original rel. * * indxpath.c may have selected an index that is binary-compatible with @@ -879,20 +898,34 @@ create_hashjoin_plan(HashPath *best_path, * equivalent operator that the index will recognize. * * If the index key is on the right, commute the clause to put it on the * left. (Someday the executor might not need this, but for now it does.) + * * If the indexable operator is marked 'amopreqcheck' in pg_amop, then + * the index is "lossy" for this operator: it may return more tuples than + * actually satisfy the operator condition. For each such operator, we + * must add (the original form of) the indexqual clause to the "qpquals" + * of the indexscan node, where the operator will be re-evaluated to + * ensure it passes. * * This code used to be entirely bogus for multi-index scans. Now it keeps * track of which index applies to each subgroup of index qual clauses... * - * Returns a modified copy of the indexqual list --- the original is not - * changed. Note also that the copy shares no substructure with the - * original; this is needed in case there is a subplan in it (we need + * Both the input list and the output lists have the form of lists of sublists + * of qual clauses --- the top-level list has one entry for each indexscan + * to be performed. The semantics are OR-of-ANDs. + * + * fixed_indexquals receives a modified copy of the indexqual list --- the + * original is not changed. Note also that the copy shares no substructure + * with the original; this is needed in case there is a subplan in it (we need * two separate copies of the subplan tree, or things will go awry). + * + * recheck_indexquals similarly receives a full copy of whichever clauses + * need rechecking. */ - -static List * -fix_indxqual_references(List *indexquals, IndexPath *index_path) +static void +fix_indxqual_references(List *indexquals, IndexPath *index_path, + List **fixed_indexquals, List **recheck_indexquals) { List *fixed_quals = NIL; + List *recheck_quals = NIL; int baserelid = lfirsti(index_path->path.parent->relids); List *ixinfo = index_path->indexinfo; List *i; @@ -901,14 +934,20 @@ fix_indxqual_references(List *indexquals, IndexPath *index_path) { List *indexqual = lfirst(i); IndexOptInfo *index = (IndexOptInfo *) lfirst(ixinfo); + List *fixed_qual; + List *recheck_qual; + + fix_indxqual_sublist(indexqual, baserelid, index, + &fixed_qual, &recheck_qual); + fixed_quals = lappend(fixed_quals, fixed_qual); + if (recheck_qual != NIL) + recheck_quals = lappend(recheck_quals, recheck_qual); - fixed_quals = lappend(fixed_quals, - fix_indxqual_sublist(indexqual, - baserelid, - index)); ixinfo = lnext(ixinfo); } - return fixed_quals; + + *fixed_indexquals = fixed_quals; + *recheck_indexquals = recheck_quals; } /* @@ -916,12 +955,19 @@ fix_indxqual_references(List *indexquals, IndexPath *index_path) * * For each qual clause, commute if needed to put the indexkey operand on the * left, and then fix its varattno. (We do not need to change the other side - * of the clause.) Also change the operator if necessary. + * of the clause.) Also change the operator if necessary, and check for + * lossy index behavior. + * + * Returns two lists: the list of fixed indexquals, and the list (usually + * empty) of original clauses that must be rechecked as qpquals because + * the index is lossy for this operator type. */ -static List * -fix_indxqual_sublist(List *indexqual, int baserelid, IndexOptInfo *index) +static void +fix_indxqual_sublist(List *indexqual, int baserelid, IndexOptInfo *index, + List **fixed_quals, List **recheck_quals) { List *fixed_qual = NIL; + List *recheck_qual = NIL; List *i; foreach(i, indexqual) @@ -968,14 +1014,24 @@ fix_indxqual_sublist(List *indexqual, int baserelid, IndexOptInfo *index) * is merely binary-compatible with the index. This shouldn't * fail, since indxpath.c found it before... */ - newopno = indexable_operator(newclause, opclass, index->relam, true); + newopno = indexable_operator(newclause, opclass, true); if (newopno == InvalidOid) elog(ERROR, "fix_indxqual_sublist: failed to find substitute op"); ((Oper *) newclause->oper)->opno = newopno; fixed_qual = lappend(fixed_qual, newclause); + + /* + * Finally, check to see if index is lossy for this operator. + * If so, add (a copy of) original form of clause to recheck list. + */ + if (op_requires_recheck(newopno, opclass)) + recheck_qual = lappend(recheck_qual, + copyObject((Node *) clause)); } - return fixed_qual; + + *fixed_quals = fixed_qual; + *recheck_quals = recheck_qual; } static Node * diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 3f537fb0d9..6d609853aa 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/optimizer/util/plancat.c,v 1.67 2001/07/15 22:48:18 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/optimizer/util/plancat.c,v 1.68 2001/08/21 16:36:03 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -103,8 +103,7 @@ find_secondary_indexes(Oid relationObjectId) IndexOptInfo *info; int i; Relation indexRelation; - Oid relam; - uint16 amorderstrategy; + int16 amorderstrategy; indexTuple = SearchSysCache(INDEXRELID, ObjectIdGetDatum(indexoid), @@ -138,7 +137,6 @@ find_secondary_indexes(Oid relationObjectId) else info->indpred = NIL; info->unique = index->indisunique; - info->lossy = index->indislossy; for (i = 0; i < INDEX_MAX_KEYS; i++) { @@ -160,8 +158,7 @@ find_secondary_indexes(Oid relationObjectId) /* Extract info from the relation descriptor for the index */ indexRelation = index_open(index->indexrelid); - relam = indexRelation->rd_rel->relam; - info->relam = relam; + info->relam = indexRelation->rd_rel->relam; info->pages = indexRelation->rd_rel->relpages; info->tuples = indexRelation->rd_rel->reltuples; info->amcostestimate = index_cost_estimator(indexRelation); @@ -181,14 +178,12 @@ find_secondary_indexes(Oid relationObjectId) amopTuple = SearchSysCache(AMOPSTRATEGY, - ObjectIdGetDatum(relam), ObjectIdGetDatum(index->indclass[i]), - UInt16GetDatum(amorderstrategy), - 0); + Int16GetDatum(amorderstrategy), + 0, 0); if (!HeapTupleIsValid(amopTuple)) - elog(ERROR, "find_secondary_indexes: no amop %u %u %d", - relam, index->indclass[i], - (int) amorderstrategy); + elog(ERROR, "find_secondary_indexes: no amop %u %d", + index->indclass[i], (int) amorderstrategy); amop = (Form_pg_amop) GETSTRUCT(amopTuple); info->ordering[i] = amop->amopopr; ReleaseSysCache(amopTuple); @@ -370,7 +365,7 @@ has_unique_index(RelOptInfo *rel, AttrNumber attno) IndexOptInfo *index = (IndexOptInfo *) lfirst(ilist); /* - * Note: ignore functional, partial, or lossy indexes, since they + * Note: ignore functional and partial indexes, since they * don't allow us to conclude that all attr values are distinct. * Also, a multicolumn unique index doesn't allow us to conclude * that just the specified attr is unique. @@ -379,8 +374,7 @@ has_unique_index(RelOptInfo *rel, AttrNumber attno) index->nkeys == 1 && index->indexkeys[0] == attno && index->indproc == InvalidOid && - index->indpred == NIL && - !index->lossy) + index->indpred == NIL) return true; } return false; diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 7604d42a03..6c5e5f7e0d 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.195 2001/08/16 20:38:53 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.196 2001/08/21 16:36:03 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -995,7 +995,6 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt) index->relname = stmt->relname; index->accessMethod = "btree"; index->indexParams = NIL; - index->withClause = NIL; index->whereClause = NULL; foreach(keys, constraint->keys) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6cd7f06480..ada73b1e7c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.246 2001/08/16 20:38:53 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.247 2001/08/21 16:36:03 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -2399,13 +2399,12 @@ RevokeStmt: REVOKE privileges ON opt_table relation_name_list FROM grantee_list * QUERY: * create index <indexname> on <relname> * [ using <access> ] "(" (<col> with <op>)+ ")" - * [ with <parameters> ] * [ where <predicate> ] * *****************************************************************************/ IndexStmt: CREATE index_opt_unique INDEX index_name ON relation_name - access_method_clause '(' index_params ')' opt_with where_clause + access_method_clause '(' index_params ')' where_clause { IndexStmt *n = makeNode(IndexStmt); n->unique = $2; @@ -2413,8 +2412,7 @@ IndexStmt: CREATE index_opt_unique INDEX index_name ON relation_name n->relname = $6; n->accessMethod = $7; n->indexParams = $9; - n->withClause = $11; - n->whereClause = $12; + n->whereClause = $11; $$ = (Node *)n; } ; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index a791a67715..c05546fb8e 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.115 2001/07/16 05:06:58 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.116 2001/08/21 16:36:04 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -538,7 +538,6 @@ ProcessUtility(Node *parsetree, stmt->idxname, /* index name */ stmt->accessMethod, /* am name */ stmt->indexParams, /* parameters */ - stmt->withClause, stmt->unique, stmt->primary, (Expr *) stmt->whereClause, diff --git a/src/backend/utils/adt/regproc.c b/src/backend/utils/adt/regproc.c index eb6ab3f464..3ae65c6c8e 100644 --- a/src/backend/utils/adt/regproc.c +++ b/src/backend/utils/adt/regproc.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/regproc.c,v 1.62 2001/06/22 19:16:23 wieck Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/regproc.c,v 1.63 2001/08/21 16:36:04 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -19,12 +19,12 @@ #include "catalog/catname.h" #include "catalog/indexing.h" #include "catalog/pg_proc.h" -#include "catalog/pg_type.h" #include "miscadmin.h" #include "utils/builtins.h" #include "utils/fmgroids.h" #include "utils/syscache.h" + /***************************************************************************** * USER I/O ROUTINES * *****************************************************************************/ @@ -32,126 +32,103 @@ /* * regprocin - converts "proname" or "proid" to proid * + * We need to accept an OID for cases where the name is ambiguous. + * * proid of '-' signifies unknown, for consistency with regprocout */ Datum regprocin(PG_FUNCTION_ARGS) { char *pro_name_or_oid = PG_GETARG_CSTRING(0); - HeapTuple proctup; - HeapTupleData tuple; RegProcedure result = InvalidOid; + int matches = 0; + ScanKeyData skey[1]; if (pro_name_or_oid[0] == '-' && pro_name_or_oid[1] == '\0') PG_RETURN_OID(InvalidOid); - if (!IsIgnoringSystemIndexes()) + if (pro_name_or_oid[0] >= '0' && + pro_name_or_oid[0] <= '9') { + Oid searchOid; + + searchOid = DatumGetObjectId(DirectFunctionCall1(oidin, + CStringGetDatum(pro_name_or_oid))); + result = (RegProcedure) GetSysCacheOid(PROCOID, + ObjectIdGetDatum(searchOid), + 0, 0, 0); + if (!RegProcedureIsValid(result)) + elog(ERROR, "No procedure with oid %s", pro_name_or_oid); + matches = 1; + } + else if (!IsIgnoringSystemIndexes()) + { + Relation hdesc; + Relation idesc; + IndexScanDesc sd; + RetrieveIndexResult indexRes; + HeapTupleData tuple; + Buffer buffer; + + ScanKeyEntryInitialize(&skey[0], 0x0, + (AttrNumber) 1, + (RegProcedure) F_NAMEEQ, + CStringGetDatum(pro_name_or_oid)); - /* - * we need to use the oid because there can be multiple entries - * with the same name. We accept int4eq_1323 and 1323. - */ - if (pro_name_or_oid[0] >= '0' && - pro_name_or_oid[0] <= '9') - { - result = (RegProcedure) - GetSysCacheOid(PROCOID, - DirectFunctionCall1(oidin, - CStringGetDatum(pro_name_or_oid)), - 0, 0, 0); - if (!RegProcedureIsValid(result)) - elog(ERROR, "No procedure with oid %s", pro_name_or_oid); - } - else - { - Relation hdesc; - Relation idesc; - IndexScanDesc sd; - ScanKeyData skey[1]; - RetrieveIndexResult indexRes; - Buffer buffer; - int matches = 0; - - ScanKeyEntryInitialize(&skey[0], - (bits16) 0x0, - (AttrNumber) 1, - (RegProcedure) F_NAMEEQ, - CStringGetDatum(pro_name_or_oid)); - - hdesc = heap_openr(ProcedureRelationName, AccessShareLock); - idesc = index_openr(ProcedureNameIndex); + hdesc = heap_openr(ProcedureRelationName, AccessShareLock); + idesc = index_openr(ProcedureNameIndex); + sd = index_beginscan(idesc, false, 1, skey); - sd = index_beginscan(idesc, false, 1, skey); - while ((indexRes = index_getnext(sd, ForwardScanDirection))) + while ((indexRes = index_getnext(sd, ForwardScanDirection))) + { + tuple.t_datamcxt = NULL; + tuple.t_data = NULL; + tuple.t_self = indexRes->heap_iptr; + heap_fetch(hdesc, SnapshotNow, &tuple, &buffer, sd); + pfree(indexRes); + if (tuple.t_data != NULL) { - tuple.t_datamcxt = NULL; - tuple.t_data = NULL; - tuple.t_self = indexRes->heap_iptr; - heap_fetch(hdesc, SnapshotNow, - &tuple, - &buffer, - sd); - pfree(indexRes); - if (tuple.t_data != NULL) - { - result = (RegProcedure) tuple.t_data->t_oid; - ReleaseBuffer(buffer); - - if (++matches > 1) - break; - } + result = (RegProcedure) tuple.t_data->t_oid; + ReleaseBuffer(buffer); + if (++matches > 1) + break; } - - index_endscan(sd); - index_close(idesc); - heap_close(hdesc, AccessShareLock); - - if (matches > 1) - elog(ERROR, "There is more than one procedure named %s.\n\tSupply the pg_proc oid inside single quotes.", pro_name_or_oid); - else if (matches == 0) - elog(ERROR, "No procedure with name %s", pro_name_or_oid); } + + index_endscan(sd); + index_close(idesc); + heap_close(hdesc, AccessShareLock); } else { Relation proc; HeapScanDesc procscan; - ScanKeyData key; - bool isnull; + HeapTuple proctup; - proc = heap_openr(ProcedureRelationName, AccessShareLock); - ScanKeyEntryInitialize(&key, - (bits16) 0, - (AttrNumber) 1, + ScanKeyEntryInitialize(&skey[0], 0x0, + (AttrNumber) Anum_pg_proc_proname, (RegProcedure) F_NAMEEQ, CStringGetDatum(pro_name_or_oid)); - procscan = heap_beginscan(proc, 0, SnapshotNow, 1, &key); - if (!HeapScanIsValid(procscan)) - { - heap_close(proc, AccessShareLock); - elog(ERROR, "regprocin: could not begin scan of %s", - ProcedureRelationName); - PG_RETURN_OID(InvalidOid); - } - proctup = heap_getnext(procscan, 0); - if (HeapTupleIsValid(proctup)) + proc = heap_openr(ProcedureRelationName, AccessShareLock); + procscan = heap_beginscan(proc, 0, SnapshotNow, 1, skey); + + while (HeapTupleIsValid(proctup = heap_getnext(procscan, 0))) { - result = (RegProcedure) heap_getattr(proctup, - ObjectIdAttributeNumber, - RelationGetDescr(proc), - &isnull); - if (isnull) - elog(ERROR, "regprocin: null procedure %s", pro_name_or_oid); + result = proctup->t_data->t_oid; + if (++matches > 1) + break; } - else - elog(ERROR, "No procedure with name %s", pro_name_or_oid); heap_endscan(procscan); heap_close(proc, AccessShareLock); } + if (matches > 1) + elog(ERROR, "There is more than one procedure named %s.\n\tSupply the pg_proc oid inside single quotes.", pro_name_or_oid); + else if (matches == 0) + elog(ERROR, "No procedure with name %s", pro_name_or_oid); + PG_RETURN_OID(result); } @@ -174,66 +151,22 @@ regprocout(PG_FUNCTION_ARGS) PG_RETURN_CSTRING(result); } - if (!IsBootstrapProcessingMode()) - { - proctup = SearchSysCache(PROCOID, - ObjectIdGetDatum(proid), - 0, 0, 0); + proctup = SearchSysCache(PROCOID, + ObjectIdGetDatum(proid), + 0, 0, 0); - if (HeapTupleIsValid(proctup)) - { - char *s; + if (HeapTupleIsValid(proctup)) + { + char *s; - s = NameStr(((Form_pg_proc) GETSTRUCT(proctup))->proname); - StrNCpy(result, s, NAMEDATALEN); - ReleaseSysCache(proctup); - } - else - { - result[0] = '-'; - result[1] = '\0'; - } + s = NameStr(((Form_pg_proc) GETSTRUCT(proctup))->proname); + StrNCpy(result, s, NAMEDATALEN); + ReleaseSysCache(proctup); } else { - Relation proc; - HeapScanDesc procscan; - ScanKeyData key; - - proc = heap_openr(ProcedureRelationName, AccessShareLock); - ScanKeyEntryInitialize(&key, - (bits16) 0, - (AttrNumber) ObjectIdAttributeNumber, - (RegProcedure) F_INT4EQ, - ObjectIdGetDatum(proid)); - - procscan = heap_beginscan(proc, 0, SnapshotNow, 1, &key); - if (!HeapScanIsValid(procscan)) - { - heap_close(proc, AccessShareLock); - elog(ERROR, "regprocout: could not begin scan of %s", - ProcedureRelationName); - } - proctup = heap_getnext(procscan, 0); - if (HeapTupleIsValid(proctup)) - { - char *s; - bool isnull; - - s = (char *) heap_getattr(proctup, 1, - RelationGetDescr(proc), &isnull); - if (!isnull) - StrNCpy(result, s, NAMEDATALEN); - else - elog(ERROR, "regprocout: null procedure %u", proid); - } - else - { - result[0] = '-'; - result[1] = '\0'; - } - heap_endscan(procscan); - heap_close(proc, AccessShareLock); + result[0] = '-'; + result[1] = '\0'; } PG_RETURN_CSTRING(result); diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c index f5f16e7718..ce4363706b 100644 --- a/src/backend/utils/cache/catcache.c +++ b/src/backend/utils/cache/catcache.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/cache/catcache.c,v 1.81 2001/06/22 19:16:23 wieck Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/cache/catcache.c,v 1.82 2001/08/21 16:36:04 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -18,6 +18,7 @@ #include "access/hash.h" #include "access/heapam.h" #include "access/valid.h" +#include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_type.h" #include "catalog/catname.h" @@ -812,7 +813,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey) sd = heap_beginscan(rel, false, SnapshotNow, 1, &key); ntp = heap_getnext(sd, 0); if (!HeapTupleIsValid(ntp)) - elog(ERROR, "SearchSelfReferences: %s not found in %s", + elog(ERROR, "IndexScanOK: %s not found in %s", IndexRelidIndex, RelationRelationName); indexSelfOid = ntp->t_data->t_oid; heap_endscan(sd); @@ -823,6 +824,16 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey) if (DatumGetObjectId(cur_skey[0].sk_argument) == indexSelfOid) return false; } + else if (cache->id == AMOPSTRATEGY || + cache->id == AMPROCNUM) + { + /* Looking for an OID or INT2 btree operator or function? */ + Oid lookup_oid = DatumGetObjectId(cur_skey[0].sk_argument); + + if (lookup_oid == OID_BTREE_OPS_OID || + lookup_oid == INT2_BTREE_OPS_OID) + return false; + } else if (cache->id == OPEROID) { /* Looking for an OID comparison function? */ @@ -858,7 +869,7 @@ SearchCatCache(CatCache *cache, MemoryContext oldcxt; /* - * one-time startup overhead + * one-time startup overhead for each cache */ if (cache->cc_tupdesc == NULL) CatalogCacheInitializeCache(cache); diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 587213d48f..1637abe999 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/cache/lsyscache.c,v 1.56 2001/06/14 01:09:22 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/cache/lsyscache.c,v 1.57 2001/08/21 16:36:05 tgl Exp $ * * NOTES * Eventually, the index information should go through here, too. @@ -16,6 +16,8 @@ #include "postgres.h" #include "access/tupmacs.h" +#include "catalog/pg_amop.h" +#include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_shadow.h" @@ -30,19 +32,48 @@ /* ---------- AMOP CACHES ---------- */ /* - * op_class + * op_in_opclass * - * Return t iff operator 'opno' is in operator class 'opclass' for - * access method 'amopid'. + * Return t iff operator 'opno' is in operator class 'opclass'. */ bool -op_class(Oid opno, Oid opclass, Oid amopid) +op_in_opclass(Oid opno, Oid opclass) { return SearchSysCacheExists(AMOPOPID, ObjectIdGetDatum(opclass), ObjectIdGetDatum(opno), - ObjectIdGetDatum(amopid), - 0); + 0, 0); +} + +/* + * op_requires_recheck + * + * Return t if operator 'opno' requires a recheck when used as a + * member of opclass 'opclass' (ie, this opclass is lossy for this + * operator). + * + * Caller should already have verified that opno is a member of opclass, + * therefore we raise an error if the tuple is not found. + */ +bool +op_requires_recheck(Oid opno, Oid opclass) +{ + HeapTuple tp; + Form_pg_amop amop_tup; + bool result; + + tp = SearchSysCache(AMOPOPID, + ObjectIdGetDatum(opclass), + ObjectIdGetDatum(opno), + 0, 0); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "op_requires_recheck: op %u is not a member of opclass %u", + opno, opclass); + amop_tup = (Form_pg_amop) GETSTRUCT(tp); + + result = amop_tup->amopreqcheck; + ReleaseSysCache(tp); + return result; } /* ---------- ATTRIBUTE CACHES ---------- */ @@ -222,6 +253,33 @@ get_atttypetypmod(Oid relid, AttrNumber attnum, /* watch this space... */ +/* ---------- OPCLASS CACHE ---------- */ + +/* + * opclass_is_btree + * + * Returns TRUE iff the specified opclass is associated with the + * btree index access method. + */ +bool +opclass_is_btree(Oid opclass) +{ + HeapTuple tp; + Form_pg_opclass cla_tup; + bool result; + + tp = SearchSysCache(CLAOID, + ObjectIdGetDatum(opclass), + 0, 0, 0); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for opclass %u", opclass); + cla_tup = (Form_pg_opclass) GETSTRUCT(tp); + + result = (cla_tup->opcamid == BTREE_AM_OID); + ReleaseSysCache(tp); + return result; +} + /* ---------- OPERATOR CACHE ---------- */ /* diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index a407d39f5d..6020597f2a 100644 --- a/src/backend/utils/cache/syscache.c +++ b/src/backend/utils/cache/syscache.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/cache/syscache.c,v 1.64 2001/08/10 18:57:37 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/cache/syscache.c,v 1.65 2001/08/21 16:36:05 tgl Exp $ * * NOTES * These routines allow the parser/planner/executor to perform @@ -27,6 +27,7 @@ #include "catalog/indexing.h" #include "catalog/pg_aggregate.h" #include "catalog/pg_amop.h" +#include "catalog/pg_amproc.h" #include "catalog/pg_group.h" #include "catalog/pg_index.h" #include "catalog/pg_inherits.h" @@ -113,23 +114,33 @@ static struct cachedesc cacheinfo[] = { 0 }}, {AccessMethodOperatorRelationName, /* AMOPOPID */ - AccessMethodOpidIndex, + AccessMethodOperatorIndex, 0, - 3, + 2, { Anum_pg_amop_amopclaid, Anum_pg_amop_amopopr, - Anum_pg_amop_amopid, + 0, 0 }}, {AccessMethodOperatorRelationName, /* AMOPSTRATEGY */ AccessMethodStrategyIndex, 0, - 3, + 2, { - Anum_pg_amop_amopid, Anum_pg_amop_amopclaid, Anum_pg_amop_amopstrategy, + 0, + 0 + }}, + {AccessMethodProcedureRelationName, /* AMPROCNUM */ + AccessMethodProcedureIndex, + 0, + 2, + { + Anum_pg_amproc_amopclaid, + Anum_pg_amproc_amprocnum, + 0, 0 }}, {AttributeRelationName, /* ATTNAME */ @@ -152,22 +163,22 @@ static struct cachedesc cacheinfo[] = { 0, 0 }}, - {OperatorClassRelationName, /* CLADEFTYPE */ - OpclassDeftypeIndex, + {OperatorClassRelationName, /* CLAAMNAME */ + OpclassAmNameIndex, 0, - 1, + 2, { - Anum_pg_opclass_opcdeftype, - 0, + Anum_pg_opclass_opcamid, + Anum_pg_opclass_opcname, 0, 0 }}, - {OperatorClassRelationName, /* CLANAME */ - OpclassNameIndex, + {OperatorClassRelationName, /* CLAOID */ + OpclassOidIndex, 0, 1, { - Anum_pg_opclass_opcname, + ObjectIdAttributeNumber, 0, 0, 0 diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 1391ea88c2..f4e2faaa2b 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -78,7 +78,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/sort/tuplesort.c,v 1.17 2001/06/02 19:01:52 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/sort/tuplesort.c,v 1.18 2001/08/21 16:36:05 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2055,7 +2055,7 @@ SelectSortFunction(Oid sortOperator, { Relation relation; HeapScanDesc scan; - ScanKeyData skey[3]; + ScanKeyData skey[1]; HeapTuple tuple; Form_pg_operator optup; Oid opclass = InvalidOid; @@ -2068,25 +2068,20 @@ SelectSortFunction(Oid sortOperator, * If the operator is registered the same way in multiple opclasses, * assume we can use the associated comparator function from any one. */ - relation = heap_openr(AccessMethodOperatorRelationName, - AccessShareLock); - - ScanKeyEntryInitialize(&skey[0], 0, - Anum_pg_amop_amopid, - F_OIDEQ, - ObjectIdGetDatum(BTREE_AM_OID)); - - ScanKeyEntryInitialize(&skey[1], 0, + ScanKeyEntryInitialize(&skey[0], 0x0, Anum_pg_amop_amopopr, F_OIDEQ, ObjectIdGetDatum(sortOperator)); - scan = heap_beginscan(relation, false, SnapshotNow, 2, skey); + relation = heap_openr(AccessMethodOperatorRelationName, AccessShareLock); + scan = heap_beginscan(relation, false, SnapshotNow, 1, skey); while (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) { Form_pg_amop aform = (Form_pg_amop) GETSTRUCT(tuple); + if (!opclass_is_btree(aform->amopclaid)) + continue; if (aform->amopstrategy == BTLessStrategyNumber) { opclass = aform->amopclaid; @@ -2107,39 +2102,18 @@ SelectSortFunction(Oid sortOperator, if (OidIsValid(opclass)) { /* Found a suitable opclass, get its comparator support function */ - relation = heap_openr(AccessMethodProcedureRelationName, - AccessShareLock); - - ScanKeyEntryInitialize(&skey[0], 0, - Anum_pg_amproc_amid, - F_OIDEQ, - ObjectIdGetDatum(BTREE_AM_OID)); - - ScanKeyEntryInitialize(&skey[1], 0, - Anum_pg_amproc_amopclaid, - F_OIDEQ, - ObjectIdGetDatum(opclass)); - - ScanKeyEntryInitialize(&skey[2], 0, - Anum_pg_amproc_amprocnum, - F_INT2EQ, - Int16GetDatum(BTORDER_PROC)); - - scan = heap_beginscan(relation, false, SnapshotNow, 3, skey); - - *sortFunction = InvalidOid; - - if (HeapTupleIsValid(tuple = heap_getnext(scan, 0))) + tuple = SearchSysCache(AMPROCNUM, + ObjectIdGetDatum(opclass), + Int16GetDatum(BTORDER_PROC), + 0, 0); + if (HeapTupleIsValid(tuple)) { Form_pg_amproc aform = (Form_pg_amproc) GETSTRUCT(tuple); *sortFunction = aform->amproc; - } - - heap_endscan(scan); - heap_close(relation, AccessShareLock); - - if (RegProcedureIsValid(*sortFunction)) + ReleaseSysCache(tuple); + Assert(RegProcedureIsValid(*sortFunction)); return; + } } /* @@ -2158,7 +2132,7 @@ SelectSortFunction(Oid sortOperator, *kind = SORTFUNC_REVLT; else *kind = SORTFUNC_LT; - *sortFunction = optup->oprcode; + *sortFunction = optup->oprcode; ReleaseSysCache(tuple); Assert(RegProcedureIsValid(*sortFunction)); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 5375abb869..f53b32c5e8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3,7 +3,7 @@ * * Copyright 2000 by PostgreSQL Global Development Group * - * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.38 2001/08/10 18:57:39 tgl Exp $ + * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.39 2001/08/21 16:36:05 tgl Exp $ */ #include "postgres_fe.h" #include "describe.h" @@ -653,7 +653,7 @@ describeTableDetails(const char *name, bool desc) /* Footer information about an index */ PGresult *result; - sprintf(buf, "SELECT i.indisunique, i.indisprimary, i.indislossy, a.amname,\n" + sprintf(buf, "SELECT i.indisunique, i.indisprimary, a.amname,\n" " pg_get_expr(i.indpred, i.indrelid) as indpred\n" "FROM pg_index i, pg_class c, pg_am a\n" "WHERE i.indexrelid = c.oid AND c.relname = '%s' AND c.relam = a.oid", @@ -666,9 +666,8 @@ describeTableDetails(const char *name, bool desc) { char *indisunique = PQgetvalue(result, 0, 0); char *indisprimary = PQgetvalue(result, 0, 1); - char *indislossy = PQgetvalue(result, 0, 2); - char *indamname = PQgetvalue(result, 0, 3); - char *indpred = PQgetvalue(result, 0, 4); + char *indamname = PQgetvalue(result, 0, 2); + char *indpred = PQgetvalue(result, 0, 3); footers = xmalloc(3 * sizeof(*footers)); /* XXX This construction is poorly internationalized. */ @@ -680,10 +679,6 @@ describeTableDetails(const char *name, bool desc) snprintf(footers[0] + strlen(footers[0]), NAMEDATALEN + 128 - strlen(footers[0]), _(" (primary key)")); - if (strcmp(indislossy, "t") == 0) - snprintf(footers[0] + strlen(footers[0]), - NAMEDATALEN + 128 - strlen(footers[0]), - _(" (lossy)")); if (strlen(indpred) > 0) { footers[1] = xmalloc(64 + strlen(indpred)); @@ -694,6 +689,8 @@ describeTableDetails(const char *name, bool desc) else footers[1] = NULL; } + + PQclear(result); } else if (view_def) { diff --git a/src/include/access/gist.h b/src/include/access/gist.h index af9b7c3b0d..760c025af6 100644 --- a/src/include/access/gist.h +++ b/src/include/access/gist.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: gist.h,v 1.30 2001/08/10 14:34:28 momjian Exp $ + * $Id: gist.h,v 1.31 2001/08/21 16:36:05 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -73,8 +73,8 @@ typedef struct GISTSTATE FmgrInfo picksplitFn[INDEX_MAX_KEYS]; FmgrInfo equalFn[INDEX_MAX_KEYS]; bool attbyval[INDEX_MAX_KEYS]; - bool haskeytype; - bool keytypbyval; + bool haskeytype[INDEX_MAX_KEYS]; + bool keytypbyval[INDEX_MAX_KEYS]; } GISTSTATE; diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 6a192ecd81..ea29659373 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: catversion.h,v 1.90 2001/08/16 20:38:54 tgl Exp $ + * $Id: catversion.h,v 1.91 2001/08/21 16:36:05 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200108151 +#define CATALOG_VERSION_NO 200108211 #endif diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 08d61e2bb1..d3e3bae704 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: index.h,v 1.38 2001/08/10 18:57:39 tgl Exp $ + * $Id: index.h,v 1.39 2001/08/21 16:36:05 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -39,7 +39,6 @@ extern Oid index_create(char *heapRelationName, IndexInfo *indexInfo, Oid accessMethodObjectId, Oid *classObjectId, - bool islossy, bool primary, bool allow_system_table_mods); diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h index 004430096a..0e14819db8 100644 --- a/src/include/catalog/indexing.h +++ b/src/include/catalog/indexing.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: indexing.h,v 1.52 2001/08/10 18:57:39 tgl Exp $ + * $Id: indexing.h,v 1.53 2001/08/21 16:36:05 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -34,7 +34,7 @@ #define Num_pg_inherits_indices 1 #define Num_pg_language_indices 2 #define Num_pg_largeobject_indices 1 -#define Num_pg_opclass_indices 3 +#define Num_pg_opclass_indices 2 #define Num_pg_operator_indices 2 #define Num_pg_proc_indices 2 #define Num_pg_relcheck_indices 1 @@ -47,9 +47,9 @@ /* * Names of indices on system catalogs */ -#define AccessMethodOpidIndex "pg_amop_opid_index" -#define AccessMethodStrategyIndex "pg_amop_strategy_index" -#define AccessProcedureIndex "pg_amproc_am_opcl_procnum_index" +#define AccessMethodOperatorIndex "pg_amop_opc_opr_index" +#define AccessMethodStrategyIndex "pg_amop_opc_strategy_index" +#define AccessMethodProcedureIndex "pg_amproc_opc_procnum_index" #define AggregateNameTypeIndex "pg_aggregate_name_type_index" #define AggregateOidIndex "pg_aggregate_oid_index" #define AmNameIndex "pg_am_name_index" @@ -70,8 +70,7 @@ #define LanguageNameIndex "pg_language_name_index" #define LanguageOidIndex "pg_language_oid_index" #define LargeObjectLOidPNIndex "pg_largeobject_loid_pn_index" -#define OpclassDeftypeIndex "pg_opclass_deftype_index" -#define OpclassNameIndex "pg_opclass_name_index" +#define OpclassAmNameIndex "pg_opclass_am_name_index" #define OpclassOidIndex "pg_opclass_oid_index" #define OperatorNameIndex "pg_operator_oprname_l_r_k_index" #define OperatorOidIndex "pg_operator_oid_index" @@ -155,9 +154,9 @@ DECLARE_UNIQUE_INDEX(pg_aggregate_name_type_index on pg_aggregate using btree(ag DECLARE_UNIQUE_INDEX(pg_aggregate_oid_index on pg_aggregate using btree(oid oid_ops)); DECLARE_UNIQUE_INDEX(pg_am_name_index on pg_am using btree(amname name_ops)); DECLARE_UNIQUE_INDEX(pg_am_oid_index on pg_am using btree(oid oid_ops)); -DECLARE_UNIQUE_INDEX(pg_amop_opid_index on pg_amop using btree(amopclaid oid_ops, amopopr oid_ops, amopid oid_ops)); -DECLARE_UNIQUE_INDEX(pg_amop_strategy_index on pg_amop using btree(amopid oid_ops, amopclaid oid_ops, amopstrategy int2_ops)); -DECLARE_UNIQUE_INDEX(pg_amproc_am_opcl_procnum_index on pg_amproc using btree(amid oid_ops, amopclaid oid_ops, amprocnum int2_ops)); +DECLARE_UNIQUE_INDEX(pg_amop_opc_opr_index on pg_amop using btree(amopclaid oid_ops, amopopr oid_ops)); +DECLARE_UNIQUE_INDEX(pg_amop_opc_strategy_index on pg_amop using btree(amopclaid oid_ops, amopstrategy int2_ops)); +DECLARE_UNIQUE_INDEX(pg_amproc_opc_procnum_index on pg_amproc using btree(amopclaid oid_ops, amprocnum int2_ops)); DECLARE_UNIQUE_INDEX(pg_attrdef_adrelid_adnum_index on pg_attrdef using btree(adrelid oid_ops, adnum int2_ops)); DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnam_index on pg_attribute using btree(attrelid oid_ops, attname name_ops)); DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnum_index on pg_attribute using btree(attrelid oid_ops, attnum int2_ops)); @@ -175,9 +174,7 @@ DECLARE_UNIQUE_INDEX(pg_inherits_relid_seqno_index on pg_inherits using btree(in DECLARE_UNIQUE_INDEX(pg_language_name_index on pg_language using btree(lanname name_ops)); DECLARE_UNIQUE_INDEX(pg_language_oid_index on pg_language using btree(oid oid_ops)); DECLARE_UNIQUE_INDEX(pg_largeobject_loid_pn_index on pg_largeobject using btree(loid oid_ops, pageno int4_ops)); -/* This column needs to allow multiple zero entries, but is in the cache */ -DECLARE_INDEX(pg_opclass_deftype_index on pg_opclass using btree(opcdeftype oid_ops)); -DECLARE_UNIQUE_INDEX(pg_opclass_name_index on pg_opclass using btree(opcname name_ops)); +DECLARE_UNIQUE_INDEX(pg_opclass_am_name_index on pg_opclass using btree(opcamid oid_ops, opcname name_ops)); DECLARE_UNIQUE_INDEX(pg_opclass_oid_index on pg_opclass using btree(oid oid_ops)); DECLARE_UNIQUE_INDEX(pg_operator_oid_index on pg_operator using btree(oid oid_ops)); DECLARE_UNIQUE_INDEX(pg_operator_oprname_l_r_k_index on pg_operator using btree(oprname name_ops, oprleft oid_ops, oprright oid_ops, oprkind char_ops)); diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h index 42adef9ad6..b630c824eb 100644 --- a/src/include/catalog/pg_amop.h +++ b/src/include/catalog/pg_amop.h @@ -4,11 +4,19 @@ * definition of the system "amop" relation (pg_amop) * along with the relation's initial contents. * + * The amop table identifies the operators associated with each index opclass. + * + * Note: the primary key for this table is <amopclaid, amopstrategy>. + * We also keep a unique index on <amopclaid, amopopr>, so that we can + * use a syscache to quickly answer questions of the form "is this operator + * in this opclass?". This implies that the same operator cannot be listed + * for multiple strategy numbers of a single opclass. + * * * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_amop.h,v 1.40 2001/08/13 18:45:36 tgl Exp $ + * $Id: pg_amop.h,v 1.41 2001/08/21 16:36:05 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -33,11 +41,10 @@ */ CATALOG(pg_amop) BKI_WITHOUT_OIDS { - Oid amopid; /* an index access method */ - Oid amopclaid; /* an index opclass */ - Oid amopopr; /* the operator OID to use */ - int2 amopstrategy; /* one of the strategy numbers defined by - * the AM */ + Oid amopclaid; /* the index opclass this entry is for */ + int2 amopstrategy; /* operator strategy number */ + bool amopreqcheck; /* index hit must be rechecked */ + Oid amopopr; /* the operator's pg_operator OID */ } FormData_pg_amop; /* ---------------- @@ -52,10 +59,10 @@ typedef FormData_pg_amop *Form_pg_amop; * ---------------- */ #define Natts_pg_amop 4 -#define Anum_pg_amop_amopid 1 -#define Anum_pg_amop_amopclaid 2 -#define Anum_pg_amop_amopopr 3 -#define Anum_pg_amop_amopstrategy 4 +#define Anum_pg_amop_amopclaid 1 +#define Anum_pg_amop_amopstrategy 2 +#define Anum_pg_amop_amopreqcheck 3 +#define Anum_pg_amop_amopopr 4 /* ---------------- * initial contents of pg_amop @@ -66,363 +73,345 @@ typedef FormData_pg_amop *Form_pg_amop; * rtree box_ops */ -DATA(insert ( 402 422 493 1 )); -DATA(insert ( 402 422 494 2 )); -DATA(insert ( 402 422 500 3 )); -DATA(insert ( 402 422 495 4 )); -DATA(insert ( 402 422 496 5 )); -DATA(insert ( 402 422 499 6 )); -DATA(insert ( 402 422 498 7 )); -DATA(insert ( 402 422 497 8 )); +DATA(insert ( 425 1 f 493 )); +DATA(insert ( 425 2 f 494 )); +DATA(insert ( 425 3 f 500 )); +DATA(insert ( 425 4 f 495 )); +DATA(insert ( 425 5 f 496 )); +DATA(insert ( 425 6 f 499 )); +DATA(insert ( 425 7 f 498 )); +DATA(insert ( 425 8 f 497 )); /* * rtree bigbox_ops */ -DATA(insert ( 402 433 493 1 )); -DATA(insert ( 402 433 494 2 )); -DATA(insert ( 402 433 500 3 )); -DATA(insert ( 402 433 495 4 )); -DATA(insert ( 402 433 496 5 )); -DATA(insert ( 402 433 499 6 )); -DATA(insert ( 402 433 498 7 )); -DATA(insert ( 402 433 497 8 )); +DATA(insert ( 422 1 f 493 )); +DATA(insert ( 422 2 f 494 )); +DATA(insert ( 422 3 f 500 )); +DATA(insert ( 422 4 f 495 )); +DATA(insert ( 422 5 f 496 )); +DATA(insert ( 422 6 f 499 )); +DATA(insert ( 422 7 f 498 )); +DATA(insert ( 422 8 f 497 )); /* * rtree poly_ops (supports polygons) */ -DATA(insert ( 402 434 485 1 )); -DATA(insert ( 402 434 486 2 )); -DATA(insert ( 402 434 492 3 )); -DATA(insert ( 402 434 487 4 )); -DATA(insert ( 402 434 488 5 )); -DATA(insert ( 402 434 491 6 )); -DATA(insert ( 402 434 490 7 )); -DATA(insert ( 402 434 489 8 )); - -/* - * rtree circle_ops (supports circles) - * - * XXX Diked out 2000-06-18 by tgl. Since we have no rtree support functions - * (union, intersection, size) for circles, we can't actually build rtree - * indexes on circles. These can be put back in someday if anyone ever - * writes such functions. - */ - -/* DATA(insert ( 402 714 1506 1 )); */ -/* DATA(insert ( 402 714 1507 2 )); */ -/* DATA(insert ( 402 714 1513 3 )); */ -/* DATA(insert ( 402 714 1508 4 )); */ -/* DATA(insert ( 402 714 1509 5 )); */ -/* DATA(insert ( 402 714 1512 6 )); */ -/* DATA(insert ( 402 714 1511 7 )); */ -/* DATA(insert ( 402 714 1510 8 )); */ +DATA(insert ( 1993 1 f 485 )); +DATA(insert ( 1993 2 f 486 )); +DATA(insert ( 1993 3 f 492 )); +DATA(insert ( 1993 4 f 487 )); +DATA(insert ( 1993 5 f 488 )); +DATA(insert ( 1993 6 f 491 )); +DATA(insert ( 1993 7 f 490 )); +DATA(insert ( 1993 8 f 489 )); /* - * nbtree int2_ops + * btree int2_ops */ -DATA(insert ( 403 421 95 1 )); -DATA(insert ( 403 421 522 2 )); -DATA(insert ( 403 421 94 3 )); -DATA(insert ( 403 421 524 4 )); -DATA(insert ( 403 421 520 5 )); +DATA(insert ( 1976 1 f 95 )); +DATA(insert ( 1976 2 f 522 )); +DATA(insert ( 1976 3 f 94 )); +DATA(insert ( 1976 4 f 524 )); +DATA(insert ( 1976 5 f 520 )); /* - * nbtree float8_ops + * btree int4_ops */ -DATA(insert ( 403 423 672 1 )); -DATA(insert ( 403 423 673 2 )); -DATA(insert ( 403 423 670 3 )); -DATA(insert ( 403 423 675 4 )); -DATA(insert ( 403 423 674 5 )); +DATA(insert ( 1978 1 f 97 )); +DATA(insert ( 1978 2 f 523 )); +DATA(insert ( 1978 3 f 96 )); +DATA(insert ( 1978 4 f 525 )); +DATA(insert ( 1978 5 f 521 )); /* - * nbtree int4_ops + * btree int8_ops */ -DATA(insert ( 403 426 97 1 )); -DATA(insert ( 403 426 523 2 )); -DATA(insert ( 403 426 96 3 )); -DATA(insert ( 403 426 525 4 )); -DATA(insert ( 403 426 521 5 )); +DATA(insert ( 1980 1 f 412 )); +DATA(insert ( 1980 2 f 414 )); +DATA(insert ( 1980 3 f 410 )); +DATA(insert ( 1980 4 f 415 )); +DATA(insert ( 1980 5 f 413 )); /* - * nbtree int8_ops + * btree oid_ops */ -DATA(insert ( 403 754 412 1 )); -DATA(insert ( 403 754 414 2 )); -DATA(insert ( 403 754 410 3 )); -DATA(insert ( 403 754 415 4 )); -DATA(insert ( 403 754 413 5 )); +DATA(insert ( 1989 1 f 609 )); +DATA(insert ( 1989 2 f 611 )); +DATA(insert ( 1989 3 f 607 )); +DATA(insert ( 1989 4 f 612 )); +DATA(insert ( 1989 5 f 610 )); /* - * nbtree oid_ops + * btree oidvector_ops */ -DATA(insert ( 403 427 609 1 )); -DATA(insert ( 403 427 611 2 )); -DATA(insert ( 403 427 607 3 )); -DATA(insert ( 403 427 612 4 )); -DATA(insert ( 403 427 610 5 )); +DATA(insert ( 1991 1 f 645 )); +DATA(insert ( 1991 2 f 647 )); +DATA(insert ( 1991 3 f 649 )); +DATA(insert ( 1991 4 f 648 )); +DATA(insert ( 1991 5 f 646 )); /* - * nbtree oidvector_ops + * btree float4_ops */ -DATA(insert ( 403 435 645 1 )); -DATA(insert ( 403 435 647 2 )); -DATA(insert ( 403 435 649 3 )); -DATA(insert ( 403 435 648 4 )); -DATA(insert ( 403 435 646 5 )); +DATA(insert ( 1970 1 f 622 )); +DATA(insert ( 1970 2 f 624 )); +DATA(insert ( 1970 3 f 620 )); +DATA(insert ( 1970 4 f 625 )); +DATA(insert ( 1970 5 f 623 )); /* - * nbtree float4_ops + * btree float8_ops */ -DATA(insert ( 403 428 622 1 )); -DATA(insert ( 403 428 624 2 )); -DATA(insert ( 403 428 620 3 )); -DATA(insert ( 403 428 625 4 )); -DATA(insert ( 403 428 623 5 )); +DATA(insert ( 1972 1 f 672 )); +DATA(insert ( 1972 2 f 673 )); +DATA(insert ( 1972 3 f 670 )); +DATA(insert ( 1972 4 f 675 )); +DATA(insert ( 1972 5 f 674 )); /* - * nbtree char_ops + * btree char_ops */ -DATA(insert ( 403 429 631 1 )); -DATA(insert ( 403 429 632 2 )); -DATA(insert ( 403 429 92 3 )); -DATA(insert ( 403 429 634 4 )); -DATA(insert ( 403 429 633 5 )); +DATA(insert ( 429 1 f 631 )); +DATA(insert ( 429 2 f 632 )); +DATA(insert ( 429 3 f 92 )); +DATA(insert ( 429 4 f 634 )); +DATA(insert ( 429 5 f 633 )); /* - * nbtree name_ops + * btree name_ops */ -DATA(insert ( 403 1181 660 1 )); -DATA(insert ( 403 1181 661 2 )); -DATA(insert ( 403 1181 93 3 )); -DATA(insert ( 403 1181 663 4 )); -DATA(insert ( 403 1181 662 5 )); +DATA(insert ( 1986 1 f 660 )); +DATA(insert ( 1986 2 f 661 )); +DATA(insert ( 1986 3 f 93 )); +DATA(insert ( 1986 4 f 663 )); +DATA(insert ( 1986 5 f 662 )); /* - * nbtree text_ops + * btree text_ops */ -DATA(insert ( 403 431 664 1 )); -DATA(insert ( 403 431 665 2 )); -DATA(insert ( 403 431 98 3 )); -DATA(insert ( 403 431 667 4 )); -DATA(insert ( 403 431 666 5 )); +DATA(insert ( 1994 1 f 664 )); +DATA(insert ( 1994 2 f 665 )); +DATA(insert ( 1994 3 f 98 )); +DATA(insert ( 1994 4 f 667 )); +DATA(insert ( 1994 5 f 666 )); /* - * nbtree abstime_ops + * btree bpchar_ops */ -DATA(insert ( 403 432 562 1 )); -DATA(insert ( 403 432 564 2 )); -DATA(insert ( 403 432 560 3 )); -DATA(insert ( 403 432 565 4 )); -DATA(insert ( 403 432 563 5 )); +DATA(insert ( 426 1 f 1058 )); +DATA(insert ( 426 2 f 1059 )); +DATA(insert ( 426 3 f 1054 )); +DATA(insert ( 426 4 f 1061 )); +DATA(insert ( 426 5 f 1060 )); /* - * nbtree bpchar_ops + * btree varchar_ops */ -DATA(insert ( 403 1076 1058 1 )); -DATA(insert ( 403 1076 1059 2 )); -DATA(insert ( 403 1076 1054 3 )); -DATA(insert ( 403 1076 1061 4 )); -DATA(insert ( 403 1076 1060 5 )); +DATA(insert ( 2003 1 f 1066 )); +DATA(insert ( 2003 2 f 1067 )); +DATA(insert ( 2003 3 f 1062 )); +DATA(insert ( 2003 4 f 1069 )); +DATA(insert ( 2003 5 f 1068 )); /* - * nbtree varchar_ops + * btree bytea_ops */ -DATA(insert ( 403 1077 1066 1 )); -DATA(insert ( 403 1077 1067 2 )); -DATA(insert ( 403 1077 1062 3 )); -DATA(insert ( 403 1077 1069 4 )); -DATA(insert ( 403 1077 1068 5 )); +DATA(insert ( 428 1 f 1957 )); +DATA(insert ( 428 2 f 1958 )); +DATA(insert ( 428 3 f 1955 )); +DATA(insert ( 428 4 f 1960 )); +DATA(insert ( 428 5 f 1959 )); /* - * nbtree bytea_ops + * btree abstime_ops */ -DATA(insert ( 403 1961 1957 1 )); -DATA(insert ( 403 1961 1958 2 )); -DATA(insert ( 403 1961 1955 3 )); -DATA(insert ( 403 1961 1960 4 )); -DATA(insert ( 403 1961 1959 5 )); +DATA(insert ( 421 1 f 562 )); +DATA(insert ( 421 2 f 564 )); +DATA(insert ( 421 3 f 560 )); +DATA(insert ( 421 4 f 565 )); +DATA(insert ( 421 5 f 563 )); /* - * nbtree date_ops + * btree date_ops */ -DATA(insert ( 403 1114 1095 1 )); -DATA(insert ( 403 1114 1096 2 )); -DATA(insert ( 403 1114 1093 3 )); -DATA(insert ( 403 1114 1098 4 )); -DATA(insert ( 403 1114 1097 5 )); +DATA(insert ( 434 1 f 1095 )); +DATA(insert ( 434 2 f 1096 )); +DATA(insert ( 434 3 f 1093 )); +DATA(insert ( 434 4 f 1098 )); +DATA(insert ( 434 5 f 1097 )); /* - * nbtree time_ops + * btree time_ops */ -DATA(insert ( 403 1115 1110 1 )); -DATA(insert ( 403 1115 1111 2 )); -DATA(insert ( 403 1115 1108 3 )); -DATA(insert ( 403 1115 1113 4 )); -DATA(insert ( 403 1115 1112 5 )); +DATA(insert ( 1996 1 f 1110 )); +DATA(insert ( 1996 2 f 1111 )); +DATA(insert ( 1996 3 f 1108 )); +DATA(insert ( 1996 4 f 1113 )); +DATA(insert ( 1996 5 f 1112 )); /* - * nbtree timetz_ops + * btree timetz_ops */ -DATA(insert ( 403 1399 1552 1 )); -DATA(insert ( 403 1399 1553 2 )); -DATA(insert ( 403 1399 1550 3 )); -DATA(insert ( 403 1399 1555 4 )); -DATA(insert ( 403 1399 1554 5 )); +DATA(insert ( 2000 1 f 1552 )); +DATA(insert ( 2000 2 f 1553 )); +DATA(insert ( 2000 3 f 1550 )); +DATA(insert ( 2000 4 f 1555 )); +DATA(insert ( 2000 5 f 1554 )); /* - * nbtree timestamp_ops + * btree timestamp_ops */ -DATA(insert ( 403 1312 1322 1 )); -DATA(insert ( 403 1312 1323 2 )); -DATA(insert ( 403 1312 1320 3 )); -DATA(insert ( 403 1312 1325 4 )); -DATA(insert ( 403 1312 1324 5 )); +DATA(insert ( 1998 1 f 1322 )); +DATA(insert ( 1998 2 f 1323 )); +DATA(insert ( 1998 3 f 1320 )); +DATA(insert ( 1998 4 f 1325 )); +DATA(insert ( 1998 5 f 1324 )); /* - * nbtree interval_ops + * btree interval_ops */ -DATA(insert ( 403 1313 1332 1 )); -DATA(insert ( 403 1313 1333 2 )); -DATA(insert ( 403 1313 1330 3 )); -DATA(insert ( 403 1313 1335 4 )); -DATA(insert ( 403 1313 1334 5 )); +DATA(insert ( 1982 1 f 1332 )); +DATA(insert ( 1982 2 f 1333 )); +DATA(insert ( 1982 3 f 1330 )); +DATA(insert ( 1982 4 f 1335 )); +DATA(insert ( 1982 5 f 1334 )); /* - * nbtree macaddr + * btree macaddr */ -DATA(insert ( 403 810 1222 1 )); -DATA(insert ( 403 810 1223 2 )); -DATA(insert ( 403 810 1220 3 )); -DATA(insert ( 403 810 1225 4 )); -DATA(insert ( 403 810 1224 5 )); +DATA(insert ( 1984 1 f 1222 )); +DATA(insert ( 1984 2 f 1223 )); +DATA(insert ( 1984 3 f 1220 )); +DATA(insert ( 1984 4 f 1225 )); +DATA(insert ( 1984 5 f 1224 )); /* - * nbtree inet + * btree inet */ -DATA(insert ( 403 935 1203 1 )); -DATA(insert ( 403 935 1204 2 )); -DATA(insert ( 403 935 1201 3 )); -DATA(insert ( 403 935 1206 4 )); -DATA(insert ( 403 935 1205 5 )); +DATA(insert ( 1974 1 f 1203 )); +DATA(insert ( 1974 2 f 1204 )); +DATA(insert ( 1974 3 f 1201 )); +DATA(insert ( 1974 4 f 1206 )); +DATA(insert ( 1974 5 f 1205 )); /* - * nbtree cidr + * btree cidr */ -DATA(insert ( 403 652 822 1 )); -DATA(insert ( 403 652 823 2 )); -DATA(insert ( 403 652 820 3 )); -DATA(insert ( 403 652 825 4 )); -DATA(insert ( 403 652 824 5 )); +DATA(insert ( 432 1 f 822 )); +DATA(insert ( 432 2 f 823 )); +DATA(insert ( 432 3 f 820 )); +DATA(insert ( 432 4 f 825 )); +DATA(insert ( 432 5 f 824 )); /* - * nbtree numeric + * btree numeric */ -DATA(insert ( 403 1768 1754 1 )); -DATA(insert ( 403 1768 1755 2 )); -DATA(insert ( 403 1768 1752 3 )); -DATA(insert ( 403 1768 1757 4 )); -DATA(insert ( 403 1768 1756 5 )); +DATA(insert ( 1988 1 f 1754 )); +DATA(insert ( 1988 2 f 1755 )); +DATA(insert ( 1988 3 f 1752 )); +DATA(insert ( 1988 4 f 1757 )); +DATA(insert ( 1988 5 f 1756 )); /* - * nbtree bool + * btree bool */ -DATA(insert ( 403 1690 58 1 )); -DATA(insert ( 403 1690 1694 2 )); -DATA(insert ( 403 1690 91 3 )); -DATA(insert ( 403 1690 1695 4 )); -DATA(insert ( 403 1690 59 5 )); +DATA(insert ( 424 1 f 58 )); +DATA(insert ( 424 2 f 1694 )); +DATA(insert ( 424 3 f 91 )); +DATA(insert ( 424 4 f 1695 )); +DATA(insert ( 424 5 f 59 )); /* - * nbtree bit + * btree bit */ -DATA(insert ( 403 424 1786 1 )); -DATA(insert ( 403 424 1788 2 )); -DATA(insert ( 403 424 1784 3 )); -DATA(insert ( 403 424 1789 4 )); -DATA(insert ( 403 424 1787 5 )); +DATA(insert ( 423 1 f 1786 )); +DATA(insert ( 423 2 f 1788 )); +DATA(insert ( 423 3 f 1784 )); +DATA(insert ( 423 4 f 1789 )); +DATA(insert ( 423 5 f 1787 )); /* - * nbtree varbit + * btree varbit */ -DATA(insert ( 403 425 1806 1 )); -DATA(insert ( 403 425 1808 2 )); -DATA(insert ( 403 425 1804 3 )); -DATA(insert ( 403 425 1809 4 )); -DATA(insert ( 403 425 1807 5 )); +DATA(insert ( 2002 1 f 1806 )); +DATA(insert ( 2002 2 f 1808 )); +DATA(insert ( 2002 3 f 1804 )); +DATA(insert ( 2002 4 f 1809 )); +DATA(insert ( 2002 5 f 1807 )); /* - * hash table _ops + * hash index _ops */ -/* int2_ops */ -DATA(insert ( 405 421 94 1 )); +/* bpchar_ops */ +DATA(insert ( 427 1 f 1054 )); +/* char_ops */ +DATA(insert ( 431 1 f 92 )); +/* cidr_ops */ +DATA(insert ( 433 1 f 820 )); +/* date_ops */ +DATA(insert ( 435 1 f 1093 )); +/* float4_ops */ +DATA(insert ( 1971 1 f 620 )); /* float8_ops */ -DATA(insert ( 405 423 670 1 )); +DATA(insert ( 1973 1 f 670 )); +/* inet_ops */ +DATA(insert ( 1975 1 f 1201 )); +/* int2_ops */ +DATA(insert ( 1977 1 f 94 )); /* int4_ops */ -DATA(insert ( 405 426 96 1 )); +DATA(insert ( 1979 1 f 96 )); /* int8_ops */ -DATA(insert ( 405 754 410 1 )); +DATA(insert ( 1981 1 f 410 )); +/* interval_ops */ +DATA(insert ( 1983 1 f 1330 )); +/* macaddr_ops */ +DATA(insert ( 1985 1 f 1220 )); +/* name_ops */ +DATA(insert ( 1987 1 f 93 )); /* oid_ops */ -DATA(insert ( 405 427 607 1 )); +DATA(insert ( 1990 1 f 607 )); /* oidvector_ops */ -DATA(insert ( 405 435 649 1 )); -/* float4_ops */ -DATA(insert ( 405 428 620 1 )); -/* char_ops */ -DATA(insert ( 405 429 92 1 )); -/* name_ops */ -DATA(insert ( 405 1181 93 1 )); +DATA(insert ( 1992 1 f 649 )); /* text_ops */ -DATA(insert ( 405 431 98 1 )); -/* bpchar_ops */ -DATA(insert ( 405 1076 1054 1 )); -/* varchar_ops */ -DATA(insert ( 405 1077 1062 1 )); -/* date_ops */ -DATA(insert ( 405 1114 1093 1 )); +DATA(insert ( 1995 1 f 98 )); /* time_ops */ -DATA(insert ( 405 1115 1108 1 )); -/* timetz_ops */ -DATA(insert ( 405 1399 1550 1 )); +DATA(insert ( 1997 1 f 1108 )); /* timestamp_ops */ -DATA(insert ( 405 1312 1320 1 )); -/* interval_ops */ -DATA(insert ( 405 1313 1330 1 )); -/* macaddr_ops */ -DATA(insert ( 405 810 1220 1 )); -/* inet_ops */ -DATA(insert ( 405 935 1201 1 )); -/* cidr_ops */ -DATA(insert ( 405 652 820 1 )); +DATA(insert ( 1999 1 f 1320 )); +/* timetz_ops */ +DATA(insert ( 2001 1 f 1550 )); +/* varchar_ops */ +DATA(insert ( 2004 1 f 1062 )); #endif /* PG_AMOP_H */ diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h index d3639becc5..e802c6fd10 100644 --- a/src/include/catalog/pg_amproc.h +++ b/src/include/catalog/pg_amproc.h @@ -2,15 +2,19 @@ * * pg_amproc.h * definition of the system "amproc" relation (pg_amproc) - * along with the relation's initial contents. The amproc - * catalog is used to store procedures used by index access - * methods that aren't associated with operators. + * along with the relation's initial contents. + * + * The amproc table identifies support procedures associated with index + * opclasses. These procedures can't be listed in pg_amop since they are + * not associated with indexable operators for the opclass. + * + * Note: the primary key for this table is <amopclaid, amprocnum>. * * * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_amproc.h,v 1.29 2001/08/13 18:45:36 tgl Exp $ + * $Id: pg_amproc.h,v 1.30 2001/08/21 16:36:05 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -35,10 +39,9 @@ */ CATALOG(pg_amproc) BKI_WITHOUT_OIDS { - Oid amid; /* the access method this proc is for */ - Oid amopclaid; /* the opclass this proc is for */ - Oid amproc; /* OID of the proc */ + Oid amopclaid; /* the index opclass this entry is for */ int2 amprocnum; /* support procedure index */ + regproc amproc; /* OID of the proc */ } FormData_pg_amproc; /* ---------------- @@ -52,11 +55,10 @@ typedef FormData_pg_amproc *Form_pg_amproc; * compiler constants for pg_amproc * ---------------- */ -#define Natts_pg_amproc 4 -#define Anum_pg_amproc_amid 1 -#define Anum_pg_amproc_amopclaid 2 +#define Natts_pg_amproc 3 +#define Anum_pg_amproc_amopclaid 1 +#define Anum_pg_amproc_amprocnum 2 #define Anum_pg_amproc_amproc 3 -#define Anum_pg_amproc_amprocnum 4 /* ---------------- * initial contents of pg_amproc @@ -64,66 +66,66 @@ typedef FormData_pg_amproc *Form_pg_amproc; */ /* rtree */ -DATA(insert (402 422 193 1)); -DATA(insert (402 422 194 2)); -DATA(insert (402 422 195 3)); -DATA(insert (402 433 193 1)); -DATA(insert (402 433 194 2)); -DATA(insert (402 433 196 3)); -DATA(insert (402 434 197 1)); -DATA(insert (402 434 198 2)); -DATA(insert (402 434 199 3)); +DATA(insert ( 422 1 193 )); +DATA(insert ( 422 2 194 )); +DATA(insert ( 422 3 196 )); +DATA(insert ( 425 1 193 )); +DATA(insert ( 425 2 194 )); +DATA(insert ( 425 3 195 )); +DATA(insert ( 1993 1 197 )); +DATA(insert ( 1993 2 198 )); +DATA(insert ( 1993 3 199 )); /* btree */ -DATA(insert (403 421 350 1)); -DATA(insert (403 423 355 1)); -DATA(insert (403 426 351 1)); -DATA(insert (403 427 356 1)); -DATA(insert (403 428 354 1)); -DATA(insert (403 429 358 1)); -DATA(insert (403 431 360 1)); -DATA(insert (403 432 357 1)); -DATA(insert (403 435 404 1)); -DATA(insert (403 754 842 1)); -DATA(insert (403 1076 1078 1)); -DATA(insert (403 1077 1079 1)); -DATA(insert (403 1114 1092 1)); -DATA(insert (403 1115 1107 1)); -DATA(insert (403 1181 359 1)); -DATA(insert (403 1312 1314 1)); -DATA(insert (403 1313 1315 1)); -DATA(insert (403 810 836 1)); -DATA(insert (403 935 926 1)); -DATA(insert (403 652 926 1)); -DATA(insert (403 1768 1769 1)); -DATA(insert (403 1690 1693 1)); -DATA(insert (403 1399 1358 1)); -DATA(insert (403 424 1596 1)); -DATA(insert (403 425 1672 1)); -DATA(insert (403 1961 1954 1)); +DATA(insert ( 421 1 357 )); +DATA(insert ( 423 1 1596 )); +DATA(insert ( 424 1 1693 )); +DATA(insert ( 426 1 1078 )); +DATA(insert ( 428 1 1954 )); +DATA(insert ( 429 1 358 )); +DATA(insert ( 432 1 926 )); +DATA(insert ( 434 1 1092 )); +DATA(insert ( 1970 1 354 )); +DATA(insert ( 1972 1 355 )); +DATA(insert ( 1974 1 926 )); +DATA(insert ( 1976 1 350 )); +DATA(insert ( 1978 1 351 )); +DATA(insert ( 1980 1 842 )); +DATA(insert ( 1982 1 1315 )); +DATA(insert ( 1984 1 836 )); +DATA(insert ( 1986 1 359 )); +DATA(insert ( 1988 1 1769 )); +DATA(insert ( 1989 1 356 )); +DATA(insert ( 1991 1 404 )); +DATA(insert ( 1994 1 360 )); +DATA(insert ( 1996 1 1107 )); +DATA(insert ( 1998 1 1314 )); +DATA(insert ( 2000 1 1358 )); +DATA(insert ( 2002 1 1672 )); +DATA(insert ( 2003 1 1079 )); /* hash */ -DATA(insert (405 421 449 1)); -DATA(insert (405 423 452 1)); -DATA(insert (405 426 450 1)); -DATA(insert (405 427 453 1)); -DATA(insert (405 428 451 1)); -DATA(insert (405 429 454 1)); -DATA(insert (405 431 456 1)); -DATA(insert (405 435 457 1)); -DATA(insert (405 652 456 1)); -DATA(insert (405 754 949 1)); -DATA(insert (405 810 399 1)); -DATA(insert (405 935 456 1)); -DATA(insert (405 1076 1080 1)); -DATA(insert (405 1077 456 1)); -DATA(insert (405 1114 450 1)); -DATA(insert (405 1115 452 1)); -DATA(insert (405 1181 455 1)); -DATA(insert (405 1312 452 1)); -DATA(insert (405 1313 1697 1)); -DATA(insert (405 1399 1696 1)); +DATA(insert ( 427 1 1080 )); +DATA(insert ( 431 1 454 )); +DATA(insert ( 433 1 456 )); +DATA(insert ( 435 1 450 )); +DATA(insert ( 1971 1 451 )); +DATA(insert ( 1973 1 452 )); +DATA(insert ( 1975 1 456 )); +DATA(insert ( 1977 1 449 )); +DATA(insert ( 1979 1 450 )); +DATA(insert ( 1981 1 949 )); +DATA(insert ( 1983 1 1697 )); +DATA(insert ( 1985 1 399 )); +DATA(insert ( 1987 1 455 )); +DATA(insert ( 1990 1 453 )); +DATA(insert ( 1992 1 457 )); +DATA(insert ( 1995 1 456 )); +DATA(insert ( 1997 1 452 )); +DATA(insert ( 1999 1 452 )); +DATA(insert ( 2001 1 1696 )); +DATA(insert ( 2004 1 456 )); #endif /* PG_AMPROC_H */ diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index e50b84ffd0..6fed33c862 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_index.h,v 1.23 2001/08/10 18:57:40 tgl Exp $ + * $Id: pg_index.h,v 1.24 2001/08/21 16:36:05 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -28,35 +28,21 @@ /* ---------------- * pg_index definition. cpp turns this into - * typedef struct FormData_pg_index. The oid of the index relation - * is stored in indexrelid; the oid of the indexed relation is stored - * in indrelid. + * typedef struct FormData_pg_index. * ---------------- */ - -/* - * it seems that all variable length fields should go at the _end_, - * because the system cache routines only copy the fields up to the - * first variable length field. so I moved indislossy, indhaskeytype, - * and indisunique before indpred. --djm 8/20/96 - */ CATALOG(pg_index) BKI_WITHOUT_OIDS { Oid indexrelid; /* OID of the index */ Oid indrelid; /* OID of the relation it indexes */ - Oid indproc; /* OID of function for functional index */ + regproc indproc; /* OID of function for functional index */ int2vector indkey; /* column numbers of indexed attributes */ oidvector indclass; /* opclass identifiers */ - bool indisclustered; /* unused */ - bool indislossy; /* index hit must be reevaluated against heap - * value to make sure it really is match; - * typically used by hash. - */ - bool indhaskeytype; /* not used, originally added by GIST */ + bool indisclustered; /* presently unused */ bool indisunique; /* is this a unique index? */ - bool indisprimary; /* is this index for primary key */ + bool indisprimary; /* is this index for primary key? */ Oid indreference; /* oid of index of referenced relation (ie - * - this index for foreign key */ + * - this index for foreign key) */ /* VARIABLE LENGTH FIELD: */ text indpred; /* expression tree for predicate, * if a partial index */ @@ -73,18 +59,16 @@ typedef FormData_pg_index *Form_pg_index; * compiler constants for pg_index * ---------------- */ -#define Natts_pg_index 12 +#define Natts_pg_index 10 #define Anum_pg_index_indexrelid 1 #define Anum_pg_index_indrelid 2 #define Anum_pg_index_indproc 3 #define Anum_pg_index_indkey 4 #define Anum_pg_index_indclass 5 #define Anum_pg_index_indisclustered 6 -#define Anum_pg_index_indislossy 7 -#define Anum_pg_index_indhaskeytype 8 -#define Anum_pg_index_indisunique 9 -#define Anum_pg_index_indisprimary 10 -#define Anum_pg_index_indreference 11 -#define Anum_pg_index_indpred 12 +#define Anum_pg_index_indisunique 7 +#define Anum_pg_index_indisprimary 8 +#define Anum_pg_index_indreference 9 +#define Anum_pg_index_indpred 10 #endif /* PG_INDEX_H */ diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h index 77adfc9121..0b3b91758c 100644 --- a/src/include/catalog/pg_opclass.h +++ b/src/include/catalog/pg_opclass.h @@ -4,11 +4,29 @@ * definition of the system "opclass" relation (pg_opclass) * along with the relation's initial contents. * + * New definition for Postgres 7.2: the primary key for this table is + * <opcamid, opcname> --- that is, there is a row for each valid combination + * of opclass name and index access method type. This row specifies the + * expected input data type for the opclass (the type of the heap column, + * or the function output type in the case of a functional index). Note + * that types binary-compatible with the specified type will be accepted too. + * + * For a given <opcamid, opcintype> pair, there can be at most one row that + * has opcdefault = true; this row is the default opclass for such data in + * such an index. + * + * Normally opckeytype = InvalidOid (zero), indicating that the data stored + * in the index is the same as the input data. If opckeytype is nonzero + * then it indicates that a conversion step is needed to produce the stored + * index data, which will be of type opckeytype (which might be the same or + * different from the input data). Performing such a conversion is the + * responsibility of the index access method --- not all AMs support this. + * * * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_opclass.h,v 1.38 2001/08/13 18:45:36 tgl Exp $ + * $Id: pg_opclass.h,v 1.39 2001/08/21 16:36:05 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -34,8 +52,11 @@ CATALOG(pg_opclass) { - NameData opcname; /* name of opclass defined by this row */ - Oid opcdeftype; /* type that opclass is default for, or 0 */ + Oid opcamid; /* index access method opclass is for */ + NameData opcname; /* name of this opclass */ + Oid opcintype; /* type of input data for opclass */ + bool opcdefault; /* T if opclass is default for opcintype */ + Oid opckeytype; /* type of index data, or InvalidOid */ } FormData_pg_opclass; /* ---------------- @@ -49,81 +70,69 @@ typedef FormData_pg_opclass *Form_pg_opclass; * compiler constants for pg_opclass * ---------------- */ -#define Natts_pg_opclass 2 -#define Anum_pg_opclass_opcname 1 -#define Anum_pg_opclass_opcdeftype 2 +#define Natts_pg_opclass 5 +#define Anum_pg_opclass_opcamid 1 +#define Anum_pg_opclass_opcname 2 +#define Anum_pg_opclass_opcintype 3 +#define Anum_pg_opclass_opcdefault 4 +#define Anum_pg_opclass_opckeytype 5 /* ---------------- * initial contents of pg_opclass * ---------------- */ -/* - * putting _null_'s in the (fixed-length) type field is bad - * (see the README in this directory), so just put zeros - * in, which are invalid OID's anyway. --djm - */ -DATA(insert OID = 421 ( int2_ops 21 )); -DESCR(""); -DATA(insert OID = 422 ( box_ops 603 )); -DESCR(""); -DATA(insert OID = 423 ( float8_ops 701 )); -DESCR(""); -DATA(insert OID = 426 ( int4_ops 23 )); -DESCR(""); -#define INT4_OPS_OID 426 -DATA(insert OID = 427 ( oid_ops 26 )); -DESCR(""); -#define OID_OPS_OID 427 -DATA(insert OID = 428 ( float4_ops 700 )); -DESCR(""); -DATA(insert OID = 429 ( char_ops 18 )); -DESCR(""); -DATA(insert OID = 431 ( text_ops 25 )); -DESCR(""); -DATA(insert OID = 432 ( abstime_ops 702 )); -DESCR(""); -DATA(insert OID = 433 ( bigbox_ops 0 )); -DESCR(""); -DATA(insert OID = 434 ( poly_ops 604 )); -DESCR(""); -DATA(insert OID = 435 ( oidvector_ops 30 )); -DESCR(""); -DATA(insert OID = 714 ( circle_ops 718 )); -DESCR(""); -DATA(insert OID = 754 ( int8_ops 20 )); -DESCR(""); -DATA(insert OID = 1076 ( bpchar_ops 1042 )); -DESCR(""); -DATA(insert OID = 1077 ( varchar_ops 1043 )); -DESCR(""); -DATA(insert OID = 1114 ( date_ops 1082 )); -DESCR(""); -DATA(insert OID = 1115 ( time_ops 1083 )); -DESCR(""); -DATA(insert OID = 1181 ( name_ops 19 )); -DESCR(""); -DATA(insert OID = 1312 ( timestamp_ops 1184 )); -DESCR(""); -DATA(insert OID = 1313 ( interval_ops 1186 )); -DESCR(""); -DATA(insert OID = 810 ( macaddr_ops 829 )); -DESCR(""); -DATA(insert OID = 935 ( inet_ops 869 )); -DESCR(""); -DATA(insert OID = 652 ( cidr_ops 650 )); -DESCR(""); -DATA(insert OID = 1768 ( numeric_ops 1700 )); -DESCR(""); -DATA(insert OID = 1690 ( bool_ops 16 )); -DESCR(""); -DATA(insert OID = 1399 ( timetz_ops 1266 )); -DESCR(""); -DATA(insert OID = 424 ( bit_ops 1560 )); -DESCR(""); -DATA(insert OID = 425 ( varbit_ops 1562 )); -DESCR(""); -DATA(insert OID = 1961 ( bytea_ops 17 )); -DESCR(""); +DATA(insert OID = 421 ( 403 abstime_ops 702 t 0 )); +DATA(insert OID = 422 ( 402 bigbox_ops 603 f 0 )); +DATA(insert OID = 423 ( 403 bit_ops 1560 t 0 )); +DATA(insert OID = 424 ( 403 bool_ops 16 t 0 )); +DATA(insert OID = 425 ( 402 box_ops 603 t 0 )); +DATA(insert OID = 426 ( 403 bpchar_ops 1042 t 0 )); +DATA(insert OID = 427 ( 405 bpchar_ops 1042 t 0 )); +DATA(insert OID = 428 ( 403 bytea_ops 17 t 0 )); +DATA(insert OID = 429 ( 403 char_ops 18 t 0 )); +DATA(insert OID = 431 ( 405 char_ops 18 t 0 )); +DATA(insert OID = 432 ( 403 cidr_ops 650 t 0 )); +DATA(insert OID = 433 ( 405 cidr_ops 650 t 0 )); +DATA(insert OID = 434 ( 403 date_ops 1082 t 0 )); +DATA(insert OID = 435 ( 405 date_ops 1082 t 0 )); +DATA(insert OID = 1970 ( 403 float4_ops 700 t 0 )); +DATA(insert OID = 1971 ( 405 float4_ops 700 t 0 )); +DATA(insert OID = 1972 ( 403 float8_ops 701 t 0 )); +DATA(insert OID = 1973 ( 405 float8_ops 701 t 0 )); +DATA(insert OID = 1974 ( 403 inet_ops 869 t 0 )); +DATA(insert OID = 1975 ( 405 inet_ops 869 t 0 )); +DATA(insert OID = 1976 ( 403 int2_ops 21 t 0 )); +#define INT2_BTREE_OPS_OID 1976 +DATA(insert OID = 1977 ( 405 int2_ops 21 t 0 )); +DATA(insert OID = 1978 ( 403 int4_ops 23 t 0 )); +#define INT4_BTREE_OPS_OID 1978 +DATA(insert OID = 1979 ( 405 int4_ops 23 t 0 )); +DATA(insert OID = 1980 ( 403 int8_ops 20 t 0 )); +DATA(insert OID = 1981 ( 405 int8_ops 20 t 0 )); +DATA(insert OID = 1982 ( 403 interval_ops 1186 t 0 )); +DATA(insert OID = 1983 ( 405 interval_ops 1186 t 0 )); +DATA(insert OID = 1984 ( 403 macaddr_ops 829 t 0 )); +DATA(insert OID = 1985 ( 405 macaddr_ops 829 t 0 )); +DATA(insert OID = 1986 ( 403 name_ops 19 t 0 )); +DATA(insert OID = 1987 ( 405 name_ops 19 t 0 )); +DATA(insert OID = 1988 ( 403 numeric_ops 1700 t 0 )); +DATA(insert OID = 1989 ( 403 oid_ops 26 t 0 )); +#define OID_BTREE_OPS_OID 1989 +DATA(insert OID = 1990 ( 405 oid_ops 26 t 0 )); +DATA(insert OID = 1991 ( 403 oidvector_ops 30 t 0 )); +DATA(insert OID = 1992 ( 405 oidvector_ops 30 t 0 )); +DATA(insert OID = 1993 ( 402 poly_ops 604 t 0 )); +DATA(insert OID = 1994 ( 403 text_ops 25 t 0 )); +DATA(insert OID = 1995 ( 405 text_ops 25 t 0 )); +DATA(insert OID = 1996 ( 403 time_ops 1083 t 0 )); +DATA(insert OID = 1997 ( 405 time_ops 1083 t 0 )); +DATA(insert OID = 1998 ( 403 timestamp_ops 1184 t 0 )); +DATA(insert OID = 1999 ( 405 timestamp_ops 1184 t 0 )); +DATA(insert OID = 2000 ( 403 timetz_ops 1266 t 0 )); +DATA(insert OID = 2001 ( 405 timetz_ops 1266 t 0 )); +DATA(insert OID = 2002 ( 403 varbit_ops 1562 t 0 )); +DATA(insert OID = 2003 ( 403 varchar_ops 1043 t 0 )); +DATA(insert OID = 2004 ( 405 varchar_ops 1043 t 0 )); #endif /* PG_OPCLASS_H */ diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 47230e9144..88c8a93135 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: defrem.h,v 1.23 2001/07/16 05:07:00 tgl Exp $ + * $Id: defrem.h,v 1.24 2001/08/21 16:36:06 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -24,7 +24,6 @@ extern void DefineIndex(char *heapRelationName, char *indexRelationName, char *accessMethodName, List *attributeList, - List *parameterList, bool unique, bool primary, Expr *predicate, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e6752a9efc..a961cb5af4 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.141 2001/08/16 20:38:55 tgl Exp $ + * $Id: parsenodes.h,v 1.142 2001/08/21 16:36:06 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -500,7 +500,6 @@ typedef struct IndexStmt char *relname; /* name of relation to index on */ char *accessMethod; /* name of access method (eg. btree) */ List *indexParams; /* a list of IndexElem */ - List *withClause; /* a list of DefElem */ Node *whereClause; /* qualification (partial-index predicate) */ List *rangetable; /* range table for qual, filled in by * transformStmt() */ diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index 4b47a84db5..43af861df0 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: relation.h,v 1.57 2001/06/05 05:26:05 tgl Exp $ + * $Id: relation.h,v 1.58 2001/08/21 16:36:06 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -183,7 +183,7 @@ typedef struct RelOptInfo * tuples - number of index tuples in index * ncolumns - number of columns in index * nkeys - number of keys used by index (input columns) - * classlist - List of PG_AMOPCLASS OIDs for the index + * classlist - List of PG_OPCLASS OIDs for the index * indexkeys - List of base-relation attribute numbers that are index keys * ordering - List of PG_OPERATOR OIDs which order the indexscan result * relam - the OID of the pg_am of the index @@ -191,7 +191,6 @@ typedef struct RelOptInfo * indproc - OID of the function if a functional index, else 0 * indpred - index predicate if a partial index, else NULL * unique - true if index is unique - * lossy - true if index is lossy (may return non-matching tuples) * * ncolumns and nkeys are the same except for a functional index, * wherein ncolumns is 1 (the single function output) while nkeys @@ -227,7 +226,6 @@ typedef struct IndexOptInfo Oid indproc; /* if a functional index */ List *indpred; /* if a partial index */ bool unique; /* if a unique index */ - bool lossy; /* if a lossy index */ } IndexOptInfo; /* diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index f676e61d1f..08fe2ccd77 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: paths.h,v 1.54 2001/06/05 17:13:51 tgl Exp $ + * $Id: paths.h,v 1.55 2001/08/21 16:36:06 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -36,8 +36,8 @@ extern RelOptInfo *make_fromexpr_rel(Query *root, FromExpr *from); * routines to generate index paths */ extern void create_index_paths(Query *root, RelOptInfo *rel); -extern Oid indexable_operator(Expr *clause, Oid opclass, Oid relam, - bool indexkey_on_left); +extern Oid indexable_operator(Expr *clause, Oid opclass, + bool indexkey_on_left); extern List *extract_or_indexqual_conditions(RelOptInfo *rel, IndexOptInfo *index, Expr *orsubclause); diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index d418e8a10d..ac7d9ba100 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: lsyscache.h,v 1.34 2001/06/14 01:09:22 tgl Exp $ + * $Id: lsyscache.h,v 1.35 2001/08/21 16:36:06 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -15,7 +15,8 @@ #include "access/htup.h" -extern bool op_class(Oid opno, Oid opclass, Oid amopid); +extern bool op_in_opclass(Oid opno, Oid opclass); +extern bool op_requires_recheck(Oid opno, Oid opclass); extern char *get_attname(Oid relid, AttrNumber attnum); extern AttrNumber get_attnum(Oid relid, char *attname); extern Oid get_atttype(Oid relid, AttrNumber attnum); @@ -23,6 +24,7 @@ extern bool get_attisset(Oid relid, char *attname); extern int32 get_atttypmod(Oid relid, AttrNumber attnum); extern void get_atttypetypmod(Oid relid, AttrNumber attnum, Oid *typid, int32 *typmod); +extern bool opclass_is_btree(Oid opclass); extern RegProcedure get_opcode(Oid opno); extern char *get_opname(Oid opno); extern bool op_mergejoinable(Oid opno, Oid ltype, Oid rtype, diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h index b9b77ca2ce..6a71d77bfc 100644 --- a/src/include/utils/syscache.h +++ b/src/include/utils/syscache.h @@ -9,7 +9,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: syscache.h,v 1.32 2001/08/10 18:57:41 tgl Exp $ + * $Id: syscache.h,v 1.33 2001/08/21 16:36:06 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -32,28 +32,29 @@ #define AMNAME 1 #define AMOPOPID 2 #define AMOPSTRATEGY 3 -#define ATTNAME 4 -#define ATTNUM 5 -#define CLADEFTYPE 6 -#define CLANAME 7 -#define GRONAME 8 -#define GROSYSID 9 -#define INDEXRELID 10 -#define INHRELID 11 -#define LANGNAME 12 -#define LANGOID 13 -#define OPERNAME 14 -#define OPEROID 15 -#define PROCNAME 16 -#define PROCOID 17 -#define RELNAME 18 -#define RELOID 19 -#define RULENAME 20 -#define SHADOWNAME 21 -#define SHADOWSYSID 22 -#define STATRELATT 23 -#define TYPENAME 24 -#define TYPEOID 25 +#define AMPROCNUM 4 +#define ATTNAME 5 +#define ATTNUM 6 +#define CLAAMNAME 7 +#define CLAOID 8 +#define GRONAME 9 +#define GROSYSID 10 +#define INDEXRELID 11 +#define INHRELID 12 +#define LANGNAME 13 +#define LANGOID 14 +#define OPERNAME 15 +#define OPEROID 16 +#define PROCNAME 17 +#define PROCOID 18 +#define RELNAME 19 +#define RELOID 20 +#define RULENAME 21 +#define SHADOWNAME 22 +#define SHADOWSYSID 23 +#define STATRELATT 24 +#define TYPENAME 25 +#define TYPEOID 26 extern void InitCatalogCache(void); diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 0ee0f83c70..933e29c849 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -121,14 +121,6 @@ WHERE pg_am.amcostestimate != 0 AND ------+---------------- (0 rows) -SELECT ctid, pg_amop.amopid -FROM pg_amop -WHERE pg_amop.amopid != 0 AND - NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amop.amopid); - ctid | amopid -------+-------- -(0 rows) - SELECT ctid, pg_amop.amopclaid FROM pg_amop WHERE pg_amop.amopclaid != 0 AND @@ -145,14 +137,6 @@ WHERE pg_amop.amopopr != 0 AND ------+--------- (0 rows) -SELECT ctid, pg_amproc.amid -FROM pg_amproc -WHERE pg_amproc.amid != 0 AND - NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amproc.amid); - ctid | amid -------+------ -(0 rows) - SELECT ctid, pg_amproc.amopclaid FROM pg_amproc WHERE pg_amproc.amopclaid != 0 AND @@ -241,12 +225,20 @@ WHERE pg_index.indrelid != 0 AND ------+---------- (0 rows) -SELECT ctid, pg_opclass.opcdeftype +SELECT ctid, pg_opclass.opcamid FROM pg_opclass -WHERE pg_opclass.opcdeftype != 0 AND - NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcdeftype); - ctid | opcdeftype -------+------------ +WHERE pg_opclass.opcamid != 0 AND + NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_opclass.opcamid); + ctid | opcamid +------+--------- +(0 rows) + +SELECT ctid, pg_opclass.opcintype +FROM pg_opclass +WHERE pg_opclass.opcintype != 0 AND + NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcintype); + ctid | opcintype +------+----------- (0 rows) SELECT ctid, pg_operator.oprleft diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 164b07adc7..2c68bb2aad 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -508,45 +508,50 @@ WHERE p1.aggtransfn = p2.oid AND p2.proisstrict AND (0 rows) -- **************** pg_opclass **************** --- There should not be multiple entries in pg_opclass with the same --- nonzero opcdeftype value, because there can be only one default opclass --- for a datatype. (But multiple entries with zero opcdeftype are OK.) +-- Look for illegal values in pg_opclass fields +SELECT p1.oid +FROM pg_opclass as p1 +WHERE p1.opcamid = 0 OR p1.opcintype = 0; + oid +----- +(0 rows) + +-- There should not be multiple entries in pg_opclass with opcdefault true +-- and the same opcamid/opcintype combination. SELECT p1.oid, p2.oid FROM pg_opclass AS p1, pg_opclass AS p2 WHERE p1.oid != p2.oid AND - p1.opcdeftype = p2.opcdeftype AND - p1.opcdeftype != 0; + p1.opcamid = p2.opcamid AND p1.opcintype = p2.opcintype AND + p1.opcdefault AND p2.opcdefault; oid | oid -----+----- (0 rows) -- **************** pg_amop **************** -- Look for illegal values in pg_amop fields -SELECT p1.amopclaid, p1.amopopr, p1.amopid +SELECT p1.amopclaid, p1.amopstrategy FROM pg_amop as p1 -WHERE p1.amopid = 0 OR p1.amopclaid = 0 OR p1.amopopr = 0 OR - p1.amopstrategy <= 0; - amopclaid | amopopr | amopid ------------+---------+-------- +WHERE p1.amopclaid = 0 OR p1.amopstrategy <= 0 OR p1.amopopr = 0; + amopclaid | amopstrategy +-----------+-------------- (0 rows) -- Cross-check amopstrategy index against parent AM -SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.amname -FROM pg_amop AS p1, pg_am AS p2 -WHERE p1.amopid = p2.oid AND p1.amopstrategy > p2.amstrategies; - amopclaid | amopopr | amopid | oid | amname ------------+---------+--------+-----+-------- +SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.amname +FROM pg_amop AS p1, pg_am AS p2, pg_opclass AS p3 +WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND + p1.amopstrategy > p2.amstrategies; + amopclaid | amopopr | oid | amname +-----------+---------+-----+-------- (0 rows) -- Detect missing pg_amop entries: should have as many strategy functions --- as AM expects for each opclass, unless there are none at all --- (some opclasses only offer support for a limited set of AMs...) +-- as AM expects for each opclass for the AM SELECT p1.oid, p1.amname, p2.oid, p2.opcname FROM pg_am AS p1, pg_opclass AS p2 -WHERE p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3 - WHERE p3.amopid = p1.oid AND p3.amopclaid = p2.oid) - AND EXISTS (SELECT * FROM pg_amop AS p3 - WHERE p3.amopid = p1.oid AND p3.amopclaid = p2.oid); +WHERE p2.opcamid = p1.oid AND + p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3 + WHERE p3.amopclaid = p2.oid); oid | amname | oid | opcname -----+--------+-----+--------- (0 rows) @@ -555,51 +560,48 @@ WHERE p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3 -- operator yielding boolean. -- NOTE: for 7.1, add restriction that operator inputs are of same type. -- We used to have opclasses like "int24_ops" but these were broken. -SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname +SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname FROM pg_amop AS p1, pg_operator AS p2 WHERE p1.amopopr = p2.oid AND (p2.oprkind != 'b' OR p2.oprresult != 16 OR p2.oprleft != p2.oprright); - amopclaid | amopopr | amopid | oid | oprname ------------+---------+--------+-----+--------- + amopclaid | amopopr | oid | oprname +-----------+---------+-----+--------- (0 rows) --- If opclass is for a specific type, operator inputs should be of that type -SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname, p3.oid, p3.opcname +-- Check that operator input types match the opclass +SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND - p3.opcdeftype != 0 AND - (p3.opcdeftype != p2.oprleft OR p3.opcdeftype != p2.oprright); - amopclaid | amopopr | amopid | oid | oprname | oid | opcname ------------+---------+--------+-----+---------+-----+--------- + (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright); + amopclaid | amopopr | oid | oprname | opcname +-----------+---------+-----+---------+--------- (0 rows) -- **************** pg_amproc **************** -- Look for illegal values in pg_amproc fields -SELECT p1.amid, p1.amopclaid, p1.amprocnum +SELECT p1.amopclaid, p1.amprocnum FROM pg_amproc as p1 -WHERE p1.amid = 0 OR p1.amopclaid = 0 OR p1.amproc = 0 OR - p1.amprocnum <= 0; - amid | amopclaid | amprocnum -------+-----------+----------- +WHERE p1.amopclaid = 0 OR p1.amprocnum <= 0 OR p1.amproc = 0; + amopclaid | amprocnum +-----------+----------- (0 rows) -- Cross-check amprocnum index against parent AM -SELECT p1.amid, p1.amopclaid, p1.amprocnum, p2.oid, p2.amname -FROM pg_amproc AS p1, pg_am AS p2 -WHERE p1.amid = p2.oid AND p1.amprocnum > p2.amsupport; - amid | amopclaid | amprocnum | oid | amname -------+-----------+-----------+-----+-------- +SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.amname +FROM pg_amproc AS p1, pg_am AS p2, pg_opclass AS p3 +WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND + p1.amprocnum > p2.amsupport; + amopclaid | amprocnum | oid | amname +-----------+-----------+-----+-------- (0 rows) -- Detect missing pg_amproc entries: should have as many support functions --- as AM expects for each opclass, unless there are none at all --- (some opclasses only offer support for a limited set of AMs...) +-- as AM expects for each opclass for the AM SELECT p1.oid, p1.amname, p2.oid, p2.opcname FROM pg_am AS p1, pg_opclass AS p2 -WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 - WHERE p3.amid = p1.oid AND p3.amopclaid = p2.oid) - AND EXISTS (SELECT * FROM pg_amproc AS p3 - WHERE p3.amid = p1.oid AND p3.amopclaid = p2.oid); +WHERE p2.opcamid = p1.oid AND + p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 + WHERE p3.amopclaid = p2.oid); oid | amname | oid | opcname -----+--------+-----+--------- (0 rows) @@ -609,49 +611,19 @@ WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 -- or different base data types. -- We can check that all the referenced instances of the same support -- routine number take the same number of parameters, but that's about it... -SELECT p1.amid, p1.amopclaid, p1.amprocnum, +SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.proname, - p3.amid, p3.amopclaid, p3.amprocnum, - p4.oid, p4.proname -FROM pg_amproc AS p1, pg_proc AS p2, pg_amproc AS p3, pg_proc AS p4 -WHERE p1.amid = p3.amid AND p1.amprocnum = p3.amprocnum AND - p1.amproc = p2.oid AND p3.amproc = p4.oid AND - (p2.proretset OR p4.proretset OR p2.pronargs != p4.pronargs); - amid | amopclaid | amprocnum | oid | proname | amid | amopclaid | amprocnum | oid | proname -------+-----------+-----------+-----+---------+------+-----------+-----------+-----+--------- -(0 rows) - --- Cross-check that each opclass that has any entries for a given AM --- has all the entries that any other opclass does. This catches cases --- where an opclass has pg_amop but not pg_amproc entries or vice versa. --- (The above tests for missing pg_amop or pg_amproc entries are redundant --- with this, but I'll leave them in place anyway.) --- All the strategy index numbers used for each AM -CREATE TEMP TABLE amopstrategies AS - SELECT DISTINCT amopid, amopstrategy FROM pg_amop; --- All the support proc numbers used for each AM -CREATE TEMP TABLE amprocnums AS - SELECT DISTINCT amid, amprocnum FROM pg_amproc; --- All the opclasses that claim to have support for each AM in either table. --- UNION implies DISTINCT, so we do not need DISTINCT in the sub-selects. -CREATE TEMP TABLE amopclassids AS - SELECT amid, amopclaid FROM pg_amproc UNION - SELECT amopid, amopclaid FROM pg_amop; --- Look for AMs that are missing one or more strategy operators -SELECT * FROM amopclassids c, amopstrategies s -WHERE c.amid = s.amopid AND NOT EXISTS - (SELECT 1 FROM pg_amop a WHERE a.amopid = c.amid AND - a.amopclaid = c.amopclaid AND a.amopstrategy = s.amopstrategy); - amid | amopclaid | amopid | amopstrategy -------+-----------+--------+-------------- -(0 rows) - --- Look for AMs that are missing one or more support procs -SELECT * FROM amopclassids c, amprocnums p -WHERE c.amid = p.amid AND NOT EXISTS - (SELECT 1 FROM pg_amproc a WHERE a.amid = c.amid AND - a.amopclaid = c.amopclaid AND a.amprocnum = p.amprocnum); - amid | amopclaid | amid | amprocnum -------+-----------+------+----------- + p3.opcname, + p4.amopclaid, p4.amprocnum, + p5.oid, p5.proname, + p6.opcname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3, + pg_amproc AS p4, pg_proc AS p5, pg_opclass AS p6 +WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND + p3.opcamid = p6.opcamid AND p1.amprocnum = p4.amprocnum AND + p1.amproc = p2.oid AND p4.amproc = p5.oid AND + (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs); + amopclaid | amprocnum | oid | proname | opcname | amopclaid | amprocnum | oid | proname | opcname +-----------+-----------+-----+---------+---------+-----------+-----------+-----+---------+--------- (0 rows) diff --git a/src/test/regress/sql/oidjoins.sql b/src/test/regress/sql/oidjoins.sql index 191116318e..3403215be6 100644 --- a/src/test/regress/sql/oidjoins.sql +++ b/src/test/regress/sql/oidjoins.sql @@ -61,10 +61,6 @@ SELECT ctid, pg_am.amcostestimate FROM pg_am WHERE pg_am.amcostestimate != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate); -SELECT ctid, pg_amop.amopid -FROM pg_amop -WHERE pg_amop.amopid != 0 AND - NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amop.amopid); SELECT ctid, pg_amop.amopclaid FROM pg_amop WHERE pg_amop.amopclaid != 0 AND @@ -73,10 +69,6 @@ SELECT ctid, pg_amop.amopopr FROM pg_amop WHERE pg_amop.amopopr != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr); -SELECT ctid, pg_amproc.amid -FROM pg_amproc -WHERE pg_amproc.amid != 0 AND - NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amproc.amid); SELECT ctid, pg_amproc.amopclaid FROM pg_amproc WHERE pg_amproc.amopclaid != 0 AND @@ -121,10 +113,14 @@ SELECT ctid, pg_index.indrelid FROM pg_index WHERE pg_index.indrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid); -SELECT ctid, pg_opclass.opcdeftype +SELECT ctid, pg_opclass.opcamid +FROM pg_opclass +WHERE pg_opclass.opcamid != 0 AND + NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_opclass.opcamid); +SELECT ctid, pg_opclass.opcintype FROM pg_opclass -WHERE pg_opclass.opcdeftype != 0 AND - NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcdeftype); +WHERE pg_opclass.opcintype != 0 AND + NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcintype); SELECT ctid, pg_operator.oprleft FROM pg_operator WHERE pg_operator.oprleft != 0 AND diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index a158592330..598f0ae2d6 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -425,85 +425,85 @@ WHERE p1.aggtransfn = p2.oid AND p2.proisstrict AND -- **************** pg_opclass **************** --- There should not be multiple entries in pg_opclass with the same --- nonzero opcdeftype value, because there can be only one default opclass --- for a datatype. (But multiple entries with zero opcdeftype are OK.) +-- Look for illegal values in pg_opclass fields + +SELECT p1.oid +FROM pg_opclass as p1 +WHERE p1.opcamid = 0 OR p1.opcintype = 0; + +-- There should not be multiple entries in pg_opclass with opcdefault true +-- and the same opcamid/opcintype combination. SELECT p1.oid, p2.oid FROM pg_opclass AS p1, pg_opclass AS p2 WHERE p1.oid != p2.oid AND - p1.opcdeftype = p2.opcdeftype AND - p1.opcdeftype != 0; + p1.opcamid = p2.opcamid AND p1.opcintype = p2.opcintype AND + p1.opcdefault AND p2.opcdefault; -- **************** pg_amop **************** -- Look for illegal values in pg_amop fields -SELECT p1.amopclaid, p1.amopopr, p1.amopid +SELECT p1.amopclaid, p1.amopstrategy FROM pg_amop as p1 -WHERE p1.amopid = 0 OR p1.amopclaid = 0 OR p1.amopopr = 0 OR - p1.amopstrategy <= 0; +WHERE p1.amopclaid = 0 OR p1.amopstrategy <= 0 OR p1.amopopr = 0; -- Cross-check amopstrategy index against parent AM -SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.amname -FROM pg_amop AS p1, pg_am AS p2 -WHERE p1.amopid = p2.oid AND p1.amopstrategy > p2.amstrategies; +SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.amname +FROM pg_amop AS p1, pg_am AS p2, pg_opclass AS p3 +WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND + p1.amopstrategy > p2.amstrategies; -- Detect missing pg_amop entries: should have as many strategy functions --- as AM expects for each opclass, unless there are none at all --- (some opclasses only offer support for a limited set of AMs...) +-- as AM expects for each opclass for the AM SELECT p1.oid, p1.amname, p2.oid, p2.opcname FROM pg_am AS p1, pg_opclass AS p2 -WHERE p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3 - WHERE p3.amopid = p1.oid AND p3.amopclaid = p2.oid) - AND EXISTS (SELECT * FROM pg_amop AS p3 - WHERE p3.amopid = p1.oid AND p3.amopclaid = p2.oid); +WHERE p2.opcamid = p1.oid AND + p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3 + WHERE p3.amopclaid = p2.oid); -- Check that amopopr points at a reasonable-looking operator, ie a binary -- operator yielding boolean. -- NOTE: for 7.1, add restriction that operator inputs are of same type. -- We used to have opclasses like "int24_ops" but these were broken. -SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname +SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname FROM pg_amop AS p1, pg_operator AS p2 WHERE p1.amopopr = p2.oid AND (p2.oprkind != 'b' OR p2.oprresult != 16 OR p2.oprleft != p2.oprright); --- If opclass is for a specific type, operator inputs should be of that type +-- Check that operator input types match the opclass -SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname, p3.oid, p3.opcname +SELECT p1.amopclaid, p1.amopopr, p2.oid, p2.oprname, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND - p3.opcdeftype != 0 AND - (p3.opcdeftype != p2.oprleft OR p3.opcdeftype != p2.oprright); + (p3.opcintype != p2.oprleft OR p3.opcintype != p2.oprright); -- **************** pg_amproc **************** -- Look for illegal values in pg_amproc fields -SELECT p1.amid, p1.amopclaid, p1.amprocnum +SELECT p1.amopclaid, p1.amprocnum FROM pg_amproc as p1 -WHERE p1.amid = 0 OR p1.amopclaid = 0 OR p1.amproc = 0 OR - p1.amprocnum <= 0; +WHERE p1.amopclaid = 0 OR p1.amprocnum <= 0 OR p1.amproc = 0; -- Cross-check amprocnum index against parent AM -SELECT p1.amid, p1.amopclaid, p1.amprocnum, p2.oid, p2.amname -FROM pg_amproc AS p1, pg_am AS p2 -WHERE p1.amid = p2.oid AND p1.amprocnum > p2.amsupport; +SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.amname +FROM pg_amproc AS p1, pg_am AS p2, pg_opclass AS p3 +WHERE p1.amopclaid = p3.oid AND p3.opcamid = p2.oid AND + p1.amprocnum > p2.amsupport; -- Detect missing pg_amproc entries: should have as many support functions --- as AM expects for each opclass, unless there are none at all --- (some opclasses only offer support for a limited set of AMs...) +-- as AM expects for each opclass for the AM SELECT p1.oid, p1.amname, p2.oid, p2.opcname FROM pg_am AS p1, pg_opclass AS p2 -WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 - WHERE p3.amid = p1.oid AND p3.amopclaid = p2.oid) - AND EXISTS (SELECT * FROM pg_amproc AS p3 - WHERE p3.amid = p1.oid AND p3.amopclaid = p2.oid); +WHERE p2.opcamid = p1.oid AND + p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 + WHERE p3.amopclaid = p2.oid); -- Unfortunately, we can't check the amproc link very well because the -- signature of the function may be different for different support routines @@ -511,43 +511,15 @@ WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 -- We can check that all the referenced instances of the same support -- routine number take the same number of parameters, but that's about it... -SELECT p1.amid, p1.amopclaid, p1.amprocnum, +SELECT p1.amopclaid, p1.amprocnum, p2.oid, p2.proname, - p3.amid, p3.amopclaid, p3.amprocnum, - p4.oid, p4.proname -FROM pg_amproc AS p1, pg_proc AS p2, pg_amproc AS p3, pg_proc AS p4 -WHERE p1.amid = p3.amid AND p1.amprocnum = p3.amprocnum AND - p1.amproc = p2.oid AND p3.amproc = p4.oid AND - (p2.proretset OR p4.proretset OR p2.pronargs != p4.pronargs); - --- Cross-check that each opclass that has any entries for a given AM --- has all the entries that any other opclass does. This catches cases --- where an opclass has pg_amop but not pg_amproc entries or vice versa. --- (The above tests for missing pg_amop or pg_amproc entries are redundant --- with this, but I'll leave them in place anyway.) - --- All the strategy index numbers used for each AM -CREATE TEMP TABLE amopstrategies AS - SELECT DISTINCT amopid, amopstrategy FROM pg_amop; - --- All the support proc numbers used for each AM -CREATE TEMP TABLE amprocnums AS - SELECT DISTINCT amid, amprocnum FROM pg_amproc; - --- All the opclasses that claim to have support for each AM in either table. --- UNION implies DISTINCT, so we do not need DISTINCT in the sub-selects. -CREATE TEMP TABLE amopclassids AS - SELECT amid, amopclaid FROM pg_amproc UNION - SELECT amopid, amopclaid FROM pg_amop; - --- Look for AMs that are missing one or more strategy operators -SELECT * FROM amopclassids c, amopstrategies s -WHERE c.amid = s.amopid AND NOT EXISTS - (SELECT 1 FROM pg_amop a WHERE a.amopid = c.amid AND - a.amopclaid = c.amopclaid AND a.amopstrategy = s.amopstrategy); - --- Look for AMs that are missing one or more support procs -SELECT * FROM amopclassids c, amprocnums p -WHERE c.amid = p.amid AND NOT EXISTS - (SELECT 1 FROM pg_amproc a WHERE a.amid = c.amid AND - a.amopclaid = c.amopclaid AND a.amprocnum = p.amprocnum); + p3.opcname, + p4.amopclaid, p4.amprocnum, + p5.oid, p5.proname, + p6.opcname +FROM pg_amproc AS p1, pg_proc AS p2, pg_opclass AS p3, + pg_amproc AS p4, pg_proc AS p5, pg_opclass AS p6 +WHERE p1.amopclaid = p3.oid AND p4.amopclaid = p6.oid AND + p3.opcamid = p6.opcamid AND p1.amprocnum = p4.amprocnum AND + p1.amproc = p2.oid AND p4.amproc = p5.oid AND + (p2.proretset OR p5.proretset OR p2.pronargs != p5.pronargs); diff --git a/src/tutorial/complex.source b/src/tutorial/complex.source index e0859a6797..fb6bb9fde9 100644 --- a/src/tutorial/complex.source +++ b/src/tutorial/complex.source @@ -7,7 +7,7 @@ -- -- Copyright (c) 1994, Regents of the University of California -- --- $Id: complex.source,v 1.8 2000/07/17 03:05:41 tgl Exp $ +-- $Id: complex.source,v 1.9 2001/08/21 16:36:06 tgl Exp $ -- --------------------------------------------------------------------------- @@ -169,14 +169,19 @@ CREATE OPERATOR > ( restrict = scalargtsel, join = scalargtjoinsel ); -INSERT INTO pg_opclass (opcname, opcdeftype) - SELECT 'complex_abs_ops', oid FROM pg_type WHERE typname = 'complex'; +INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'btree'), + 'complex_abs_ops', + (SELECT oid FROM pg_type WHERE typname = 'complex'), + true, + 0); -SELECT oid, opcname, opcdeftype +SELECT oid, * FROM pg_opclass WHERE opcname = 'complex_abs_ops'; SELECT o.oid AS opoid, o.oprname -INTO TABLE complex_ops_tmp +INTO TEMP TABLE complex_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'complex'; @@ -184,34 +189,44 @@ WHERE o.oprleft = t.oid and o.oprright = t.oid -- make sure we have the right operators SELECT * from complex_ops_tmp; -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 1, false, c.opoid + FROM pg_opclass opcl, complex_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops' and c.oprname = '<'; -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 2 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 2, false, c.opoid + FROM pg_opclass opcl, complex_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops' and c.oprname = '<='; -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, false, c.opoid + FROM pg_opclass opcl, complex_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops' and c.oprname = '='; -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 4 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 4, false, c.opoid + FROM pg_opclass opcl, complex_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops' and c.oprname = '>='; -INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) - SELECT am.oid, opcl.oid, c.opoid, 5 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 5, false, c.opoid + FROM pg_opclass opcl, complex_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops' and c.oprname = '>'; -- @@ -220,10 +235,12 @@ CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4 SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp'; -INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'btree' and opcname = 'complex_abs_ops' +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops' and proname = 'complex_abs_cmp'; -- now, we can define a btree index on complex types. First, let's populate @@ -239,49 +256,19 @@ SELECT * from test_complex where a = '(56.0,-22.5)'; SELECT * from test_complex where a < '(56.0,-22.5)'; SELECT * from test_complex where a > '(56.0,-22.5)'; -DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy) - = ( - SELECT am.oid, opcl.oid, c.opoid, 1 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' - and c.oprname = '<'); - -DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy) - = ( - SELECT am.oid, opcl.oid, c.opoid, 2 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' - and c.oprname = '<='); - -DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy) - = ( - SELECT am.oid, opcl.oid, c.opoid, 3 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' - and c.oprname = '='); - -DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy) - = ( - SELECT am.oid, opcl.oid, c.opoid, 4 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' - and c.oprname = '>='); - -DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy) - = ( - SELECT am.oid, opcl.oid, c.opoid, 5 - FROM pg_am am, pg_opclass opcl, complex_ops_tmp c - WHERE amname = 'btree' and opcname = 'complex_abs_ops' - and c.oprname = '>'); - -DELETE FROM pg_amproc where (amid, amopclaid, amproc, amprocnum) - = ( - SELECT am.oid, opcl.oid, pro.oid, 1 - FROM pg_am am, pg_opclass opcl, pg_proc pro - WHERE amname = 'btree' and opcname = 'complex_abs_ops' - and proname = 'complex_abs_cmp'); - -DELETE FROM pg_opclass WHERE opcname = 'complex_abs_ops'; +DELETE FROM pg_amop WHERE + amopclaid = (SELECT oid FROM pg_opclass WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops'); + +DELETE FROM pg_amproc WHERE + amopclaid = (SELECT oid FROM pg_opclass WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops'); + +DELETE FROM pg_opclass WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') + and opcname = 'complex_abs_ops'; DROP FUNCTION complex_in(opaque); DROP FUNCTION complex_out(opaque); diff --git a/src/tutorial/syscat.source b/src/tutorial/syscat.source index 35a4d28db9..f1be25728c 100644 --- a/src/tutorial/syscat.source +++ b/src/tutorial/syscat.source @@ -7,7 +7,7 @@ -- Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group -- Portions Copyright (c) 1994, Regents of the University of California -- --- $Id: syscat.source,v 1.4 2001/06/13 19:31:56 tgl Exp $ +-- $Id: syscat.source,v 1.5 2001/08/21 16:36:06 tgl Exp $ -- --------------------------------------------------------------------------- @@ -141,8 +141,8 @@ SELECT a.aggname, t.typname -- classes -- SELECT am.amname, opc.opcname, opr.oprname - FROM pg_am am, pg_amop amop, pg_opclass opc, pg_operator opr - WHERE amop.amopid = am.oid + FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr + WHERE opc.opcamid = am.oid and amop.amopclaid = opc.oid and amop.amopopr = opr.oid ORDER BY amname, opcname, oprname; |