Skip to content

Commit 344d62f

Browse files
peterepetere
committed
Unlogged sequences
Add support for unlogged sequences. Unlike for unlogged tables, this is not a performance feature. It allows sequences associated with unlogged tables to be excluded from replication. A new subcommand ALTER SEQUENCE ... SET LOGGED/UNLOGGED is added. An identity/serial sequence now automatically gets and follows the persistence level (logged/unlogged) of its owning table. (The sequences owned by temporary tables were already temporary through the separate mechanism in RangeVarAdjustRelationPersistence().) But you can still change the persistence of an owned sequence separately. Also, pg_dump and pg_upgrade preserve the persistence of existing sequences. Discussion: https://www.postgresql.org/message-id/flat/04e12818-2f98-257c-b926-2845d74ed04f%402ndquadrant.com
1 parent bab588c commit 344d62f

File tree

16 files changed

+237
-31
lines changed

16 files changed

+237
-31
lines changed

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

+12
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3131
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
3232
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
3333
[ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
34+
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET { LOGGED | UNLOGGED }
3435
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
3536
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
3637
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
@@ -237,6 +238,17 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
237238
</listitem>
238239
</varlistentry>
239240

241+
<varlistentry>
242+
<term><literal>SET { LOGGED | UNLOGGED }</literal></term>
243+
<listitem>
244+
<para>
245+
This form changes the sequence from unlogged to logged or vice-versa
246+
(see <xref linkend="sql-createsequence"/>). It cannot be applied to a
247+
temporary sequence.
248+
</para>
249+
</listitem>
250+
</varlistentry>
251+
240252
<varlistentry>
241253
<term><literal>OWNED BY</literal> <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable></term>
242254
<term><literal>OWNED BY NONE</literal></term>

‎doc/src/sgml/ref/alter_table.sgml

+6
Original file line numberDiff line numberDiff line change
@@ -753,6 +753,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
753753
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
754754
to a temporary table.
755755
</para>
756+
757+
<para>
758+
This also changes the persistence of any sequences linked to the table
759+
(for identity or serial columns). However, it is also possible to
760+
change the persistence of such sequences separately.
761+
</para>
756762
</listitem>
757763
</varlistentry>
758764

‎doc/src/sgml/ref/create_sequence.sgml

+22-1
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
24+
CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
2525
[ AS <replaceable class="parameter">data_type</replaceable> ]
2626
[ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
2727
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
@@ -92,6 +92,27 @@ SELECT * FROM <replaceable>name</replaceable>;
9292
</listitem>
9393
</varlistentry>
9494

95+
<varlistentry>
96+
<term><literal>UNLOGGED</literal></term>
97+
<listitem>
98+
<para>
99+
If specified, the sequence is created as an unlogged sequence. Changes
100+
to unlogged sequences are not written to the write-ahead log. They are
101+
not crash-safe: an unlogged sequence is automatically reset to its
102+
initial state after a crash or unclean shutdown. Unlogged sequences are
103+
also not replicated to standby servers.
104+
</para>
105+
106+
<para>
107+
Unlike unlogged tables, unlogged sequences do not offer a significant
108+
performance advantage. This option is mainly intended for sequences
109+
associated with unlogged tables via identity columns or serial columns.
110+
In those cases, it usually wouldn't make sense to have the sequence
111+
WAL-logged and replicated but not its associated table.
112+
</para>
113+
</listitem>
114+
</varlistentry>
115+
95116
<varlistentry>
96117
<term><literal>IF NOT EXISTS</literal></term>
97118
<listitem>

‎doc/src/sgml/ref/create_table.sgml

+5
Original file line numberDiff line numberDiff line change
@@ -215,6 +215,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
215215
Any indexes created on an unlogged table are automatically unlogged as
216216
well.
217217
</para>
218+
219+
<para>
220+
If this is specified, any sequences created together with the unlogged
221+
table (for identity or serial columns) are also created as unlogged.
222+
</para>
218223
</listitem>
219224
</varlistentry>
220225

‎doc/src/sgml/ref/pg_dump.sgml

+4-3
Original file line numberDiff line numberDiff line change
@@ -981,9 +981,10 @@ PostgreSQL documentation
981981
<term><option>--no-unlogged-table-data</option></term>
982982
<listitem>
983983
<para>
984-
Do not dump the contents of unlogged tables. This option has no
985-
effect on whether or not the table definitions (schema) are dumped;
986-
it only suppresses dumping the table data. Data in unlogged tables
984+
Do not dump the contents of unlogged tables and sequences. This
985+
option has no effect on whether or not the table and sequence
986+
definitions (schema) are dumped; it only suppresses dumping the table
987+
and sequence data. Data in unlogged tables and sequences
987988
is always excluded when dumping from a standby server.
988989
</para>
989990
</listitem>

‎src/backend/commands/sequence.c

+50-8
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
#include "catalog/objectaccess.h"
3131
#include "catalog/pg_sequence.h"
3232
#include "catalog/pg_type.h"
33+
#include "catalog/storage_xlog.h"
3334
#include "commands/defrem.h"
3435
#include "commands/sequence.h"
3536
#include "commands/tablecmds.h"
@@ -95,6 +96,7 @@ static HTAB *seqhashtab = NULL; /* hash table for SeqTable items */
9596
static SeqTableData *last_used_seq = NULL;
9697

9798
static void fill_seq_with_data(Relation rel, HeapTuple tuple);
99+
static void fill_seq_fork_with_data(Relation rel, HeapTuple tuple, ForkNumber forkNum);
98100
static Relation lock_and_open_sequence(SeqTable seq);
99101
static void create_seq_hashtable(void);
100102
static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
@@ -133,12 +135,6 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
133135
bool pgs_nulls[Natts_pg_sequence];
134136
int i;
135137

136-
/* Unlogged sequences are not implemented -- not clear if useful. */
137-
if (seq->sequence->relpersistence == RELPERSISTENCE_UNLOGGED)
138-
ereport(ERROR,
139-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
140-
errmsg("unlogged sequences are not supported")));
141-
142138
/*
143139
* If if_not_exists was given and a relation with the same name already
144140
* exists, bail out. (Note: we needn't check this when not if_not_exists,
@@ -492,9 +488,33 @@ SetSequence(Oid seq_relid, bool transactional, int64 last_value, int64 log_cnt,
492488

493489
/*
494490
* Initialize a sequence's relation with the specified tuple as content
491+
*
492+
* This handles unlogged sequences by writing to both the main and the init
493+
* fork as necessary.
495494
*/
496495
static void
497496
fill_seq_with_data(Relation rel, HeapTuple tuple)
497+
{
498+
fill_seq_fork_with_data(rel, tuple, MAIN_FORKNUM);
499+
500+
if (rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
501+
{
502+
SMgrRelation srel;
503+
504+
srel = smgropen(rel->rd_node, InvalidBackendId);
505+
smgrcreate(srel, INIT_FORKNUM, false);
506+
log_smgrcreate(&rel->rd_node, INIT_FORKNUM);
507+
fill_seq_fork_with_data(rel, tuple, INIT_FORKNUM);
508+
FlushRelationBuffers(rel);
509+
smgrclose(srel);
510+
}
511+
}
512+
513+
/*
514+
* Initialize a sequence's relation fork with the specified tuple as content
515+
*/
516+
static void
517+
fill_seq_fork_with_data(Relation rel, HeapTuple tuple, ForkNumber forkNum)
498518
{
499519
Buffer buf;
500520
Page page;
@@ -503,7 +523,7 @@ fill_seq_with_data(Relation rel, HeapTuple tuple)
503523

504524
/* Initialize first page of relation with special magic number */
505525

506-
buf = ReadBuffer(rel, P_NEW);
526+
buf = ReadBufferExtended(rel, forkNum, P_NEW, RBM_NORMAL, NULL);
507527
Assert(BufferGetBlockNumber(buf) == 0);
508528

509529
page = BufferGetPage(buf);
@@ -549,7 +569,7 @@ fill_seq_with_data(Relation rel, HeapTuple tuple)
549569
elog(ERROR, "failed to add sequence tuple to page");
550570

551571
/* XLOG stuff */
552-
if (RelationNeedsWAL(rel))
572+
if (RelationNeedsWAL(rel) || forkNum == INIT_FORKNUM)
553573
{
554574
xl_seq_rec xlrec;
555575
XLogRecPtr recptr;
@@ -682,6 +702,28 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)
682702
return address;
683703
}
684704

705+
void
706+
SequenceChangePersistence(Oid relid, char newrelpersistence)
707+
{
708+
SeqTable elm;
709+
Relation seqrel;
710+
Buffer buf;
711+
HeapTupleData seqdatatuple;
712+
713+
init_sequence(relid, &elm, &seqrel);
714+
715+
/* check the comment above nextval_internal()'s equivalent call. */
716+
if (RelationNeedsWAL(seqrel))
717+
GetTopTransactionId();
718+
719+
(void) read_seq_tuple(seqrel, &buf, &seqdatatuple);
720+
RelationSetNewRelfilenode(seqrel, newrelpersistence);
721+
fill_seq_with_data(seqrel, &seqdatatuple);
722+
UnlockReleaseBuffer(buf);
723+
724+
relation_close(seqrel, NoLock);
725+
}
726+
685727
void
686728
DeleteSequenceTuple(Oid relid)
687729
{

‎src/backend/commands/tablecmds.c

+29-3
Original file line numberDiff line numberDiff line change
@@ -319,6 +319,7 @@ struct DropRelationCallbackState
319319
#define ATT_COMPOSITE_TYPE 0x0010
320320
#define ATT_FOREIGN_TABLE 0x0020
321321
#define ATT_PARTITIONED_INDEX 0x0040
322+
#define ATT_SEQUENCE 0x0080
322323

323324
/*
324325
* ForeignTruncateInfo
@@ -4660,7 +4661,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
46604661
pass = AT_PASS_MISC;
46614662
break;
46624663
case AT_SetLogged: /* SET LOGGED */
4663-
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
4664+
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_SEQUENCE);
46644665
if (tab->chgPersistence)
46654666
ereport(ERROR,
46664667
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4675,7 +4676,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
46754676
pass = AT_PASS_MISC;
46764677
break;
46774678
case AT_SetUnLogged: /* SET UNLOGGED */
4678-
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
4679+
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_SEQUENCE);
46794680
if (tab->chgPersistence)
46804681
ereport(ERROR,
46814682
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -5425,7 +5426,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
54255426
* and assigns a new relfilenode, we automatically create or drop an
54265427
* init fork for the relation as appropriate.
54275428
*/
5428-
if (tab->rewrite > 0)
5429+
if (tab->rewrite > 0 && tab->relkind != RELKIND_SEQUENCE)
54295430
{
54305431
/* Build a temporary relation and copy data */
54315432
Relation OldHeap;
@@ -5546,6 +5547,11 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
55465547

55475548
InvokeObjectPostAlterHook(RelationRelationId, tab->relid, 0);
55485549
}
5550+
else if (tab->rewrite > 0 && tab->relkind == RELKIND_SEQUENCE)
5551+
{
5552+
if (tab->chgPersistence)
5553+
SequenceChangePersistence(tab->relid, tab->newrelpersistence);
5554+
}
55495555
else
55505556
{
55515557
/*
@@ -5564,6 +5570,23 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
55645570
if (tab->newTableSpace)
55655571
ATExecSetTableSpace(tab->relid, tab->newTableSpace, lockmode);
55665572
}
5573+
5574+
/*
5575+
* Also change persistence of owned sequences, so that it matches the
5576+
* table persistence.
5577+
*/
5578+
if (tab->chgPersistence)
5579+
{
5580+
List *seqlist = getOwnedSequences(tab->relid);
5581+
ListCell *lc;
5582+
5583+
foreach(lc, seqlist)
5584+
{
5585+
Oid seq_relid = lfirst_oid(lc);
5586+
5587+
SequenceChangePersistence(seq_relid, tab->newrelpersistence);
5588+
}
5589+
}
55675590
}
55685591

55695592
/*
@@ -6224,6 +6247,9 @@ ATSimplePermissions(AlterTableType cmdtype, Relation rel, int allowed_targets)
62246247
case RELKIND_FOREIGN_TABLE:
62256248
actual_target = ATT_FOREIGN_TABLE;
62266249
break;
6250+
case RELKIND_SEQUENCE:
6251+
actual_target = ATT_SEQUENCE;
6252+
break;
62276253
default:
62286254
actual_target = 0;
62296255
break;

‎src/backend/parser/parse_utilcmd.c

+1
Original file line numberDiff line numberDiff line change
@@ -445,6 +445,7 @@ generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column,
445445
seqstmt = makeNode(CreateSeqStmt);
446446
seqstmt->for_identity = for_identity;
447447
seqstmt->sequence = makeRangeVar(snamespace, sname, -1);
448+
seqstmt->sequence->relpersistence = cxt->relation->relpersistence;
448449
seqstmt->options = seqoptions;
449450

450451
/*

‎src/bin/pg_dump/pg_dump.c

+13-2
Original file line numberDiff line numberDiff line change
@@ -16570,6 +16570,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1657016570
PQExpBuffer query = createPQExpBuffer();
1657116571
PQExpBuffer delqry = createPQExpBuffer();
1657216572
char *qseqname;
16573+
TableInfo *owning_tab = NULL;
1657316574

1657416575
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
1657516576

@@ -16678,7 +16679,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1667816679

1667916680
if (tbinfo->is_identity_sequence)
1668016681
{
16681-
TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab);
16682+
owning_tab = findTableByOid(tbinfo->owning_tab);
1668216683

1668316684
appendPQExpBuffer(query,
1668416685
"ALTER TABLE %s ",
@@ -16696,7 +16697,9 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1669616697
else
1669716698
{
1669816699
appendPQExpBuffer(query,
16699-
"CREATE SEQUENCE %s\n",
16700+
"CREATE %sSEQUENCE %s\n",
16701+
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
16702+
"UNLOGGED " : "",
1670016703
fmtQualifiedDumpable(tbinfo));
1670116704

1670216705
if (strcmp(seqtype, "bigint") != 0)
@@ -16722,7 +16725,15 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1672216725
cache, (cycled ? "\n CYCLE" : ""));
1672316726

1672416727
if (tbinfo->is_identity_sequence)
16728+
{
1672516729
appendPQExpBufferStr(query, "\n);\n");
16730+
if (tbinfo->relpersistence != owning_tab->relpersistence)
16731+
appendPQExpBuffer(query,
16732+
"ALTER SEQUENCE %s SET %s;\n",
16733+
fmtQualifiedDumpable(tbinfo),
16734+
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
16735+
"UNLOGGED" : "LOGGED");
16736+
}
1672616737
else
1672716738
appendPQExpBufferStr(query, ";\n");
1672816739

‎src/bin/psql/describe.c

+6-2
Original file line numberDiff line numberDiff line change
@@ -1681,8 +1681,12 @@ describeOneTableDetails(const char *schemaname,
16811681
printTableInit(&cont, &myopt, title.data, 7, numrows);
16821682
printTableInitialized = true;
16831683

1684-
printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1685-
schemaname, relationname);
1684+
if (tableinfo.relpersistence == 'u')
1685+
printfPQExpBuffer(&title, _("Unlogged sequence \"%s.%s\""),
1686+
schemaname, relationname);
1687+
else
1688+
printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1689+
schemaname, relationname);
16861690

16871691
printTableAddHeader(&cont, gettext_noop("Type"), true, 'l');
16881692
printTableAddHeader(&cont, gettext_noop("Start"), true, 'r');

‎src/bin/psql/tab-complete.c

+4-1
Original file line numberDiff line numberDiff line change
@@ -2116,14 +2116,17 @@ psql_completion(const char *text, int start, int end)
21162116
/* ALTER SEQUENCE <name> */
21172117
else if (Matches("ALTER", "SEQUENCE", MatchAny))
21182118
COMPLETE_WITH("AS", "INCREMENT", "MINVALUE", "MAXVALUE", "RESTART",
2119-
"NO", "CACHE", "CYCLE", "SET SCHEMA", "OWNED BY",
2119+
"NO", "CACHE", "CYCLE", "SET", "OWNED BY",
21202120
"OWNER TO", "RENAME TO");
21212121
/* ALTER SEQUENCE <name> AS */
21222122
else if (TailMatches("ALTER", "SEQUENCE", MatchAny, "AS"))
21232123
COMPLETE_WITH_CS("smallint", "integer", "bigint");
21242124
/* ALTER SEQUENCE <name> NO */
21252125
else if (Matches("ALTER", "SEQUENCE", MatchAny, "NO"))
21262126
COMPLETE_WITH("MINVALUE", "MAXVALUE", "CYCLE");
2127+
/* ALTER SEQUENCE <name> SET */
2128+
else if (Matches("ALTER", "SEQUENCE", MatchAny, "SET"))
2129+
COMPLETE_WITH("SCHEMA", "LOGGED", "UNLOGGED");
21272130
/* ALTER SERVER <name> */
21282131
else if (Matches("ALTER", "SERVER", MatchAny))
21292132
COMPLETE_WITH("VERSION", "OPTIONS", "OWNER TO", "RENAME TO");

‎src/include/commands/sequence.h

+1
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@ extern List *sequence_options(Oid relid);
5858

5959
extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stmt);
6060
extern ObjectAddress AlterSequence(ParseState *pstate, AlterSeqStmt *stmt);
61+
extern void SequenceChangePersistence(Oid relid, char newrelpersistence);
6162
extern void DeleteSequenceTuple(Oid relid);
6263
extern void ResetSequence(Oid seq_relid);
6364
extern void SetSequence(Oid seq_relid, bool transactional, int64 last_value, int64 log_cnt, bool is_called);

0 commit comments

Comments
 (0)