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

Commit6743c5a

Browse files
committed
Fix propagation of persistence to sequences in ALTER TABLE / ADD COLUMN
Fix for344d62f: That commit introduced unlogged sequences andmade it so that identity/serial sequences automatically get thepersistence level of their owning table. But this works only forCREATE TABLE and not for ALTER TABLE / ADD COLUMN. The latter wouldalways create the sequence as logged (default), independent of thepersistence setting of the table. This is fixed here.Note: It is allowed to change the persistence of identity sequencesdirectly using ALTER SEQUENCE. So mistakes in existing databases canbe fixed manually.Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/c4b6e2ed-bcdf-4ea7-965f-e49761094827%40eisentraut.org
1 parent3c44746 commit6743c5a

File tree

3 files changed

+100
-1
lines changed

3 files changed

+100
-1
lines changed

‎src/backend/parser/parse_utilcmd.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -459,7 +459,16 @@ generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column,
459459
seqstmt=makeNode(CreateSeqStmt);
460460
seqstmt->for_identity=for_identity;
461461
seqstmt->sequence=makeRangeVar(snamespace,sname,-1);
462-
seqstmt->sequence->relpersistence=cxt->relation->relpersistence;
462+
463+
/*
464+
* Copy the persistence of the table. For CREATE TABLE, we get the
465+
* persistence from cxt->relation, which comes from the CreateStmt in
466+
* progress. For ALTER TABLE, the parser won't set
467+
* cxt->relation->relpersistence, but we have cxt->rel as the existing
468+
* table, so we copy the persistence from there.
469+
*/
470+
seqstmt->sequence->relpersistence=cxt->rel ?cxt->rel->rd_rel->relpersistence :cxt->relation->relpersistence;
471+
463472
seqstmt->options=seqoptions;
464473

465474
/*

‎src/test/regress/expected/identity.out

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -365,6 +365,78 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl
365365

366366
ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error
367367
ERROR: identity column type must be smallint, integer, or bigint
368+
-- check that unlogged propagates to sequence
369+
CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text);
370+
ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
371+
ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY;
372+
\d itest17
373+
Unlogged table "public.itest17"
374+
Column | Type | Collation | Nullable | Default
375+
--------+---------+-----------+----------+------------------------------
376+
a | integer | | not null | generated always as identity
377+
b | text | | |
378+
c | integer | | not null | generated always as identity
379+
380+
\d itest17_a_seq
381+
Unlogged sequence "public.itest17_a_seq"
382+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
383+
---------+-------+---------+------------+-----------+---------+-------
384+
integer | 1 | 1 | 2147483647 | 1 | no | 1
385+
Sequence for identity column: public.itest17.a
386+
387+
\d itest17_c_seq
388+
Unlogged sequence "public.itest17_c_seq"
389+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
390+
---------+-------+---------+------------+-----------+---------+-------
391+
integer | 1 | 1 | 2147483647 | 1 | no | 1
392+
Sequence for identity column: public.itest17.c
393+
394+
CREATE TABLE itest18 (a int NOT NULL, b text);
395+
ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
396+
\d itest18
397+
Unlogged table "public.itest18"
398+
Column | Type | Collation | Nullable | Default
399+
--------+---------+-----------+----------+------------------------------
400+
a | integer | | not null | generated always as identity
401+
b | text | | |
402+
403+
\d itest18_a_seq
404+
Unlogged sequence "public.itest18_a_seq"
405+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
406+
---------+-------+---------+------------+-----------+---------+-------
407+
integer | 1 | 1 | 2147483647 | 1 | no | 1
408+
Sequence for identity column: public.itest18.a
409+
410+
ALTER TABLE itest18 SET LOGGED;
411+
\d itest18
412+
Table "public.itest18"
413+
Column | Type | Collation | Nullable | Default
414+
--------+---------+-----------+----------+------------------------------
415+
a | integer | | not null | generated always as identity
416+
b | text | | |
417+
418+
\d itest18_a_seq
419+
Sequence "public.itest18_a_seq"
420+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
421+
---------+-------+---------+------------+-----------+---------+-------
422+
integer | 1 | 1 | 2147483647 | 1 | no | 1
423+
Sequence for identity column: public.itest18.a
424+
425+
ALTER TABLE itest18 SET UNLOGGED;
426+
\d itest18
427+
Unlogged table "public.itest18"
428+
Column | Type | Collation | Nullable | Default
429+
--------+---------+-----------+----------+------------------------------
430+
a | integer | | not null | generated always as identity
431+
b | text | | |
432+
433+
\d itest18_a_seq
434+
Unlogged sequence "public.itest18_a_seq"
435+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
436+
---------+-------+---------+------------+-----------+---------+-------
437+
integer | 1 | 1 | 2147483647 | 1 | no | 1
438+
Sequence for identity column: public.itest18.a
439+
368440
-- kinda silly to change property in the same command, but it should work
369441
ALTER TABLE itest3
370442
ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY,

‎src/test/regress/sql/identity.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -214,6 +214,24 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl
214214

215215
ALTERTABLE itest3 ALTER COLUMN a TYPEtext;-- error
216216

217+
-- check that unlogged propagates to sequence
218+
CREATE UNLOGGED TABLE itest17 (aintNOT NULL, btext);
219+
ALTERTABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYSAS IDENTITY;
220+
ALTERTABLE itest17 ADD COLUMN cint GENERATED ALWAYSAS IDENTITY;
221+
\d itest17
222+
\d itest17_a_seq
223+
\d itest17_c_seq
224+
CREATETABLEitest18 (aintNOT NULL, btext);
225+
ALTERTABLE itest18SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYSAS IDENTITY;
226+
\d itest18
227+
\d itest18_a_seq
228+
ALTERTABLE itest18SET LOGGED;
229+
\d itest18
230+
\d itest18_a_seq
231+
ALTERTABLE itest18SET UNLOGGED;
232+
\d itest18
233+
\d itest18_a_seq
234+
217235
-- kinda silly to change property in the same command, but it should work
218236
ALTERTABLE itest3
219237
ADD COLUMN cint GENERATED BY DEFAULTAS IDENTITY,

0 commit comments

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