Disallow creation of indexes on system columns (except for OID).

tglsfdc · tglsfdc · commit c34df8a003c3 · 2016-04-16T12:11:41.000-04:00
Although OID acts pretty much like user data, the other system columns do
not, so an index on one would likely misbehave.  And it's pretty hard to
see a use-case for one, anyway.  Let's just forbid the case rather than
worry about whether it should be supported.

David Rowley
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
@@ -18,6 +18,7 @@
 #include "access/amapi.h"
 #include "access/htup_details.h"
 #include "access/reloptions.h"
+#include "access/sysattr.h"
 #include "access/xact.h"
 #include "catalog/catalog.h"
 #include "catalog/index.h"
@@ -37,6 +38,7 @@
 #include "nodes/nodeFuncs.h"
 #include "optimizer/clauses.h"
 #include "optimizer/planner.h"
+#include "optimizer/var.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_func.h"
 #include "parser/parse_oper.h"
@@ -574,6 +576,41 @@ DefineIndex(Oid relationId,
 	if (stmt->primary)
 		index_check_primary_key(rel, indexInfo, is_alter_table);
 
+	/*
+	 * We disallow indexes on system columns other than OID.  They would not
+	 * necessarily get updated correctly, and they don't seem useful anyway.
+	 */
+	for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
+	{
+		AttrNumber	attno = indexInfo->ii_KeyAttrNumbers[i];
+
+		if (attno < 0 && attno != ObjectIdAttributeNumber)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			   errmsg("index creation on system columns is not supported")));
+	}
+
+	/*
+	 * Also check for system columns used in expressions or predicates.
+	 */
+	if (indexInfo->ii_Expressions || indexInfo->ii_Predicate)
+	{
+		Bitmapset  *indexattrs = NULL;
+
+		pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
+		pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);
+
+		for (i = FirstLowInvalidHeapAttributeNumber + 1; i < 0; i++)
+		{
+			if (i != ObjectIdAttributeNumber &&
+				bms_is_member(i - FirstLowInvalidHeapAttributeNumber,
+							  indexattrs))
+				ereport(ERROR,
+						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("index creation on system columns is not supported")));
+		}
+	}
+
 	/*
 	 * Report index creation if appropriate (delay this till after most of the
 	 * error checks)
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
@@ -2541,6 +2541,22 @@ ERROR:  cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey
 HINT:  You can drop constraint cwi_replaced_pkey on table cwi_test instead.
 DROP TABLE cwi_test;
 --
+-- Check handling of indexes on system columns
+--
+CREATE TABLE oid_table (a INT) WITH OIDS;
+-- An index on the OID column should be allowed
+CREATE INDEX ON oid_table (oid);
+-- Other system columns cannot be indexed
+CREATE INDEX ON oid_table (ctid);
+ERROR:  index creation on system columns is not supported
+-- nor used in expressions
+CREATE INDEX ON oid_table ((ctid >= '(1000,0)'));
+ERROR:  index creation on system columns is not supported
+-- nor used in predicates
+CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)';
+ERROR:  index creation on system columns is not supported
+DROP TABLE oid_table;
+--
 -- Tests for IS NULL/IS NOT NULL with b-tree indexes
 --
 SELECT unique1, unique2 INTO onek_with_null FROM onek;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
@@ -824,6 +824,25 @@ DROP INDEX cwi_replaced_pkey;	-- Should fail; a constraint depends on it
 
 DROP TABLE cwi_test;
 
+--
+-- Check handling of indexes on system columns
+--
+CREATE TABLE oid_table (a INT) WITH OIDS;
+
+-- An index on the OID column should be allowed
+CREATE INDEX ON oid_table (oid);
+
+-- Other system columns cannot be indexed
+CREATE INDEX ON oid_table (ctid);
+
+-- nor used in expressions
+CREATE INDEX ON oid_table ((ctid >= '(1000,0)'));
+
+-- nor used in predicates
+CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)';
+
+DROP TABLE oid_table;
+
 --
 -- Tests for IS NULL/IS NOT NULL with b-tree indexes
 --
