From ed0097e4f9e6b1227935e01fa67f12a238b66064 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Sat, 13 Aug 2016 18:31:14 -0400
Subject: Add SQL-accessible functions for inspecting index AM properties.
Per discussion, we should provide such functions to replace the lost
ability to discover AM properties by inspecting pg_am (cf commit
65c5fcd35). The added functionality is also meant to displace any code
that was looking directly at pg_index.indoption, since we'd rather not
believe that the bit meanings in that field are part of any client API
contract.
As future-proofing, define the SQL API to not assume that properties that
are currently AM-wide or index-wide will remain so unless they logically
must be; instead, expose them only when inquiring about a specific index
or even specific index column. Also provide the ability for an index
AM to override the behavior.
In passing, document pg_am.amtype, overlooked in commit 473b93287.
Andrew Gierth, with kibitzing by me and others
Discussion: <87mvl5on7n.fsf@news-spur.riddles.org.uk>
---
doc/src/sgml/catalogs.sgml | 29 ++++++--
doc/src/sgml/func.sgml | 165 +++++++++++++++++++++++++++++++++++++++++++++
doc/src/sgml/indexam.sgml | 50 ++++++++++++++
3 files changed, 240 insertions(+), 4 deletions(-)
(limited to 'doc/src')
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index ccb9b97a8cb..4e09e06aed7 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -529,10 +529,11 @@
- The catalog pg_am stores information about index
- access methods. There is one row for each index access method supported by
- the system. The requirements for index access methods are discussed in
- detail in .
+ The catalog pg_am stores information about
+ relation access methods. There is one row for each access method supported
+ by the system.
+ Currently, only indexes have access methods. The requirements for index
+ access methods are discussed in detail in .
@@ -573,10 +574,30 @@
+
+ amtype
+ char
+
+
+ Currently always i to indicate an index access
+ method; other values may be allowed in future
+
+
+
+
+ Before PostgreSQL 9.6, pg_am
+ contained many additional columns representing properties of index access
+ methods. That data is now only directly visible at the C code level.
+ However, pg_index_column_has_property() and related
+ functions have been added to allow SQL queries to inspect index access
+ method properties; see .
+
+
+
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 783033403a4..426e562b036 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16289,6 +16289,18 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
pg_get_viewdef
+
+ pg_index_column_has_property
+
+
+
+ pg_index_has_property
+
+
+
+ pg_indexam_has_property
+
+
pg_options_to_table
@@ -16476,6 +16488,21 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
materialized view; lines with fields are wrapped to specified
number of columns, pretty-printing is implied
+
+ pg_index_column_has_property(index_oid, column_no, prop_name)
+ boolean
+ test whether an index column has a specified property
+
+
+ pg_index_has_property(index_oid, prop_name)
+ boolean
+ test whether an index has a specified property
+
+
+ pg_indexam_has_property(am_oid, prop_name)
+ boolean
+ test whether an index access method has a specified property
+
pg_options_to_table(reloptions)
setof record
@@ -16619,6 +16646,144 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
its OID.
+
+ pg_index_column_has_property,
+ pg_index_has_property, and
+ pg_indexam_has_property return whether the
+ specified index column, index, or index access method possesses the named
+ property. NULL is returned if the property name is not
+ known or does not apply to the particular object, or if the OID or column
+ number does not identify a valid object. Refer to
+ for column properties,
+ for index properties, and
+ for access method properties.
+ (Note that extension access methods can define additional property names
+ for their indexes.)
+
+
+
+ Index Column Properties
+
+
+ NameDescription
+
+
+
+ asc
+ Does the column sort in ascending order on a forward scan?
+
+
+
+ desc
+ Does the column sort in descending order on a forward scan?
+
+
+
+ nulls_first
+ Does the column sort with nulls first on a forward scan?
+
+
+
+ nulls_last
+ Does the column sort with nulls last on a forward scan?
+
+
+
+ orderable
+ Does the column possess any defined sort ordering?
+
+
+
+ distance_orderable
+ Can the column be scanned in order by a distance
+ operator, for example ORDER BY col <-> constant ?
+
+
+
+ returnable
+ Can the column value be returned by an index-only scan?
+
+
+
+ search_array
+ Does the column natively support col = ANY(array)
+ searches?
+
+
+
+ search_nulls
+ Does the column support IS NULL and
+ IS NOT NULL searches?
+
+
+
+
+
+
+
+ Index Properties
+
+
+ NameDescription
+
+
+
+ clusterable
+ Can the index be used in a CLUSTER command?
+
+
+
+ index_scan
+ Does the index support plain (non-bitmap) scans?
+
+
+
+ bitmap_scan
+ Does the index support bitmap scans?
+
+
+
+ backward_scan
+ Can the index be scanned backwards?
+
+
+
+
+
+
+
+ Index Access Method Properties
+
+
+ NameDescription
+
+
+
+ can_order
+ Does the access method support ASC,
+ DESC and related keywords in
+ CREATE INDEX?
+
+
+
+ can_unique
+ Does the access method support unique indexes?
+
+
+
+ can_multi_col
+ Does the access method support indexes with multiple columns?
+
+
+
+ can_exclude
+ Does the access method support exclusion constraints?
+
+
+
+
+
+
pg_options_to_table returns the set of storage
option name/value pairs
diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml
index fa4842b73fa..b59cd0363a2 100644
--- a/doc/src/sgml/indexam.sgml
+++ b/doc/src/sgml/indexam.sgml
@@ -129,6 +129,7 @@ typedef struct IndexAmRoutine
amcanreturn_function amcanreturn; /* can be NULL */
amcostestimate_function amcostestimate;
amoptions_function amoptions;
+ amproperty_function amproperty; /* can be NULL */
amvalidate_function amvalidate;
ambeginscan_function ambeginscan;
amrescan_function amrescan;
@@ -407,6 +408,55 @@ amoptions (ArrayType *reloptions,
bool
+amproperty (Oid index_oid, int attno,
+ IndexAMProperty prop, const char *propname,
+ bool *res, bool *isnull);
+
+ The amproperty method allows index access methods to override
+ the default behavior of pg_index_column_has_property
+ and related functions.
+ If the access method does not have any special behavior for index property
+ inquiries, the amproperty field in
+ its IndexAmRoutine struct can be set to NULL.
+ Otherwise, the amproperty method will be called with
+ index_oid and attno both zero for
+ pg_indexam_has_property calls,
+ or with index_oid valid and attno zero for
+ pg_index_has_property calls,
+ or with index_oid valid and attno greater than
+ zero for pg_index_column_has_property calls.
+ prop is an enum value identifying the property being tested,
+ while propname is the original property name string.
+ If the core code does not recognize the property name
+ then prop is AMPROP_UNKNOWN.
+ Access methods can define custom property names by
+ checking propname for a match (use pg_strcasecmp
+ to match, for consistency with the core code); for names known to the core
+ code, it's better to inspect prop.
+ If the amproperty method returns true then
+ it has determined the property test result: it must set *res
+ to the boolean value to return, or set *isnull
+ to true to return a NULL. (Both of the referenced variables
+ are initialized to false before the call.)
+ If the amproperty method returns false then
+ the core code will proceed with its normal logic for determining the
+ property test result.
+
+
+
+ Access methods that support ordering operators should
+ implement AMPROP_DISTANCE_ORDERABLE property testing, as the
+ core code does not know how to do that and will return NULL. It may
+ also be advantageous to implement AMPROP_RETURNABLE testing,
+ if that can be done more cheaply than by opening the index and calling
+ amcanreturn, which is the core code's default behavior.
+ The default behavior should be satisfactory for all other standard
+ properties.
+
+
+
+
+bool
amvalidate (Oid opclassoid);
Validate the catalog entries for the specified operator class, so far as
--
cgit v1.2.3