v2-0001-Unlogged-sequences.patch

From 761d9be68da6557ccd03f06f41e7858380679406 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Fri, 11 Feb 2022 09:44:37 +0100
Subject: [PATCH v2] 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.

An identity/serial sequence is automatically made unlogged when its
owning table is.  (See also discussion in bug #15631.)

TODO:
- ALTER SEQUENCE ... SET LOGGED/UNLOGGED

Discussion: https://www.postgresql.org/message-id/flat/04e12818-2f98-257c-b926-2845d74ed04f%402ndquadrant.com
---
 doc/src/sgml/ref/create_sequence.sgml      | 23 +++++++++-
 doc/src/sgml/ref/pg_dump.sgml              |  6 +--
 src/backend/commands/sequence.c            | 36 +++++++++++----
 src/backend/commands/tablecmds.c           | 22 ++++++++++
 src/backend/parser/parse_utilcmd.c         |  1 +
 src/bin/pg_dump/pg_dump.c                  |  4 +-
 src/bin/psql/describe.c                    |  8 +++-
 src/test/recovery/t/014_unlogged_reinit.pl | 51 +++++++++++++++++++---
 src/test/regress/expected/alter_table.out  | 40 +++++++++--------
 src/test/regress/expected/sequence.out     | 12 ++++-
 src/test/regress/sql/alter_table.sql       |  9 ++--
 src/test/regress/sql/sequence.sql          |  8 +++-
 12 files changed, 173 insertions(+), 47 deletions(-)

diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 20bdbc002f..a84aa5bf56 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -21,7 +21,7 @@
 
  <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 @@ <title>Parameters</title>
     </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 2f0042fd96..95ccc1561a 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -981,9 +981,9 @@ <title>Options</title>
       <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 ab592ce2f1..a68f7ef585 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,
@@ -338,9 +334,33 @@ ResetSequence(Oid seq_relid)
 
 /*
  * 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;
@@ -349,7 +369,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);
@@ -395,7 +415,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;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3e83f375b5..4c5eee78ac 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -16300,6 +16300,28 @@ ATPrepChangePersistence(Relation rel, bool toLogged)
 
 	table_close(pg_constraint, AccessShareLock);
 
+	/*
+	 * Check that a permanent table does not own an unlogged sequence.
+	 * (Unlogged table owning a logged sequence is ok.)
+	 */
+	if (toLogged)
+	{
+		List	   *seqlist = getOwnedSequences(RelationGetRelid(rel));
+		ListCell   *lc;
+
+		foreach(lc, seqlist)
+		{
+			Oid         seq_relid = lfirst_oid(lc);
+
+			if (get_rel_persistence(seq_relid) != RELPERSISTENCE_PERMANENT)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						 errmsg("could not change table \"%s\" to logged because it owns unlogged sequence \"%s\"",
+								RelationGetRelationName(rel),
+								get_rel_name(seq_relid))));
+		}
+	}
+
 	return true;
 }
 
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 99efa26ce4..5a37dd064c 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 3b4b63d897..3b22906e1a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16531,7 +16531,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 654ef2d7c3..85791411cc 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1720,8 +1720,12 @@ describeOneTableDetails(const char *schemaname,
 		}
 		PQclear(result);
 
-		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);
 
 		myopt.footers = footers;
 		myopt.topt.default_footer = false;
diff --git a/src/test/recovery/t/014_unlogged_reinit.pl b/src/test/recovery/t/014_unlogged_reinit.pl
index c1bb89176b..2600321534 100644
--- a/src/test/recovery/t/014_unlogged_reinit.pl
+++ b/src/test/recovery/t/014_unlogged_reinit.pl
@@ -10,7 +10,7 @@
 use warnings;
 use PostgreSQL::Test::Cluster;
 use PostgreSQL::Test::Utils;
-use Test::More tests => 12;
+use Test::More tests => 23;
 
 my $node = PostgreSQL::Test::Cluster->new('main');
 
@@ -18,16 +18,25 @@
 $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.
 
@@ -46,6 +55,17 @@
 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');
 
@@ -56,6 +76,8 @@
 # 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');
@@ -66,13 +88,21 @@
 $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');
@@ -82,3 +112,10 @@
 	'vm fork in tablespace removed at startup');
 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');
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 16e0475663..a070cffc2a 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3448,7 +3448,7 @@ ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
 ALTER TABLE old_system_table DROP COLUMN othercol;
 DROP TABLE old_system_table;
 -- set logged
-CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+CREATE UNLOGGED TABLE unlogged1(f1 INTEGER PRIMARY KEY, f2 TEXT);
 -- check relpersistence of an unlogged table
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
 UNION ALL
@@ -3456,21 +3456,23 @@ SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class
 UNION ALL
 SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
 ORDER BY relname;
-     relname      | relkind | relpersistence 
-------------------+---------+----------------
- toast index      | i       | u
- toast table      | t       | u
- unlogged1        | r       | u
- unlogged1_f1_seq | S       | p
- unlogged1_pkey   | i       | u
-(5 rows)
+    relname     | relkind | relpersistence 
+----------------+---------+----------------
+ toast index    | i       | u
+ toast table    | t       | u
+ unlogged1      | r       | u
+ unlogged1_pkey | i       | u
+(4 rows)
 
-CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
-CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
+CREATE UNLOGGED TABLE unlogged2(f1 INTEGER PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
+CREATE UNLOGGED TABLE unlogged3(f1 INTEGER PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
+CREATE UNLOGGED TABLE unlogged4(f1 SERIAL PRIMARY KEY); -- sequence
 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
 ERROR:  could not change table "unlogged2" to logged because it references unlogged table "unlogged1"
 ALTER TABLE unlogged1 SET LOGGED;
+ALTER TABLE unlogged4 SET LOGGED; -- fails because of sequence
+ERROR:  could not change table "unlogged4" to logged because it owns unlogged sequence "unlogged4_f1_seq"
 -- check relpersistence of an unlogged table after changing to permanent
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
 UNION ALL
@@ -3478,19 +3480,19 @@ SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN pg_class
 UNION ALL
 SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
 ORDER BY relname;
-     relname      | relkind | relpersistence 
-------------------+---------+----------------
- toast index      | i       | p
- toast table      | t       | p
- unlogged1        | r       | p
- unlogged1_f1_seq | S       | p
- unlogged1_pkey   | i       | p
-(5 rows)
+    relname     | relkind | relpersistence 
+----------------+---------+----------------
+ toast index    | i       | p
+ toast table    | t       | p
+ unlogged1      | r       | p
+ unlogged1_pkey | i       | p
+(4 rows)
 
 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
 DROP TABLE unlogged3;
 DROP TABLE unlogged2;
 DROP TABLE unlogged1;
+DROP TABLE unlogged4;
 -- set unlogged
 CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
 -- check relpersistence of a permanent table
diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out
index 71c2b0f1df..acb4191b1d 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,16 @@ 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;  -- not implemented
+ERROR:  ALTER action SET LOGGED cannot be performed on relation "sequence_test_unlogged"
+DETAIL:  This operation is not supported for sequences.
+ALTER SEQUENCE sequence_test_unlogged SET UNLOGGED;  -- not implemented
+ERROR:  ALTER action SET UNLOGGED cannot be performed on relation "sequence_test_unlogged"
+DETAIL:  This operation is not supported for sequences.
+DROP SEQUENCE sequence_test_unlogged;
 -- Test sequences in read-only transactions
 CREATE TEMPORARY SEQUENCE sequence_test_temp1;
 START TRANSACTION READ ONLY;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index ac894c0602..38864475d0 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2180,7 +2180,7 @@ CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
 DROP TABLE old_system_table;
 
 -- set logged
-CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
+CREATE UNLOGGED TABLE unlogged1(f1 INTEGER PRIMARY KEY, f2 TEXT);
 -- check relpersistence of an unlogged table
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
 UNION ALL
@@ -2188,11 +2188,13 @@ CREATE UNLOGGED TABLE unlogged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
 UNION ALL
 SELECT 'toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
 ORDER BY relname;
-CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
-CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
+CREATE UNLOGGED TABLE unlogged2(f1 INTEGER PRIMARY KEY, f2 INTEGER REFERENCES unlogged1); -- foreign key
+CREATE UNLOGGED TABLE unlogged3(f1 INTEGER PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
+CREATE UNLOGGED TABLE unlogged4(f1 SERIAL PRIMARY KEY); -- sequence
 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
 ALTER TABLE unlogged1 SET LOGGED;
+ALTER TABLE unlogged4 SET LOGGED; -- fails because of sequence
 -- check relpersistence of an unlogged table after changing to permanent
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
 UNION ALL
@@ -2204,6 +2206,7 @@ CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unl
 DROP TABLE unlogged3;
 DROP TABLE unlogged2;
 DROP TABLE unlogged1;
+DROP TABLE unlogged4;
 -- set unlogged
 CREATE TABLE logged1(f1 SERIAL PRIMARY KEY, f2 TEXT);
 -- check relpersistence of a permanent table
diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql
index 7928ee23ee..987b2ae634 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,13 @@ CREATE 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;  -- not implemented
+ALTER SEQUENCE sequence_test_unlogged SET UNLOGGED;  -- not implemented
+DROP SEQUENCE sequence_test_unlogged;
+
 -- Test sequences in read-only transactions
 CREATE TEMPORARY SEQUENCE sequence_test_temp1;
 START TRANSACTION READ ONLY;

base-commit: a745b936507f57334e2c0108dcea5965848e3e35
-- 
2.35.1