Skip to content

Commit 10a3471

Browse files
tglsfdctglsfdc
committed
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 parent 8a2f5d2 commit 10a3471

File tree

18 files changed

+513
-106
lines changed

18 files changed

+513
-106
lines changed

‎doc/src/sgml/ref/alter_sequence.sgml

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.19 2007/10/25 18:54:03 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.20 2008/05/16 23:36:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -26,7 +26,7 @@ PostgreSQL documentation
2626
<synopsis>
2727
ALTER SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
2828
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
29-
[ RESTART [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
29+
[ RESTART [ [ WITH ] <replaceable class="parameter">start</replaceable> ] ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
3030
[ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
3131
ALTER SEQUENCE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
3232
ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
@@ -112,12 +112,15 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <rep
112112
<term><replaceable class="parameter">start</replaceable></term>
113113
<listitem>
114114
<para>
115-
The optional clause <literal>RESTART WITH <replaceable
116-
class="parameter">start</replaceable></literal> changes the
115+
The optional clause <literal>RESTART [ WITH <replaceable
116+
class="parameter">start</replaceable> ]</literal> changes the
117117
current value of the sequence. This is equivalent to calling the
118118
<function>setval</> function with <literal>is_called</literal> =
119119
<literal>false</>: the specified value will be returned by the
120120
<emphasis>next</> call of <function>nextval</>.
121+
Writing <literal>RESTART</> with no <replaceable
122+
class="parameter">start</replaceable> value is equivalent to supplying
123+
the start value used when the sequence was created.
121124
</para>
122125
</listitem>
123126
</varlistentry>

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

Lines changed: 52 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.25 2008/03/28 00:21:55 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.26 2008/05/16 23:36:04 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,8 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
23+
TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
24+
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
2425
</synopsis>
2526
</refsynopsisdiv>
2627

@@ -50,6 +51,25 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
5051
</listitem>
5152
</varlistentry>
5253

54+
<varlistentry>
55+
<term><literal>RESTART IDENTITY</literal></term>
56+
<listitem>
57+
<para>
58+
Automatically restart sequences owned by columns of
59+
the truncated table(s).
60+
</para>
61+
</listitem>
62+
</varlistentry>
63+
64+
<varlistentry>
65+
<term><literal>CONTINUE IDENTITY</literal></term>
66+
<listitem>
67+
<para>
68+
Do not change the values of sequences. This is the default.
69+
</para>
70+
</listitem>
71+
</varlistentry>
72+
5373
<varlistentry>
5474
<term><literal>CASCADE</literal></term>
5575
<listitem>
@@ -66,7 +86,7 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
6686
<listitem>
6787
<para>
6888
Refuse to truncate if any of the tables have foreign-key references
69-
from tables that are not to be truncated. This is the default.
89+
from tables that are not listed in the command. This is the default.
7090
</para>
7191
</listitem>
7292
</varlistentry>
@@ -119,11 +139,23 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
119139
cause visible inconsistency between the contents of the truncated
120140
table and other tables in the database.
121141
</para>
142+
</warning>
143+
144+
<para>
145+
<command>TRUNCATE</> is transaction-safe with respect to the data
146+
in the tables: the truncation will be safely rolled back if the surrounding
147+
transaction does not commit.
148+
</para>
122149

150+
<warning>
123151
<para>
124-
<command>TRUNCATE</> is transaction-safe, however: the truncation
125-
will be safely rolled back if the surrounding transaction does not
126-
commit.
152+
Any <command>ALTER SEQUENCE RESTART</> operations performed as a
153+
consequence of using the <literal>RESTART IDENTITY</> option are
154+
nontransactional and will not be rolled back. To minimize risk,
155+
these operations are performed only after all the rest of
156+
<command>TRUNCATE</>'s work is done. In practice this will only
157+
be an issue if <command>TRUNCATE</> is performed inside a
158+
transaction block that is aborted afterwards.
127159
</para>
128160
</warning>
129161
</refsect1>
@@ -132,13 +164,22 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
132164
<title>Examples</title>
133165

134166
<para>
135-
Truncate the tables <literal>bigtable</literal> and <literal>fattable</literal>:
167+
Truncate the tables <literal>bigtable</literal> and
168+
<literal>fattable</literal>:
136169

137170
<programlisting>
138171
TRUNCATE bigtable, fattable;
139172
</programlisting>
140173
</para>
141174

175+
<para>
176+
The same, and also reset any associated sequence generators:
177+
178+
<programlisting>
179+
TRUNCATE bigtable, fattable RESTART IDENTITY;
180+
</programlisting>
181+
</para>
182+
142183
<para>
143184
Truncate the table <literal>othertable</literal>, and cascade to any tables
144185
that reference <literal>othertable</literal> via foreign-key
@@ -154,7 +195,10 @@ TRUNCATE othertable CASCADE;
154195
<title>Compatibility</title>
155196

156197
<para>
157-
There is no <command>TRUNCATE</command> command in the SQL standard.
198+
The draft SQL:2008 standard includes a <command>TRUNCATE</command> command,
199+
but at this writing it is uncertain whether that will reach standardization
200+
or be fully compatible with <productname>PostgreSQL</productname>'s
201+
implementation.
158202
</para>
159203
</refsect1>
160204
</refentry>

‎src/backend/catalog/pg_depend.c

Lines changed: 53 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_depend.c,v 1.27 2008/03/26 21:10:37 alvherre Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_depend.c,v 1.28 2008/05/16 23:36:04 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -418,6 +418,58 @@ markSequenceUnowned(Oid seqId)
418418
heap_close(depRel, RowExclusiveLock);
419419
}
420420

421+
/*
422+
* Collect a list of OIDs of all sequences owned by the specified relation.
423+
*/
424+
List *
425+
getOwnedSequences(Oid relid)
426+
{
427+
List *result = NIL;
428+
Relation depRel;
429+
ScanKeyData key[2];
430+
SysScanDesc scan;
431+
HeapTuple tup;
432+
433+
depRel = heap_open(DependRelationId, AccessShareLock);
434+
435+
ScanKeyInit(&key[0],
436+
Anum_pg_depend_refclassid,
437+
BTEqualStrategyNumber, F_OIDEQ,
438+
ObjectIdGetDatum(RelationRelationId));
439+
ScanKeyInit(&key[1],
440+
Anum_pg_depend_refobjid,
441+
BTEqualStrategyNumber, F_OIDEQ,
442+
ObjectIdGetDatum(relid));
443+
444+
scan = systable_beginscan(depRel, DependReferenceIndexId, true,
445+
SnapshotNow, 2, key);
446+
447+
while (HeapTupleIsValid(tup = systable_getnext(scan)))
448+
{
449+
Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
450+
451+
/*
452+
* We assume any auto dependency of a sequence on a column must be
453+
* what we are looking for. (We need the relkind test because indexes
454+
* can also have auto dependencies on columns.)
455+
*/
456+
if (deprec->classid == RelationRelationId &&
457+
deprec->objsubid == 0 &&
458+
deprec->refobjsubid != 0 &&
459+
deprec->deptype == DEPENDENCY_AUTO &&
460+
get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE)
461+
{
462+
result = lappend_oid(result, deprec->objid);
463+
}
464+
}
465+
466+
systable_endscan(scan);
467+
468+
heap_close(depRel, AccessShareLock);
469+
470+
return result;
471+
}
472+
421473

422474
/*
423475
* get_constraint_index

0 commit comments

Comments
 (0)