Support index INCLUDE in the AM properties interface.

This rectifies an oversight in commit 8224de4f4, by adding a new
property 'can_include' for pg_indexam_has_property, and adjusting the
results of pg_index_column_has_property to give more appropriate
results for INCLUDEd columns.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3dbfa1dec342293b8760f6bb62e0c9bce32a94ef..3bf21477adb3dc0d46abce0dd760ffa06e9e0a68 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17555,6 +17555,12 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
       <entry>Does the access method support exclusion constraints?
       </entry>
      </row>
+     <row>
+      <entry><literal>can_include</literal></entry>
+      <entry>Does the access method support the <literal>INCLUDE</literal>
+        clause of <literal>CREATE INDEX</literal>?
+      </entry>
+     </row>
     </tbody>
    </tgroup>
   </table>

diff --git a/src/backend/utils/adt/amutils.c b/src/backend/utils/adt/amutils.c
index 0f7ceb62eb56458645282c30bfa23f8540227c8f..0f8ad4ef0fa2469124535594e1448eeea9a83404 100644 (file)
--- a/src/backend/utils/adt/amutils.c
+++ b/src/backend/utils/adt/amutils.c
@@ -80,7 +80,10 @@ static const struct am_propname am_propnames[] =
    },
    {
        "can_exclude", AMPROP_CAN_EXCLUDE
-   }
+   },
+   {
+       "can_include", AMPROP_CAN_INCLUDE
+   },
 };
 
 static IndexAMProperty
@@ -101,7 +104,8 @@ lookup_prop_name(const char *name)
 /*
  * Common code for properties that are just bit tests of indoptions.
  *
- * relid/attno: identify the index column to test the indoptions of.
+ * tuple: the pg_index heaptuple
+ * attno: identify the index column to test the indoptions of.
  * guard: if false, a boolean false result is forced (saves code in caller).
  * iopt_mask: mask for interesting indoption bit.
  * iopt_expect: value for a "true" result (should be 0 or iopt_mask).
@@ -110,12 +114,10 @@ lookup_prop_name(const char *name)
  * otherwise sets *res to the boolean value to return.
  */
 static bool
-test_indoption(Oid relid, int attno, bool guard,
+test_indoption(HeapTuple tuple, int attno, bool guard,
               int16 iopt_mask, int16 iopt_expect,
               bool *res)
 {
-   HeapTuple   tuple;
-   Form_pg_index rd_index PG_USED_FOR_ASSERTS_ONLY;
    Datum       datum;
    bool        isnull;
    int2vector *indoption;
@@ -127,14 +129,6 @@ test_indoption(Oid relid, int attno, bool guard,
        return true;
    }
 
-   tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(relid));
-   if (!HeapTupleIsValid(tuple))
-       return false;
-   rd_index = (Form_pg_index) GETSTRUCT(tuple);
-
-   Assert(relid == rd_index->indexrelid);
-   Assert(attno > 0 && attno <= rd_index->indnatts);
-
    datum = SysCacheGetAttr(INDEXRELID, tuple,
                            Anum_pg_index_indoption, &isnull);
    Assert(!isnull);
@@ -144,8 +138,6 @@ test_indoption(Oid relid, int attno, bool guard,
 
    *res = (indoption_val & iopt_mask) == iopt_expect;
 
-   ReleaseSysCache(tuple);
-
    return true;
 }
 
@@ -195,9 +187,10 @@ indexam_property(FunctionCallInfo fcinfo,
    }
 
    /*
-    * At this point, either index_oid == InvalidOid or it's a valid index
-    * OID.  Also, after this test, either attno == 0 for index-wide or
-    * AM-wide tests, or it's a valid column number in a valid index.
+    * At this point, either index_oid == InvalidOid or it's a valid index OID.
+    * Also, after this test and the one below, either attno == 0 for
+    * index-wide or AM-wide tests, or it's a valid column number in a valid
+    * index.
     */
    if (attno < 0 || attno > natts)
        PG_RETURN_NULL();
@@ -224,80 +217,138 @@ indexam_property(FunctionCallInfo fcinfo,
 
    if (attno > 0)
    {
-       /* Handle column-level properties */
+       HeapTuple   tuple;
+       Form_pg_index rd_index;
+       bool        iskey = true;
+
+       /*
+        * Handle column-level properties. Many of these need the pg_index row
+        * (which we also need to use to check for nonkey atts) so we fetch
+        * that first.
+        */
+       tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(index_oid));
+       if (!HeapTupleIsValid(tuple))
+           PG_RETURN_NULL();
+       rd_index = (Form_pg_index) GETSTRUCT(tuple);
+
+       Assert(index_oid == rd_index->indexrelid);
+       Assert(attno > 0 && attno <= rd_index->indnatts);
+
+       isnull = true;
+
+       /*
+        * If amcaninclude, we might be looking at an attno for a nonkey
+        * column, for which we (generically) assume that most properties are
+        * null.
+        */
+       if (routine->amcaninclude
+           && attno > rd_index->indnkeyatts)
+           iskey = false;
+
        switch (prop)
        {
            case AMPROP_ASC:
-               if (test_indoption(index_oid, attno, routine->amcanorder,
+               if (iskey &&
+                   test_indoption(tuple, attno, routine->amcanorder,
                                   INDOPTION_DESC, 0, &res))
-                   PG_RETURN_BOOL(res);
-               PG_RETURN_NULL();
+                   isnull = false;
+               break;
 
            case AMPROP_DESC:
-               if (test_indoption(index_oid, attno, routine->amcanorder,
+               if (iskey &&
+                   test_indoption(tuple, attno, routine->amcanorder,
                                   INDOPTION_DESC, INDOPTION_DESC, &res))
-                   PG_RETURN_BOOL(res);
-               PG_RETURN_NULL();
+                   isnull = false;
+               break;
 
            case AMPROP_NULLS_FIRST:
-               if (test_indoption(index_oid, attno, routine->amcanorder,
+               if (iskey &&
+                   test_indoption(tuple, attno, routine->amcanorder,
                                   INDOPTION_NULLS_FIRST, INDOPTION_NULLS_FIRST, &res))
-                   PG_RETURN_BOOL(res);
-               PG_RETURN_NULL();
+                   isnull = false;
+               break;
 
            case AMPROP_NULLS_LAST:
-               if (test_indoption(index_oid, attno, routine->amcanorder,
+               if (iskey &&
+                   test_indoption(tuple, attno, routine->amcanorder,
                                   INDOPTION_NULLS_FIRST, 0, &res))
-                   PG_RETURN_BOOL(res);
-               PG_RETURN_NULL();
+                   isnull = false;
+               break;
 
            case AMPROP_ORDERABLE:
-               PG_RETURN_BOOL(routine->amcanorder);
+               /*
+                * generic assumption is that nonkey columns are not orderable
+                */
+               res = iskey ? routine->amcanorder : false;
+               isnull = false;
+               break;
 
            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
+                * supports it at all). The planner has its own idea based on
                 * whether it finds an operator with amoppurpose 'o', but
                 * getting there from just the index column type seems like a
-                * lot of work.  So instead we expect the AM to handle this in
-                * its amproperty routine.  The generic result is to return
-                * false if the AM says it never supports this, and null
-                * otherwise (meaning we don't know).
+                * lot of work. So instead we expect the AM to handle this in
+                * its amproperty routine. The generic result is to return
+                * false if the AM says it never supports this, or if this is a
+                * nonkey column, and null otherwise (meaning we don't know).
                 */
-               if (!routine->amcanorderbyop)
-                   PG_RETURN_BOOL(false);
-               PG_RETURN_NULL();
+               if (!iskey || !routine->amcanorderbyop)
+               {
+                   res = false;
+                   isnull = false;
+               }
+               break;
 
            case AMPROP_RETURNABLE:
-               if (!routine->amcanreturn)
-                   PG_RETURN_BOOL(false);
 
-               /*
-                * If possible, the AM should handle this test in its
-                * amproperty function without opening the rel.  But this is
-                * the generic fallback if it does not.
-                */
+               /* note that we ignore iskey for this property */
+
+               isnull = false;
+               res = false;
+
+               if (routine->amcanreturn)
                {
+                   /*
+                    * If possible, the AM should handle this test in its
+                    * amproperty function without opening the rel. 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, AccessShareLock);
                }
-
-               PG_RETURN_BOOL(res);
+               break;
 
            case AMPROP_SEARCH_ARRAY:
-               PG_RETURN_BOOL(routine->amsearcharray);
+               if (iskey)
+               {
+                   res = routine->amsearcharray;
+                   isnull = false;
+               }
+               break;
 
            case AMPROP_SEARCH_NULLS:
-               PG_RETURN_BOOL(routine->amsearchnulls);
+               if (iskey)
+               {
+                   res = routine->amsearchnulls;
+                   isnull = false;
+               }
+               break;
 
            default:
-               PG_RETURN_NULL();
+               break;
        }
+
+       ReleaseSysCache(tuple);
+
+       if (!isnull)
+           PG_RETURN_BOOL(res);
+       PG_RETURN_NULL();
    }
 
    if (OidIsValid(index_oid))
@@ -344,6 +395,9 @@ indexam_property(FunctionCallInfo fcinfo,
        case AMPROP_CAN_EXCLUDE:
            PG_RETURN_BOOL(routine->amgettuple ? true : false);
 
+       case AMPROP_CAN_INCLUDE:
+           PG_RETURN_BOOL(routine->amcaninclude);
+
        default:
            PG_RETURN_NULL();
    }

diff --git a/src/include/access/amapi.h b/src/include/access/amapi.h
index d16fa6823b6b837fa07ff999c1b388dafa028e3f..14526a6bb2c7c84500678194eb3c5b2396bbb8af 100644 (file)
--- a/src/include/access/amapi.h
+++ b/src/include/access/amapi.h
@@ -50,7 +50,8 @@ typedef enum IndexAMProperty
    AMPROP_CAN_ORDER,           /* AM properties */
    AMPROP_CAN_UNIQUE,
    AMPROP_CAN_MULTI_COL,
-   AMPROP_CAN_EXCLUDE
+   AMPROP_CAN_EXCLUDE,
+   AMPROP_CAN_INCLUDE
 } IndexAMProperty;
 
 
@@ -196,6 +197,12 @@ typedef struct IndexAmRoutine
    /* type of data stored in index, or InvalidOid if variable */
    Oid         amkeytype;
 
+   /*
+    * If you add new properties to either the above or the below lists, then
+    * they should also (usually) be exposed via the property API (see
+    * IndexAMProperty at the top of the file, and utils/adt/amutils.c).
+    */
+
    /* interface functions */
    ambuild_function ambuild;
    ambuildempty_function ambuildempty;

diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out
index 74f7c9f1fd7be2fbfa9f491670328e5ad2f114f2..24cd3c5e2e4a4936b0f59bbdc07806a43008fe45 100644 (file)
--- a/src/test/regress/expected/amutils.out
+++ b/src/test/regress/expected/amutils.out
@@ -12,7 +12,7 @@ select prop,
                     'clusterable', 'index_scan', 'bitmap_scan',
                     'backward_scan',
                     'can_order', 'can_unique', 'can_multi_col',
-                    'can_exclude',
+                    'can_exclude', 'can_include',
                     'bogus']::text[])
          with ordinality as u(prop,ord)
  where a.amname = 'btree'
@@ -36,8 +36,9 @@ select prop,
  can_unique         | t  |       | 
  can_multi_col      | t  |       | 
  can_exclude        | t  |       | 
+ can_include        | t  |       | 
  bogus              |    |       | 
-(18 rows)
+(19 rows)
 
 select prop,
        pg_indexam_has_property(a.oid, prop) as "AM",
@@ -50,7 +51,7 @@ select prop,
                     'clusterable', 'index_scan', 'bitmap_scan',
                     'backward_scan',
                     'can_order', 'can_unique', 'can_multi_col',
-                    'can_exclude',
+                    'can_exclude', 'can_include',
                     'bogus']::text[])
          with ordinality as u(prop,ord)
  where a.amname = 'gist'
@@ -74,8 +75,9 @@ select prop,
  can_unique         | f  |       | 
  can_multi_col      | t  |       | 
  can_exclude        | t  |       | 
+ can_include        | f  |       | 
  bogus              |    |       | 
-(18 rows)
+(19 rows)
 
 select prop,
        pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as btree,
@@ -128,7 +130,7 @@ select prop,
 select amname, prop, pg_indexam_has_property(a.oid, prop) as p
   from pg_am a,
        unnest(array['can_order', 'can_unique', 'can_multi_col',
-                    'can_exclude', 'bogus']::text[])
+                    'can_exclude', 'can_include', 'bogus']::text[])
          with ordinality as u(prop,ord)
  where amtype = 'i'
  order by amname, ord;
@@ -138,33 +140,39 @@ select amname, prop, pg_indexam_has_property(a.oid, prop) as p
  brin   | can_unique    | f
  brin   | can_multi_col | t
  brin   | can_exclude   | f
+ brin   | can_include   | f
  brin   | bogus         | 
  btree  | can_order     | t
  btree  | can_unique    | t
  btree  | can_multi_col | t
  btree  | can_exclude   | t
+ btree  | can_include   | t
  btree  | bogus         | 
  gin    | can_order     | f
  gin    | can_unique    | f
  gin    | can_multi_col | t
  gin    | can_exclude   | f
+ gin    | can_include   | f
  gin    | bogus         | 
  gist   | can_order     | f
  gist   | can_unique    | f
  gist   | can_multi_col | t
  gist   | can_exclude   | t
+ gist   | can_include   | f
  gist   | bogus         | 
  hash   | can_order     | f
  hash   | can_unique    | f
  hash   | can_multi_col | f
  hash   | can_exclude   | t
+ hash   | can_include   | f
  hash   | bogus         | 
  spgist | can_order     | f
  spgist | can_unique    | f
  spgist | can_multi_col | f
  spgist | can_exclude   | t
+ spgist | can_include   | f
  spgist | bogus         | 
-(30 rows)
+(36 rows)
 
 --
 -- additional checks for pg_index_column_has_property
@@ -206,3 +214,40 @@ select col, prop, pg_index_column_has_property(o, col, prop)
    4 | bogus       | 
 (24 rows)
 
+CREATE INDEX foocover ON foo (f1) INCLUDE (f2,f3);
+select col, prop, pg_index_column_has_property(o, col, prop)
+  from (values ('foocover'::regclass)) v1(o),
+       (values (1,'orderable'),(2,'asc'),(3,'desc'),
+               (4,'nulls_first'),(5,'nulls_last'),
+               (6,'distance_orderable'),(7,'returnable'),
+               (8, 'bogus')) v2(idx,prop),
+       generate_series(1,3) col
+ order by col, idx;
+ col |        prop        | pg_index_column_has_property 
+-----+--------------------+------------------------------
+   1 | orderable          | t
+   1 | asc                | t
+   1 | desc               | f
+   1 | nulls_first        | f
+   1 | nulls_last         | t
+   1 | distance_orderable | f
+   1 | returnable         | t
+   1 | bogus              | 
+   2 | orderable          | f
+   2 | asc                | 
+   2 | desc               | 
+   2 | nulls_first        | 
+   2 | nulls_last         | 
+   2 | distance_orderable | f
+   2 | returnable         | t
+   2 | bogus              | 
+   3 | orderable          | f
+   3 | asc                | 
+   3 | desc               | 
+   3 | nulls_first        | 
+   3 | nulls_last         | 
+   3 | distance_orderable | f
+   3 | returnable         | t
+   3 | bogus              | 
+(24 rows)
+

diff --git a/src/test/regress/sql/amutils.sql b/src/test/regress/sql/amutils.sql
index cec1dcb53b648b04b9e87133afd682348ffdd5e2..8ca85ecf00696aa6a711885aaffd853368b2da14 100644 (file)
--- a/src/test/regress/sql/amutils.sql
+++ b/src/test/regress/sql/amutils.sql
@@ -13,7 +13,7 @@ select prop,
                     'clusterable', 'index_scan', 'bitmap_scan',
                     'backward_scan',
                     'can_order', 'can_unique', 'can_multi_col',
-                    'can_exclude',
+                    'can_exclude', 'can_include',
                     'bogus']::text[])
          with ordinality as u(prop,ord)
  where a.amname = 'btree'
@@ -30,7 +30,7 @@ select prop,
                     'clusterable', 'index_scan', 'bitmap_scan',
                     'backward_scan',
                     'can_order', 'can_unique', 'can_multi_col',
-                    'can_exclude',
+                    'can_exclude', 'can_include',
                     'bogus']::text[])
          with ordinality as u(prop,ord)
  where a.amname = 'gist'
@@ -66,7 +66,7 @@ select prop,
 select amname, prop, pg_indexam_has_property(a.oid, prop) as p
   from pg_am a,
        unnest(array['can_order', 'can_unique', 'can_multi_col',
-                    'can_exclude', 'bogus']::text[])
+                    'can_exclude', 'can_include', 'bogus']::text[])
          with ordinality as u(prop,ord)
  where amtype = 'i'
  order by amname, ord;
@@ -85,3 +85,14 @@ select col, prop, pg_index_column_has_property(o, col, prop)
                (6, 'bogus')) v2(idx,prop),
        generate_series(1,4) col
  order by col, idx;
+
+CREATE INDEX foocover ON foo (f1) INCLUDE (f2,f3);
+
+select col, prop, pg_index_column_has_property(o, col, prop)
+  from (values ('foocover'::regclass)) v1(o),
+       (values (1,'orderable'),(2,'asc'),(3,'desc'),
+               (4,'nulls_first'),(5,'nulls_last'),
+               (6,'distance_orderable'),(7,'returnable'),
+               (8, 'bogus')) v2(idx,prop),
+       generate_series(1,3) col
+ order by col, idx;