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

Commitf8dc198

Browse files
committed
Fix ALTER SEQUENCE locking
In1753b1b, the pg_sequence systemcatalog was introduced. This made sequence metadata changestransactional, while the actual sequence values are still behavingnontransactionally. This requires some refinement in how ALTERSEQUENCE, which operates on both, locks the sequence and the catalog.The main problems were:- Concurrent ALTER SEQUENCE causes "tuple concurrently updated" error, caused by updates to pg_sequence catalog.- Sequence WAL writes and catalog updates are not protected by same lock, which could lead to inconsistent recovery order.- nextval() disregarding uncommitted ALTER SEQUENCE changes.To fix, nextval() and friends now lock the sequence usingRowExclusiveLock instead of AccessShareLock. ALTER SEQUENCE locks thesequence using ShareRowExclusiveLock. This means that nextval() andALTER SEQUENCE block each other, and ALTER SEQUENCE on the same sequenceblocks itself. (This was already the case previously for the OWNER TO,RENAME, and SET SCHEMA variants.) Also, rearrange some code so that theentire AlterSequence is protected by the lock on the sequence.As an exception, use reduced locking for ALTER SEQUENCE ... RESTART.Since that is basically a setval(), it does not require the full lockingof other ALTER SEQUENCE actions. So check whether we are only running aRESTART and run with less locking if so.Reviewed-by: Michael Paquier <michael.paquier@gmail.com>Reported-by: Jason Petersen <jason@citusdata.com>Reported-by: Andres Freund <andres@anarazel.de>
1 parentb1c45af commitf8dc198

File tree

5 files changed

+180
-21
lines changed

5 files changed

+180
-21
lines changed

‎doc/src/sgml/ref/alter_sequence.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -304,6 +304,13 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
304304
8.3, it sometimes did.)
305305
</para>
306306

307+
<para>
308+
<command>ALTER SEQUENCE</command> blocks
309+
concurrent <function>nextval</function>, <function>currval</function>,
310+
<function>lastval</function>, and <command>setval</command> calls, except
311+
if only the <literal>RESTART</literal> clause is used.
312+
</para>
313+
307314
<para>
308315
For historical reasons, <command>ALTER TABLE</command> can be used with
309316
sequences too; but the only variants of <command>ALTER TABLE</command>

‎src/backend/commands/sequence.c

Lines changed: 48 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -93,11 +93,12 @@ static HTAB *seqhashtab = NULL; /* hash table for SeqTable items */
9393
staticSeqTableData*last_used_seq=NULL;
9494

9595
staticvoidfill_seq_with_data(Relationrel,HeapTupletuple);
96-
staticRelationopen_share_lock(SeqTableseq);
96+
staticRelationlock_and_open_sequence(SeqTableseq);
9797
staticvoidcreate_seq_hashtable(void);
9898
staticvoidinit_sequence(Oidrelid,SeqTable*p_elm,Relation*p_rel);
9999
staticForm_pg_sequence_dataread_seq_tuple(Relationrel,
100100
Buffer*buf,HeapTupleseqdatatuple);
101+
staticLOCKMODEalter_sequence_get_lock_level(List*options);
101102
staticvoidinit_params(ParseState*pstate,List*options,boolfor_identity,
102103
boolisInit,
103104
Form_pg_sequenceseqform,
@@ -427,7 +428,9 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)
427428
HeapTupletuple;
428429

429430
/* Open and lock sequence. */
430-
relid=RangeVarGetRelid(stmt->sequence,AccessShareLock,stmt->missing_ok);
431+
relid=RangeVarGetRelid(stmt->sequence,
432+
alter_sequence_get_lock_level(stmt->options),
433+
stmt->missing_ok);
431434
if (relid==InvalidOid)
432435
{
433436
ereport(NOTICE,
@@ -443,12 +446,6 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)
443446
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CLASS,
444447
stmt->sequence->relname);
445448

446-
/* lock page' buffer and read tuple into new sequence structure */
447-
seqdata=read_seq_tuple(seqrel,&buf,&seqdatatuple);
448-
449-
/* Copy old sequence data into workspace */
450-
memcpy(&newseqdata,seqdata,sizeof(FormData_pg_sequence_data));
451-
452449
rel=heap_open(SequenceRelationId,RowExclusiveLock);
453450
tuple=SearchSysCacheCopy1(SEQRELID,
454451
ObjectIdGetDatum(relid));
@@ -458,6 +455,12 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)
458455

459456
seqform= (Form_pg_sequence)GETSTRUCT(tuple);
460457

458+
/* lock page's buffer and read tuple into new sequence structure */
459+
seqdata=read_seq_tuple(seqrel,&buf,&seqdatatuple);
460+
461+
/* Copy old sequence data into workspace */
462+
memcpy(&newseqdata,seqdata,sizeof(FormData_pg_sequence_data));
463+
461464
/* Check and set new values */
462465
init_params(pstate,stmt->options,stmt->for_identity, false,seqform,&changed_seqform,&newseqdata,&owned_by);
463466

@@ -508,12 +511,12 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)
508511

509512
ObjectAddressSet(address,RelationRelationId,relid);
510513

511-
relation_close(seqrel,NoLock);
512-
513514
if (changed_seqform)
514515
CatalogTupleUpdate(rel,&tuple->t_self,tuple);
515516
heap_close(rel,RowExclusiveLock);
516517

518+
relation_close(seqrel,NoLock);
519+
517520
returnaddress;
518521
}
519522

@@ -594,7 +597,7 @@ nextval_internal(Oid relid, bool check_permissions)
594597
boolcycle;
595598
boollogit= false;
596599

597-
/* open andAccessShareLock sequence */
600+
/* open andlock sequence */
598601
init_sequence(relid,&elm,&seqrel);
599602

600603
if (check_permissions&&
@@ -829,7 +832,7 @@ currval_oid(PG_FUNCTION_ARGS)
829832
SeqTableelm;
830833
Relationseqrel;
831834

832-
/* open andAccessShareLock sequence */
835+
/* open andlock sequence */
833836
init_sequence(relid,&elm,&seqrel);
834837

835838
if (pg_class_aclcheck(elm->relid,GetUserId(),
@@ -869,7 +872,7 @@ lastval(PG_FUNCTION_ARGS)
869872
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
870873
errmsg("lastval is not yet defined in this session")));
871874

872-
seqrel=open_share_lock(last_used_seq);
875+
seqrel=lock_and_open_sequence(last_used_seq);
873876

874877
/* nextval() must have already been called for this sequence */
875878
Assert(last_used_seq->last_valid);
@@ -913,7 +916,7 @@ do_setval(Oid relid, int64 next, bool iscalled)
913916
int64maxv,
914917
minv;
915918

916-
/* open andAccessShareLock sequence */
919+
/* open andlock sequence */
917920
init_sequence(relid,&elm,&seqrel);
918921

919922
if (pg_class_aclcheck(elm->relid,GetUserId(),ACL_UPDATE)!=ACLCHECK_OK)
@@ -1042,15 +1045,15 @@ setval3_oid(PG_FUNCTION_ARGS)
10421045

10431046

10441047
/*
1045-
* Open the sequence and acquireAccessShareLock if needed
1048+
* Open the sequence and acquirelock if needed
10461049
*
10471050
* If we haven't touched the sequence already in this transaction,
1048-
* we need to acquireAccessShareLock. We arrange for the lock to
1051+
* we need to acquirea lock. We arrange for the lock to
10491052
* be owned by the top transaction, so that we don't need to do it
10501053
* more than once per xact.
10511054
*/
10521055
staticRelation
1053-
open_share_lock(SeqTableseq)
1056+
lock_and_open_sequence(SeqTableseq)
10541057
{
10551058
LocalTransactionIdthislxid=MyProc->lxid;
10561059

@@ -1063,7 +1066,7 @@ open_share_lock(SeqTable seq)
10631066
PG_TRY();
10641067
{
10651068
CurrentResourceOwner=TopTransactionResourceOwner;
1066-
LockRelationOid(seq->relid,AccessShareLock);
1069+
LockRelationOid(seq->relid,RowExclusiveLock);
10671070
}
10681071
PG_CATCH();
10691072
{
@@ -1078,7 +1081,7 @@ open_share_lock(SeqTable seq)
10781081
seq->lxid=thislxid;
10791082
}
10801083

1081-
/* We now know we haveAccessShareLock, and can safely open the rel */
1084+
/* We now know we havethe lock, and can safely open the rel */
10821085
returnrelation_open(seq->relid,NoLock);
10831086
}
10841087

@@ -1135,7 +1138,7 @@ init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel)
11351138
/*
11361139
* Open the sequence relation.
11371140
*/
1138-
seqrel=open_share_lock(elm);
1141+
seqrel=lock_and_open_sequence(elm);
11391142

11401143
if (seqrel->rd_rel->relkind!=RELKIND_SEQUENCE)
11411144
ereport(ERROR,
@@ -1216,6 +1219,30 @@ read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple)
12161219
returnseq;
12171220
}
12181221

1222+
/*
1223+
* Check the sequence options list and return the appropriate lock level for
1224+
* ALTER SEQUENCE.
1225+
*
1226+
* Most sequence option changes require a self-exclusive lock and should block
1227+
* concurrent nextval() et al. But RESTART does not, because it's not
1228+
* transactional. Also take a lower lock if no option at all is present.
1229+
*/
1230+
staticLOCKMODE
1231+
alter_sequence_get_lock_level(List*options)
1232+
{
1233+
ListCell*option;
1234+
1235+
foreach(option,options)
1236+
{
1237+
DefElem*defel= (DefElem*)lfirst(option);
1238+
1239+
if (strcmp(defel->defname,"restart")!=0)
1240+
returnShareRowExclusiveLock;
1241+
}
1242+
1243+
returnRowExclusiveLock;
1244+
}
1245+
12191246
/*
12201247
* init_params: process the options list of CREATE or ALTER SEQUENCE, and
12211248
* store the values into appropriate fields of seqform, for changes that go
@@ -1850,7 +1877,7 @@ pg_sequence_last_value(PG_FUNCTION_ARGS)
18501877
boolis_called;
18511878
int64result;
18521879

1853-
/* open andAccessShareLock sequence */
1880+
/* open andlock sequence */
18541881
init_sequence(relid,&elm,&seqrel);
18551882

18561883
if (pg_class_aclcheck(relid,GetUserId(),ACL_SELECT |ACL_USAGE)!=ACLCHECK_OK)
Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,85 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: s1alter s1commit s2nv
4+
step s1alter: ALTER SEQUENCE seq1 MAXVALUE 10;
5+
step s1commit: COMMIT;
6+
step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15);
7+
ERROR: nextval: reached maximum value of sequence "seq1" (10)
8+
9+
starting permutation: s1alter s2nv s1commit
10+
step s1alter: ALTER SEQUENCE seq1 MAXVALUE 10;
11+
step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15); <waiting ...>
12+
step s1commit: COMMIT;
13+
step s2nv: <... completed>
14+
error in steps s1commit s2nv: ERROR: nextval: reached maximum value of sequence "seq1" (10)
15+
16+
starting permutation: s2begin s2nv s1alter2 s2commit s1commit
17+
step s2begin: BEGIN;
18+
step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15);
19+
nextval
20+
21+
1
22+
2
23+
3
24+
4
25+
5
26+
6
27+
7
28+
8
29+
9
30+
10
31+
11
32+
12
33+
13
34+
14
35+
15
36+
step s1alter2: ALTER SEQUENCE seq1 MAXVALUE 20; <waiting ...>
37+
step s2commit: COMMIT;
38+
step s1alter2: <... completed>
39+
step s1commit: COMMIT;
40+
41+
starting permutation: s1restart s2nv s1commit
42+
step s1restart: ALTER SEQUENCE seq1 RESTART WITH 5;
43+
step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15);
44+
nextval
45+
46+
5
47+
6
48+
7
49+
8
50+
9
51+
10
52+
11
53+
12
54+
13
55+
14
56+
15
57+
16
58+
17
59+
18
60+
19
61+
step s1commit: COMMIT;
62+
63+
starting permutation: s2begin s2nv s1restart s2commit s1commit
64+
step s2begin: BEGIN;
65+
step s2nv: SELECT nextval('seq1') FROM generate_series(1, 15);
66+
nextval
67+
68+
1
69+
2
70+
3
71+
4
72+
5
73+
6
74+
7
75+
8
76+
9
77+
10
78+
11
79+
12
80+
13
81+
14
82+
15
83+
step s1restart: ALTER SEQUENCE seq1 RESTART WITH 5;
84+
step s2commit: COMMIT;
85+
step s1commit: COMMIT;

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@ test: alter-table-1
5858
test: alter-table-2
5959
test: alter-table-3
6060
test: create-trigger
61+
test: sequence-ddl
6162
test: async-notify
6263
test: vacuum-reltuples
6364
test: timeouts
Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
# Test sequence usage and concurrent sequence DDL
2+
3+
setup
4+
{
5+
CREATESEQUENCEseq1;
6+
}
7+
8+
teardown
9+
{
10+
DROPSEQUENCEseq1;
11+
}
12+
13+
session"s1"
14+
setup{ BEGIN;}
15+
step"s1alter"{ALTERSEQUENCEseq1MAXVALUE10;}
16+
step"s1alter2"{ALTERSEQUENCEseq1MAXVALUE20;}
17+
step"s1restart"{ALTERSEQUENCEseq1RESTARTWITH5;}
18+
step"s1commit"{COMMIT;}
19+
20+
session"s2"
21+
step"s2begin"{BEGIN;}
22+
step"s2nv"{SELECTnextval('seq1')FROMgenerate_series(1,15);}
23+
step"s2commit"{COMMIT;}
24+
25+
permutation"s1alter""s1commit""s2nv"
26+
27+
# Prior to PG10, the s2nv would see the uncommitted s1alter change,
28+
# but now it waits.
29+
permutation"s1alter""s2nv""s1commit"
30+
31+
# nextval doesn't release lock until transaction end, so s1alter2 has
32+
# to wait for s2commit.
33+
permutation"s2begin""s2nv""s1alter2""s2commit""s1commit"
34+
35+
# RESTART is nontransactional, so s2nv sees it right away
36+
permutation"s1restart""s2nv""s1commit"
37+
38+
# RESTART does not wait
39+
permutation"s2begin""s2nv""s1restart""s2commit""s1commit"

0 commit comments

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