Here is an updated patch that now also includes SET LOGGED/UNLOGGED
support. So this version addresses all known issues and open problems.
On 28.02.22 10:56, Peter Eisentraut wrote:
rebased patch, no functional changes
On 11.02.22 10:12, Peter Eisentraut wrote:
On 25.06.19 20:37, Andres Freund wrote:
I.e. I think it'd be better if we just added a fork argument to
fill_seq_with_data(), and then do something like
smgrcreate(srel, INIT_FORKNUM, false);
log_smgrcreate(&rel->rd_node, INIT_FORKNUM);
fill_seq_with_data(rel, tuple, INIT_FORKNUM);
and add a FlushBuffer() to the end of fill_seq_with_data() if writing
INIT_FORKNUM. The if (RelationNeedsWAL(rel)) would need an || forkNum ==
INIT_FORKNUM.
Now that logical replication of sequences is nearing completion, I
figured it would be suitable to dust off this old discussion on
unlogged sequences, mainly so that sequences attached to unlogged
tables can be excluded from replication.
Attached is a new patch that incorporates the above suggestions, with
some slight refactoring. The only thing I didn't/couldn't do was to
call FlushBuffers(), since that is not an exported function. So this
still calls FlushRelationBuffers(), which was previously not liked.
Ideas welcome.
I have also re-tested the crash reported by Michael Paquier in the old
discussion and added test cases that catch them.
The rest of the patch is just documentation, DDL support, client
support, etc.
What is not done yet is support for ALTER SEQUENCE ... SET
LOGGED/UNLOGGED. This is a bit of a problem because:
1. The new behavior is that a serial/identity sequence of a new
unlogged table is now also unlogged.
2. There is also a new restriction that changing a table to logged is
not allowed if it is linked to an unlogged sequence. (This is IMO
similar to the existing restriction on linking mixed logged/unlogged
tables via foreign keys.)
3. Thus, currently, you can't create an unlogged table with a
serial/identity column and then change it to logged. This is
reflected in some of the test changes I had to make in alter_table.sql
to work around this. These should eventually go away.
Interestingly, there is grammar support for ALTER SEQUENCE ... SET
LOGGED/UNLOGGED because there is this:
| ALTER SEQUENCE qualified_name alter_table_cmds
{
AlterTableStmt *n = makeNode(AlterTableStmt);
n->relation = $3;
n->cmds = $4;
n->objtype = OBJECT_SEQUENCE;
n->missing_ok = false;
$$ = (Node *)n;
}
But it is rejected later in tablecmds.c. In fact, it appears that
this piece of grammar is currently useless because there are no
alter_table_cmds that actually work for sequences. (This used to be
different because things like OWNER TO also went through here.)
I tried to make tablecmds.c handle sequences as well, but that became
messy. So I'm thinking about making ALTER SEQUENCE ... SET
LOGGED/UNLOGGED an entirely separate code path and rip out the above
grammar, but that needs some further pondering.
But all that is a bit of a separate effort, so in the meantime some
review of the changes in and around fill_seq_with_data() would be useful.
From 42584b9f0b2d34c6ac33fe9a978c22b37231e779 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 24 Mar 2022 13:54:15 +0100
Subject: [PATCH v4] 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.)
A new subcommand ALTER SEQUENCE ... SET LOGGED/UNLOGGED is added.
Also, identity/serial sequences automatically follow persistence
changes of their associated tables.
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 | 6 +-
src/backend/commands/sequence.c | 58 ++++++++--
src/backend/commands/tablecmds.c | 32 +++++-
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 | 49 +++++++-
src/test/regress/expected/alter_table.out | 125 +++++++++++++--------
src/test/regress/expected/sequence.out | 20 +++-
src/test/regress/sql/alter_table.sql | 37 +++---
src/test/regress/sql/sequence.sql | 10 +-
15 files changed, 297 insertions(+), 94 deletions(-)
diff --git a/doc/src/sgml/ref/alter_sequence.sgml
b/doc/src/sgml/ref/alter_sequence.sgml
index 3cd9ece49f..148085d4f2 100644
--- a/doc/src/sgml/ref/alter_sequence.sgml
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -31,6 +31,7 @@
[ 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 @@ <title>Parameters</title>
</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 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 c13cada3bf..698924cbfd 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;
@@ -528,6 +548,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)
{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 80faae985e..ab63f381d5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -318,6 +318,7 @@ struct DropRelationCallbackState
#define ATT_COMPOSITE_TYPE 0x0010
#define ATT_FOREIGN_TABLE 0x0020
#define ATT_PARTITIONED_INDEX 0x0040
+#define ATT_SEQUENCE 0x0080
/*
* ForeignTruncateInfo
@@ -4659,7 +4660,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),
@@ -4674,7 +4675,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
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),
@@ -5424,7 +5425,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;
@@ -5545,6 +5546,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
{
/*
@@ -5563,6 +5569,23 @@ ATRewriteTables(AlterTableStmt *parsetree, List
**wqueue, LOCKMODE lockmode,
if (tab->newTableSpace)
ATExecSetTableSpace(tab->relid,
tab->newTableSpace, lockmode);
}
+
+ /*
+ * Also change persistence of owned sequences.
+ */
+ if (tab->chgPersistence)
+ {
+ List *seqlist = getOwnedSequences(tab->relid);
+ ListCell *lc;
+
+ foreach(lc, seqlist)
+ {
+ Oid seq_relid = lfirst_oid(lc);
+
+ SequenceChangePersistence(seq_relid,
tab->newrelpersistence);
+ }
+ }
+
}
/*
@@ -6223,6 +6246,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 cd946c7692..2826559d09 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 e5816c4cce..a19480879e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16614,7 +16614,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 714097cad1..ccff620e2d 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1732,8 +1732,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/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5c064595a9..416215d21f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2100,7 +2100,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"))
@@ -2108,6 +2108,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 9fecc41954..c0e0603e61 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 ResetSequenceCaches(void);
diff --git a/src/test/recovery/t/014_unlogged_reinit.pl
b/src/test/recovery/t/014_unlogged_reinit.pl
index f3199fbd2e..573197738b 100644
--- a/src/test/recovery/t/014_unlogged_reinit.pl
+++ b/src/test/recovery/t/014_unlogged_reinit.pl
@@ -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.
@@ -44,6 +53,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');
@@ -54,6 +74,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');
@@ -64,13 +86,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');
@@ -81,4 +111,11 @@
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 aabc564e2c..3c0c29060d 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3448,89 +3448,116 @@ 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);
+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
-- check relpersistence of an unlogged table
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged[1-4]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged[1-4]'
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'
+SELECT r.relname || ' 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 ~
'^unlogged[1-4]'
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
+-----------------------+---------+----------------
+ unlogged1 | r | u
+ unlogged1 toast index | i | u
+ unlogged1 toast table | t | u
+ unlogged1_pkey | i | u
+ unlogged2 | r | u
+ unlogged2_pkey | i | u
+ unlogged3 | r | u
+ unlogged3_pkey | i | u
+ unlogged4 | r | u
+ unlogged4_f1_seq | S | u
+ unlogged4_pkey | i | u
+(11 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
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;
-- check relpersistence of an unlogged table after changing to permanent
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged[1-4]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged[1-4]'
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'
+SELECT r.relname || ' 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 ~
'^unlogged[1-4]'
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
+-----------------------+---------+----------------
+ unlogged1 | r | p
+ unlogged1 toast index | i | p
+ unlogged1 toast table | t | p
+ unlogged1_pkey | i | p
+ unlogged2 | r | u
+ unlogged2_pkey | i | u
+ unlogged3 | r | p
+ unlogged3_pkey | i | p
+ unlogged4 | r | p
+ unlogged4_f1_seq | S | p
+ unlogged4_pkey | i | p
+(11 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);
+CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); --
foreign key
+CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); --
self-referencing foreign key
-- check relpersistence of a permanent table
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged[1-3]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged[1-3]'
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 ~ '^logged1'
+SELECT r.relname || '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 ~ '^logged[1-3]'
ORDER BY relname;
- relname | relkind | relpersistence
-----------------+---------+----------------
- logged1 | r | p
- logged1_f1_seq | S | p
- logged1_pkey | i | p
- toast index | i | p
- toast table | t | p
-(5 rows)
+ relname | relkind | relpersistence
+---------------------+---------+----------------
+ logged1 | r | p
+ logged1 toast table | t | p
+ logged1_f1_seq | S | p
+ logged1_pkey | i | p
+ logged1toast index | i | p
+ logged2 | r | p
+ logged2_f1_seq | S | p
+ logged2_pkey | i | p
+ logged3 | r | p
+ logged3_f1_seq | S | p
+ logged3_pkey | i | p
+(11 rows)
-CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); --
foreign key
-CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); --
self-referencing foreign key
ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a
permanent table exists
ERROR: could not change table "logged1" to unlogged because it references
logged table "logged2"
ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
ALTER TABLE logged2 SET UNLOGGED;
ALTER TABLE logged1 SET UNLOGGED;
-- check relpersistence of a permanent table after changing to unlogged
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged[1-3]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged[1-3]'
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 ~ '^logged1'
+SELECT r.relname || ' 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 ~
'^logged[1-3]'
ORDER BY relname;
- relname | relkind | relpersistence
-----------------+---------+----------------
- logged1 | r | u
- logged1_f1_seq | S | p
- logged1_pkey | i | u
- toast index | i | u
- toast table | t | u
-(5 rows)
+ relname | relkind | relpersistence
+---------------------+---------+----------------
+ logged1 | r | u
+ logged1 toast index | i | u
+ logged1 toast table | t | u
+ logged1_f1_seq | S | u
+ logged1_pkey | i | u
+ logged2 | r | u
+ logged2_f1_seq | S | u
+ logged2_pkey | i | u
+ logged3 | r | u
+ logged3_f1_seq | S | u
+ logged3_pkey | i | u
+(11 rows)
ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
DROP TABLE logged3;
diff --git a/src/test/regress/expected/sequence.out
b/src/test/regress/expected/sequence.out
index 71c2b0f1df..7cb2f7cc02 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,24 @@ 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 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 cce1cb1dd3..a9d4dcb734 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2180,51 +2180,54 @@ 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);
+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
-- check relpersistence of an unlogged table
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged[1-4]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged[1-4]'
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'
+SELECT r.relname || ' 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 ~
'^unlogged[1-4]'
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
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;
-- check relpersistence of an unlogged table after changing to permanent
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^unlogged[1-4]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^unlogged[1-4]'
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'
+SELECT r.relname || ' 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 ~
'^unlogged[1-4]'
ORDER BY relname;
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);
+CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); --
foreign key
+CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); --
self-referencing foreign key
-- check relpersistence of a permanent table
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged[1-3]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged[1-3]'
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 ~ '^logged1'
+SELECT r.relname || '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 ~ '^logged[1-3]'
ORDER BY relname;
-CREATE TABLE logged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged1); --
foreign key
-CREATE TABLE logged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES logged3); --
self-referencing foreign key
ALTER TABLE logged1 SET UNLOGGED; -- fails because a foreign key from a
permanent table exists
ALTER TABLE logged3 SET UNLOGGED; -- skip self-referencing foreign key
ALTER TABLE logged2 SET UNLOGGED;
ALTER TABLE logged1 SET UNLOGGED;
-- check relpersistence of a permanent table after changing to unlogged
-SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged1'
+SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~
'^logged[1-3]'
UNION ALL
-SELECT 'toast table', t.relkind, t.relpersistence FROM pg_class r JOIN
pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged1'
+SELECT r.relname || ' toast table', t.relkind, t.relpersistence FROM pg_class
r JOIN pg_class t ON t.oid = r.reltoastrelid WHERE r.relname ~ '^logged[1-3]'
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 ~ '^logged1'
+SELECT r.relname || ' 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 ~
'^logged[1-3]'
ORDER BY relname;
ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
DROP TABLE logged3;
diff --git a/src/test/regress/sql/sequence.sql
b/src/test/regress/sql/sequence.sql
index 7928ee23ee..674f5f1f66 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,15 @@ 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;
+\d sequence_test_unlogged
+ALTER SEQUENCE sequence_test_unlogged SET UNLOGGED;
+\d sequence_test_unlogged
+DROP SEQUENCE sequence_test_unlogged;
+
-- Test sequences in read-only transactions
CREATE TEMPORARY SEQUENCE sequence_test_temp1;
START TRANSACTION READ ONLY;
base-commit: ac9c5dc5d4cc15e396b59ca66df25969e51a2a00
--
2.35.1
