Skip to content

Commit f23efc3

Browse files
vbwagnervbwagner
committed
Merge branch 'PGPRO-291_updates' into PGPRO9_6
2 parents 9e3ae05 + 931d3bd commit f23efc3

File tree

18 files changed

+150
-122
lines changed

18 files changed

+150
-122
lines changed

‎doc/src/sgml/indexam.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -110,7 +110,7 @@ typedef struct IndexAmRoutine
110110
bool amclusterable;
111111
/* does AM handle predicate locks? */
112112
bool ampredlocks;
113-
/* does AM support columns included with clause INCLUDING? */
113+
/* does AM support columns included with clause INCLUDE? */
114114
bool amcaninclude;
115115
/* type of data stored in index, or InvalidOid if variable */
116116
Oid amkeytype;
@@ -906,7 +906,7 @@ amrestrpos (IndexScanDesc scan);
906906
multiple entries with identical keys. An access method that supports this
907907
feature sets <structfield>amcanunique</> true.
908908
(At present, only b-tree supports it.) Columns which are present in the
909-
<literal>INCLUDING</> clause are not used to enforce uniqueness.
909+
<literal>INCLUDE</> clause are not used to enforce uniqueness.
910910
</para>
911911

912912
<para>

‎doc/src/sgml/indices.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -652,7 +652,7 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
652652
or the uniqueness of the combined values of more than one column.
653653
<synopsis>
654654
CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>)
655-
<optional>INCLUDING (<replaceable>column</replaceable> <optional>, ...</optional>)</optional>;
655+
<optional>INCLUDE (<replaceable>column</replaceable> <optional>, ...</optional>)</optional>;
656656
</synopsis>
657657
Currently, only B-tree indexes can be declared unique.
658658
</para>
@@ -662,7 +662,7 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla
662662
indexed values are not allowed. Null values are not considered
663663
equal. A multicolumn unique index will only reject cases where all
664664
indexed columns are equal in multiple rows. Columns included with clause
665-
<literal>INCLUDING</literal> aren't used to enforce constraints (UNIQUE,
665+
<literal>INCLUDE</literal> aren't used to enforce constraints (UNIQUE,
666666
PRIMARY KEY, etc).
667667
</para>
668668

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 14 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ doc/src/sgml/ref/create_index.sgml
2323
<synopsis>
2424
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
2525
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
26-
[ INCLUDING ( <replaceable class="parameter">column_name</replaceable> ) ]
26+
[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> ) ]
2727
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
2828
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
2929
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -140,30 +140,36 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
140140
</varlistentry>
141141

142142
<varlistentry>
143-
<term><literal>INCLUDING</literal></term>
143+
<term><literal>INCLUDE</literal></term>
144144
<listitem>
145145
<para>
146-
An optional <literal>INCLUDING</> clause allows a list of columns to be
146+
An optional <literal>INCLUDE</> clause allows a list of columns to be
147147
specified which will be included in the index, in the non-key portion of
148148
the index. Columns which are part of this clause cannot also exist in the
149149
key columns portion of the index, and vice versa. The
150-
<literal>INCLUDING</> columns exist solely to allow more queries to benefit
150+
<literal>INCLUDE</> columns exist solely to allow more queries to benefit
151151
from <firstterm>index-only scans</> by including certain columns in the
152152
index, the value of which would otherwise have to be obtained by reading
153-
the table's heap. Having these columns in the <literal>INCLUDING</> clause
153+
the table's heap. Having these columns in the <literal>INCLUDE</> clause
154154
in some cases allows <productname>&productname;</> to skip the heap read
155155
completely. This also allows <literal>UNIQUE</> indexes to be defined on
156156
one set of columns, which can include another set of column in the
157-
<literal>INCLUDING</> clause, on which the uniqueness is not enforced upon.
157+
<literal>INCLUDE</> clause, on which the uniqueness is not enforced upon.
158158
It's the same with other constraints (PRIMARY KEY and EXCLUDE). This can
159159
also can be used for non-unique indexes as any columns which are not required
160160
for the searching or ordering of records can be included in the
161-
<literal>INCLUDING</> clause, which can slightly reduce the size of the index,
161+
<literal>INCLUDE</> clause, which can slightly reduce the size of the index,
162162
due to storing included attributes only in leaf index pages.
163163
Currently, only the B-tree access method supports this feature.
164164
Expressions as included columns are not supported since they cannot be used
165165
in index-only scan.
166166
</para>
167+
<note>
168+
<para>
169+
First versions of this feture used keyword INCLUDING, which is still supported,
170+
but deprecated now.
171+
</para>
172+
</note>
167173
</listitem>
168174
</varlistentry>
169175

@@ -631,7 +637,7 @@ CREATE UNIQUE INDEX title_idx ON films (title);
631637
and included columns <literal>director</literal> and <literal>rating</literal>
632638
in the table <literal>films</literal>:
633639
<programlisting>
634-
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDING (director, rating);
640+
CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);
635641
</programlisting>
636642
</para>
637643

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -59,8 +59,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
5959

6060
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
6161
{ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
62-
UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDING (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |
63-
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDING (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |
62+
UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDE (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |
63+
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> <optional>INCLUDE (<replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional> |
6464
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
6565
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
6666
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
@@ -486,7 +486,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
486486
<varlistentry>
487487
<term><literal>UNIQUE</> (column constraint)</term>
488488
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
489-
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
489+
<optional>INCLUDE ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
490490
<listitem>
491491
<para>
492492
The <literal>UNIQUE</literal> constraint specifies that a
@@ -511,12 +511,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
511511
<para>
512512
Adding a unique constraint will automatically create a unique btree
513513
index on the column or group of columns used in the constraint.
514-
Optional clause <literal>INCLUDING</literal> allows to add into the index
514+
Optional clause <literal>INCLUDE</literal> allows to add into the index
515515
a portion of columns on which the uniqueness is not enforced upon.
516516
Note, that althogh constraint is not enforced upon included columns, it still
517517
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
518518
can cause cascade constraint and index deletion.
519-
See paragraph about <literal>INCLUDING</literal> in
519+
See paragraph about <literal>INCLUDE</literal> in
520520
<xref linkend="SQL-CREATEINDEX"> for more information.
521521
</para>
522522

@@ -526,7 +526,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
526526
<varlistentry>
527527
<term><literal>PRIMARY KEY</> (column constraint)</term>
528528
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
529-
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
529+
<optional>INCLUDE ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
530530
<listitem>
531531
<para>
532532
The <literal>PRIMARY KEY</> constraint specifies that a column or
@@ -553,12 +553,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
553553
<para>
554554
Adding a <literal>PRIMARY KEY</literal> constraint will automatically create a unique btree
555555
index on the column or group of columns used in the constraint.
556-
Optional clause <literal>INCLUDING</literal> allows to add into the index
556+
An optional clause <literal>INCLUDE</literal> allows to add into the index
557557
a portion of columns on which the constraint is not enforced upon.
558558
Note, that althogh constraint is not enforced upon included columns, it still
559559
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
560560
can cause cascade constraint and index deletion.
561-
See paragraph about <literal>INCLUDING</literal> in
561+
See paragraph about <literal>INCLUDE</literal> in
562562
<xref linkend="SQL-CREATEINDEX"> for more information.
563563
</para>
564564
</listitem>

‎src/backend/access/common/indextuple.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -444,7 +444,7 @@ CopyIndexTuple(IndexTuple source)
444444
}
445445

446446
/*
447-
* Reform index tuple. Truncate nonkey (INCLUDING) attributes.
447+
* Reform index tuple. Truncate nonkey (INCLUDE) attributes.
448448
*/
449449
IndexTuple
450450
index_truncate_tuple(Relation idxrel, IndexTuple olditup)

‎src/backend/access/nbtree/README

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -676,3 +676,15 @@ Also, index searches using a key of a different datatype require comparisons
676676
to behave sanely across two datatypes. The extensions to three or more
677677
datatypes within a family are not strictly required by the btree index
678678
mechanism itself, but the planner relies on them for optimization purposes.
679+
680+
Included attributes in B-tree indexes
681+
-------------------------------------
682+
683+
Since 10.0 there is an optional INCLUDE clause, that allows to add
684+
a portion of non-key attributes to index. They exist to allow more queries
685+
to benefit from index-only scans. We never use included attributes in
686+
ScanKeys, neither for search nor for inserts. That allows us to include
687+
into B-tree any datatypes, even those which don't have suitable opclass.
688+
Included columns only stored in regular items on leaf pages. All inner
689+
keys and high keys are truncated and contain only key attributes.
690+
That helps to reduce the size of index.

‎src/backend/commands/indexcmds.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -350,12 +350,12 @@ DefineIndex(Oid relationId,
350350
numberOfKeyAttributes = list_length(stmt->indexParams);
351351

352352
/*
353-
* We append any INCLUDING columns onto the indexParams list so that
353+
* We append any INCLUDE columns onto the indexParams list so that
354354
* we have one list with all columns. Later we can determine which of these
355-
* are key columns, and which are just part of the INCLUDING list by check
355+
* are key columns, and which are just part of the INCLUDE list by checking
356356
* the list position. A list item in a position less than
357357
* ii_NumIndexKeyAttrs is part of the key columns, and anything equal to
358-
* and over is part of the INCLUDING columns.
358+
* and over is part of the INCLUDE columns.
359359
*/
360360
stmt->indexParams = list_concat(stmt->indexParams,
361361
stmt->indexIncludingParams);

‎src/backend/optimizer/path/pathkeys.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -451,7 +451,7 @@ build_index_pathkeys(PlannerInfo *root,
451451
PathKey *cpathkey;
452452

453453
/*
454-
* INCLUDING columns are stored in index unordered,
454+
* INCLUDE columns are stored in index unordered,
455455
* so they don't support ordered index scan.
456456
*/
457457
if(i >= index->nkeycolumns)

‎src/backend/parser/gram.y

Lines changed: 15 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -595,7 +595,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
595595

596596
HANDLER HAVING HEADER_P HOLD HOUR_P
597597

598-
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P
598+
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
599599
INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
600600
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
601601
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -3281,19 +3281,20 @@ ConstraintElem:
32813281
$$ = (Node *)n;
32823282
}
32833283
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
3284-
opt_definition OptConsTableSpace ExclusionWhereClause
3284+
opt_c_including opt_definition OptConsTableSpace ExclusionWhereClause
32853285
ConstraintAttributeSpec
32863286
{
32873287
Constraint *n = makeNode(Constraint);
32883288
n->contype = CONSTR_EXCLUSION;
32893289
n->location = @1;
32903290
n->access_method = $2;
32913291
n->exclusions = $4;
3292-
n->options = $6;
3292+
n->including = $6;
3293+
n->options = $7;
32933294
n->indexname = NULL;
3294-
n->indexspace = $7;
3295-
n->where_clause = $8;
3296-
processCASbits($9, @9, "EXCLUDE",
3295+
n->indexspace = $8;
3296+
n->where_clause = $9;
3297+
processCASbits($10, @10, "EXCLUDE",
32973298
&n->deferrable, &n->initdeferred, NULL,
32983299
NULL, yyscanner);
32993300
$$ = (Node *)n;
@@ -3339,13 +3340,18 @@ columnElem: ColId
33393340
}
33403341
;
33413342

3342-
opt_c_including: INCLUDING optcincluding { $$ = $2; }
3343+
opt_c_including: include_keyword optcincluding { $$ = $2; }
33433344
| /* EMPTY */ { $$ = NIL; }
33443345
;
33453346

33463347
optcincluding : '(' columnList ')' { $$ = $2; }
33473348
;
33483349

3350+
include_keyword:
3351+
INCLUDE {}
3352+
| INCLUDING /* Deprecated. Only in early versions of PGPRO. */ {}
3353+
;
3354+
33493355
key_match: MATCH FULL
33503356
{
33513357
$$ = FKCONSTR_MATCH_FULL;
@@ -6771,7 +6777,7 @@ index_elem: ColId opt_collate opt_class opt_asc_desc opt_nulls_order
67716777

67726778
optincluding : '(' index_including_params ')' { $$ = $2; }
67736779
;
6774-
opt_including: INCLUDING optincluding { $$ = $2; }
6780+
opt_including: include_keyword optincluding { $$ = $2; }
67756781
| /* EMPTY */ { $$ = NIL; }
67766782
;
67776783

@@ -13883,6 +13889,7 @@ unreserved_keyword:
1388313889
| IMMUTABLE
1388413890
| IMPLICIT_P
1388513891
| IMPORT_P
13892+
| INCLUDE
1388613893
| INCLUDING
1388713894
| INCREMENT
1388813895
| INDEX

‎src/backend/parser/parse_utilcmd.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1955,7 +1955,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
19551955
index->indexParams = lappend(index->indexParams, iparam);
19561956
}
19571957

1958-
/* Here is some ugly code duplication. But we do need it. */
1958+
/* Here is some code duplication. But we do need it. */
19591959
foreach(lc, constraint->including)
19601960
{
19611961
char *key = strVal(lfirst(lc));

‎src/backend/utils/adt/ruleutils.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1255,7 +1255,7 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
12551255
/* Report the INCLUDED attributes, if any. */
12561256
if ((!attrsOnly) && keyno == idxrec->indnkeyatts)
12571257
{
1258-
appendStringInfoString(&buf, ") INCLUDING (");
1258+
appendStringInfoString(&buf, ") INCLUDE (");
12591259
sep = "";
12601260
}
12611261

@@ -1665,7 +1665,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
16651665
Anum_pg_constraint_conincluding, &isnull);
16661666
if (!isnull)
16671667
{
1668-
appendStringInfoString(&buf, " INCLUDING (");
1668+
appendStringInfoString(&buf, " INCLUDE (");
16691669

16701670
decompile_column_index_array(val, conForm->conrelid, &buf);
16711671

‎src/backend/utils/cache/relcache.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1259,7 +1259,9 @@ RelationInitIndexAccessInfo(Relation relation)
12591259
InitIndexAmRoutine(relation);
12601260

12611261
/*
1262-
* Allocate arrays to hold data
1262+
* Allocate arrays to hold data.
1263+
* Opclasses are not used for included columns, so
1264+
* allocate them for indnkeyatts only.
12631265
*/
12641266
relation->rd_opfamily = (Oid *)
12651267
MemoryContextAllocZero(indexcxt, indnkeyatts * sizeof(Oid));

‎src/bin/pg_dump/pg_dump.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16167,7 +16167,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
1616716167
}
1616816168

1616916169
if (indxinfo->indnkeyattrs < indxinfo->indnattrs)
16170-
appendPQExpBuffer(q, ") INCLUDING (");
16170+
appendPQExpBuffer(q, ") INCLUDE (");
1617116171

1617216172
for (k = indxinfo->indnkeyattrs; k < indxinfo->indnattrs; k++)
1617316173
{

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -191,6 +191,7 @@ PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
191191
PG_KEYWORD("implicit", IMPLICIT_P, UNRESERVED_KEYWORD)
192192
PG_KEYWORD("import", IMPORT_P, UNRESERVED_KEYWORD)
193193
PG_KEYWORD("in", IN_P, RESERVED_KEYWORD)
194+
PG_KEYWORD("include", INCLUDE, UNRESERVED_KEYWORD)
194195
PG_KEYWORD("including", INCLUDING, UNRESERVED_KEYWORD)
195196
PG_KEYWORD("increment", INCREMENT, UNRESERVED_KEYWORD)
196197
PG_KEYWORD("index", INDEX, UNRESERVED_KEYWORD)

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2403,7 +2403,7 @@ INSERT INTO func_index_heap VALUES('QWERTY');
24032403
-- Test unique index with included columns
24042404
--
24052405
CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text);
2406-
CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDING(f3);
2406+
CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3);
24072407
INSERT INTO covering_index_heap VALUES(1,1,'AAA');
24082408
INSERT INTO covering_index_heap VALUES(1,2,'AAA');
24092409
-- this should fail because of unique index on f1,f2:
@@ -2413,7 +2413,7 @@ DETAIL: Key (f1, f2)=(1, 2) already exists.
24132413
-- and this shouldn't:
24142414
INSERT INTO covering_index_heap VALUES(1,4,'AAA');
24152415
-- Try to build index on table that already contains data
2416-
CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDING(f3);
2416+
CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3);
24172417
-- Try to use existing covering index as primary key
24182418
ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX
24192419
covering_pkey;

0 commit comments

Comments
 (0)