Skip to content

Commit 511e902

Browse files
tglsfdctglsfdc
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 between truncating and committing left the sequences out of sync with the table contents, with potentially bad consequences as were noted in a Warning on the TRUNCATE man page. To fix, create a new storage file (relfilenode) for a sequence that is to be reset due to RESTART IDENTITY. If the transaction aborts, we'll automatically revert to the old storage file. This acts just like a rewriting ALTER TABLE operation. A penalty is that we have to take exclusive lock on the sequence, but since we've already got exclusive lock on its owning table, that seems unlikely to be much of a problem. The interaction of this with usual nontransactional behaviors of sequence operations is a bit weird, but it's hard to see what would be completely consistent. Our choice is to discard cached-but-unissued sequence values both when the RESTART is executed, and at rollback if any; but to not touch the currval() state either time. In passing, move the sequence reset operations to happen before not after any AFTER TRUNCATE triggers are fired. The previous ordering was not logically sensible, but was forced by the need to minimize inconsistency if the triggers caused an error. Transactional rollback is a much better solution to that. Patch by Steve Singer, rather heavily adjusted by me.
1 parent cfad144 commit 511e902

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
struct SeqTableData *next; /* link to next SeqTable object */
7070
Oid relid; /* pg_class OID of this sequence */
71+
Oid filenode; /* last seen relfilenode of this sequence */
7172
LocalTransactionId lxid; /* xact in which we last did a seq op */
7273
bool last_valid; /* do we have a valid "last" value? */
7374
int64 last; /* value last returned by nextval */
@@ -87,6 +88,7 @@ static SeqTable seqtab = NULL; /* Head of list of SeqTable items */
8788
*/
8889
static SeqTableData *last_used_seq = NULL;
8990

91+
static void fill_seq_with_data(Relation rel, HeapTuple tuple);
9092
static int64 nextval_internal(Oid relid);
9193
static Relation open_share_lock(SeqTable seq);
9294
static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
@@ -109,9 +111,6 @@ DefineSequence(CreateSeqStmt *seq)
109111
CreateStmt *stmt = makeNode(CreateStmt);
110112
Oid seqoid;
111113
Relation rel;
112-
Buffer buf;
113-
Page page;
114-
sequence_magic *sm;
115114
HeapTuple tuple;
116115
TupleDesc tupDesc;
117116
Datum value[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(Oid seq_relid)
238+
{
239+
Relation seq_rel;
240+
SeqTable elm;
241+
Form_pg_sequence seq;
242+
Buffer buf;
243+
Page page;
244+
HeapTuple tuple;
245+
HeapTupleData tupledata;
246+
ItemId lp;
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+
static void
301+
fill_seq_with_data(Relation rel, HeapTuple tuple)
302+
{
303+
Buffer buf;
304+
Page page;
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
Oid relid;
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(Oid relid, List *options)
348-
{
349412
SeqTable elm;
350413
Relation seqrel;
351414
Buffer buf;
@@ -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
Oid seq_relid = lfirst_oid(seqcell);
935934
Relation seq_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+
Oid seq_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-
Oid seq_relid = lfirst_oid(cell);
1085-
1086-
AlterSequenceInternal(seq_relid, options);
1087-
}
1088-
}
10891079
}
10901080

10911081
/*

0 commit comments

Comments
 (0)