Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit c34df8a

Browse filesBrowse files
tglsfdctglsfdc
committed
Disallow creation of indexes on system columns (except for OID).
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
1 parent 99f2f3c commit c34df8a
Copy full SHA for c34df8a

File tree

Expand file treeCollapse file tree

3 files changed

+72
-0
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+72
-0
lines changed

‎src/backend/commands/indexcmds.c

Copy file name to clipboardExpand all lines: src/backend/commands/indexcmds.c
+37Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
#include "access/amapi.h"
1919
#include "access/htup_details.h"
2020
#include "access/reloptions.h"
21+
#include "access/sysattr.h"
2122
#include "access/xact.h"
2223
#include "catalog/catalog.h"
2324
#include "catalog/index.h"
@@ -37,6 +38,7 @@
3738
#include "nodes/nodeFuncs.h"
3839
#include "optimizer/clauses.h"
3940
#include "optimizer/planner.h"
41+
#include "optimizer/var.h"
4042
#include "parser/parse_coerce.h"
4143
#include "parser/parse_func.h"
4244
#include "parser/parse_oper.h"
@@ -574,6 +576,41 @@ DefineIndex(Oid relationId,
574576
if (stmt->primary)
575577
index_check_primary_key(rel, indexInfo, is_alter_table);
576578

579+
/*
580+
* We disallow indexes on system columns other than OID. They would not
581+
* necessarily get updated correctly, and they don't seem useful anyway.
582+
*/
583+
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
584+
{
585+
AttrNumber attno = indexInfo->ii_KeyAttrNumbers[i];
586+
587+
if (attno < 0 && attno != ObjectIdAttributeNumber)
588+
ereport(ERROR,
589+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
590+
errmsg("index creation on system columns is not supported")));
591+
}
592+
593+
/*
594+
* Also check for system columns used in expressions or predicates.
595+
*/
596+
if (indexInfo->ii_Expressions || indexInfo->ii_Predicate)
597+
{
598+
Bitmapset *indexattrs = NULL;
599+
600+
pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
601+
pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);
602+
603+
for (i = FirstLowInvalidHeapAttributeNumber + 1; i < 0; i++)
604+
{
605+
if (i != ObjectIdAttributeNumber &&
606+
bms_is_member(i - FirstLowInvalidHeapAttributeNumber,
607+
indexattrs))
608+
ereport(ERROR,
609+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
610+
errmsg("index creation on system columns is not supported")));
611+
}
612+
}
613+
577614
/*
578615
* Report index creation if appropriate (delay this till after most of the
579616
* error checks)

‎src/test/regress/expected/create_index.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/create_index.out
+16Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2541,6 +2541,22 @@ ERROR: cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey
25412541
HINT: You can drop constraint cwi_replaced_pkey on table cwi_test instead.
25422542
DROP TABLE cwi_test;
25432543
--
2544+
-- Check handling of indexes on system columns
2545+
--
2546+
CREATE TABLE oid_table (a INT) WITH OIDS;
2547+
-- An index on the OID column should be allowed
2548+
CREATE INDEX ON oid_table (oid);
2549+
-- Other system columns cannot be indexed
2550+
CREATE INDEX ON oid_table (ctid);
2551+
ERROR: index creation on system columns is not supported
2552+
-- nor used in expressions
2553+
CREATE INDEX ON oid_table ((ctid >= '(1000,0)'));
2554+
ERROR: index creation on system columns is not supported
2555+
-- nor used in predicates
2556+
CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)';
2557+
ERROR: index creation on system columns is not supported
2558+
DROP TABLE oid_table;
2559+
--
25442560
-- Tests for IS NULL/IS NOT NULL with b-tree indexes
25452561
--
25462562
SELECT unique1, unique2 INTO onek_with_null FROM onek;

‎src/test/regress/sql/create_index.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/create_index.sql
+19Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -824,6 +824,25 @@ DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it
824824

825825
DROP TABLE cwi_test;
826826

827+
--
828+
-- Check handling of indexes on system columns
829+
--
830+
CREATE TABLE oid_table (a INT) WITH OIDS;
831+
832+
-- An index on the OID column should be allowed
833+
CREATE INDEX ON oid_table (oid);
834+
835+
-- Other system columns cannot be indexed
836+
CREATE INDEX ON oid_table (ctid);
837+
838+
-- nor used in expressions
839+
CREATE INDEX ON oid_table ((ctid >= '(1000,0)'));
840+
841+
-- nor used in predicates
842+
CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)';
843+
844+
DROP TABLE oid_table;
845+
827846
--
828847
-- Tests for IS NULL/IS NOT NULL with b-tree indexes
829848
--

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.