8000 ALTER INDEX fix locking issues · postgrespro/postgres_cluster@5fe774c · GitHub
[go: up one dir, main page]
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
Appearance settings

Commit 5fe774c

Browse files
knizhnikknizhnik
committed
ALTER INDEX fix locking issues
1 parent a4d897e commit 5fe774c

File tree

4 files changed

+55
-16
lines changed

4 files changed

+55
-16
lines changed

‎src/backend/commands/indexcmds.c

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -304,13 +304,15 @@ AlterIndex(Oid indexRelationId, IndexStmt *stmt)
304304
Assert(stmt->whereClause);
305305
CheckPredicate((Expr *) stmt->whereClause);
306306

307-
/* Open the target index relation */
308-
indexRelation = index_open(indexRelationId, RowExclusiveLock);
309-
namespaceId = RelationGetNamespace(indexRelation);
310-
311307
/* Open and lock the parent heap relation */
312308
heapRelationId = IndexGetRelation(indexRelationId, false);
313-
heapRelation = heap_open(heapRelationId, ShareLock);
309+
heapRelation = heap_open(heapRelationId, AccessShareLock);
310+
311+
/* Open the target index relation */
312+
/* indexRelation = index_open(indexRelationId, RowExclusiveLock); */
313+
//indexRelation = index_open(indexRelationId, ShareUpdateExclusiveLock);
314+
indexRelation = index_open(indexRelationId, AccessShareLock);
315+
namespaceId = RelationGetNamespace(indexRelation);
314316

315317
pg_index = heap_open(IndexRelationId, RowExclusiveLock);
316318

@@ -338,6 +340,7 @@ AlterIndex(Oid indexRelationId, IndexStmt *stmt)
338340
CatalogUpdateIndexes(pg_index, updatedTuple);
339341
heap_freetuple(updatedTuple);
340342
heap_freetuple(tuple);
343+
heap_close(pg_index, NoLock);
341344

342345
slot = MakeSingleTupleTableSlot(RelationGetDescr(heapRelation));
343346

@@ -391,7 +394,6 @@ AlterIndex(Oid indexRelationId, IndexStmt *stmt)
391394
ExecDropSingleTupleTableSlot(slot);
392395
FreeExecutorState(estate);
393396

394-
heap_close(pg_index, NoLock);
395397
heap_close(heapRelation, NoLock);
396398
index_close(indexRelation, NoLock);
397399
}

‎src/backend/tcop/utility.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1244,7 +1244,7 @@ ProcessUtilitySlow(Node *parsetree,
12441244
* eventually be needed here, so the lockmode calculation
12451245
* needs to match what DefineIndex() does.
12461246
*/
1247-
lockmode = stmt->concurrent ? ShareUpdateExclusiveLock
1247+
lockmode = stmt->is_alter ? AccessShareLock : stmt->concurrent ? ShareUpdateExclusiveLock
12481248
: ShareLock;
12491249
relid =
12501250
RangeVarGetRelidExtended(stmt->relation, lockmode,

‎src/bin/insbench/insbench.cpp

Lines changed: 45 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
#include <inttypes.h>
66
#include <sys/time.h>
77
#include <pthread.h>
8+
#include <unistd.h>
89

910
#include <string>
1011
#include <vector>
@@ -13,6 +14,7 @@
1314
#include <pqxx/transaction>
1415
#include <pqxx/nontransaction>
1516
#include <pqxx/pipeline>
17+
#include <pqxx/version>
1618

1719
using namespace std;
1820
using namespace pqxx;
@@ -39,6 +41,8 @@ struct config
3941
int nIndexes;
4042
int nIterations;
4143
int transactionSize;
44+
bool useSystemTime;
45+
bool noPK;
4246
string connection;
4347

4448
config() {
@@ -47,6 +51,8 @@ struct config
4751
nIndexes = 8;
4852
nIterations = 10000;
4953
transactionSize = 100;
54+
useSystemTime = false;
55+
noPK = false;
5056
}
5157
};
5258

@@ -55,6 +61,7 @@ bool running;
5561
int nIndexUpdates;
5662
time_t maxIndexUpdateTime;
5763
time_t totalIndexUpdateTime;
64+
time_t currTimestamp;
5865

5966
#define USEC 1000000
6067

@@ -79,14 +86,30 @@ void exec(transaction_base& txn, char const* sql, ...)
7986
void* inserter(void* arg)
8087
{
8188
connection con(cfg.connection);
82-
con.prepare("insert", "insert into t values ($1,$2,$3,$4,$5,$6,$7,$8,$9)")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint");
89+
if (cfg.useSystemTime)
90+
{
91+
#if PQXX_VERSION_MAJOR >= 4
92+
con.prepare("insert", "insert into t values ($1,$2,$3,$4,$5,$6,$7,$8,$9)");
93+
#else
94+
con.prepare("insert", "insert into t values ($1,$2,$3,$4,$5,$6,$7,$8,$9)")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint")("bigint");
95+
#endif
96+
} else {
97+
con.prepare("insert", "insert into t (select generate_series($1::integer,$2::integer),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random()*1000000000),ceil(random() F438 *1000000000),ceil(random()*1000000000),ceil(random()*1000000000))");
98+
}
99+
83100
for (int i = 0; i < cfg.nIterations; i++)
84101
{
85102
work txn(con);
86-
for (int j = 0; j < cfg.transactionSize; j++)
87-
{
88-
txn.prepared("insert")(getCurrentTime())(random())(random())(random())(random())(random())(random())(random())(random()).exec();
89-
}
103+
if (cfg.useSystemTime)
104+
{
105+
for (int j = 0; j < cfg.transactionSize; j++)
106+
{
107+
txn.prepared("insert")(getCurrentTime())(random())(random())(random())(random())(random())(random())(random())(random()).exec();
108+
}
109+
} else {
110+
currTimestamp = i*cfg.transactionSize;
111+
txn.prepared("insert")(i*cfg.transactionSize)((i+1)*cfg.transactionSize-1).exec();
112+
}
90113
txn.commit();
91114
}
92115
return NULL;
@@ -97,14 +120,16 @@ void* indexUpdater(void* arg)
97120
connection con(cfg.connection);
98121
while (running) {
99122
sleep(cfg.indexUpdateInterval);
123+
printf("Alter indexes\n");
100124
time_t now = getCurrentTime();
101125
{
102126
work txn(con);
103127
for (int i = 0; i < cfg.nIndexes; i++) {
104-
exec(txn, "alter index idx%d where pk<%lu", i, now);
128+
exec(txn, "alter index idx%d where pk<%lu", i, cfg.useSystemTime ? now : currTimestamp);
105129
}
106130
txn.commit();
107131
}
132+
printf("End alter indexes\n");
108133
nIndexUpdates += 1;
109134
time_t elapsed = getCurrentTime() - now;
110135
totalIndexUpdateTime += elapsed;
@@ -122,12 +147,16 @@ void initializeDatabase()
122147
time_t now = getCurrentTime();
123148
exec(txn, "drop table if exists t");
124149
exec(txn, &quo 10000 t;create table t (pk bigint, k1 bigint, k2 bigint, k3 bigint, k4 bigint, k5 bigint, k6 bigint, k7 bigint, k8 bigint)");
125-
exec(txn, "create index pk on t(pk)");
150+
if (!cfg.noPK) {
151+
exec(txn, "create index pk on t(pk)");
152+
}
126153
for (int i = 0; i < cfg.nIndexes; i++) {
127154
if (cfg.indexUpdateInterval == 0) {
128155
exec(txn, "create index idx%d on t(k%d)", i, i+1);
129-
} else {
156+
} else if (cfg.useSystemTime) {
130157
exec(txn, "create index idx%d on t(k%d) where pk<%ld", i, i+1, now);
158+
} else {
159+
exec(txn, "create index idx%d on t(k%d) where pk<%ld", i, i+1, 0);
131160
}
132161
}
133162
txn.commit();
@@ -162,6 +191,12 @@ int main (int argc, char* argv[])
162191
case 'c':
163192
cfg.connection = string(argv[++i]);
164193
continue;
194+
case 'q':
195+
cfg.useSystemTime = true;
196+
continue;
197+
case 'p':
198+
cfg.noPK = true;
199+
continue;
165200
}
166201
}
167202
printf("Options:\n"
@@ -170,6 +205,8 @@ int main (int argc, char* argv[])
170205
"\t-u N\tindex update interval (0)\n"
171206
"\t-n N\tnumber of iterations (10000)\n"
172207
"\t-i N\tnumber of indexes (8)\n"
208+
"\t-q\tuse system time and libpq\n"
209+
"\t-p\tno primary key\n"
173210
"\t-c STR\tdatabase connection string\n");
174211
return 1;
175212
}

‎src/bin/insbench/makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,4 +7,4 @@ insbench: insbench.cpp
77
$(CXX) $(CXXFLAGS) -o insbench insbench.cpp -lpqxx
88

99
clean:
10-
rm -f insbench
10+
rm -f insbench

0 commit comments

Comments
 (0)
0