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

1 files changed, 26 insertions, 0 deletions

diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index e60349e207..3cce3ee314 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -130,3 +130,29 @@ DROP TABLE trunc_trigger_test;
 DROP TABLE trunc_trigger_log;
 
 DROP FUNCTION trunctrigger();
+
+-- test TRUNCATE ... RESTART IDENTITY
+CREATE SEQUENCE truncate_a_id1 START WITH 33;
+CREATE TABLE truncate_a (id serial,
+                         id1 integer default nextval('truncate_a_id1'));
+ALTER SEQUENCE truncate_a_id1 OWNED BY truncate_a.id1;
+
+INSERT INTO truncate_a DEFAULT VALUES;
+INSERT INTO truncate_a DEFAULT VALUES;
+SELECT * FROM truncate_a;
+
+TRUNCATE truncate_a;
+
+INSERT INTO truncate_a DEFAULT VALUES;
+INSERT INTO truncate_a DEFAULT VALUES;
+SELECT * FROM truncate_a;
+
+TRUNCATE truncate_a RESTART IDENTITY;
+
+INSERT INTO truncate_a DEFAULT VALUES;
+INSERT INTO truncate_a DEFAULT VALUES;
+SELECT * FROM truncate_a;
+
+DROP TABLE truncate_a;
+
+SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped