From 511e902b51c2a1c0d012426ceb6486b1202120f3 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 17 Nov 2010 16:42:18 -0500 Subject: 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. --- doc/src/sgml/ref/truncate.sgml | 63 +++++++++++++++++++----------------------- 1 file changed, 28 insertions(+), 35 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml index f32d255c74b..9f12ca4b3b3 100644 --- a/doc/src/sgml/ref/truncate.sgml +++ b/doc/src/sgml/ref/truncate.sgml @@ -108,7 +108,9 @@ TRUNCATE [ TABLE ] [ ONLY ] name [, TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on, which blocks all other concurrent operations - on the table. If concurrent access to a table is required, then + on the table. When RESTART IDENTITY is specified, any + sequences that are to be restarted are likewise locked exclusively. + If concurrent access to a table is required, then the DELETE command should be used instead. @@ -130,7 +132,8 @@ TRUNCATE [ TABLE ] [ ONLY ] name [, the tables, then all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation is - performed. The triggers will fire in the order that the tables are + performed and any sequences are reset. + The triggers will fire in the order that the tables are to be processed (first those listed in the command, and then any that were added due to cascading). @@ -159,32 +162,21 @@ TRUNCATE [ TABLE ] [ ONLY ] name [, transaction does not commit. - - - Any ALTER SEQUENCE RESTART operations performed as a - consequence of using the RESTART IDENTITY option are - nontransactional and will not be rolled back on failure. To minimize - the risk, these operations are performed only after all the rest of - TRUNCATE's work is done. However, there is still a risk - if TRUNCATE is performed inside a transaction block that is - aborted afterwards. For example, consider - - -BEGIN; -TRUNCATE TABLE foo RESTART IDENTITY; -COPY foo FROM ...; -COMMIT; - - - If the COPY fails partway through, the table data - rolls back correctly, but the sequences will be left with values - that are probably smaller than they had before, possibly leading - to duplicate-key failures or other problems in later transactions. - If this is likely to be a problem, it's best to avoid using - RESTART IDENTITY, and accept that the new contents of - the table will have higher serial numbers than the old. - - + + When RESTART IDENTITY is specified, the implied + ALTER SEQUENCE RESTART operations are also done + transactionally; that is, they will be rolled back if the surrounding + transaction does not commit. This is unlike the normal behavior of + ALTER SEQUENCE RESTART. Be aware that if any additional + sequence operations are done on the restarted sequences before the + transaction rolls back, the effects of these operations on the sequences + will be rolled back, but not their effects on currval(); + that is, after the transaction currval() will continue to + reflect the last sequence value obtained inside the failed transaction, + even though the sequence itself may no longer be consistent with that. + This is similar to the usual behavior of currval() after + a failed transaction. + @@ -222,13 +214,14 @@ TRUNCATE othertable CASCADE; Compatibility - The SQL:2008 standard includes a TRUNCATE command with the syntax - TRUNCATE TABLE tablename. - The clauses CONTINUE IDENTITY/RESTART IDENTITY - also appear in that standard but have slightly different but related meanings. - Some of the concurrency behavior of this command is left implementation-defined - by the standard, so the above notes should be considered and compared with - other implementations if necessary. + The SQL:2008 standard includes a TRUNCATE command + with the syntax TRUNCATE TABLE + tablename. The clauses + CONTINUE IDENTITY/RESTART IDENTITY + also appear in that standard, but have slightly different though related + meanings. Some of the concurrency behavior of this command is left + implementation-defined by the standard, so the above notes should be + considered and compared with other implementations if necessary. -- cgit v1.2.3