0001-Unlogged-sequences-20220331.patch

From cf6b207b24ba8c356481ce17b3482823f3f63a2f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@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