From 10a3471bed7b57fb986a5be8afdee5f0dda419de Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 16 May 2008 23:36:05 +0000 Subject: Add a RESTART (without parameter) option to ALTER SEQUENCE, allowing a sequence to be reset to its original starting value. This requires adding the original start value to the set of parameters (columns) of a sequence object, which is a user-visible change with potential compatibility implications; it also forces initdb. Also add hopefully-SQL-compatible RESTART/CONTINUE IDENTITY options to TRUNCATE TABLE. RESTART IDENTITY executes ALTER SEQUENCE RESTART for all sequences "owned by" any of the truncated relations. CONTINUE IDENTITY is a no-op option. Zoltan Boszormenyi --- doc/src/sgml/ref/alter_sequence.sgml | 11 ++++--- doc/src/sgml/ref/truncate.sgml | 60 +++++++++++++++++++++++++++++++----- 2 files changed, 59 insertions(+), 12 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml index 3c982eee6f1..31e64dac35d 100644 --- a/doc/src/sgml/ref/alter_sequence.sgml +++ b/doc/src/sgml/ref/alter_sequence.sgml @@ -1,5 +1,5 @@ @@ -26,7 +26,7 @@ PostgreSQL documentation ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] - [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] + [ RESTART [ [ WITH ] start ] ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table.column | NONE } ] ALTER SEQUENCE name RENAME TO new_name ALTER SEQUENCE name SET SCHEMA new_schema @@ -112,12 +112,15 @@ ALTER SEQUENCE name SET SCHEMA start - The optional clause RESTART WITH start changes the + The optional clause RESTART [ WITH start ] changes the current value of the sequence. This is equivalent to calling the setval function with is_called = false: the specified value will be returned by the next call of nextval. + Writing RESTART with no start value is equivalent to supplying + the start value used when the sequence was created. diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml index 486a2d3e992..effe903b099 100644 --- a/doc/src/sgml/ref/truncate.sgml +++ b/doc/src/sgml/ref/truncate.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,8 @@ PostgreSQL documentation -TRUNCATE [ TABLE ] name [, ...] [ CASCADE | RESTRICT ] +TRUNCATE [ TABLE ] name [, ... ] + [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] @@ -50,6 +51,25 @@ TRUNCATE [ TABLE ] name [, ...] [ C + + RESTART IDENTITY + + + Automatically restart sequences owned by columns of + the truncated table(s). + + + + + + CONTINUE IDENTITY + + + Do not change the values of sequences. This is the default. + + + + CASCADE @@ -66,7 +86,7 @@ TRUNCATE [ TABLE ] name [, ...] [ C Refuse to truncate if any of the tables have foreign-key references - from tables that are not to be truncated. This is the default. + from tables that are not listed in the command. This is the default. @@ -119,11 +139,23 @@ TRUNCATE [ TABLE ] name [, ...] [ C cause visible inconsistency between the contents of the truncated table and other tables in the database. + + + + TRUNCATE is transaction-safe with respect to the data + in the tables: the truncation will be safely rolled back if the surrounding + transaction does not commit. + + - TRUNCATE is transaction-safe, however: the truncation - will be safely rolled back if the surrounding 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. To minimize risk, + these operations are performed only after all the rest of + TRUNCATE's work is done. In practice this will only + be an issue if TRUNCATE is performed inside a + transaction block that is aborted afterwards. @@ -132,13 +164,22 @@ TRUNCATE [ TABLE ] name [, ...] [ C Examples - Truncate the tables bigtable and fattable: + Truncate the tables bigtable and + fattable: TRUNCATE bigtable, fattable; + + The same, and also reset any associated sequence generators: + + +TRUNCATE bigtable, fattable RESTART IDENTITY; + + + Truncate the table othertable, and cascade to any tables that reference othertable via foreign-key @@ -154,7 +195,10 @@ TRUNCATE othertable CASCADE; Compatibility - There is no TRUNCATE command in the SQL standard. + The draft SQL:2008 standard includes a TRUNCATE command, + but at this writing it is uncertain whether that will reach standardization + or be fully compatible with PostgreSQL's + implementation. -- cgit v1.2.3