Skip to content

Commit 463e9fe

Browse files
vbwagnervbwagner
committed
Merge branch 'PGPRO9_6' into PGPROEE9_6
2 parents 1b66532 + f23efc3 commit 463e9fe

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 | CONSTANT ] TABLE [
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> ] }
@@ -509,7 +509,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED | CONSTANT ] TABLE [
509509
<varlistentry>
510510
<term><literal>UNIQUE</> (column constraint)</term>
511511
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
512-
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
512+
<optional>INCLUDE ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
513513
<listitem>
514514
<para>
515515
The <literal>UNIQUE</literal> constraint specifies that a
@@ -534,12 +534,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED | CONSTANT ] TABLE [
534534
<para>
535535
Adding a unique constraint will automatically create a unique btree
536536
index on the column or group of columns used in the constraint.
537-
Optional clause <literal>INCLUDING</literal> allows to add into the index
537+
Optional clause <literal>INCLUDE</literal> allows to add into the index
538538
a portion of columns on which the uniqueness is not enforced upon.
539539
Note, that althogh constraint is not enforced upon included columns, it still
540540
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
541541
can cause cascade constraint and index deletion.
542-
See paragraph about <literal>INCLUDING</literal> in
542+
See paragraph about <literal>INCLUDE</literal> in
543543
<xref linkend="SQL-CREATEINDEX"> for more information.
544544
</para>
545545

@@ -549,7 +549,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED | CONSTANT ] TABLE [
549549
<varlistentry>
550550
<term><literal>PRIMARY KEY</> (column constraint)</term>
551551
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
552-
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
552+
<optional>INCLUDE ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
553553
<listitem>
554554
<para>
555555
The <literal>PRIMARY KEY</> constraint specifies that a column or
@@ -576,12 +576,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED | CONSTANT ] TABLE [
576576
<para>
577577
Adding a <literal>PRIMARY KEY</literal> constraint will automatically create a unique btree
578578
index on the column or group of columns used in the constraint.
579-
Optional clause <literal>INCLUDING</literal> allows to add into the index
579+
An optional clause <literal>INCLUDE</literal> allows to add into the index
580580
a portion of columns on which the constraint is not enforced upon.
581581
Note, that althogh constraint is not enforced upon included columns, it still
582582
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
583583
can cause cascade constraint and index deletion.
584-
See paragraph about <literal>INCLUDING</literal> in
584+
See paragraph about <literal>INCLUDE</literal> in
585585
<xref linkend="SQL-CREATEINDEX"> for more information.
586586
</para>
587587
</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
@@ -596,7 +596,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
596596

597597
HANDLER HAVING HEADER_P HOLD HOUR_P
598598

599-
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P
599+
IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
600600
INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
601601
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
602602
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -3295,19 +3295,20 @@ ConstraintElem:
32953295
$$ = (Node *)n;
32963296
}
32973297
| EXCLUDE access_method_clause '(' ExclusionConstraintList ')'
3298-
opt_definition OptConsTableSpace ExclusionWhereClause
3298+
opt_c_including opt_definition OptConsTableSpace ExclusionWhereClause
32993299
ConstraintAttributeSpec
33003300
{
33013301
Constraint *n = makeNode(Constraint);
33023302
n->contype = CONSTR_EXCLUSION;
33033303
n->location = @1;
33043304
n->access_method = $2;
33053305
n->exclusions = $4;
3306-
n->options = $6;
3306+
n->including = $6;
3307+
n->options = $7;
33073308
n->indexname = NULL;
3308-
n->indexspace = $7;
3309-
n->where_clause = $8;
3310-
processCASbits($9, @9, "EXCLUDE",
3309+
n->indexspace = $8;
3310+
n->where_clause = $9;
3311+
processCASbits($10, @10, "EXCLUDE",
33113312
&n->deferrable, &n->initdeferred, NULL,
33123313
NULL, yyscanner);
33133314
$$ = (Node *)n;
@@ -3353,13 +3354,18 @@ columnElem: ColId
33533354
}
33543355
;
33553356

3356-
opt_c_including: INCLUDING optcincluding { $$ = $2; }
3357+
opt_c_including: include_keyword optcincluding { $$ = $2; }
33573358
| /* EMPTY */ { $$ = NIL; }
33583359
;
33593360

33603361
optcincluding : '(' columnList ')' { $$ = $2; }
33613362
;
33623363

3364+
include_keyword:
3365+
INCLUDE {}
3366+
| INCLUDING /* Deprecated. Only in early versions of PGPRO. */ {}
3367+
;
3368+
33633369
key_match: MATCH FULL
33643370
{
33653371
$$ = FKCONSTR_MATCH_FULL;
@@ -6786,7 +6792,7 @@ index_elem: ColId opt_collate opt_class opt_asc_desc opt_nulls_order
67866792

67876793
optincluding : '(' index_including_params ')' { $$ = $2; }
67886794
;
6789-
opt_including: INCLUDING optincluding { $$ = $2; }
6795+
opt_including: include_keyword optincluding { $$ = $2; }
67906796
| /* EMPTY */ { $$ = NIL; }
67916797
;
67926798

@@ -13943,6 +13949,7 @@ unreserved_keyword:
1394313949
| IMMUTABLE
1394413950
| IMPLICIT_P
1394513951
| IMPORT_P
13952+
| INCLUDE
1394613953
| INCLUDING
1394713954
| INCREMENT
1394813955
| 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
@@ -1260,7 +1260,9 @@ RelationInitIndexAccessInfo(Relation relation)
12601260
InitIndexAmRoutine(relation);
12611261

12621262
/*
1263-
* Allocate arrays to hold data
1263+
* Allocate arrays to hold data.
1264+
* Opclasses are not used for included columns, so
1265+
* allocate them for indnkeyatts only.
12641266
*/
12651267
relation->rd_opfamily = (Oid *)
12661268
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
@@ -16277,7 +16277,7 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
1627716277
}
1627816278

1627916279
if (indxinfo->indnkeyattrs < indxinfo->indnattrs)
16280-
appendPQExpBuffer(q, ") INCLUDING (");
16280+
appendPQExpBuffer(q, ") INCLUDE (");
1628116281

1628216282
for (k = indxinfo->indnkeyattrs; k < indxinfo->indnattrs; k++)
1628316283
{

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -193,6 +193,7 @@ PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
193193
PG_KEYWORD("implicit", IMPLICIT_P, UNRESERVED_KEYWORD)
194194
PG_KEYWORD("import", IMPORT_P, UNRESERVED_KEYWORD)
195195
PG_KEYWORD("in", IN_P, RESERVED_KEYWORD)
196+
PG_KEYWORD("include", INCLUDE, UNRESERVED_KEYWORD)
196197
PG_KEYWORD("including", INCLUDING, UNRESERVED_KEYWORD)
197198
PG_KEYWORD("increment", INCREMENT, UNRESERVED_KEYWORD)
198199
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)