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

Commit511e902

Browse files
committed
Make TRUNCATE ... RESTART IDENTITY restart sequences transactionally.
In the previous coding, we simply issued ALTER SEQUENCE RESTART commands,which do not roll back on error. This meant that an error betweentruncating and committing left the sequences out of sync with the tablecontents, with potentially bad consequences as were noted in a Warning onthe TRUNCATE man page.To fix, create a new storage file (relfilenode) for a sequence that is tobe reset due to RESTART IDENTITY. If the transaction aborts, we'llautomatically revert to the old storage file. This acts just like arewriting ALTER TABLE operation. A penalty is that we have to takeexclusive lock on the sequence, but since we've already got exclusive lockon its owning table, that seems unlikely to be much of a problem.The interaction of this with usual nontransactional behaviors of sequenceoperations is a bit weird, but it's hard to see what would be completelyconsistent. Our choice is to discard cached-but-unissued sequence valuesboth when the RESTART is executed, and at rollback if any; but to not touchthe currval() state either time.In passing, move the sequence reset operations to happen before not afterany AFTER TRUNCATE triggers are fired. The previous ordering was notlogically sensible, but was forced by the need to minimize inconsistencyif the triggers caused an error. Transactional rollback is a much bettersolution to that.Patch by Steve Singer, rather heavily adjusted by me.
1 parentcfad144 commit511e902

File tree

7 files changed

+203
-102
lines changed

7 files changed

+203
-102
lines changed

‎doc/src/sgml/ref/truncate.sgml

Lines changed: 28 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -108,7 +108,9 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
108108
<para>
109109
<command>TRUNCATE</> acquires an <literal>ACCESS EXCLUSIVE</> lock on each
110110
table it operates on, which blocks all other concurrent operations
111-
on the table. If concurrent access to a table is required, then
111+
on the table. When <literal>RESTART IDENTITY</> is specified, any
112+
sequences that are to be restarted are likewise locked exclusively.
113+
If concurrent access to a table is required, then
112114
the <command>DELETE</> command should be used instead.
113115
</para>
114116

@@ -130,7 +132,8 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
130132
the tables, then all <literal>BEFORE TRUNCATE</literal> triggers are
131133
fired before any truncation happens, and all <literal>AFTER
132134
TRUNCATE</literal> triggers are fired after the last truncation is
133-
performed. The triggers will fire in the order that the tables are
135+
performed and any sequences are reset.
136+
The triggers will fire in the order that the tables are
134137
to be processed (first those listed in the command, and then any
135138
that were added due to cascading).
136139
</para>
@@ -159,32 +162,21 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [,
159162
transaction does not commit.
160163
</para>
161164

162-
<warning>
163-
<para>
164-
Any <command>ALTER SEQUENCE RESTART</> operations performed as a
165-
consequence of using the <literal>RESTART IDENTITY</> option are
166-
nontransactional and will not be rolled back on failure. To minimize
167-
the risk, these operations are performed only after all the rest of
168-
<command>TRUNCATE</>'s work is done. However, there is still a risk
169-
if <command>TRUNCATE</> is performed inside a transaction block that is
170-
aborted afterwards. For example, consider
171-
172-
<programlisting>
173-
BEGIN;
174-
TRUNCATE TABLE foo RESTART IDENTITY;
175-
COPY foo FROM ...;
176-
COMMIT;
177-
</programlisting>
178-
179-
If the <command>COPY</> fails partway through, the table data
180-
rolls back correctly, but the sequences will be left with values
181-
that are probably smaller than they had before, possibly leading
182-
to duplicate-key failures or other problems in later transactions.
183-
If this is likely to be a problem, it's best to avoid using
184-
<literal>RESTART IDENTITY</>, and accept that the new contents of
185-
the table will have higher serial numbers than the old.
186-
</para>
187-
</warning>
165+
<para>
166+
When <literal>RESTART IDENTITY</> is specified, the implied
167+
<command>ALTER SEQUENCE RESTART</> operations are also done
168+
transactionally; that is, they will be rolled back if the surrounding
169+
transaction does not commit. This is unlike the normal behavior of
170+
<command>ALTER SEQUENCE RESTART</>. Be aware that if any additional
171+
sequence operations are done on the restarted sequences before the
172+
transaction rolls back, the effects of these operations on the sequences
173+
will be rolled back, but not their effects on <function>currval()</>;
174+
that is, after the transaction <function>currval()</> will continue to
175+
reflect the last sequence value obtained inside the failed transaction,
176+
even though the sequence itself may no longer be consistent with that.
177+
This is similar to the usual behavior of <function>currval()</> after
178+
a failed transaction.
179+
</para>
188180
</refsect1>
189181

190182
<refsect1>
@@ -222,13 +214,14 @@ TRUNCATE othertable CASCADE;
222214
<title>Compatibility</title>
223215

224216
<para>
225-
The SQL:2008 standard includes a <command>TRUNCATE</command> command with the syntax
226-
<literal>TRUNCATE TABLE <replaceable>tablename</replaceable></literal>.
227-
The clauses <literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
228-
also appear in that standard but have slightly different but related meanings.
229-
Some of the concurrency behavior of this command is left implementation-defined
230-
by the standard, so the above notes should be considered and compared with
231-
other implementations if necessary.
217+
The SQL:2008 standard includes a <command>TRUNCATE</command> command
218+
with the syntax <literal>TRUNCATE TABLE
219+
<replaceable>tablename</replaceable></literal>. The clauses
220+
<literal>CONTINUE IDENTITY</literal>/<literal>RESTART IDENTITY</literal>
221+
also appear in that standard, but have slightly different though related
222+
meanings. Some of the concurrency behavior of this command is left
223+
implementation-defined by the standard, so the above notes should be
224+
considered and compared with other implementations if necessary.
232225
</para>
233226
</refsect1>
234227
</refentry>

‎src/backend/commands/sequence.c

Lines changed: 117 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,7 @@ typedef struct SeqTableData
6868
{
6969
structSeqTableData*next;/* link to next SeqTable object */
7070
Oidrelid;/* pg_class OID of this sequence */
71+
Oidfilenode;/* last seen relfilenode of this sequence */
7172
LocalTransactionIdlxid;/* xact in which we last did a seq op */
7273
boollast_valid;/* do we have a valid "last" value? */
7374
int64last;/* value last returned by nextval */
@@ -87,6 +88,7 @@ static SeqTable seqtab = NULL;/* Head of list of SeqTable items */
8788
*/
8889
staticSeqTableData*last_used_seq=NULL;
8990

91+
staticvoidfill_seq_with_data(Relationrel,HeapTupletuple);
9092
staticint64nextval_internal(Oidrelid);
9193
staticRelationopen_share_lock(SeqTableseq);
9294
staticvoidinit_sequence(Oidrelid,SeqTable*p_elm,Relation*p_rel);
@@ -109,9 +111,6 @@ DefineSequence(CreateSeqStmt *seq)
109111
CreateStmt*stmt=makeNode(CreateStmt);
110112
Oidseqoid;
111113
Relationrel;
112-
Bufferbuf;
113-
Pagepage;
114-
sequence_magic*sm;
115114
HeapTupletuple;
116115
TupleDesctupDesc;
117116
Datumvalue[SEQ_COL_LASTCOL];
@@ -211,6 +210,100 @@ DefineSequence(CreateSeqStmt *seq)
211210
rel=heap_open(seqoid,AccessExclusiveLock);
212211
tupDesc=RelationGetDescr(rel);
213212

213+
/* now initialize the sequence's data */
214+
tuple=heap_form_tuple(tupDesc,value,null);
215+
fill_seq_with_data(rel,tuple);
216+
217+
/* process OWNED BY if given */
218+
if (owned_by)
219+
process_owned_by(rel,owned_by);
220+
221+
heap_close(rel,NoLock);
222+
}
223+
224+
/*
225+
* Reset a sequence to its initial value.
226+
*
227+
* The change is made transactionally, so that on failure of the current
228+
* transaction, the sequence will be restored to its previous state.
229+
* We do that by creating a whole new relfilenode for the sequence; so this
230+
* works much like the rewriting forms of ALTER TABLE.
231+
*
232+
* Caller is assumed to have acquired AccessExclusiveLock on the sequence,
233+
* which must not be released until end of transaction. Caller is also
234+
* responsible for permissions checking.
235+
*/
236+
void
237+
ResetSequence(Oidseq_relid)
238+
{
239+
Relationseq_rel;
240+
SeqTableelm;
241+
Form_pg_sequenceseq;
242+
Bufferbuf;
243+
Pagepage;
244+
HeapTupletuple;
245+
HeapTupleDatatupledata;
246+
ItemIdlp;
247+
248+
/*
249+
* Read the old sequence. This does a bit more work than really
250+
* necessary, but it's simple, and we do want to double-check that it's
251+
* indeed a sequence.
252+
*/
253+
init_sequence(seq_relid,&elm,&seq_rel);
254+
seq=read_info(elm,seq_rel,&buf);
255+
256+
/*
257+
* Copy the existing sequence tuple.
258+
*/
259+
page=BufferGetPage(buf);
260+
lp=PageGetItemId(page,FirstOffsetNumber);
261+
Assert(ItemIdIsNormal(lp));
262+
263+
tupledata.t_data= (HeapTupleHeader)PageGetItem(page,lp);
264+
tupledata.t_len=ItemIdGetLength(lp);
265+
tuple=heap_copytuple(&tupledata);
266+
267+
/* Now we're done with the old page */
268+
UnlockReleaseBuffer(buf);
269+
270+
/*
271+
* Modify the copied tuple to execute the restart (compare the RESTART
272+
* action in AlterSequence)
273+
*/
274+
seq= (Form_pg_sequence)GETSTRUCT(tuple);
275+
seq->last_value=seq->start_value;
276+
seq->is_called= false;
277+
seq->log_cnt=1;
278+
279+
/*
280+
* Create a new storage file for the sequence. We want to keep the
281+
* sequence's relfrozenxid at 0, since it won't contain any unfrozen XIDs.
282+
*/
283+
RelationSetNewRelfilenode(seq_rel,InvalidTransactionId);
284+
285+
/*
286+
* Insert the modified tuple into the new storage file.
287+
*/
288+
fill_seq_with_data(seq_rel,tuple);
289+
290+
/* Clear local cache so that we don't think we have cached numbers */
291+
/* Note that we do not change the currval() state */
292+
elm->cached=elm->last;
293+
294+
relation_close(seq_rel,NoLock);
295+
}
296+
297+
/*
298+
* Initialize a sequence's relation with the specified tuple as content
299+
*/
300+
staticvoid
301+
fill_seq_with_data(Relationrel,HeapTupletuple)
302+
{
303+
Bufferbuf;
304+
Pagepage;
305+
sequence_magic*sm;
306+
214307
/* Initialize first page of relation with special magic number */
215308

216309
buf=ReadBuffer(rel,P_NEW);
@@ -225,8 +318,7 @@ DefineSequence(CreateSeqStmt *seq)
225318
/* hack: ensure heap_insert will insert on the just-created page */
226319
RelationSetTargetBlock(rel,0);
227320

228-
/* Now form & insert sequence tuple */
229-
tuple=heap_form_tuple(tupDesc,value,null);
321+
/* Now insert sequence tuple */
230322
simple_heap_insert(rel,tuple);
231323

232324
Assert(ItemPointerGetOffsetNumber(&(tuple->t_self))==FirstOffsetNumber);
@@ -306,12 +398,6 @@ DefineSequence(CreateSeqStmt *seq)
306398
END_CRIT_SECTION();
307399

308400
UnlockReleaseBuffer(buf);
309-
310-
/* process OWNED BY if given */
311-
if (owned_by)
312-
process_owned_by(rel,owned_by);
313-
314-
heap_close(rel,NoLock);
315401
}
316402

317403
/*
@@ -323,29 +409,6 @@ void
323409
AlterSequence(AlterSeqStmt*stmt)
324410
{
325411
Oidrelid;
326-
327-
/* find sequence */
328-
relid=RangeVarGetRelid(stmt->sequence, false);
329-
330-
/* allow ALTER to sequence owner only */
331-
/* if you change this, see also callers of AlterSequenceInternal! */
332-
if (!pg_class_ownercheck(relid,GetUserId()))
333-
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CLASS,
334-
stmt->sequence->relname);
335-
336-
/* do the work */
337-
AlterSequenceInternal(relid,stmt->options);
338-
}
339-
340-
/*
341-
* AlterSequenceInternal
342-
*
343-
* Same as AlterSequence except that the sequence is specified by OID
344-
* and we assume the caller already checked permissions.
345-
*/
346-
void
347-
AlterSequenceInternal(Oidrelid,List*options)
348-
{
349412
SeqTableelm;
350413
Relationseqrel;
351414
Bufferbuf;
@@ -355,8 +418,14 @@ AlterSequenceInternal(Oid relid, List *options)
355418
List*owned_by;
356419

357420
/* open and AccessShareLock sequence */
421+
relid=RangeVarGetRelid(stmt->sequence, false);
358422
init_sequence(relid,&elm,&seqrel);
359423

424+
/* allow ALTER to sequence owner only */
425+
if (!pg_class_ownercheck(relid,GetUserId()))
426+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CLASS,
427+
stmt->sequence->relname);
428+
360429
/* lock page' buffer and read tuple into new sequence structure */
361430
seq=read_info(elm,seqrel,&buf);
362431
page=BufferGetPage(buf);
@@ -365,7 +434,7 @@ AlterSequenceInternal(Oid relid, List *options)
365434
memcpy(&new,seq,sizeof(FormData_pg_sequence));
366435

367436
/* Check and set new values */
368-
init_params(options, false,&new,&owned_by);
437+
init_params(stmt->options, false,&new,&owned_by);
369438

370439
/* Clear local cache so that we don't think we have cached numbers */
371440
/* Note that we do not change the currval() state */
@@ -937,6 +1006,7 @@ init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel)
9371006
(errcode(ERRCODE_OUT_OF_MEMORY),
9381007
errmsg("out of memory")));
9391008
elm->relid=relid;
1009+
elm->filenode=InvalidOid;
9401010
elm->lxid=InvalidLocalTransactionId;
9411011
elm->last_valid= false;
9421012
elm->last=elm->cached=elm->increment=0;
@@ -955,6 +1025,18 @@ init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel)
9551025
errmsg("\"%s\" is not a sequence",
9561026
RelationGetRelationName(seqrel))));
9571027

1028+
/*
1029+
* If the sequence has been transactionally replaced since we last saw it,
1030+
* discard any cached-but-unissued values. We do not touch the currval()
1031+
* state, however.
1032+
*/
1033+
if (seqrel->rd_rel->relfilenode!=elm->filenode)
1034+
{
1035+
elm->filenode=seqrel->rd_rel->relfilenode;
1036+
elm->cached=elm->last;
1037+
}
1038+
1039+
/* Return results */
9581040
*p_elm=elm;
9591041
*p_rel=seqrel;
9601042
}

‎src/backend/commands/tablecmds.c

Lines changed: 14 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -916,10 +916,9 @@ ExecuteTruncate(TruncateStmt *stmt)
916916

917917
/*
918918
* If we are asked to restart sequences, find all the sequences, lock them
919-
* (we only need AccessShareLock because that's all that ALTER SEQUENCE
920-
* takes), and check permissions. We want to do this early since it's
921-
* pointless to do all the truncation work only to fail on sequence
922-
* permissions.
919+
* (we need AccessExclusiveLock for ResetSequence), and check permissions.
920+
* We want to do this early since it's pointless to do all the truncation
921+
* work only to fail on sequence permissions.
923922
*/
924923
if (stmt->restart_seqs)
925924
{
@@ -934,7 +933,7 @@ ExecuteTruncate(TruncateStmt *stmt)
934933
Oidseq_relid=lfirst_oid(seqcell);
935934
Relationseq_rel;
936935

937-
seq_rel=relation_open(seq_relid,AccessShareLock);
936+
seq_rel=relation_open(seq_relid,AccessExclusiveLock);
938937

939938
/* This check must match AlterSequence! */
940939
if (!pg_class_ownercheck(seq_relid,GetUserId()))
@@ -1043,6 +1042,16 @@ ExecuteTruncate(TruncateStmt *stmt)
10431042
}
10441043
}
10451044

1045+
/*
1046+
* Restart owned sequences if we were asked to.
1047+
*/
1048+
foreach(cell,seq_relids)
1049+
{
1050+
Oidseq_relid=lfirst_oid(cell);
1051+
1052+
ResetSequence(seq_relid);
1053+
}
1054+
10461055
/*
10471056
* Process all AFTER STATEMENT TRUNCATE triggers.
10481057
*/
@@ -1067,25 +1076,6 @@ ExecuteTruncate(TruncateStmt *stmt)
10671076

10681077
heap_close(rel,NoLock);
10691078
}
1070-
1071-
/*
1072-
* Lastly, restart any owned sequences if we were asked to. This is done
1073-
* last because it's nontransactional: restarts will not roll back if we
1074-
* abort later. Hence it's important to postpone them as long as
1075-
* possible. (This is also a big reason why we locked and
1076-
* permission-checked the sequences beforehand.)
1077-
*/
1078-
if (stmt->restart_seqs)
1079-
{
1080-
List*options=list_make1(makeDefElem("restart",NULL));
1081-
1082-
foreach(cell,seq_relids)
1083-
{
1084-
Oidseq_relid=lfirst_oid(cell);
1085-
1086-
AlterSequenceInternal(seq_relid,options);
1087-
}
1088-
}
10891079
}
10901080

10911081
/*

0 commit comments

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