Skip to content

Commit 6743c5a

Browse files
peterepetere
committed
Fix propagation of persistence to sequences in ALTER TABLE / ADD COLUMN
Fix for 344d62f: That commit introduced unlogged sequences and made it so that identity/serial sequences automatically get the persistence level of their owning table. But this works only for CREATE TABLE and not for ALTER TABLE / ADD COLUMN. The latter would always create the sequence as logged (default), independent of the persistence setting of the table. This is fixed here. Note: It is allowed to change the persistence of identity sequences directly using ALTER SEQUENCE. So mistakes in existing databases can be 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 parent 3c44746 commit 6743c5a

File tree

3 files changed

+100
-1
lines changed

3 files changed

+100
-1
lines changed

‎src/backend/parser/parse_utilcmd.c

+10-1
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

+72
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

+18
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
ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error
216216

217+
-- check that unlogged propagates to sequence
218+
CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text);
219+
ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
220+
ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY;
221+
\d itest17
222+
\d itest17_a_seq
223+
\d itest17_c_seq
224+
CREATE TABLE itest18 (a int NOT NULL, b text);
225+
ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
226+
\d itest18
227+
\d itest18_a_seq
228+
ALTER TABLE itest18 SET LOGGED;
229+
\d itest18
230+
\d itest18_a_seq
231+
ALTER TABLE itest18 SET 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
ALTER TABLE itest3
219237
ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY,

0 commit comments

Comments
 (0)