Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit10a3471

Browse files
committed
Add a RESTART (without parameter) option to ALTER SEQUENCE, allowing a
sequence to be reset to its original starting value. This requires adding theoriginal 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 toTRUNCATE TABLE. RESTART IDENTITY executes ALTER SEQUENCE RESTART for allsequences "owned by" any of the truncated relations. CONTINUE IDENTITY isa no-op option.Zoltan Boszormenyi
1 parent8a2f5d2 commit10a3471

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 notto be truncated. This is the default.
89+
from tables that are notlisted 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(Oidrelid)
426+
{
427+
List*result=NIL;
428+
RelationdepRel;
429+
ScanKeyDatakey[2];
430+
SysScanDescscan;
431+
HeapTupletup;
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_dependdeprec= (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+
returnresult;
471+
}
472+
421473

422474
/*
423475
* get_constraint_index

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp