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

Commitedca342

Browse files
author
Amit Kapila
committed
Allow the use of a hash index on the subscriber during replication.
Commit89e46da allowed using BTREE indexes that are neitherPRIMARY KEY nor REPLICA IDENTITY on the subscriber during apply ofupdate/delete. This patch extends that functionality to also allow HASHindexes.We explored supporting other index access methods as well but they don'thave a fixed strategy for equality operation which is required by thecurrent infrastructure in logical replication to scan the indexes.Author: Kuroda HayatoReviewed-by: Peter Smith, Onder Kalaci, Amit KapilaDiscussion:https://postgr.es/m/TYAPR01MB58669D7414E59664E17A5827F522A@TYAPR01MB5866.jpnprd01.prod.outlook.com
1 parenta5ea825 commitedca342

File tree

7 files changed

+186
-16
lines changed

7 files changed

+186
-16
lines changed

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -134,9 +134,9 @@
134134
to replica identity <literal>FULL</literal>, which means the entire row becomes
135135
the key. When replica identity <literal>FULL</literal> is specified,
136136
indexes can be used on the subscriber side for searching the rows. Candidate
137-
indexes must be btree, non-partial, and the leftmost index field must be a
138-
column (not an expression) that references the published table column. These
139-
restrictions on the non-unique index properties adhere to some of the
137+
indexes must be btree or hash, non-partial, and the leftmost index field must
138+
be acolumn (not an expression) that references the published table column.
139+
Theserestrictions on the non-unique index properties adhere to some of the
140140
restrictions that are enforced for primary keys. If there are no such
141141
suitable indexes, the search on the subscriber side can be very inefficient,
142142
therefore replica identity <literal>FULL</literal> should only be used as a

‎src/backend/executor/execReplication.c

Lines changed: 50 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@
1919
#include"access/tableam.h"
2020
#include"access/transam.h"
2121
#include"access/xact.h"
22+
#include"catalog/pg_am_d.h"
2223
#include"commands/trigger.h"
2324
#include"executor/executor.h"
2425
#include"executor/nodeModifyTable.h"
@@ -41,6 +42,49 @@
4142
staticbooltuples_equal(TupleTableSlot*slot1,TupleTableSlot*slot2,
4243
TypeCacheEntry**eq);
4344

45+
/*
46+
* Returns the fixed strategy number, if any, of the equality operator for the
47+
* given index access method, otherwise, InvalidStrategy.
48+
*
49+
* Currently, only Btree and Hash indexes are supported. The other index access
50+
* methods don't have a fixed strategy for equality operation - instead, the
51+
* support routines of each operator class interpret the strategy numbers
52+
* according to the operator class's definition.
53+
*/
54+
StrategyNumber
55+
get_equal_strategy_number_for_am(Oidam)
56+
{
57+
intret;
58+
59+
switch (am)
60+
{
61+
caseBTREE_AM_OID:
62+
ret=BTEqualStrategyNumber;
63+
break;
64+
caseHASH_AM_OID:
65+
ret=HTEqualStrategyNumber;
66+
break;
67+
default:
68+
/* XXX: Only Btree and Hash indexes are supported */
69+
ret=InvalidStrategy;
70+
break;
71+
}
72+
73+
returnret;
74+
}
75+
76+
/*
77+
* Return the appropriate strategy number which corresponds to the equality
78+
* operator.
79+
*/
80+
staticStrategyNumber
81+
get_equal_strategy_number(Oidopclass)
82+
{
83+
Oidam=get_opclass_method(opclass);
84+
85+
returnget_equal_strategy_number_for_am(am);
86+
}
87+
4488
/*
4589
* Setup a ScanKey for a search in the relation 'rel' for a tuple 'key' that
4690
* is setup to match 'rel' (*NOT* idxrel!).
@@ -77,6 +121,7 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
77121
Oidopfamily;
78122
RegProcedureregop;
79123
inttable_attno=indkey->values[index_attoff];
124+
StrategyNumbereq_strategy;
80125

81126
if (!AttributeNumberIsValid(table_attno))
82127
{
@@ -93,20 +138,22 @@ build_replindex_scan_key(ScanKey skey, Relation rel, Relation idxrel,
93138
*/
94139
optype=get_opclass_input_type(opclass->values[index_attoff]);
95140
opfamily=get_opclass_family(opclass->values[index_attoff]);
141+
eq_strategy=get_equal_strategy_number(opclass->values[index_attoff]);
96142

97143
operator=get_opfamily_member(opfamily,optype,
98144
optype,
99-
BTEqualStrategyNumber);
145+
eq_strategy);
146+
100147
if (!OidIsValid(operator))
101148
elog(ERROR,"missing operator %d(%u,%u) in opfamily %u",
102-
BTEqualStrategyNumber,optype,optype,opfamily);
149+
eq_strategy,optype,optype,opfamily);
103150

104151
regop=get_opcode(operator);
105152

106153
/* Initialize the scankey. */
107154
ScanKeyInit(&skey[skey_attoff],
108155
index_attoff+1,
109-
BTEqualStrategyNumber,
156+
eq_strategy,
110157
regop,
111158
searchslot->tts_values[table_attno-1]);
112159

‎src/backend/replication/logical/relation.c

Lines changed: 41 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,9 @@
1717

1818
#include"postgres.h"
1919

20+
#ifdefUSE_ASSERT_CHECKING
21+
#include"access/amapi.h"
22+
#endif
2023
#include"access/genam.h"
2124
#include"access/table.h"
2225
#include"catalog/namespace.h"
@@ -779,7 +782,7 @@ RemoteRelContainsLeftMostColumnOnIdx(IndexInfo *indexInfo, AttrMap *attrmap)
779782

780783
/*
781784
* Returns the oid of an index that can be used by the apply worker to scan
782-
* the relation. The index must be btree, non-partial, and the leftmost
785+
* the relation. The index must be btree or hash, non-partial, and the leftmost
783786
* field must be a column (not an expression) that references the remote
784787
* relation column. These limitations help to keep the index scan similar
785788
* to PK/RI index scans.
@@ -791,11 +794,11 @@ RemoteRelContainsLeftMostColumnOnIdx(IndexInfo *indexInfo, AttrMap *attrmap)
791794
* compare the tuples for non-PK/RI index scans. See
792795
* RelationFindReplTupleByIndex().
793796
*
794-
* XXX:There are no fundamental problems for supporting non-btree indexes.
795-
*We mostly need to relax the limitations in RelationFindReplTupleByIndex().
796-
*For partial indexes, therequired changes are likely to be larger. If
797-
*none ofthetuples satisfy theexpression for the index scan, we fall-back
798-
*to sequential execution,which might not be a good idea in some cases.
797+
* XXX:See IsIndexUsableForReplicaIdentityFull() to know the challenges in
798+
*supporting indexes other than btree and hash. For partial indexes, the
799+
* required changes are likely to be larger. If none of the tuples satisfy
800+
* the expression for the index scan, we fall-back to sequential execution,
801+
* which might not be a good idea in some cases.
799802
*
800803
* We expect to call this function when REPLICA IDENTITY FULL is defined for
801804
* the remote relation.
@@ -834,15 +837,43 @@ FindUsableIndexForReplicaIdentityFull(Relation localrel, AttrMap *attrmap)
834837
/*
835838
* Returns true if the index is usable for replica identity full. For details,
836839
* see FindUsableIndexForReplicaIdentityFull.
840+
*
841+
* Currently, only Btree and Hash indexes can be returned as usable. This
842+
* is due to following reasons:
843+
*
844+
* 1) Other index access methods don't have a fixed strategy for equality
845+
* operation. Refer get_equal_strategy_number_for_am().
846+
*
847+
* 2) For indexes other than PK and REPLICA IDENTITY, we need to match the
848+
* local and remote tuples. The equality routine tuples_equal() cannot accept
849+
* a datatype (e.g. point or box) that does not have a default operator class
850+
* for Btree or Hash.
851+
*
852+
* XXX: Note that BRIN and GIN indexes do not implement "amgettuple" which
853+
* will be used later to fetch the tuples. See RelationFindReplTupleByIndex().
837854
*/
838855
bool
839856
IsIndexUsableForReplicaIdentityFull(IndexInfo*indexInfo)
840857
{
841-
boolis_btree= (indexInfo->ii_Am==BTREE_AM_OID);
842-
boolis_partial= (indexInfo->ii_Predicate!=NIL);
843-
boolis_only_on_expression=IsIndexOnlyOnExpression(indexInfo);
858+
/* Ensure that the index access method has a valid equal strategy */
859+
if (get_equal_strategy_number_for_am(indexInfo->ii_Am)==InvalidStrategy)
860+
return false;
861+
if (indexInfo->ii_Predicate!=NIL)
862+
return false;
863+
if (IsIndexOnlyOnExpression(indexInfo))
864+
return false;
865+
866+
#ifdefUSE_ASSERT_CHECKING
867+
{
868+
IndexAmRoutine*amroutine;
844869

845-
returnis_btree&& !is_partial&& !is_only_on_expression;
870+
/* The given index access method must implement amgettuple. */
871+
amroutine=GetIndexAmRoutineByAmId(indexInfo->ii_Am, false);
872+
Assert(amroutine->amgettuple!=NULL);
873+
}
874+
#endif
875+
876+
return true;
846877
}
847878

848879
/*

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1255,6 +1255,28 @@ get_opclass_opfamily_and_input_type(Oid opclass, Oid *opfamily, Oid *opcintype)
12551255
return true;
12561256
}
12571257

1258+
/*
1259+
* get_opclass_method
1260+
*
1261+
*Returns the OID of the index access method the opclass belongs to.
1262+
*/
1263+
Oid
1264+
get_opclass_method(Oidopclass)
1265+
{
1266+
HeapTupletp;
1267+
Form_pg_opclasscla_tup;
1268+
Oidresult;
1269+
1270+
tp=SearchSysCache1(CLAOID,ObjectIdGetDatum(opclass));
1271+
if (!HeapTupleIsValid(tp))
1272+
elog(ERROR,"cache lookup failed for opclass %u",opclass);
1273+
cla_tup= (Form_pg_opclass)GETSTRUCT(tp);
1274+
1275+
result=cla_tup->opcmethod;
1276+
ReleaseSysCache(tp);
1277+
returnresult;
1278+
}
1279+
12581280
/*---------- OPERATOR CACHE ---------- */
12591281

12601282
/*

‎src/include/executor/executor.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -646,6 +646,7 @@ extern void check_exclusion_constraint(Relation heap, Relation index,
646646
/*
647647
* prototypes from functions in execReplication.c
648648
*/
649+
externStrategyNumberget_equal_strategy_number_for_am(Oidam);
649650
externboolRelationFindReplTupleByIndex(Relationrel,Oididxoid,
650651
LockTupleModelockmode,
651652
TupleTableSlot*searchslot,

‎src/include/utils/lsyscache.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -106,6 +106,7 @@ extern Oidget_opclass_family(Oid opclass);
106106
externOidget_opclass_input_type(Oidopclass);
107107
externboolget_opclass_opfamily_and_input_type(Oidopclass,
108108
Oid*opfamily,Oid*opcintype);
109+
externOidget_opclass_method(Oidopclass);
109110
externRegProcedureget_opcode(Oidopno);
110111
externchar*get_opname(Oidopno);
111112
externOidget_op_rettype(Oidopno);

‎src/test/subscription/t/032_subscribe_use_index.pl

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -478,6 +478,74 @@
478478
# data
479479
# =============================================================================
480480

481+
# =============================================================================
482+
# Testcase start: Subscription can use hash index
483+
#
484+
485+
# create tables on pub and sub
486+
$node_publisher->safe_psql('postgres',
487+
"CREATE TABLE test_replica_id_full (x int, y text)");
488+
$node_publisher->safe_psql('postgres',
489+
"ALTER TABLE test_replica_id_full REPLICA IDENTITY FULL");
490+
$node_subscriber->safe_psql('postgres',
491+
"CREATE TABLE test_replica_id_full (x int, y text)");
492+
$node_subscriber->safe_psql('postgres',
493+
"CREATE INDEX test_replica_id_full_idx ON test_replica_id_full USING HASH (x)");
494+
495+
# insert some initial data
496+
$node_publisher->safe_psql('postgres',
497+
"INSERT INTO test_replica_id_full SELECT i, (i%10)::text FROM generate_series(0,10) i"
498+
);
499+
500+
# create pub/sub
501+
$node_publisher->safe_psql('postgres',
502+
"CREATE PUBLICATION tap_pub_rep_full FOR TABLE test_replica_id_full");
503+
$node_subscriber->safe_psql('postgres',
504+
"CREATE SUBSCRIPTION tap_sub_rep_full CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub_rep_full"
505+
);
506+
507+
# wait for initial table synchronization to finish
508+
$node_subscriber->wait_for_subscription_sync($node_publisher,$appname);
509+
510+
# delete 2 rows
511+
$node_publisher->safe_psql('postgres',
512+
"DELETE FROM test_replica_id_full WHERE x IN (5, 6)");
513+
514+
# update 2 rows
515+
$node_publisher->safe_psql('postgres',
516+
"UPDATE test_replica_id_full SET x = 100, y = '200' WHERE x IN (1, 2)");
517+
518+
# wait until the index is used on the subscriber
519+
$node_publisher->wait_for_catchup($appname);
520+
$node_subscriber->poll_query_until('postgres',
521+
q{select (idx_scan = 4) from pg_stat_all_indexes where indexrelname = 'test_replica_id_full_idx';}
522+
)
523+
ordie
524+
"Timed out while waiting for check subscriber tap_sub_rep_full deletes 2 rows and updates 2 rows via index";
525+
526+
# make sure that the subscriber has the correct data after the UPDATE
527+
$result =$node_subscriber->safe_psql('postgres',
528+
"select count(*) from test_replica_id_full WHERE (x = 100 and y = '200')"
529+
);
530+
is($result,qq(2),
531+
'ensure subscriber has the correct data at the end of the test');
532+
533+
# make sure that the subscriber has the correct data after the first DELETE
534+
$result =$node_subscriber->safe_psql('postgres',
535+
"select count(*) from test_replica_id_full where x in (5, 6)");
536+
is($result,qq(0),
537+
'ensure subscriber has the correct data at the end of the test');
538+
539+
# cleanup pub
540+
$node_publisher->safe_psql('postgres',"DROP PUBLICATION tap_pub_rep_full");
541+
$node_publisher->safe_psql('postgres',"DROP TABLE test_replica_id_full");
542+
# cleanup sub
543+
$node_subscriber->safe_psql('postgres',"DROP SUBSCRIPTION tap_sub_rep_full");
544+
$node_subscriber->safe_psql('postgres',"DROP TABLE test_replica_id_full");
545+
546+
# Testcase end: Subscription can use hash index
547+
# =============================================================================
548+
481549
$node_subscriber->stop('fast');
482550
$node_publisher->stop('fast');
483551

0 commit comments

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