Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit89e46da

Browse files
author
Amit Kapila
committed
Allow the use of indexes other than PK and REPLICA IDENTITY on the subscriber.
Using REPLICA IDENTITY FULL on the publisher can lead to a full table scanper tuple change on the subscription when REPLICA IDENTITY or PK index isnot available. This makes REPLICA IDENTITY FULL impractical to use apartfrom some small number of use cases.This patch allows using indexes other than PRIMARY KEY or REPLICAIDENTITY on the subscriber during apply of update/delete. The index thatcan be used must be a btree index, not a partial index, and it must haveat least one column reference (i.e. cannot consist of only expressions).We can uplift these restrictions in the future. There is no smartmechanism to pick the index. If there is more than one index thatsatisfies these requirements, we just pick the first one. We discussedusing some of the optimizer's low-level APIs for this but ruled it outas that can be a maintenance burden in the long run.This patch improves the performance in the vast majority of cases and theimprovement is proportional to the amount of data in the table. However,there could be some regression in a small number of cases where the indexeshave a lot of duplicate and dead rows. It was discussed that those aremostly impractical cases but we can provide a table or subscription leveloption to disable this feature if required.Author: Onder Kalaci, Amit KapilaReviewed-by: Peter Smith, Shi yu, Hou Zhijie, Vignesh C, Kuroda Hayato, Amit KapilaDiscussion:https://postgr.es/m/CACawEhVLqmAAyPXdHEPv1ssU2c=dqOniiGz7G73HfyS7+nGV4w@mail.gmail.com
1 parent720de00 commit89e46da

File tree

6 files changed

+325
-69
lines changed

6 files changed

+325
-69
lines changed

‎doc/src/sgml/logical-replication.sgml

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -132,7 +132,14 @@
132132
certain additional requirements) can also be set to be the replica
133133
identity. If the table does not have any suitable key, then it can be set
134134
to replica identity <quote>full</quote>, which means the entire row becomes
135-
the key. This, however, is very inefficient and should only be used as a
135+
the key. When replica identity <quote>full</quote> is specified,
136+
indexes can be used on the subscriber side for searching the rows. Candidate
137+
indexes must be btree, non-partial, and have at least one column reference
138+
(i.e. cannot consist of only expressions). These restrictions
139+
on the non-unique index properties adhere to some of the restrictions that
140+
are enforced for primary keys. If there are no such suitable indexes,
141+
the search on the subscriber side can be very inefficient, therefore
142+
replica identity <quote>full</quote> should only be used as a
136143
fallback if no other solution is possible. If a replica identity other
137144
than <quote>full</quote> is set on the publisher side, a replica identity
138145
comprising the same or fewer columns must also be set on the subscriber

‎src/backend/executor/execReplication.c

Lines changed: 79 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@
2525
#include"nodes/nodeFuncs.h"
2626
#include"parser/parse_relation.h"
2727
#include"parser/parsetree.h"
28+
#include"replication/logicalrelation.h"
2829
#include"storage/bufmgr.h"
2930
#include"storage/lmgr.h"
3031
#include"utils/builtins.h"
@@ -37,49 +38,63 @@
3738
#include"utils/typcache.h"
3839

3940

41+
staticbooltuples_equal(TupleTableSlot*slot1,TupleTableSlot*slot2,
42+
TypeCacheEntry**eq);
43+
4044
/*
4145
* Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
4246
* is setup to match 'rel' (*NOT* idxrel!).
4347
*
44-
* Returns whether any column contains NULLs.
48+
* Returns how many columns to use for the index scan.
49+
*
50+
* This is not generic routine, it expects the idxrel to be a btree, non-partial
51+
* and have at least one column reference (i.e. cannot consist of only
52+
* expressions).
4553
*
46-
*This is not generic routine, it expects the idxrel to be replication
47-
*identity of a rel and meet all limitations associated with that.
54+
*By definition, replication identity of a rel meets all limitations associated
55+
*with that. Note that any other index could also meet these limitations.
4856
*/
49-
staticbool
57+
staticint
5058
build_replindex_scan_key(ScanKeyskey,Relationrel,Relationidxrel,
5159
TupleTableSlot*searchslot)
5260
{
53-
intattoff;
61+
intindex_attoff;
62+
intskey_attoff=0;
5463
boolisnull;
5564
DatumindclassDatum;
5665
oidvector*opclass;
5766
int2vector*indkey=&idxrel->rd_index->indkey;
58-
boolhasnulls= false;
59-
60-
Assert(RelationGetReplicaIndex(rel)==RelationGetRelid(idxrel)||
61-
RelationGetPrimaryKeyIndex(rel)==RelationGetRelid(idxrel));
6267

6368
indclassDatum=SysCacheGetAttr(INDEXRELID,idxrel->rd_indextuple,
6469
Anum_pg_index_indclass,&isnull);
6570
Assert(!isnull);
6671
opclass= (oidvector*)DatumGetPointer(indclassDatum);
6772

68-
/* Build scankey for every attribute in the index. */
69-
for (attoff=0;attoff<IndexRelationGetNumberOfKeyAttributes(idxrel);attoff++)
73+
/* Build scankey for every non-expression attribute in the index. */
74+
for (index_attoff=0;index_attoff<IndexRelationGetNumberOfKeyAttributes(idxrel);
75+
index_attoff++)
7076
{
7177
Oidoperator;
78+
Oidoptype;
7279
Oidopfamily;
7380
RegProcedureregop;
74-
intpkattno=attoff+1;
75-
intmainattno=indkey->values[attoff];
76-
Oidoptype=get_opclass_input_type(opclass->values[attoff]);
81+
inttable_attno=indkey->values[index_attoff];
82+
83+
if (!AttributeNumberIsValid(table_attno))
84+
{
85+
/*
86+
* XXX: Currently, we don't support expressions in the scan key,
87+
* see code below.
88+
*/
89+
continue;
90+
}
7791

7892
/*
7993
* Load the operator info. We need this to get the equality operator
8094
* function for the scan key.
8195
*/
82-
opfamily=get_opclass_family(opclass->values[attoff]);
96+
optype=get_opclass_input_type(opclass->values[index_attoff]);
97+
opfamily=get_opclass_family(opclass->values[index_attoff]);
8398

8499
operator=get_opfamily_member(opfamily,optype,
85100
optype,
@@ -91,23 +106,25 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
91106
regop=get_opcode(operator);
92107

93108
/* Initialize the scankey. */
94-
ScanKeyInit(&skey[attoff],
95-
pkattno,
109+
ScanKeyInit(&skey[skey_attoff],
110+
index_attoff+1,
96111
BTEqualStrategyNumber,
97112
regop,
98-
searchslot->tts_values[mainattno-1]);
113+
searchslot->tts_values[table_attno-1]);
99114

100-
skey[attoff].sk_collation=idxrel->rd_indcollation[attoff];
115+
skey[skey_attoff].sk_collation=idxrel->rd_indcollation[index_attoff];
101116

102117
/* Check for null value. */
103-
if (searchslot->tts_isnull[mainattno-1])
104-
{
105-
hasnulls= true;
106-
skey[attoff].sk_flags |=SK_ISNULL;
107-
}
118+
if (searchslot->tts_isnull[table_attno-1])
119+
skey[skey_attoff].sk_flags |= (SK_ISNULL |SK_SEARCHNULL);
120+
121+
skey_attoff++;
108122
}
109123

110-
returnhasnulls;
124+
/* There must always be at least one attribute for the index scan. */
125+
Assert(skey_attoff>0);
126+
127+
returnskey_attoff;
111128
}
112129

113130
/*
@@ -123,33 +140,58 @@ RelationFindReplTupleByIndex(Relation rel, Oid idxoid,
123140
TupleTableSlot*outslot)
124141
{
125142
ScanKeyDataskey[INDEX_MAX_KEYS];
143+
intskey_attoff;
126144
IndexScanDescscan;
127145
SnapshotDatasnap;
128146
TransactionIdxwait;
129147
Relationidxrel;
130148
boolfound;
149+
TypeCacheEntry**eq=NULL;
150+
boolisIdxSafeToSkipDuplicates;
131151

132152
/* Open the index. */
133153
idxrel=index_open(idxoid,RowExclusiveLock);
134154

135-
/* Start an index scan. */
155+
isIdxSafeToSkipDuplicates= (GetRelationIdentityOrPK(rel)==idxoid);
156+
136157
InitDirtySnapshot(snap);
137-
scan=index_beginscan(rel,idxrel,&snap,
138-
IndexRelationGetNumberOfKeyAttributes(idxrel),
139-
0);
140158

141159
/* Build scan key. */
142-
build_replindex_scan_key(skey,rel,idxrel,searchslot);
160+
skey_attoff=build_replindex_scan_key(skey,rel,idxrel,searchslot);
161+
162+
/* Start an index scan. */
163+
scan=index_beginscan(rel,idxrel,&snap,skey_attoff,0);
143164

144165
retry:
145166
found= false;
146167

147-
index_rescan(scan,skey,IndexRelationGetNumberOfKeyAttributes(idxrel),NULL,0);
168+
index_rescan(scan,skey,skey_attoff,NULL,0);
148169

149170
/* Try to find the tuple */
150-
if (index_getnext_slot(scan,ForwardScanDirection,outslot))
171+
while (index_getnext_slot(scan,ForwardScanDirection,outslot))
151172
{
152-
found= true;
173+
/*
174+
* Avoid expensive equality check if the index is primary key or
175+
* replica identity index.
176+
*/
177+
if (!isIdxSafeToSkipDuplicates)
178+
{
179+
if (eq==NULL)
180+
{
181+
#ifdefUSE_ASSERT_CHECKING
182+
/* apply assertions only once for the input idxoid */
183+
IndexInfo*indexInfo=BuildIndexInfo(idxrel);
184+
185+
Assert(IsIndexUsableForReplicaIdentityFull(indexInfo));
186+
#endif
187+
188+
eq=palloc0(sizeof(*eq)*outslot->tts_tupleDescriptor->natts);
189+
}
190+
191+
if (!tuples_equal(outslot,searchslot,eq))
192+
continue;
193+
}
194+
153195
ExecMaterializeSlot(outslot);
154196

155197
xwait=TransactionIdIsValid(snap.xmin) ?
@@ -164,6 +206,10 @@ RelationFindReplTupleByIndex(Relation rel, Oid idxoid,
164206
XactLockTableWait(xwait,NULL,NULL,XLTW_None);
165207
gotoretry;
166208
}
209+
210+
/* Found our tuple and it's not locked */
211+
found= true;
212+
break;
167213
}
168214

169215
/* Found tuple, try to lock it in the lockmode. */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp