Latest patch. Names and scopes are as per discussion. New files for code and regression test. Docs included.
-- Andrew (irc:RhodiumToad)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 7830334..4552a74 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -16290,6 +16290,18 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); </indexterm> <indexterm> + <primary>pg_indexam_has_property</primary> + </indexterm> + + <indexterm> + <primary>pg_index_column_has_property</primary> + </indexterm> + + <indexterm> + <primary>pg_index_has_property</primary> + </indexterm> + + <indexterm> <primary>pg_options_to_table</primary> </indexterm> @@ -16477,6 +16489,21 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); number of columns, pretty-printing is implied</entry> </row> <row> + <entry><literal><function>pg_indexam_has_property(<parameter>am_oid</parameter>, <parameter>prop_name</>)</function></literal></entry> + <entry><type>boolean</type></entry> + <entry>Test whether an index access method has a specified property</entry> + </row> + <row> + <entry><literal><function>pg_index_column_has_property(<parameter>index_oid</parameter>, <parameter>column_no</>, <parameter>prop_name</>)</function></literal></entry> + <entry><type>boolean</type></entry> + <entry>Test whether an index column has a specified property</entry> + </row> + <row> + <entry><literal><function>pg_index_has_property(<parameter>index_oid</parameter>, <parameter>prop_name</>)</function></literal></entry> + <entry><type>boolean</type></entry> + <entry>Test whether the access method for the specified index has a specified property</entry> + </row> + <row> <entry><literal><function>pg_options_to_table(<parameter>reloptions</parameter>)</function></literal></entry> <entry><type>setof record</type></entry> <entry>get the set of storage option name/value pairs</entry> @@ -16620,6 +16647,141 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); </para> <para> + <function>pg_indexam_has_property</function>, + <function>pg_index_has_property</function>, and + <function>pg_index_column_has_property</function> return whether the + specified access method, index, or index column possesses the named + property. <literal>NULL</literal> is returned if the property name is not + known; <literal>true</literal> if the property is present, + <literal>false</literal> if it is not. Refer to + <xref linkend="functions-info-index-column-props"> for column properties, + <xref linkend="functions-info-index-props"> for index properties, and + <xref linkend="functions-info-index-am-props"> for access method properties. + </para> + + <table id="functions-info-index-column-props"> + <title>Index Column Properties</title> + <tgroup cols="2"> + <thead> + <row><entry>Name</entry><entry>Description</entry></row> + </thead> + <tbody> + <row> + <entry><literal>asc</literal></entry> + <entry>Does the column sort in ascending order on a forward scan? + </entry> + </row> + <row> + <entry><literal>desc</literal></entry> + <entry>Does the column sort in descending order on a forward scan? + </entry> + </row> + <row> + <entry><literal>nulls_first</literal></entry> + <entry>Does the column sort with nulls first on a forward scan? + </entry> + </row> + <row> + <entry><literal>nulls_last</literal></entry> + <entry>Does the column sort with nulls last on a forward scan? + </entry> + </row> + <row> + <entry><literal>orderable</literal></entry> + <entry>Does the column possess any ordering properties such + as <literal>ASC</> or <literal>DESC</></entry> + </row> + <row> + <entry><literal>distance_orderable</literal></entry> + <entry>Can the column be returned in order by a "distance" operator, + for example <literal>ORDER BY col <-> constant</></entry> + </row> + <row> + <entry><literal>returnable</literal></entry> + <entry>Can the column value be returned in an index-only scan? + </entry> + </row> + <row> + <entry><literal>search_array</literal></entry> + <entry>Does the column support array queries with <literal>ANY</> + natively in the index AM?</entry> + </row> + <row> + <entry><literal>search_nulls</literal></entry> + <entry>Does the column support <literal>IS NULL</> or + <literal>IS NOT NULL</> conditions in the index? + </entry> + </row> + </tbody> + </tgroup> + </table> + + <table id="functions-info-index-props"> + <title>Index Properties</title> + <tgroup cols="2"> + <thead> + <row><entry>Name</entry><entry>Description</entry></row> + </thead> + <tbody> + <row> + <entry><literal>clusterable</literal></entry> + <entry>Can this index be used in a <literal>CLUSTER</> operation? + </entry> + </row> + <row> + <entry><literal>backward_scan</literal></entry> + <entry>Can this index be scanned in the reverse direction? + </entry> + </row> + <row> + <entry><literal>index_scan</literal></entry> + <entry>Does this index support plain (non-bitmap) scans? + </entry> + </row> + <row> + <entry><literal>bitmap_scan</literal></entry> + <entry>Does this index support bitmap scans? + </entry> + </row> + </tbody> + </tgroup> + </table> + + <table id="functions-info-indexam-props"> + <title>Index Access Method Properties</title> + <tgroup cols="2"> + <thead> + <row><entry>Name</entry><entry>Description</entry></row> + </thead> + <tbody> + <row> + <entry><literal>can_order</literal></entry> + <entry>Does this access method support <literal>ASC</>, + <literal>DESC</> and related keywords on columns in + <literal>CREATE INDEX</>? + </entry> + </row> + <row> + <entry><literal>can_unique</literal></entry> + <entry>Does this access method support + <literal>CREATE UNIQUE INDEX</>? + </entry> + </row> + <row> + <entry><literal>can_multi_col</literal></entry> + <entry>Does this access method support multiple columns? + </entry> + </row> + <row> + <entry><literal>can_exclude</literal></entry> + <entry>Does this access method support exclusion constraints? + </entry> + </row> + </tbody> + </tgroup> + </table> + + <para> <function>pg_options_to_table</function> returns the set of storage option name/value pairs (<literal>option_name</>/<literal>option_value</>) when passed diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index e8034b9..fece954 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -16,11 +16,14 @@ #include "access/gist_private.h" #include "access/gistscan.h" +#include "access/htup_details.h" #include "catalog/pg_collation.h" +#include "catalog/pg_opclass.h" #include "miscadmin.h" #include "utils/index_selfuncs.h" #include "utils/memutils.h" #include "utils/rel.h" +#include "utils/syscache.h" /* non-export function prototypes */ @@ -87,6 +90,7 @@ gisthandler(PG_FUNCTION_ARGS) amroutine->amendscan = gistendscan; amroutine->ammarkpos = NULL; amroutine->amrestrpos = NULL; + amroutine->amproperty = gistproperty; PG_RETURN_POINTER(amroutine); } @@ -1490,6 +1494,98 @@ freeGISTstate(GISTSTATE *giststate) MemoryContextDelete(giststate->scanCxt); } + +/* + * gistproperty() -- Check arbitrary properties of indexes. + * + * This is optional for most cases, but for GiST, the logic for + * distance_orderable (which is currently supported by no other AM) is here for + * simplicity. We also handle returnable to save opening the rel for + * gistcanreturn. + */ +bool gistproperty(Oid index_oid, int attno, + IndexAMProperty prop, const char *propname, + bool *res, bool *isnull) +{ + HeapTuple tuple; + Form_pg_index rd_index; + Form_pg_opclass rd_opclass; + Datum datum; + oidvector *indclass; + Oid opclass, + opfamily, + opcintype; + int16 procno; + + if (attno == 0) + return false; + + switch (prop) + { + case AMPROP_DISTANCE_ORDERABLE: + procno = GIST_DISTANCE_PROC; + break; + case AMPROP_RETURNABLE: + procno = GIST_FETCH_PROC; + break; + default: + return false; + } + + /* + * Currently, GiST distance ordered scans require that there be a distance + * function in the opclass with the default types (i.e. the one loaded into + * the relcache entry, see use of index_getprocid above). So we assume that + * if such a function exists, then there's a reason for it (rather than + * grubbing through all the opfamily's operators to find an ordered one). + */ + + /* First we need to know the opclass. */ + + tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(index_oid)); + if (!HeapTupleIsValid(tuple)) + { + *isnull = true; + return true; + } + rd_index = (Form_pg_index) GETSTRUCT(tuple); + + datum = SysCacheGetAttr(INDEXRELID, tuple, + Anum_pg_index_indclass, isnull); + Assert(!*isnull); + + /* caller is supposed to guarantee this */ + Assert(attno > 0 && attno <= rd_index->indnatts); + + indclass = ((oidvector *) DatumGetPointer(datum)); + opclass = indclass->values[attno - 1]; + ReleaseSysCache(tuple); + + /* Now look up the opclass */ + + tuple = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass)); + if (!HeapTupleIsValid(tuple)) + { + *isnull = true; + return true; + } + rd_opclass = (Form_pg_opclass) GETSTRUCT(tuple); + + opfamily = rd_opclass->opcfamily; + opcintype = rd_opclass->opcintype; + ReleaseSysCache(tuple); + + /* and the function */ + + *res = SearchSysCacheExists4(AMPROCNUM, + ObjectIdGetDatum(opfamily), + ObjectIdGetDatum(opcintype), + ObjectIdGetDatum(opcintype), + Int16GetDatum(procno) ); + *isnull = false; + return true; +} + /* * gistvacuumpage() -- try to remove LP_DEAD items from the given page. * Function assumes that buffer is exclusively locked. diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index 1f47973..12b01ac 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -116,6 +116,7 @@ bthandler(PG_FUNCTION_ARGS) amroutine->amendscan = btendscan; amroutine->ammarkpos = btmarkpos; amroutine->amrestrpos = btrestrpos; + amroutine->amproperty = btproperty; PG_RETURN_POINTER(amroutine); } @@ -1128,3 +1129,26 @@ btcanreturn(Relation index, int attno) { return true; } + +/* + * btproperty() -- Check arbitrary properties of indexes. + * + * This is optional, but handling AMPROP_CAN_RETURN here saves opening the rel + * for the generic answer. + */ +bool btproperty(Oid index_oid, int attno, + IndexAMProperty prop, const char *propname, + bool *res, bool *isnull) +{ + switch (prop) + { + case AMPROP_RETURNABLE: + if (attno == 0) + return false; + *res = true; + return true; + + default: + return false; /* punt to generic code */ + } +} diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index 2b4ebc7..b9e217a 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -9,7 +9,7 @@ top_builddir = ../../../.. include $(top_builddir)/src/Makefile.global # keep this list arranged alphabetically or it gets to be a mess -OBJS = acl.o arrayfuncs.o array_expanded.o array_selfuncs.o \ +OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \ array_typanalyze.o array_userfuncs.o arrayutils.o ascii.o \ bool.o cash.o char.o date.o datetime.o datum.o dbsize.o domains.o \ encode.o enum.o expandeddatum.o \ diff --git a/src/backend/utils/adt/amutils.c b/src/backend/utils/adt/amutils.c new file mode 100644 index 0000000..1521f5d --- /dev/null +++ b/src/backend/utils/adt/amutils.c @@ -0,0 +1,343 @@ +/*------------------------------------------------------------------------- + * + * amutils.c + * SQL-level index access methods API support. + * + * Copyright (c) 2016, PostgreSQL Global Development Group + * + * + * IDENTIFICATION + * src/backend/utils/adt/amutils.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/amapi.h" +#include "access/genam.h" +#include "access/htup_details.h" +#include "catalog/pg_class.h" +#include "catalog/pg_index.h" +#include "utils/builtins.h" +#include "utils/syscache.h" + + +struct am_propnames +{ + const char *name; + IndexAMProperty prop; +} am_propnames[] = { + { "asc", AMPROP_ASC}, + { "desc", AMPROP_DESC}, + { "nulls_first", AMPROP_NULLS_FIRST}, + { "nulls_last", AMPROP_NULLS_LAST}, + { "orderable", AMPROP_ORDERABLE}, + { "distance_orderable", AMPROP_DISTANCE_ORDERABLE}, + { "returnable", AMPROP_RETURNABLE}, + { "search_array", AMPROP_SEARCH_ARRAY}, + { "search_nulls", AMPROP_SEARCH_NULLS}, + { "backward_scan", AMPROP_BACKWARD_SCAN}, + { "clusterable", AMPROP_CLUSTERABLE}, + { "index_scan", AMPROP_INDEX_SCAN}, + { "bitmap_scan", AMPROP_BITMAP_SCAN}, + { "can_order", AMPROP_CAN_ORDER}, + { "can_unique", AMPROP_CAN_UNIQUE}, + { "can_multi_col", AMPROP_CAN_MULTI_COL}, + { "can_exclude", AMPROP_CAN_EXCLUDE} +}; + +static IndexAMProperty +lookup_prop_name(const char *name) +{ + int i; + + for (i = 0; i < lengthof(am_propnames); i++) + if (pg_strcasecmp(am_propnames[i].name, name) == 0) + return am_propnames[i].prop; + + return AMPROP_UNKNOWN; +} + +/* + * Common code for the properties that are just bit tests of indoptions. + * + * Returns false for "unknown/inapplicable", otherwise sets *res to the + * boolean value to return. + */ + +static bool +test_indoption(Oid relid, int attno, bool guard, + int16 iopt_test, int16 iopt_expect, + bool *res) +{ + HeapTuple tuple; + Form_pg_index rd_index; + Datum datum; + bool isnull; + int2vector *indoption; + int16 indoption_val; + + if (!guard) + { + *res = false; + return true; + } + + tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tuple)) + return false; + rd_index = (Form_pg_index) GETSTRUCT(tuple); + + Assert(relid == rd_index->indexrelid); + + datum = SysCacheGetAttr(INDEXRELID, tuple, + Anum_pg_index_indoption, &isnull); + Assert(!isnull); + + indoption = ((int2vector *) DatumGetPointer(datum)); + indoption_val = indoption->values[attno - 1]; + + *res = (indoption_val & iopt_test) == iopt_expect; + + ReleaseSysCache(tuple); + + return true; +} + + +/* + * Test properties of an index or index AM. + * + * This is common code for different SQL-level funcs, so the amoid and + * index_oid parameters are mutually exclusive; we look up the amoid from the + * index_oid if needed, or if no index oid is given, we're looking at AM-wide + * properties. + */ +static Datum +indexam_property(FunctionCallInfo fcinfo, + Datum propname, + Oid amoid, Oid index_oid, int attno) +{ + IndexAmRoutine *routine = NULL; + bool res = false; + bool isnull = false; + char *name; + int natts = 0; + IndexAMProperty prop; + HeapTuple tuple; + Form_pg_class rd_rel; + + if (!OidIsValid(amoid) && !OidIsValid(index_oid)) + PG_RETURN_NULL(); + + Assert(!OidIsValid(amoid) || !OidIsValid(index_oid)); + + name = text_to_cstring(DatumGetTextPP(propname)); + + prop = lookup_prop_name(name); + + if (OidIsValid(index_oid)) + { + tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(index_oid)); + if (!HeapTupleIsValid(tuple)) + PG_RETURN_NULL(); + rd_rel = (Form_pg_class) GETSTRUCT(tuple); + if (rd_rel->relkind != RELKIND_INDEX) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("relation %u is not an index", index_oid))); + amoid = rd_rel->relam; + natts = rd_rel->relnatts; + ReleaseSysCache(tuple); + } + + if (attno < 0 || attno > natts) + PG_RETURN_NULL(); + + /* + * From this point, attno == 0 for index-wide or AM-wide tests, or + * otherwise it's a valid column number in a valid index. + */ + + routine = GetIndexAmRoutineByAmId(amoid); + + /* + * If there's an AM property routine, give it first bite at the cherry. + * (It can return false to indicate that the generic answer is ok.) + */ + if (routine->amproperty + && routine->amproperty(index_oid, attno, prop, name, &res, &isnull)) + { + if (isnull) + PG_RETURN_NULL(); + PG_RETURN_BOOL(res); + } + + if (attno > 0) + { + /* these are allowed only at column level: */ + + switch (prop) + { + case AMPROP_ASC: + if (test_indoption(index_oid, attno, routine->amcanorder, + INDOPTION_DESC, 0, &res)) + PG_RETURN_BOOL(res); + PG_RETURN_NULL(); + + case AMPROP_DESC: + if (test_indoption(index_oid, attno, routine->amcanorder, + INDOPTION_DESC, INDOPTION_DESC, &res)) + PG_RETURN_BOOL(res); + PG_RETURN_NULL(); + + case AMPROP_NULLS_FIRST: + if (test_indoption(index_oid, attno, routine->amcanorder, + INDOPTION_NULLS_FIRST, INDOPTION_NULLS_FIRST, &res)) + PG_RETURN_BOOL(res); + PG_RETURN_NULL(); + + case AMPROP_NULLS_LAST: + if (test_indoption(index_oid, attno, routine->amcanorder, + INDOPTION_NULLS_FIRST, 0, &res)) + PG_RETURN_BOOL(res); + PG_RETURN_NULL(); + + case AMPROP_ORDERABLE: + PG_RETURN_BOOL(routine->amcanorder); + + case AMPROP_DISTANCE_ORDERABLE: + /* + * The conditions for whether a column is distance-orderable + * are really up to the AM (at time of writing, only GiST + * supports it at all). The planner has its own idea based on + * whether it finds an operator with a sortfamily, but getting + * there from just the index column type seems like a lot of + * work. So instead we expect the AM to work this out itself + * above, and the generic result is to return false if the AM + * says it never supports this, and null otherwise (meaning we + * don't know). + */ + if (!routine->amcanorderbyop && attno > 0) + PG_RETURN_BOOL(false); + PG_RETURN_NULL(); + + case AMPROP_SEARCH_ARRAY: + PG_RETURN_BOOL(routine->amsearcharray); + + case AMPROP_SEARCH_NULLS: + PG_RETURN_BOOL(routine->amsearchnulls); + + case AMPROP_RETURNABLE: + if (!routine->amcanreturn) + PG_RETURN_BOOL(false); + + /* + * Ideally, the AM should handle this itself without opening + * the rel in its own amproperty function. But this is the + * generic fallback if it does not. + */ + + { + Relation indexrel = index_open(index_oid, AccessShareLock); + + res = index_can_return(indexrel, attno); + + index_close(indexrel, NoLock); + } + + PG_RETURN_BOOL(res); + + default: + PG_RETURN_NULL(); + } + } + + if (!OidIsValid(index_oid)) + { + /* These are allowed AM-wide only: */ + + switch (prop) + { + case AMPROP_CAN_ORDER: + PG_RETURN_BOOL(routine->amcanorder); + + case AMPROP_CAN_UNIQUE: + PG_RETURN_BOOL(routine->amcanunique); + + case AMPROP_CAN_MULTI_COL: + PG_RETURN_BOOL(routine->amcanmulticol); + + case AMPROP_CAN_EXCLUDE: + PG_RETURN_BOOL(routine->amgettuple ? true : false); + + default: + PG_RETURN_NULL(); + } + } + else + { + /* These are allowed for a specified index only: */ + + switch (prop) + { + case AMPROP_BACKWARD_SCAN: + PG_RETURN_BOOL(routine->amcanbackward); + + case AMPROP_CLUSTERABLE: + PG_RETURN_BOOL(routine->amclusterable); + + case AMPROP_INDEX_SCAN: + PG_RETURN_BOOL(routine->amgettuple ? true : false); + + case AMPROP_BITMAP_SCAN: + PG_RETURN_BOOL(routine->amgetbitmap ? true : false); + + default: + PG_RETURN_NULL(); + } + } + + /* NOTREACHED */ + PG_RETURN_NULL(); +} + +/* + * Test property of an AM specified by the AM Oid. + */ +Datum +pg_indexam_has_property(PG_FUNCTION_ARGS) +{ + Oid amoid = PG_GETARG_OID(0); + Datum propname = PG_GETARG_DATUM(1); + + return indexam_property(fcinfo, propname, amoid, InvalidOid, 0); +} + +/* + * Test property of the AM for an index specified by relation Oid. + */ +Datum +pg_index_has_property(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + Datum propname = PG_GETARG_DATUM(1); + + return indexam_property(fcinfo, propname, InvalidOid, relid, 0); +} + +/* + * Test for index column properties + */ +Datum +pg_index_column_has_property(PG_FUNCTION_ARGS) +{ + Oid relid = PG_GETARG_OID(0); + int32 attno = PG_GETARG_INT32(1); + Datum propname = PG_GETARG_DATUM(2); + + if (attno == 0) + PG_RETURN_NULL(); + + return indexam_property(fcinfo, propname, InvalidOid, relid, attno); +} diff --git a/src/include/access/amapi.h b/src/include/access/amapi.h index 35f1061..82b101c 100644 --- a/src/include/access/amapi.h +++ b/src/include/access/amapi.h @@ -25,6 +25,32 @@ struct IndexPath; /* Likewise, this file shouldn't depend on execnodes.h. */ struct IndexInfo; +typedef enum IndexAMProperty { + + AMPROP_UNKNOWN = 0, + + AMPROP_ASC = 1, + AMPROP_DESC, + AMPROP_NULLS_FIRST, + AMPROP_NULLS_LAST, + AMPROP_ORDERABLE, + AMPROP_DISTANCE_ORDERABLE, + AMPROP_RETURNABLE, + AMPROP_SEARCH_ARRAY, + AMPROP_SEARCH_NULLS, + + AMPROP_BACKWARD_SCAN = 31, + AMPROP_CLUSTERABLE, + AMPROP_INDEX_SCAN, + AMPROP_BITMAP_SCAN, + + AMPROP_CAN_ORDER = 61, + AMPROP_CAN_UNIQUE, + AMPROP_CAN_MULTI_COL, + AMPROP_CAN_EXCLUDE + +} IndexAMProperty; + /* * Callback function signatures --- see indexam.sgml for more info. @@ -104,6 +130,11 @@ typedef void (*ammarkpos_function) (IndexScanDesc scan); /* restore marked scan position */ typedef void (*amrestrpos_function) (IndexScanDesc scan); +/* property */ +typedef bool (*amproperty_function) (Oid index_oid, int attno, + IndexAMProperty prop, const char *propname, + bool *res, bool *isnull); + /* * API struct for an index AM. Note this must be stored in a single palloc'd @@ -162,6 +193,7 @@ typedef struct IndexAmRoutine amendscan_function amendscan; ammarkpos_function ammarkpos; /* can be NULL */ amrestrpos_function amrestrpos; /* can be NULL */ + amproperty_function amproperty; /* can be NULL */ } IndexAmRoutine; diff --git a/src/include/access/gist_private.h b/src/include/access/gist_private.h index 23e0fe3..40bd7f0 100644 --- a/src/include/access/gist_private.h +++ b/src/include/access/gist_private.h @@ -435,6 +435,9 @@ extern bool gistinsert(Relation r, Datum *values, bool *isnull, extern MemoryContext createTempGistContext(void); extern GISTSTATE *initGISTstate(Relation index); extern void freeGISTstate(GISTSTATE *giststate); +extern bool gistproperty(Oid index_oid, int attno, + IndexAMProperty prop, const char *propname, + bool *res, bool *isnull); extern void gistdoinsert(Relation r, IndexTuple itup, Size freespace, diff --git a/src/include/access/nbtree.h b/src/include/access/nbtree.h index 19437d2..203dd73 100644 --- a/src/include/access/nbtree.h +++ b/src/include/access/nbtree.h @@ -676,7 +676,9 @@ extern IndexBulkDeleteResult *btbulkdelete(IndexVacuumInfo *info, extern IndexBulkDeleteResult *btvacuumcleanup(IndexVacuumInfo *info, IndexBulkDeleteResult *stats); extern bool btcanreturn(Relation index, int attno); - +extern bool btproperty(Oid index_oid, int attno, + IndexAMProperty prop, const char *propname, + bool *res, bool *isnull); /* * prototypes for functions in nbtinsert.c */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 270dd21..7f2fed0 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -564,6 +564,13 @@ DESCR("spgist index access method handler"); DATA(insert OID = 335 ( brinhandler PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 325 "2281" _null_ _null_ _null_ _null_ _null_ brinhandler _null_ _null_ _null_ )); DESCR("brin index access method handler"); +DATA(insert OID = 336 ( pg_indexam_has_property PGNSP PGUID 12 1 0 0 0 f f f f t f s s 2 0 16 "26 25" _null_ _null_ _null_ _null_ _null_ pg_indexam_has_property _null_ _null_ _null_ )); +DESCR("test property of an index access method"); +DATA(insert OID = 337 ( pg_index_has_property PGNSP PGUID 12 1 0 0 0 f f f f t f s s 2 0 16 "26 25" _null_ _null_ _null_ _null_ _null_ pg_index_has_property _null_ _null_ _null_ )); +DESCR("test property of an index"); +DATA(insert OID = 4032 ( pg_index_column_has_property PGNSP PGUID 12 1 0 0 0 f f f f t f s s 3 0 16 "26 23 25" _null_ _null_ _null_ _null_ _null_ pg_index_column_has_property _null_ _null_ _null_ )); +DESCR("test boolean property of index column"); + DATA(insert OID = 338 ( amvalidate PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 16 "26" _null_ _null_ _null_ _null_ _null_ amvalidate _null_ _null_ _null_ )); DESCR("validate an operator class"); DATA(insert OID = 3952 ( brin_summarize_new_values PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 23 "2205" _null_ _null_ _null_ _null_ _null_ brin_summarize_new_values _null_ _null_ _null_ )); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 8cebc86..a91be98 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -108,6 +108,11 @@ extern Datum pg_has_role_id_id(PG_FUNCTION_ARGS); extern Datum pg_has_role_name(PG_FUNCTION_ARGS); extern Datum pg_has_role_id(PG_FUNCTION_ARGS); +/* amutils.c */ +extern Datum pg_indexam_has_property(PG_FUNCTION_ARGS); +extern Datum pg_index_has_property(PG_FUNCTION_ARGS); +extern Datum pg_index_column_has_property(PG_FUNCTION_ARGS); + /* bool.c */ extern Datum boolin(PG_FUNCTION_ARGS); extern Datum boolout(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out new file mode 100644 index 0000000..8a4fd98 --- /dev/null +++ b/src/test/regress/expected/amutils.out @@ -0,0 +1,237 @@ +-- +-- pg_index_column_has_property +-- +CREATE INDEX onek_props1 ON onek (unique1 desc, unique2 asc, ten nulls first, hundred nulls last); +select i, prop, pg_index_column_has_property(o, i, prop) + from (values ('onek_props1'::regclass)) v1(o), + (values (1,'orderable'),(2,'asc'),(3,'desc'), + (4,'nulls_first'),(5,'nulls_last')) v2(idx,prop), + generate_series(1,4) i + order by i, idx; + i | prop | pg_index_column_has_property +---+-------------+------------------------------ + 1 | orderable | t + 1 | asc | f + 1 | desc | t + 1 | nulls_first | t + 1 | nulls_last | f + 2 | orderable | t + 2 | asc | t + 2 | desc | f + 2 | nulls_first | f + 2 | nulls_last | t + 3 | orderable | t + 3 | asc | t + 3 | desc | f + 3 | nulls_first | t + 3 | nulls_last | f + 4 | orderable | t + 4 | asc | t + 4 | desc | f + 4 | nulls_first | f + 4 | nulls_last | t +(20 rows) + +select prop, pg_index_column_has_property(o, 1, prop) + from (values ('gcircleind'::regclass)) v1(o), + (values (1,'orderable'),(2,'asc'),(3,'desc'), + (4,'nulls_first'),(5,'nulls_last'), + (6,'distance_orderable')) v2(idx,prop) + order by idx; + prop | pg_index_column_has_property +--------------------+------------------------------ + orderable | f + asc | f + desc | f + nulls_first | f + nulls_last | f + distance_orderable | t +(6 rows) + +DROP INDEX onek_props1; +-- +-- index AM capabilites +-- +select cap, + pg_indexam_has_property(a.oid, cap) as "AM", + pg_index_has_property('onek_hundred'::regclass, cap) as "Index", + pg_index_column_has_property('onek_hundred'::regclass, 1, cap) as "Column" + from pg_am a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + where a.amname='btree' + order by ord; + cap | AM | Index | Column +--------------------+----+-------+-------- + asc | | | t + desc | | | f + nulls_first | | | f + nulls_last | | | t + orderable | | | t + distance_orderable | | | f + returnable | | | t + search_array | | | t + search_nulls | | | t + backward_scan | | t | + clusterable | | t | + index_scan | | t | + bitmap_scan | | t | + can_order | t | | + can_unique | t | | + can_multi_col | t | | + can_exclude | t | | +(17 rows) + +select cap, + pg_indexam_has_property(a.oid, cap) as "AM", + pg_index_has_property('gcircleind'::regclass, cap) as "Index", + pg_index_column_has_property('gcircleind'::regclass, 1, cap) as "Column" + from pg_am a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + where a.amname='gist' + order by ord; + cap | AM | Index | Column +--------------------+----+-------+-------- + asc | | | f + desc | | | f + nulls_first | | | f + nulls_last | | | f + orderable | | | f + distance_orderable | | | t + returnable | | | f + search_array | | | f + search_nulls | | | t + backward_scan | | f | + clusterable | | t | + index_scan | | t | + bitmap_scan | | t | + can_order | f | | + can_unique | f | | + can_multi_col | t | | + can_exclude | t | | +(17 rows) + +select cap, + pg_index_column_has_property('onek_hundred'::regclass, 1, cap) as btree, + pg_index_column_has_property('hash_i4_index'::regclass, 1, cap) as hash, + pg_index_column_has_property('gcircleind'::regclass, 1, cap) as gist, + pg_index_column_has_property('sp_radix_ind'::regclass, 1, cap) as spgist, + pg_index_column_has_property('botharrayidx'::regclass, 1, cap) as gin + from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + order by ord; + cap | btree | hash | gist | spgist | gin +--------------------+-------+------+------+--------+----- + asc | t | f | f | f | f + desc | f | f | f | f | f + nulls_first | f | f | f | f | f + nulls_last | t | f | f | f | f + orderable | t | f | f | f | f + distance_orderable | f | f | t | f | f + returnable | t | f | f | t | f + search_array | t | f | f | f | f + search_nulls | t | f | t | t | f + backward_scan | | | | | + clusterable | | | | | + index_scan | | | | | + bitmap_scan | | | | | + can_order | | | | | + can_unique | | | | | + can_multi_col | | | | | + can_exclude | | | | | +(17 rows) + +select cap, + pg_index_has_property('onek_hundred'::regclass, cap) as btree, + pg_index_has_property('hash_i4_index'::regclass, cap) as hash, + pg_index_has_property('gcircleind'::regclass, cap) as gist, + pg_index_has_property('sp_radix_ind'::regclass, cap) as spgist, + pg_index_has_property('botharrayidx'::regclass, cap) as gin + from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + order by ord; + cap | btree | hash | gist | spgist | gin +--------------------+-------+------+------+--------+----- + asc | | | | | + desc | | | | | + nulls_first | | | | | + nulls_last | | | | | + orderable | | | | | + distance_orderable | | | | | + returnable | | | | | + search_array | | | | | + search_nulls | | | | | + backward_scan | t | t | f | f | f + clusterable | t | f | t | f | f + index_scan | t | t | t | t | f + bitmap_scan | t | t | t | t | t + can_order | | | | | + can_unique | | | | | + can_multi_col | | | | | + can_exclude | | | | | +(17 rows) + +select cap, + pg_indexam_has_property(bt_oid, cap) as btree, + pg_indexam_has_property(hash_oid, cap) as hash, + pg_indexam_has_property(gist_oid, cap) as gist, + pg_indexam_has_property(spgist_oid, cap) as spgist, + pg_indexam_has_property(gin_oid, cap) as gin, + pg_indexam_has_property(brin_oid, cap) as brin + from (select max(oid) filter (where amname='btree') as bt_oid, + max(oid) filter (where amname='hash') as hash_oid, + max(oid) filter (where amname='gist') as gist_oid, + max(oid) filter (where amname='spgist') as spgist_oid, + max(oid) filter (where amname='gin') as gin_oid, + max(oid) filter (where amname='brin') as brin_oid + from pg_am) a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + order by ord; + cap | btree | hash | gist | spgist | gin | brin +--------------------+-------+------+------+--------+-----+------ + asc | | | | | | + desc | | | | | | + nulls_first | | | | | | + nulls_last | | | | | | + orderable | | | | | | + distance_orderable | | | | | | + returnable | | | | | | + search_array | | | | | | + search_nulls | | | | | | + backward_scan | | | | | | + clusterable | | | | | | + index_scan | | | | | | + bitmap_scan | | | | | | + can_order | t | f | f | f | f | f + can_unique | t | f | f | f | f | f + can_multi_col | t | f | t | f | t | t + can_exclude | t | t | t | t | f | f +(17 rows) + diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 4ebad04..3815182 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -92,7 +92,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview test: alter_generic alter_operator misc psql async dbsize misc_functions # rules cannot run concurrently with any test that creates a view -test: rules psql_crosstab select_parallel +test: rules psql_crosstab select_parallel amutils # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 5c7038d..8958d8c 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -126,6 +126,7 @@ test: misc_functions test: rules test: psql_crosstab test: select_parallel +test: amutils test: select_views test: portals_p2 test: foreign_key diff --git a/src/test/regress/sql/amutils.sql b/src/test/regress/sql/amutils.sql new file mode 100644 index 0000000..02bca0c --- /dev/null +++ b/src/test/regress/sql/amutils.sql @@ -0,0 +1,106 @@ +-- +-- pg_index_column_has_property +-- +CREATE INDEX onek_props1 ON onek (unique1 desc, unique2 asc, ten nulls first, hundred nulls last); + +select i, prop, pg_index_column_has_property(o, i, prop) + from (values ('onek_props1'::regclass)) v1(o), + (values (1,'orderable'),(2,'asc'),(3,'desc'), + (4,'nulls_first'),(5,'nulls_last')) v2(idx,prop), + generate_series(1,4) i + order by i, idx; + +select prop, pg_index_column_has_property(o, 1, prop) + from (values ('gcircleind'::regclass)) v1(o), + (values (1,'orderable'),(2,'asc'),(3,'desc'), + (4,'nulls_first'),(5,'nulls_last'), + (6,'distance_orderable')) v2(idx,prop) + order by idx; + +DROP INDEX onek_props1; + +-- +-- index AM capabilites +-- +select cap, + pg_indexam_has_property(a.oid, cap) as "AM", + pg_index_has_property('onek_hundred'::regclass, cap) as "Index", + pg_index_column_has_property('onek_hundred'::regclass, 1, cap) as "Column" + from pg_am a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + where a.amname='btree' + order by ord; + +select cap, + pg_indexam_has_property(a.oid, cap) as "AM", + pg_index_has_property('gcircleind'::regclass, cap) as "Index", + pg_index_column_has_property('gcircleind'::regclass, 1, cap) as "Column" + from pg_am a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + where a.amname='gist' + order by ord; + +select cap, + pg_index_column_has_property('onek_hundred'::regclass, 1, cap) as btree, + pg_index_column_has_property('hash_i4_index'::regclass, 1, cap) as hash, + pg_index_column_has_property('gcircleind'::regclass, 1, cap) as gist, + pg_index_column_has_property('sp_radix_ind'::regclass, 1, cap) as spgist, + pg_index_column_has_property('botharrayidx'::regclass, 1, cap) as gin + from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + order by ord; + +select cap, + pg_index_has_property('onek_hundred'::regclass, cap) as btree, + pg_index_has_property('hash_i4_index'::regclass, cap) as hash, + pg_index_has_property('gcircleind'::regclass, cap) as gist, + pg_index_has_property('sp_radix_ind'::regclass, cap) as spgist, + pg_index_has_property('botharrayidx'::regclass, cap) as gin + from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + order by ord; + +select cap, + pg_indexam_has_property(bt_oid, cap) as btree, + pg_indexam_has_property(hash_oid, cap) as hash, + pg_indexam_has_property(gist_oid, cap) as gist, + pg_indexam_has_property(spgist_oid, cap) as spgist, + pg_indexam_has_property(gin_oid, cap) as gin, + pg_indexam_has_property(brin_oid, cap) as brin + from (select max(oid) filter (where amname='btree') as bt_oid, + max(oid) filter (where amname='hash') as hash_oid, + max(oid) filter (where amname='gist') as gist_oid, + max(oid) filter (where amname='spgist') as spgist_oid, + max(oid) filter (where amname='gin') as gin_oid, + max(oid) filter (where amname='brin') as brin_oid + from pg_am) a, + unnest(array['asc', 'desc', 'nulls_first', 'nulls_last', + 'orderable', 'distance_orderable', 'returnable', + 'search_array', 'search_nulls', 'backward_scan', + 'clusterable', 'index_scan', 'bitmap_scan', + 'can_order', 'can_unique', 'can_multi_col', + 'can_exclude']) + with ordinality as u(cap,ord) + order by ord;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers