Skip to content

Commit a9b16e0

Browse files
vbwagnervbwagner
committed
Merge branch 'CORE-155-covering-indexes' into PGPRO9_5
2 parents dfad4a7 + cf7c1d0 commit a9b16e0

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

46 files changed

+1011
-264
lines changed

‎contrib/dblink/dblink.c

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -101,7 +101,7 @@ static remoteConn *getConnectionByName(const char *name);
101101
static HTAB *createConnHash(void);
102102
static void createNewConnection(const char *name, remoteConn *rconn);
103103
static void deleteConnection(const char *name);
104-
static char **get_pkey_attnames(Relation rel, int16 *numatts);
104+
static char **get_pkey_attnames(Relation rel, int16 *indnkeyatts);
105105
static char **get_text_array_contents(ArrayType *array, int *numitems);
106106
static char *get_sql_insert(Relation rel, int *pkattnums, int pknumatts, char **src_pkattvals, char **tgt_pkattvals);
107107
static char *get_sql_delete(Relation rel, int *pkattnums, int pknumatts, char **tgt_pkattvals);
@@ -1486,7 +1486,7 @@ PG_FUNCTION_INFO_V1(dblink_get_pkey);
14861486
Datum
14871487
dblink_get_pkey(PG_FUNCTION_ARGS)
14881488
{
1489-
int16 numatts;
1489+
int16 indnkeyatts;
14901490
char **results;
14911491
FuncCallContext *funcctx;
14921492
int32 call_cntr;
@@ -1512,7 +1512,7 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15121512
rel = get_rel_from_relname(PG_GETARG_TEXT_P(0), AccessShareLock, ACL_SELECT);
15131513

15141514
/* get the array of attnums */
1515-
results = get_pkey_attnames(rel, &numatts);
1515+
results = get_pkey_attnames(rel, &indnkeyatts);
15161516

15171517
relation_close(rel, AccessShareLock);
15181518

@@ -1532,9 +1532,9 @@ dblink_get_pkey(PG_FUNCTION_ARGS)
15321532
attinmeta = TupleDescGetAttInMetadata(tupdesc);
15331533
funcctx->attinmeta = attinmeta;
15341534

1535-
if ((results != NULL) && (numatts > 0))
1535+
if ((results != NULL) && (indnkeyatts > 0))
15361536
{
1537-
funcctx->max_calls = numatts;
1537+
funcctx->max_calls = indnkeyatts;
15381538

15391539
/* got results, keep track of them */
15401540
funcctx->user_fctx = results;
@@ -2024,10 +2024,10 @@ dblink_fdw_validator(PG_FUNCTION_ARGS)
20242024
* get_pkey_attnames
20252025
*
20262026
* Get the primary key attnames for the given relation.
2027-
* Return NULL, and set numatts = 0, if no primary key exists.
2027+
* Return NULL, and set indnkeyatts = 0, if no primary key exists.
20282028
*/
20292029
static char **
2030-
get_pkey_attnames(Relation rel, int16 *numatts)
2030+
get_pkey_attnames(Relation rel, int16 *indnkeyatts)
20312031
{
20322032
Relation indexRelation;
20332033
ScanKeyData skey;
@@ -2037,8 +2037,8 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20372037
char **result = NULL;
20382038
TupleDesc tupdesc;
20392039

2040-
/* initialize numatts to 0 in case no primary key exists */
2041-
*numatts = 0;
2040+
/* initialize indnkeyatts to 0 in case no primary key exists */
2041+
*indnkeyatts = 0;
20422042

20432043
tupdesc = rel->rd_att;
20442044

@@ -2059,12 +2059,12 @@ get_pkey_attnames(Relation rel, int16 *numatts)
20592059
/* we're only interested if it is the primary key */
20602060
if (index->indisprimary)
20612061
{
2062-
*numatts = index->indnkeyatts;
2063-
if (*numatts > 0)
2062+
*indnkeyatts = index->indnkeyatts;
2063+
if (*indnkeyatts > 0)
20642064
{
2065-
result = (char **) palloc(*numatts * sizeof(char *));
2065+
result = (char **) palloc(*indnkeyatts * sizeof(char *));
20662066

2067-
for (i = 0; i < *numatts; i++)
2067+
for (i = 0; i < *indnkeyatts; i++)
20682068
result[i] = SPI_fname(tupdesc, index->indkey.values[i]);
20692069
}
20702070
break;

‎contrib/tcn/tcn.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -138,9 +138,9 @@ triggered_change_notification(PG_FUNCTION_ARGS)
138138
/* we're only interested if it is the primary key and valid */
139139
if (index->indisprimary && IndexIsValid(index))
140140
{
141-
int numatts = index->indnatts;
141+
int indnkeyatts = index->indnkeyatts;
142142

143-
if (numatts > 0)
143+
if (indnkeyatts > 0)
144144
{
145145
int i;
146146

@@ -150,7 +150,7 @@ triggered_change_notification(PG_FUNCTION_ARGS)
150150
appendStringInfoCharMacro(payload, ',');
151151
appendStringInfoCharMacro(payload, operation);
152152

153-
for (i = 0; i < numatts; i++)
153+
for (i = 0; i < indnkeyatts; i++)
154154
{
155155
int colno = index->indkey.values[i];
156156

‎doc/src/sgml/catalogs.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3721,7 +3721,7 @@
37213721
<literal>pg_class.relnatts</literal>)</entry>
37223722
</row>
37233723

3724-
<row>
3724+
<row>
37253725
<entry><structfield>indnkeyatts</structfield></entry>
37263726
<entry><type>int2</type></entry>
37273727
<entry></entry>

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

Lines changed: 20 additions & 17 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> | ( <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> [, ...] ) ]
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> ]
@@ -145,22 +145,25 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
145145
<para>
146146
An optional <literal>INCLUDING</> clause allows a list of columns to be
147147
specified which will be included in the index, in the non-key portion of
148-
the index. Columns which are part of this clause cannot also exist in the
149-
key columns portion of the index, and vice versa. The
150-
<literal>INCLUDING</> columns exist solely to allow more queries to benefit
151-
from <firstterm>index-only scans</> by including certain columns in the
152-
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
154-
in some cases allows <productname>PostgreSQL</> to skip the heap read
155-
completely. This also allows <literal>UNIQUE</> indexes to be defined on
156-
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.
158-
It's the same with other constraints (PRIMARY KEY and EXCLUDE). This can
159-
also can be used for non-unique indexes as any columns which are not required
160-
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,
162-
due to storing included attributes only in leaf index pages.
163-
Currently, only the B-tree access method supports this feature.
148+
the index. Columns which are part of this clause cannot also exist in
149+
the key columns portion of the index, and vice versa. The
150+
<literal>INCLUDING</> columns exist solely to allow more queries to
151+
benefit from <firstterm>index-only scans</> by including certain
152+
columns in the index, the value of which would otherwise have to be
153+
obtained by reading the table's heap. Having these columns in the
154+
<literal>INCLUDING</> clause in some cases allows
155+
<productname>PostgreSQL</> to skip the heap read completely. This
156+
also allows <literal>UNIQUE</> indexes to be defined on one set of
157+
columns, which can include another set of column in the
158+
<literal>INCLUDING</> clause, on which the uniqueness is not enforced
159+
upon. It's the same with other constraints (PRIMARY KEY and EXCLUDE).
160+
This can also can be used for non-unique indexes as any columns which
161+
are not required for the searching or ordering of records can be
162+
included in the <literal>INCLUDING</> clause, which can slightly reduce
163+
the size of the index, due to storing included attributes only in leaf
164+
index pages. Currently, only the B-tree access method supports this
165+
feature. Expressions as included columns are not supported since
166+
they cannot be used in index-only scan.
164167
</para>
165168
</listitem>
166169
</varlistentry>

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

Lines changed: 32 additions & 4 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> |
63-
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
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> |
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> ] }
@@ -476,7 +476,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
476476

477477
<varlistentry>
478478
<term><literal>UNIQUE</> (column constraint)</term>
479-
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
479+
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
480+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
480481

481482
<listitem>
482483
<para>
@@ -498,12 +499,27 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
498499
primary key constraint defined for the table. (Otherwise it
499500
would just be the same constraint listed twice.)
500501
</para>
502+
503+
<para>
504+
Adding a unique constraint will automatically create a unique btree
505+
index on the column or group of columns used in the constraint.
506+
Optional clause <literal>INCLUDING</literal> allows to add into the index
507+
a portion of columns on which the uniqueness is not enforced upon.
508+
Note, that althogh constraint is not enforced upon included columns, it still
509+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
510+
can cause cascade constraint and index deletion.
511+
See paragraph about <literal>INCLUDING</literal> in
512+
<xref linkend="SQL-CREATEINDEX"> for more information.
513+
</para>
514+
501515
</listitem>
502516
</varlistentry>
503517

504518
<varlistentry>
505519
<term><literal>PRIMARY KEY</> (column constraint)</term>
506-
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
520+
<term><literal>PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )
521+
<optional>INCLUDING ( <replaceable class="PARAMETER">column_name</replaceable> [, ...])</optional></> (table constraint)</term>
522+
507523
<listitem>
508524
<para>
509525
The <literal>PRIMARY KEY</> constraint specifies that a column or
@@ -526,6 +542,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
526542
about the design of the schema, since a primary key implies that other
527543
tables can rely on this set of columns as a unique identifier for rows.
528544
</para>
545+
546+
<para>
547+
Adding a <literal>PRIMARY KEY</literal> constraint will automatically create a unique btree
548+
index on the column or group of columns used in the constraint.
549+
Optional clause <literal>INCLUDING</literal> allows to add into the index
550+
a portion of columns on which the constraint is not enforced upon.
551+
Note, that althogh constraint is not enforced upon included columns, it still
552+
depends on them. Consequently, some operations on these columns (e.g. <literal>DROP COLUMN</literal>)
553+
can cause cascade constraint and index deletion.
554+
See paragraph about <literal>INCLUDING</literal> in
555+
<xref linkend="SQL-CREATEINDEX"> for more information.
556+
</para>
529557
</listitem>
530558
</varlistentry>
531559

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

Lines changed: 9 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -448,25 +448,28 @@ CopyIndexTuple(IndexTuple source)
448448
* Reform index tuple. Truncate nonkey (INCLUDING) attributes.
449449
*/
450450
IndexTuple
451-
index_reform_tuple(Relation idxrel, IndexTuple olditup, int natts, int nkeyatts)
451+
index_truncate_tuple(Relation idxrel, IndexTuple olditup)
452452
{
453453
TupleDesc itupdesc = RelationGetDescr(idxrel);
454454
Datum values[INDEX_MAX_KEYS];
455455
bool isnull[INDEX_MAX_KEYS];
456456
IndexTuple newitup;
457+
int indnatts = IndexRelationGetNumberOfAttributes(idxrel);
458+
int indnkeyatts = IndexRelationGetNumberOfKeyAttributes(idxrel);
457459

458-
Assert(natts <= INDEX_MAX_KEYS);
459-
Assert(nkeyatts > 0);
460-
Assert(nkeyatts <= natts);
460+
Assert(indnatts <= INDEX_MAX_KEYS);
461+
Assert(indnkeyatts > 0);
462+
Assert(indnkeyatts <= indnatts);
461463

462464
index_deform_tuple(olditup, itupdesc, values, isnull);
463465

464466
/* form new tuple that will contain only key attributes */
465-
itupdesc->natts = nkeyatts;
467+
itupdesc->natts = indnkeyatts;
466468
newitup = index_form_tuple(itupdesc, values, isnull);
467469
newitup->t_tid = olditup->t_tid;
468470

469-
itupdesc->natts = natts;
471+
itupdesc->natts = indnatts;
470472

473+
Assert(IndexTupleSize(newitup) <= IndexTupleSize(olditup));
471474
return newitup;
472475
}

‎src/backend/access/index/genam.c

Lines changed: 21 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -175,14 +175,15 @@ BuildIndexValueDescription(Relation indexRelation,
175175
StringInfoData buf;
176176
Form_pg_index idxrec;
177177
HeapTuple ht_idx;
178-
int natts = indexRelation->rd_rel->relnatts;
179-
int nkeyatts;
178+
int indnkeyatts;
180179
int i;
181180
int keyno;
182181
Oid indexrelid = RelationGetRelid(indexRelation);
183182
Oid indrelid;
184183
AclResult aclresult;
185184

185+
indnkeyatts = IndexRelationGetNumberOfKeyAttributes(indexRelation);
186+
186187
/*
187188
* Check permissions- if the user does not have access to view all of the
188189
* key columns then return NULL to avoid leaking data.
@@ -220,7 +221,7 @@ BuildIndexValueDescription(Relation indexRelation,
220221
* No table-level access, so step through the columns in the index and
221222
* make sure the user has SELECT rights on all of them.
222223
*/
223-
for (keyno = 0; keyno < idxrec->indnatts; keyno++)
224+
for (keyno = 0; keyno < idxrec->indnkeyatts; keyno++)
224225
{
225226
AttrNumber attnum = idxrec->indkey.values[keyno];
226227

@@ -246,8 +247,7 @@ BuildIndexValueDescription(Relation indexRelation,
246247
appendStringInfo(&buf, "(%s)=(",
247248
pg_get_indexdef_columns(indexrelid, true));
248249

249-
nkeyatts = IndexRelationGetNumberOfKeyAttributes(indexRelation);
250-
for (i = 0; i < natts; i++)
250+
for (i = 0; i < indnkeyatts; i++)
251251
{
252252
char *val;
253253

@@ -257,38 +257,25 @@ BuildIndexValueDescription(Relation indexRelation,
257257
{
258258
Oid foutoid;
259259
bool typisvarlena;
260-
TupleDesc tupdesc = RelationGetDescr(indexRelation);
261260
/*
262-
* For key attributes the provided data is not necessarily of the
263-
* type stored in the index; rather it is of the index opclass's
264-
* input type. So look at rd_opcintype not the index tupdesc.
265-
*
266-
* Note: this is a bit shaky for opclasses that have pseudotype
267-
* input types such as ANYARRAY or RECORD. Currently, the
268-
* typoutput functions associated with the pseudotypes will work
269-
* okay, but we might have to try harder in future.
270-
*
271-
* For included attributes just use info stored in the index
272-
* tupdesc.
273-
*/
274-
if (i < nkeyatts)
275-
{
276-
getTypeOutputInfo(indexRelation->rd_opcintype[i],
277-
&foutoid, &typisvarlena);
278-
val = OidOutputFunctionCall(foutoid, values[i]);
279-
}
280-
else
281-
{
282-
getTypeOutputInfo(tupdesc->attrs[i]->atttypid, &foutoid, &typisvarlena);
283-
val = OidOutputFunctionCall(foutoid, values[i]);
284-
}
261+
* The provided data is not necessarily of the type stored in the
262+
* index; rather it is of the index opclass's input type. So look
263+
* at rd_opcintype not the index tupdesc.
264+
*
265+
* Note: this is a bit shaky for opclasses that have pseudotype
266+
* input types such as ANYARRAY or RECORD. Currently, the
267+
* typoutput functions associated with the pseudotypes will work
268+
* okay, but we might have to try harder in future.
269+
*/
270+
getTypeOutputInfo(indexRelation->rd_opcintype[i],
271+
&foutoid, &typisvarlena);
272+
val = OidOutputFunctionCall(foutoid, values[i]);
285273
}
286274

287275
if (i > 0)
288276
appendStringInfoString(&buf, ", ");
289277
appendStringInfoString(&buf, val);
290278
}
291-
292279
appendStringInfoChar(&buf, ')');
293280

294281
return buf.data;
@@ -376,15 +363,15 @@ systable_beginscan(Relation heapRelation,
376363
{
377364
int j;
378365

379-
for (j = 0; j < irel->rd_index->indnatts; j++)
366+
for (j = 0; j < IndexRelationGetNumberOfAttributes(irel); j++)
380367
{
381368
if (key[i].sk_attno == irel->rd_index->indkey.values[j])
382369
{
383370
key[i].sk_attno = j + 1;
384371
break;
385372
}
386373
}
387-
if (j == irel->rd_index->indnatts)
374+
if (j == IndexRelationGetNumberOfAttributes(irel))
388375
elog(ERROR, "column is not in index");
389376
}
390377

@@ -578,15 +565,15 @@ systable_beginscan_ordered(Relation heapRelation,
578565
{
579566
int j;
580567

581-
for (j = 0; j < indexRelation->rd_index->indnatts; j++)
568+
for (j = 0; j < IndexRelationGetNumberOfAttributes(indexRelation); j++)
582569
{
583570
if (key[i].sk_attno == indexRelation->rd_index->indkey.values[j])
584571
{
585572
key[i].sk_attno = j + 1;
586573
break;
587574
}
588575
}
589-
if (j == indexRelation->rd_index->indnatts)
576+
if (j == IndexRelationGetNumberOfAttributes(indexRelation))
590577
elog(ERROR, "column is not in index");
591578
}
592579

0 commit comments

Comments
 (0)