From cf6b207b24ba8c356481ce17b3482823f3f63a2f Mon Sep 17 00:00:00 2001 From: Peter Eisentraut 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 ] name [ RESTART [ [ WITH ] restart ] ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ] +ALTER SEQUENCE [ IF EXISTS ] name SET { LOGGED | UNLOGGED } ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema @@ -237,6 +238,17 @@ ALTER SEQUENCE [ IF EXISTS ] name S + + SET { LOGGED | UNLOGGED } + + + This form changes the sequence from unlogged to logged or vice-versa + (see ). It cannot be applied to a + temporary sequence. + + + + OWNED BY table_name.column_name OWNED BY NONE 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 -CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name +CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name [ AS data_type ] [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] @@ -92,6 +92,27 @@ SELECT * FROM name; + + UNLOGGED + + + 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. + + + + 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. + + + + IF NOT EXISTS 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 - 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. 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 */ 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 AS */ else if (TailMatches("ALTER", "SEQUENCE", MatchAny, "AS")) @@ -2123,6 +2123,9 @@ psql_completion(const char *text, int start, int end) /* ALTER SEQUENCE NO */ else if (Matches("ALTER", "SEQUENCE", MatchAny, "NO")) COMPLETE_WITH("MINVALUE", "MAXVALUE", "CYCLE"); + /* ALTER SEQUENCE SET */ + else if (Matches("ALTER", "SEQUENCE", MatchAny, "SET")) + COMPLETE_WITH("SCHEMA", "LOGGED", "UNLOGGED"); /* ALTER SERVER */ 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