Hi,
Here's a slightly improved patch, adding a couple checks and tests for
owned sequences to ensure both objects have the same persistence. In
particular:
* When linking a sequence to a table (ALTER SEQUENCE ... OWNED BY),
there's an ereport(ERROR) if the relpersistence values do not match.
* Disallow changing persistence for owned sequences directly.
But I wonder about two things:
1) Do we need to do something about pg_upgrade? I mean, we did not have
unlogged sequences until now, so existing databases may have unlogged
tables with logged sequences. If people run pg_upgrade, what should be
the end result? Should it convert the sequences to unlogged ones, should
it fail and force the user to fix this manually, or what?
2) Does it actually make sense to force owned sequences to have the same
relpersistence as the table? I can imagine use cases where it's OK to
discard and recalculate the data, but I'd still want to ensure unique
IDs. Like some data loads, for example.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From cf6b207b24ba8c356481ce17b3482823f3f63a2f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 29 Mar 2022 12:53:53 +0200
Subject: [PATCH] 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().)
Discussion: https://www.postgresql.org/message-id/flat/04e12818-2f98-257c-b926-2845d74ed04f%402ndquadrant.com
---
doc/src/sgml/ref/alter_sequence.sgml | 12 ++++
doc/src/sgml/ref/create_sequence.sgml | 23 ++++++-
doc/src/sgml/ref/pg_dump.sgml | 7 +-
src/backend/commands/sequence.c | 68 ++++++++++++++++---
src/backend/commands/tablecmds.c | 68 ++++++++++++++++++-
src/backend/parser/parse_utilcmd.c | 1 +
src/bin/pg_dump/pg_dump.c | 4 +-
src/bin/psql/describe.c | 8 ++-
src/bin/psql/tab-complete.c | 5 +-
src/include/commands/sequence.h | 1 +
src/test/recovery/t/014_unlogged_reinit.pl | 61 +++++++++++++++--
src/test/regress/expected/alter_table.out | 4 +-
src/test/regress/expected/sequence.out | 78 +++++++++++++++++++++-
src/test/regress/sql/sequence.sql | 42 +++++++++++-
14 files changed, 350 insertions(+), 32 deletions(-)
diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml
index 3cd9ece49f2..148085d4f20 100644
--- a/doc/src/sgml/ref/alter_sequence.sgml
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -31,6 +31,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
[ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
+ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET { LOGGED | UNLOGGED }
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
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
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>SET { LOGGED | UNLOGGED }</literal></term>
+ <listitem>
+ <para>
+ This form changes the sequence from unlogged to logged or vice-versa
+ (see <xref linkend="sql-createsequence"/>). It cannot be applied to a
+ temporary sequence.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>OWNED BY</literal> <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable></term>
<term><literal>OWNED BY NONE</literal></term>
diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 20bdbc002fa..a84aa5bf56a 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
+CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
[ AS <replaceable class="parameter">data_type</replaceable> ]
[ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
[ 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>;
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>UNLOGGED</literal></term>
+ <listitem>
+ <para>
+ If specified, the sequence is created as an unlogged sequence. Changes
+ to unlogged sequences are not written to the write-ahead log. They are
+ not crash-safe: an unlogged sequence is automatically reset to its
+ initial state after a crash or unclean shutdown. Unlogged sequences are
+ also not replicated to standby servers.
+ </para>
+
+ <para>
+ Unlike unlogged tables, unlogged sequences do not offer a significant
+ performance advantage. This option is mainly intended for sequences
+ associated with unlogged tables via identity columns or serial columns.
+ In those cases, it usually wouldn't make sense to have the sequence
+ WAL-logged and replicated but not its associated table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>IF NOT EXISTS</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 2f0042fd968..723b2a1a66a 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -981,9 +981,10 @@ PostgreSQL documentation
<term><option>--no-unlogged-table-data</option></term>
<listitem>
<para>
- Do not dump the contents of unlogged tables. This option has no
- effect on whether or not the table definitions (schema) are dumped;
- it only suppresses dumping the table data. Data in unlogged tables
+ Do not dump the contents of unlogged tables and sequences. This
+ option has no effect on whether or not the table and sequence
+ definitions (schema) are dumped; it only suppresses dumping the table
+ and sequence data. Data in unlogged tables and sequences
is always excluded when dumping from a standby server.
</para>
</listitem>
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 717bb0b2aa9..4c9bd4dd902 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -30,6 +30,7 @@
#include "catalog/objectaccess.h"
#include "catalog/pg_sequence.h"
#include "catalog/pg_type.h"
+#include "catalog/storage_xlog.h"
#include "commands/defrem.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
@@ -95,6 +96,7 @@ static HTAB *seqhashtab = NULL; /* hash table for SeqTable items */
static SeqTableData *last_used_seq = NULL;
static void fill_seq_with_data(Relation rel, HeapTuple tuple);
+static void fill_seq_fork_with_data(Relation rel, HeapTuple tuple, ForkNumber forkNum);
static Relation lock_and_open_sequence(SeqTable seq);
static void create_seq_hashtable(void);
static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
@@ -133,12 +135,6 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
bool pgs_nulls[Natts_pg_sequence];
int i;
- /* Unlogged sequences are not implemented -- not clear if useful. */
- if (seq->sequence->relpersistence == RELPERSISTENCE_UNLOGGED)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("unlogged sequences are not supported")));
-
/*
* If if_not_exists was given and a relation with the same name already
* 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,
/*
* Initialize a sequence's relation with the specified tuple as content
+ *
+ * This handles unlogged sequences by writing to both the main and the init
+ * fork as necessary.
*/
static void
fill_seq_with_data(Relation rel, HeapTuple tuple)
+{
+ fill_seq_fork_with_data(rel, tuple, MAIN_FORKNUM);
+
+ if (rel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
+ {
+ SMgrRelation srel;
+
+ srel = smgropen(rel->rd_node, InvalidBackendId);
+ smgrcreate(srel, INIT_FORKNUM, false);
+ log_smgrcreate(&rel->rd_node, INIT_FORKNUM);
+ fill_seq_fork_with_data(rel, tuple, INIT_FORKNUM);
+ FlushRelationBuffers(rel);
+ smgrclose(srel);
+ }
+}
+
+/*
+ * Initialize a sequence's relation fork with the specified tuple as content
+ */
+static void
+fill_seq_fork_with_data(Relation rel, HeapTuple tuple, ForkNumber forkNum)
{
Buffer buf;
Page page;
@@ -503,7 +523,7 @@ fill_seq_with_data(Relation rel, HeapTuple tuple)
/* Initialize first page of relation with special magic number */
- buf = ReadBuffer(rel, P_NEW);
+ buf = ReadBufferExtended(rel, forkNum, P_NEW, RBM_NORMAL, NULL);
Assert(BufferGetBlockNumber(buf) == 0);
page = BufferGetPage(buf);
@@ -549,7 +569,7 @@ fill_seq_with_data(Relation rel, HeapTuple tuple)
elog(ERROR, "failed to add sequence tuple to page");
/* XLOG stuff */
- if (RelationNeedsWAL(rel))
+ if (RelationNeedsWAL(rel) || forkNum == INIT_FORKNUM)
{
xl_seq_rec xlrec;
XLogRecPtr recptr;
@@ -682,6 +702,28 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)
return address;
}
+void
+SequenceChangePersistence(Oid relid, char newrelpersistence)
+{
+ SeqTable elm;
+ Relation seqrel;
+ Buffer buf;
+ HeapTupleData seqdatatuple;
+
+ init_sequence(relid, &elm, &seqrel);
+
+ /* check the comment above nextval_internal()'s equivalent call. */
+ if (RelationNeedsWAL(seqrel))
+ GetTopTransactionId();
+
+ (void) read_seq_tuple(seqrel, &buf, &seqdatatuple);
+ RelationSetNewRelfilenode(seqrel, newrelpersistence);
+ fill_seq_with_data(seqrel, &seqdatatuple);
+ UnlockReleaseBuffer(buf);
+
+ relation_close(seqrel, NoLock);
+}
+
void
DeleteSequenceTuple(Oid relid)
{
@@ -1730,8 +1772,8 @@ init_params(ParseState *pstate, List *options, bool for_identity,
*
* Ownership permissions on the sequence are already checked,
* but if we are establishing a new owned-by dependency, we must
- * enforce that the referenced table has the same owner and namespace
- * as the sequence.
+ * enforce that the referenced table has the same owner, namespace
+ * and persistence as the sequence.
*/
static void
process_owned_by(Relation seqrel, List *owned_by, bool for_identity)
@@ -1791,6 +1833,12 @@ process_owned_by(Relation seqrel, List *owned_by, bool for_identity)
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("sequence must be in same schema as table it is linked to")));
+ /* We also insist on the persistence being the same. */
+ if (seqrel->rd_rel->relpersistence != tablerel->rd_rel->relpersistence)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("sequence must have the same persistence as table it is linked to")));
+
/* Now, fetch the attribute number from the system cache */
attnum = get_attnum(RelationGetRelid(tablerel), attrname);
if (attnum == InvalidAttrNumber)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 51b4a00d50d..baf35aa93a1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -319,6 +319,7 @@ struct DropRelationCallbackState
#define ATT_COMPOSITE_TYPE 0x0010
#define ATT_FOREIGN_TABLE 0x0020
#define ATT_PARTITIONED_INDEX 0x0040
+#define ATT_SEQUENCE 0x0080
/*
* ForeignTruncateInfo
@@ -4660,7 +4661,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_SetLogged: /* SET LOGGED */
- ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
+ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_SEQUENCE);
if (tab->chgPersistence)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4672,10 +4673,28 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
tab->rewrite |= AT_REWRITE_ALTER_PERSISTENCE;
tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
}
+
+ /* disallow changing persistence for owned sequences directly */
+ if (tab->relkind == RELKIND_SEQUENCE)
+ {
+ Oid tableId;
+ int32 colId;
+
+ if (sequenceIsOwned(tab->relid, DEPENDENCY_AUTO, &tableId, &colId) ||
+ sequenceIsOwned(tab->relid, DEPENDENCY_INTERNAL, &tableId, &colId))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change persistence of sequence \"%s\"",
+ get_rel_name(tab->relid)),
+ errdetail("Sequence \"%s\" is linked to table \"%s\".",
+ get_rel_name(tab->relid),
+ get_rel_name(tableId))));
+ }
+
pass = AT_PASS_MISC;
break;
case AT_SetUnLogged: /* SET UNLOGGED */
- ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
+ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_SEQUENCE);
if (tab->chgPersistence)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4687,6 +4706,24 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
tab->rewrite |= AT_REWRITE_ALTER_PERSISTENCE;
tab->newrelpersistence = RELPERSISTENCE_UNLOGGED;
}
+
+ /* disallow changing persistence for owned sequences directly */
+ if (tab->relkind == RELKIND_SEQUENCE)
+ {
+ Oid tableId;
+ int32 colId;
+
+ if (sequenceIsOwned(tab->relid, DEPENDENCY_AUTO, &tableId, &colId) ||
+ sequenceIsOwned(tab->relid, DEPENDENCY_INTERNAL, &tableId, &colId))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot change persistence of sequence \"%s\"",
+ get_rel_name(tab->relid)),
+ errdetail("Sequence \"%s\" is linked to table \"%s\".",
+ get_rel_name(tab->relid),
+ get_rel_name(tableId))));
+ }
+
pass = AT_PASS_MISC;
break;
case AT_DropOids: /* SET WITHOUT OIDS */
@@ -5425,7 +5462,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
* and assigns a new relfilenode, we automatically create or drop an
* init fork for the relation as appropriate.
*/
- if (tab->rewrite > 0)
+ if (tab->rewrite > 0 && tab->relkind != RELKIND_SEQUENCE)
{
/* Build a temporary relation and copy data */
Relation OldHeap;
@@ -5546,6 +5583,11 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
InvokeObjectPostAlterHook(RelationRelationId, tab->relid, 0);
}
+ else if (tab->rewrite > 0 && tab->relkind == RELKIND_SEQUENCE)
+ {
+ if (tab->chgPersistence)
+ SequenceChangePersistence(tab->relid, tab->newrelpersistence);
+ }
else
{
/*
@@ -5564,6 +5606,23 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
if (tab->newTableSpace)
ATExecSetTableSpace(tab->relid, tab->newTableSpace, lockmode);
}
+
+ /*
+ * Also change persistence of owned sequences, so that it matches the
+ * table persistence.
+ */
+ if (tab->chgPersistence)
+ {
+ List *seqlist = getOwnedSequences(tab->relid);
+ ListCell *lc;
+
+ foreach(lc, seqlist)
+ {
+ Oid seq_relid = lfirst_oid(lc);
+
+ SequenceChangePersistence(seq_relid, tab->newrelpersistence);
+ }
+ }
}
/*
@@ -6224,6 +6283,9 @@ ATSimplePermissions(AlterTableType cmdtype, Relation rel, int allowed_targets)
case RELKIND_FOREIGN_TABLE:
actual_target = ATT_FOREIGN_TABLE;
break;
+ case RELKIND_SEQUENCE:
+ actual_target = ATT_SEQUENCE;
+ break;
default:
actual_target = 0;
break;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index cd946c76921..2826559d09b 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -445,6 +445,7 @@ generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column,
seqstmt = makeNode(CreateSeqStmt);
seqstmt->for_identity = for_identity;
seqstmt->sequence = makeRangeVar(snamespace, sname, -1);
+ seqstmt->sequence->relpersistence = cxt->relation->relpersistence;
seqstmt->options = seqoptions;
/*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 535b1601655..d8e6cb2b2ee 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16696,7 +16696,9 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
else
{
appendPQExpBuffer(query,
- "CREATE SEQUENCE %s\n",
+ "CREATE %sSEQUENCE %s\n",
+ tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
+ "UNLOGGED " : "",
fmtQualifiedDumpable(tbinfo));
if (strcmp(seqtype, "bigint") != 0)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4dddf087893..73bbbe2eb40 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1681,8 +1681,12 @@ describeOneTableDetails(const char *schemaname,
printTableInit(&cont, &myopt, title.data, 7, numrows);
printTableInitialized = true;
- printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
- schemaname, relationname);
+ if (tableinfo.relpersistence == 'u')
+ printfPQExpBuffer(&title, _("Unlogged sequence \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
+ schemaname, relationname);
printTableAddHeader(&cont, gettext_noop("Type"), true, 'l');
printTableAddHeader(&cont, gettext_noop("Start"), true, 'r');
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 3f9dfffd57f..89e5f010881 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2115,7 +2115,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER SEQUENCE <name> */
else if (Matches("ALTER", "SEQUENCE", MatchAny))
COMPLETE_WITH("AS", "INCREMENT", "MINVALUE", "MAXVALUE", "RESTART",
- "NO", "CACHE", "CYCLE", "SET SCHEMA", "OWNED BY",
+ "NO", "CACHE", "CYCLE", "SET", "OWNED BY",
"OWNER TO", "RENAME TO");
/* ALTER SEQUENCE <name> AS */
else if (TailMatches("ALTER", "SEQUENCE", MatchAny, "AS"))
@@ -2123,6 +2123,9 @@ psql_completion(const char *text, int start, int end)
/* ALTER SEQUENCE <name> NO */
else if (Matches("ALTER", "SEQUENCE", MatchAny, "NO"))
COMPLETE_WITH("MINVALUE", "MAXVALUE", "CYCLE");
+ /* ALTER SEQUENCE <name> SET */
+ else if (Matches("ALTER", "SEQUENCE", MatchAny, "SET"))
+ COMPLETE_WITH("SCHEMA", "LOGGED", "UNLOGGED");
/* ALTER SERVER <name> */
else if (Matches("ALTER", "SERVER", MatchAny))
COMPLETE_WITH("VERSION", "OPTIONS", "OWNER TO", "RENAME TO");
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 5bab90db8e0..f2381982d5d 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -58,6 +58,7 @@ extern List *sequence_options(Oid relid);
extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stmt);
extern ObjectAddress AlterSequence(ParseState *pstate, AlterSeqStmt *stmt);
+extern void SequenceChangePersistence(Oid relid, char newrelpersistence);
extern void DeleteSequenceTuple(Oid relid);
extern void ResetSequence(Oid seq_relid);
extern void SetSequence(Oid seq_relid, bool transactional, int64 last_value, int64 log_cnt, bool is_called);
diff --git a/src/test/recovery/t/014_unlogged_reinit.pl b/src/test/recovery/t/014_unlogged_reinit.pl
index f3199fbd2e1..0dca3f69fe3 100644
--- a/src/test/recovery/t/014_unlogged_reinit.pl
+++ b/src/test/recovery/t/014_unlogged_reinit.pl
@@ -18,16 +18,27 @@ $node->init;
$node->start;
my $pgdata = $node->data_dir;
-# Create an unlogged table to test that forks other than init are not
-# copied.
+# Create an unlogged table and an unlogged sequence to test that forks
+# other than init are not copied.
$node->safe_psql('postgres', 'CREATE UNLOGGED TABLE base_unlogged (id int)');
+$node->safe_psql('postgres', 'CREATE UNLOGGED SEQUENCE seq_unlogged');
my $baseUnloggedPath = $node->safe_psql('postgres',
q{select pg_relation_filepath('base_unlogged')});
+my $seqUnloggedPath = $node->safe_psql('postgres',
+ q{select pg_relation_filepath('seq_unlogged')});
# Test that main and init forks exist.
-ok(-f "$pgdata/${baseUnloggedPath}_init", 'init fork in base exists');
-ok(-f "$pgdata/$baseUnloggedPath", 'main fork in base exists');
+ok(-f "$pgdata/${baseUnloggedPath}_init", 'table init fork exists');
+ok(-f "$pgdata/$baseUnloggedPath", 'table main fork exists');
+ok(-f "$pgdata/${seqUnloggedPath}_init", 'sequence init fork exists');
+ok(-f "$pgdata/$seqUnloggedPath", 'sequence main fork exists');
+
+# Test the sequence
+is($node->safe_psql('postgres', "SELECT nextval('seq_unlogged')"),
+ 1, 'sequence nextval');
+is($node->safe_psql('postgres', "SELECT nextval('seq_unlogged')"),
+ 2, 'sequence nextval again');
# Create an unlogged table in a tablespace.
@@ -44,6 +55,19 @@ my $ts1UnloggedPath = $node->safe_psql('postgres',
ok(-f "$pgdata/${ts1UnloggedPath}_init", 'init fork in tablespace exists');
ok(-f "$pgdata/$ts1UnloggedPath", 'main fork in tablespace exists');
+# Create more unlogged sequences for testing.
+$node->safe_psql('postgres', 'CREATE UNLOGGED SEQUENCE seq_unlogged2');
+# This rewrites the sequence relation in AlterSequence().
+$node->safe_psql('postgres', 'ALTER SEQUENCE seq_unlogged2 INCREMENT 2');
+$node->safe_psql('postgres', "SELECT nextval('seq_unlogged2')");
+
+$node->safe_psql('postgres',
+ 'CREATE UNLOGGED TABLE tab_seq_unlogged3 (a int GENERATED ALWAYS AS IDENTITY)'
+);
+# This rewrites the sequence relation in ResetSequence().
+$node->safe_psql('postgres', 'TRUNCATE tab_seq_unlogged3 RESTART IDENTITY');
+$node->safe_psql('postgres', 'INSERT INTO tab_seq_unlogged3 DEFAULT VALUES');
+
# Crash the postmaster.
$node->stop('immediate');
@@ -54,6 +78,8 @@ append_to_file("$pgdata/${baseUnloggedPath}_fsm", 'TEST_FSM');
# Remove main fork to test that it is recopied from init.
unlink("$pgdata/${baseUnloggedPath}")
or BAIL_OUT("could not remove \"${baseUnloggedPath}\": $!");
+unlink("$pgdata/${seqUnloggedPath}")
+ or BAIL_OUT("could not remove \"${seqUnloggedPath}\": $!");
# the same for the tablespace
append_to_file("$pgdata/${ts1UnloggedPath}_vm", 'TEST_VM');
@@ -64,13 +90,25 @@ unlink("$pgdata/${ts1UnloggedPath}")
$node->start;
# check unlogged table in base
-ok(-f "$pgdata/${baseUnloggedPath}_init", 'init fork in base still exists');
-ok(-f "$pgdata/$baseUnloggedPath", 'main fork in base recreated at startup');
+ok( -f "$pgdata/${baseUnloggedPath}_init",
+ 'table init fork in base still exists');
+ok(-f "$pgdata/$baseUnloggedPath",
+ 'table main fork in base recreated at startup');
ok(!-f "$pgdata/${baseUnloggedPath}_vm",
'vm fork in base removed at startup');
ok( !-f "$pgdata/${baseUnloggedPath}_fsm",
'fsm fork in base removed at startup');
+# check unlogged sequence
+ok(-f "$pgdata/${seqUnloggedPath}_init", 'sequence init fork still exists');
+ok(-f "$pgdata/$seqUnloggedPath", 'sequence main fork recreated at startup');
+
+# Test the sequence after restart
+is($node->safe_psql('postgres', "SELECT nextval('seq_unlogged')"),
+ 1, 'sequence nextval after restart');
+is($node->safe_psql('postgres', "SELECT nextval('seq_unlogged')"),
+ 2, 'sequence nextval after restart again');
+
# check unlogged table in tablespace
ok( -f "$pgdata/${ts1UnloggedPath}_init",
'init fork still exists in tablespace');
@@ -81,4 +119,15 @@ ok( !-f "$pgdata/${ts1UnloggedPath}_vm",
ok( !-f "$pgdata/${ts1UnloggedPath}_fsm",
'fsm fork in tablespace removed at startup');
+# Test other sequences
+is($node->safe_psql('postgres', "SELECT nextval('seq_unlogged2')"),
+ 1, 'altered sequence nextval after restart');
+is($node->safe_psql('postgres', "SELECT nextval('seq_unlogged2')"),
+ 3, 'altered sequence nextval after restart again');
+
+$node->safe_psql('postgres',
+ "INSERT INTO tab_seq_unlogged3 VALUES (DEFAULT), (DEFAULT)");
+is($node->safe_psql('postgres', "SELECT * FROM tab_seq_unlogged3"),
+ "1\n2", 'reset sequence nextval after restart');
+
done_testing();
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 07473dd6600..5ede56d9b55 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3461,7 +3461,7 @@ ORDER BY relname;
unlogged1 | r | u
unlogged1 toast index | i | u
unlogged1 toast table | t | u
- unlogged1_f1_seq | S | p
+ unlogged1_f1_seq | S | u
unlogged1_pkey | i | u
(5 rows)
@@ -3528,7 +3528,7 @@ ORDER BY relname;
logged1 | r | u
logged1 toast index | i | u
logged1 toast table | t | u
- logged1_f1_seq | S | p
+ logged1_f1_seq | S | u
logged1_pkey | i | u
(5 rows)
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 71c2b0f1dff..179edcc2dba 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -2,8 +2,6 @@
-- CREATE SEQUENCE
--
-- various error cases
-CREATE UNLOGGED SEQUENCE sequence_testx;
-ERROR: unlogged sequences are not supported
CREATE SEQUENCE sequence_testx INCREMENT BY 0;
ERROR: INCREMENT must not be zero
CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20;
@@ -600,6 +598,82 @@ DROP SEQUENCE seq2;
-- should fail
SELECT lastval();
ERROR: lastval is not yet defined in this session
+-- unlogged sequences
+-- (more tests in src/test/recovery/)
+CREATE UNLOGGED SEQUENCE sequence_test_unlogged;
+ALTER SEQUENCE sequence_test_unlogged SET LOGGED;
+\d sequence_test_unlogged
+ Sequence "public.sequence_test_unlogged"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+--------+-------+---------+---------------------+-----------+---------+-------
+ bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
+
+ALTER SEQUENCE sequence_test_unlogged SET UNLOGGED;
+\d sequence_test_unlogged
+ Unlogged sequence "public.sequence_test_unlogged"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+--------+-------+---------+---------------------+-----------+---------+-------
+ bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
+
+DROP SEQUENCE sequence_test_unlogged;
+-- test owned sequences inherit persistence from linked table, and can't
+-- be altered directly
+CREATE TABLE sequence_test_owned (a serial primary key);
+\d sequence_test_owned_a_seq
+ Sequence "public.sequence_test_owned_a_seq"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+---------+-------+---------+------------+-----------+---------+-------
+ integer | 1 | 1 | 2147483647 | 1 | no | 1
+Owned by: public.sequence_test_owned.a
+
+-- can't modify persistence of owned sequence
+ALTER SEQUENCE sequence_test_owned_a_seq SET LOGGED;
+ERROR: cannot change persistence of sequence "sequence_test_owned_a_seq"
+DETAIL: Sequence "sequence_test_owned_a_seq" is linked to table "sequence_test_owned".
+ALTER SEQUENCE sequence_test_owned_a_seq SET UNLOGGED;
+ERROR: cannot change persistence of sequence "sequence_test_owned_a_seq"
+DETAIL: Sequence "sequence_test_owned_a_seq" is linked to table "sequence_test_owned".
+-- change table to unlogged, check sequence persistence changed
+ALTER TABLE sequence_test_owned SET UNLOGGED;
+\d sequence_test_owned_a_seq
+ Unlogged sequence "public.sequence_test_owned_a_seq"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+---------+-------+---------+------------+-----------+---------+-------
+ integer | 1 | 1 | 2147483647 | 1 | no | 1
+Owned by: public.sequence_test_owned.a
+
+-- can't modify persistence of owned sequence (modified table persistence)
+ALTER SEQUENCE sequence_test_owned_a_seq SET LOGGED;
+ERROR: cannot change persistence of sequence "sequence_test_owned_a_seq"
+DETAIL: Sequence "sequence_test_owned_a_seq" is linked to table "sequence_test_owned".
+ALTER SEQUENCE sequence_test_owned_a_seq SET UNLOGGED;
+ERROR: cannot change persistence of sequence "sequence_test_owned_a_seq"
+DETAIL: Sequence "sequence_test_owned_a_seq" is linked to table "sequence_test_owned".
+-- change table sequence back, check sequence persistence changed again
+ALTER TABLE sequence_test_owned SET LOGGED;
+\d sequence_test_owned_a_seq
+ Sequence "public.sequence_test_owned_a_seq"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+---------+-------+---------+------------+-----------+---------+-------
+ integer | 1 | 1 | 2147483647 | 1 | no | 1
+Owned by: public.sequence_test_owned.a
+
+DROP TABLE sequence_test_owned;
+-- test we can't link sequence that mismatches the table persistence
+CREATE TABLE sequence_test_tab_owned (a int);
+CREATE UNLOGGED SEQUENCE sequence_test_seq_owned;
+ALTER SEQUENCE sequence_test_seq_owned OWNED BY sequence_test_tab_owned.a;
+ERROR: sequence must have the same persistence as table it is linked to
+-- now switch the persistence for both objects and try again
+ALTER TABLE sequence_test_tab_owned SET UNLOGGED;
+ALTER SEQUENCE sequence_test_seq_owned SET LOGGED;
+-- should fail again (persistence mismatch)
+ALTER SEQUENCE sequence_test_seq_owned OWNED BY sequence_test_tab_owned.a;
+ERROR: sequence must have the same persistence as table it is linked to
+-- now switch the table to logged and retry, this time it should succeed
+ALTER TABLE sequence_test_tab_owned SET LOGGED;
+ALTER SEQUENCE sequence_test_seq_owned OWNED BY sequence_test_tab_owned.a;
+DROP TABLE sequence_test_tab_owned;
-- Test sequences in read-only transactions
CREATE TEMPORARY SEQUENCE sequence_test_temp1;
START TRANSACTION READ ONLY;
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 7928ee23ee8..f46dee6e433 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -3,7 +3,6 @@
--
-- various error cases
-CREATE UNLOGGED SEQUENCE sequence_testx;
CREATE SEQUENCE sequence_testx INCREMENT BY 0;
CREATE SEQUENCE sequence_testx INCREMENT BY -1 MINVALUE 20;
CREATE SEQUENCE sequence_testx INCREMENT BY 1 MAXVALUE -20;
@@ -272,6 +271,47 @@ DROP SEQUENCE seq2;
-- should fail
SELECT lastval();
+-- unlogged sequences
+-- (more tests in src/test/recovery/)
+CREATE UNLOGGED SEQUENCE sequence_test_unlogged;
+ALTER SEQUENCE sequence_test_unlogged SET LOGGED;
+\d sequence_test_unlogged
+ALTER SEQUENCE sequence_test_unlogged SET UNLOGGED;
+\d sequence_test_unlogged
+DROP SEQUENCE sequence_test_unlogged;
+
+-- test owned sequences inherit persistence from linked table, and can't
+-- be altered directly
+CREATE TABLE sequence_test_owned (a serial primary key);
+\d sequence_test_owned_a_seq
+-- can't modify persistence of owned sequence
+ALTER SEQUENCE sequence_test_owned_a_seq SET LOGGED;
+ALTER SEQUENCE sequence_test_owned_a_seq SET UNLOGGED;
+-- change table to unlogged, check sequence persistence changed
+ALTER TABLE sequence_test_owned SET UNLOGGED;
+\d sequence_test_owned_a_seq
+-- can't modify persistence of owned sequence (modified table persistence)
+ALTER SEQUENCE sequence_test_owned_a_seq SET LOGGED;
+ALTER SEQUENCE sequence_test_owned_a_seq SET UNLOGGED;
+-- change table sequence back, check sequence persistence changed again
+ALTER TABLE sequence_test_owned SET LOGGED;
+\d sequence_test_owned_a_seq
+DROP TABLE sequence_test_owned;
+
+-- test we can't link sequence that mismatches the table persistence
+CREATE TABLE sequence_test_tab_owned (a int);
+CREATE UNLOGGED SEQUENCE sequence_test_seq_owned;
+ALTER SEQUENCE sequence_test_seq_owned OWNED BY sequence_test_tab_owned.a;
+-- now switch the persistence for both objects and try again
+ALTER TABLE sequence_test_tab_owned SET UNLOGGED;
+ALTER SEQUENCE sequence_test_seq_owned SET LOGGED;
+-- should fail again (persistence mismatch)
+ALTER SEQUENCE sequence_test_seq_owned OWNED BY sequence_test_tab_owned.a;
+-- now switch the table to logged and retry, this time it should succeed
+ALTER TABLE sequence_test_tab_owned SET LOGGED;
+ALTER SEQUENCE sequence_test_seq_owned OWNED BY sequence_test_tab_owned.a;
+DROP TABLE sequence_test_tab_owned;
+
-- Test sequences in read-only transactions
CREATE TEMPORARY SEQUENCE sequence_test_temp1;
START TRANSACTION READ ONLY;
--
2.34.1
