The discussion in bug #15631 revealed that serial/identity sequences of
temporary tables should really also be temporary (easy), and that
serial/identity sequences of unlogged tables should also be unlogged.
But there is no support for unlogged sequences, so I looked into that.
If you copy the initial sequence relation file to the init fork, then
this all seems to work out just fine. Attached is a patch. The
low-level copying seems to be handled quite inconsistently across the
code, so I'm not sure what the most appropriate way to do this would be.
I'm looking for feedback from those who have worked on tableam and
storage manager to see what the right interfaces are or whether some new
interfaces might perhaps be appropriate.
(What's still missing in this patch is ALTER SEQUENCE SET
{LOGGED|UNLOGGED} as well as propagating the analogous ALTER TABLE
command to owned sequences.)
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From c15d00d102a7dc2e13d7cc239b27cdcd189bac19 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Sat, 4 May 2019 17:04:45 +0200
Subject: [PATCH v1 1/4] Make command order in test more sensible
---
src/test/regress/expected/sequence.out | 2 +-
src/test/regress/sql/sequence.sql | 4 ++--
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/src/test/regress/expected/sequence.out
b/src/test/regress/expected/sequence.out
index a0d2b22d3c..75eb5015cf 100644
--- a/src/test/regress/expected/sequence.out
+++ b/src/test/regress/expected/sequence.out
@@ -599,7 +599,6 @@ DROP SEQUENCE seq2;
-- should fail
SELECT lastval();
ERROR: lastval is not yet defined in this session
-CREATE USER regress_seq_user;
-- Test sequences in read-only transactions
CREATE TEMPORARY SEQUENCE sequence_test_temp1;
START TRANSACTION READ ONLY;
@@ -623,6 +622,7 @@ SELECT setval('sequence_test2', 1); -- error
ERROR: cannot execute setval() in a read-only transaction
ROLLBACK;
-- privileges tests
+CREATE USER regress_seq_user;
-- nextval
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
diff --git a/src/test/regress/sql/sequence.sql
b/src/test/regress/sql/sequence.sql
index a7b9e63372..7928ee23ee 100644
--- a/src/test/regress/sql/sequence.sql
+++ b/src/test/regress/sql/sequence.sql
@@ -272,8 +272,6 @@ CREATE SEQUENCE seq2;
-- should fail
SELECT lastval();
-CREATE USER regress_seq_user;
-
-- Test sequences in read-only transactions
CREATE TEMPORARY SEQUENCE sequence_test_temp1;
START TRANSACTION READ ONLY;
@@ -287,6 +285,8 @@ CREATE TEMPORARY SEQUENCE sequence_test_temp1;
-- privileges tests
+CREATE USER regress_seq_user;
+
-- nextval
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
base-commit: 414cca40d506dd3f17b49ae3139853139192c2ba
--
2.22.0
From a66fbef1fc05bd4d5907851bf93c4e268cd477ab Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 11 Jun 2019 16:40:41 +0200
Subject: [PATCH v1 2/4] Fix comment
The last argument of smgrextend() was renamed from isTemp to skipFsync
in debcec7dc31a992703911a9953e299c8d730c778, but the comments at two
call sites were not updated.
---
src/backend/access/heap/rewriteheap.c | 7 +++----
src/backend/catalog/storage.c | 6 +++---
2 files changed, 6 insertions(+), 7 deletions(-)
diff --git a/src/backend/access/heap/rewriteheap.c
b/src/backend/access/heap/rewriteheap.c
index 369694fa2e..916231e2c4 100644
--- a/src/backend/access/heap/rewriteheap.c
+++ b/src/backend/access/heap/rewriteheap.c
@@ -703,10 +703,9 @@ raw_heap_insert(RewriteState state, HeapTuple tup)
true);
/*
- * Now write the page. We say isTemp = true even if
it's not a
- * temp table, because there's no need for smgr to
schedule an
- * fsync for this write; we'll do it ourselves in
- * end_heap_rewrite.
+ * Now write the page. We say skipFsync = true because
there's no
+ * need for smgr to schedule an fsync for this write;
we'll do it
+ * ourselves in end_heap_rewrite.
*/
RelationOpenSmgr(state->rs_new_rel);
diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c
index 3cc886f7fe..d6112fa535 100644
--- a/src/backend/catalog/storage.c
+++ b/src/backend/catalog/storage.c
@@ -358,9 +358,9 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst,
PageSetChecksumInplace(page, blkno);
/*
- * Now write the page. We say isTemp = true even if it's not a
temp
- * rel, because there's no need for smgr to schedule an fsync
for this
- * write; we'll do it ourselves below.
+ * Now write the page. We say skipFsync = true because there's
no
+ * need for smgr to schedule an fsync for this write; we'll do
it
+ * ourselves below.
*/
smgrextend(dst, forkNum, blkno, buf.data, true);
}
--
2.22.0
From 0935f356e7024e7351da8a4446f933e73d27cb8a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 3 May 2019 20:55:21 +0200
Subject: [PATCH v1 3/4] Unlogged sequences
---
doc/src/sgml/ref/create_sequence.sgml | 23 ++++++++++-
src/backend/commands/sequence.c | 44 +++++++++++++++++++---
src/test/recovery/t/014_unlogged_reinit.pl | 33 ++++++++++++----
src/test/regress/expected/sequence.out | 5 ++-
src/test/regress/sql/sequence.sql | 5 ++-
5 files changed, 93 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/ref/create_sequence.sgml
b/doc/src/sgml/ref/create_sequence.sgml
index 3e0d339c85..6d06c6eaeb 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 ]
@@ -91,6 +91,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/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 0960b339ca..186ddecba5 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"
@@ -133,12 +134,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,
@@ -228,6 +223,43 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
if (owned_by)
process_owned_by(rel, owned_by, seq->for_identity);
+ /*
+ * create init fork for unlogged sequences
+ *
+ * The logic follows that of RelationCreateStorage() and
+ * RelationCopyStorage().
+ */
+ if (seq->sequence->relpersistence == RELPERSISTENCE_UNLOGGED)
+ {
+ SMgrRelation srel;
+ PGAlignedBlock buf;
+ Page page = (Page) buf.data;
+
+ FlushRelationBuffers(rel);
+
+ srel = smgropen(rel->rd_node, InvalidBackendId);
+ smgrcreate(srel, INIT_FORKNUM, false);
+ log_smgrcreate(&rel->rd_node, INIT_FORKNUM);
+
+ Assert(smgrnblocks(srel, MAIN_FORKNUM) == 1);
+
+ smgrread(srel, MAIN_FORKNUM, 0, buf.data);
+
+ if (!PageIsVerified(page, 0))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_CORRUPTED),
+ errmsg("invalid page in block %u of
relation %s",
+ 0,
+
relpathbackend(srel->smgr_rnode.node,
+
srel->smgr_rnode.backend,
+
MAIN_FORKNUM))));
+
+ log_newpage(&srel->smgr_rnode.node, INIT_FORKNUM, 0, page,
false);
+ PageSetChecksumInplace(page, 0);
+ smgrextend(srel, INIT_FORKNUM, 0, buf.data, false);
+ smgrclose(srel);
+ }
+
table_close(rel, NoLock);
/* fill in pg_sequence */
diff --git a/src/test/recovery/t/014_unlogged_reinit.pl
b/src/test/recovery/t/014_unlogged_reinit.pl
index 103c0a2b91..57f502d78a 100644
--- a/src/test/recovery/t/014_unlogged_reinit.pl
+++ b/src/test/recovery/t/014_unlogged_reinit.pl
@@ -7,7 +7,7 @@
use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 12;
+use Test::More tests => 20;
my $node = get_new_node('main');
@@ -15,16 +15,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.
@@ -53,6 +62,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');
@@ -63,13 +74,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');
diff --git a/src/test/regress/expected/sequence.out
b/src/test/regress/expected/sequence.out
index 75eb5015cf..c35ffb9f16 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;
@@ -599,6 +597,9 @@ 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 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/sequence.sql
b/src/test/regress/sql/sequence.sql
index 7928ee23ee..db04f308fc 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,10 @@ CREATE SEQUENCE seq2;
-- should fail
SELECT lastval();
+-- unlogged sequences
+-- (more tests in src/test/recovery/)
+CREATE SEQUENCE sequence_test_unlogged;
+
-- Test sequences in read-only transactions
CREATE TEMPORARY SEQUENCE sequence_test_temp1;
START TRANSACTION READ ONLY;
--
2.22.0
From 7a13bed00756b7136d3d633abfdb1d0a4dbfb6cb Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Tue, 11 Jun 2019 16:47:34 +0200
Subject: [PATCH v1 4/4] Make identity/serial sequences unlogged when their
table is
see bug #15631
---
src/backend/parser/parse_utilcmd.c | 1 +
src/test/regress/expected/alter_table.out | 4 ++--
2 files changed, 3 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index 7450d74b7a..30e4fb21db 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -434,6 +434,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/test/regress/expected/alter_table.out
b/src/test/regress/expected/alter_table.out
index c845a16d55..ab58803aef 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3247,7 +3247,7 @@ ORDER BY relname;
toast index | i | u
toast table | t | u
unlogged1 | r | u
- unlogged1_f1_seq | S | p
+ unlogged1_f1_seq | S | u
unlogged1_pkey | i | u
(5 rows)
@@ -3269,7 +3269,7 @@ ORDER BY relname;
toast index | i | p
toast table | t | p
unlogged1 | r | p
- unlogged1_f1_seq | S | p
+ unlogged1_f1_seq | S | u
unlogged1_pkey | i | p
(5 rows)
--
2.22.0
