Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit344d62f

Browse files
committed
Unlogged sequences
Add support for unlogged sequences. Unlike for unlogged tables, thisis not a performance feature. It allows sequences associated withunlogged 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 thepersistence level (logged/unlogged) of its owning table. (Thesequences owned by temporary tables were already temporary through theseparate mechanism in RangeVarAdjustRelationPersistence().) But youcan still change the persistence of an owned sequence separately.Also, pg_dump and pg_upgrade preserve the persistence of existingsequences.Discussion:https://www.postgresql.org/message-id/flat/04e12818-2f98-257c-b926-2845d74ed04f%402ndquadrant.com
1 parentbab588c commit344d62f

File tree

16 files changed

+237
-31
lines changed

16 files changed

+237
-31
lines changed

‎doc/src/sgml/ref/alter_sequence.sgml‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3131
[ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
3232
[ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
3333
[ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
34+
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET { LOGGED | UNLOGGED }
3435
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
3536
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
3637
ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
@@ -237,6 +238,17 @@ ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> S
237238
</listitem>
238239
</varlistentry>
239240

241+
<varlistentry>
242+
<term><literal>SET { LOGGED | UNLOGGED }</literal></term>
243+
<listitem>
244+
<para>
245+
This form changes the sequence from unlogged to logged or vice-versa
246+
(see <xref linkend="sql-createsequence"/>). It cannot be applied to a
247+
temporary sequence.
248+
</para>
249+
</listitem>
250+
</varlistentry>
251+
240252
<varlistentry>
241253
<term><literal>OWNED BY</literal> <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable></term>
242254
<term><literal>OWNED BY NONE</literal></term>

‎doc/src/sgml/ref/alter_table.sgml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -753,6 +753,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
753753
(see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied
754754
to a temporary table.
755755
</para>
756+
757+
<para>
758+
This also changes the persistence of any sequences linked to the table
759+
(for identity or serial columns). However, it is also possible to
760+
change the persistence of such sequences separately.
761+
</para>
756762
</listitem>
757763
</varlistentry>
758764

‎doc/src/sgml/ref/create_sequence.sgml‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
24+
CREATE [{TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
2525
[ AS <replaceable class="parameter">data_type</replaceable> ]
2626
[ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
2727
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
@@ -92,6 +92,27 @@ SELECT * FROM <replaceable>name</replaceable>;
9292
</listitem>
9393
</varlistentry>
9494

95+
<varlistentry>
96+
<term><literal>UNLOGGED</literal></term>
97+
<listitem>
98+
<para>
99+
If specified, the sequence is created as an unlogged sequence. Changes
100+
to unlogged sequences are not written to the write-ahead log. They are
101+
not crash-safe: an unlogged sequence is automatically reset to its
102+
initial state after a crash or unclean shutdown. Unlogged sequences are
103+
also not replicated to standby servers.
104+
</para>
105+
106+
<para>
107+
Unlike unlogged tables, unlogged sequences do not offer a significant
108+
performance advantage. This option is mainly intended for sequences
109+
associated with unlogged tables via identity columns or serial columns.
110+
In those cases, it usually wouldn't make sense to have the sequence
111+
WAL-logged and replicated but not its associated table.
112+
</para>
113+
</listitem>
114+
</varlistentry>
115+
95116
<varlistentry>
96117
<term><literal>IF NOT EXISTS</literal></term>
97118
<listitem>

‎doc/src/sgml/ref/create_table.sgml‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -215,6 +215,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
215215
Any indexes created on an unlogged table are automatically unlogged as
216216
well.
217217
</para>
218+
219+
<para>
220+
If this is specified, any sequences created together with the unlogged
221+
table (for identity or serial columns) are also created as unlogged.
222+
</para>
218223
</listitem>
219224
</varlistentry>
220225

‎doc/src/sgml/ref/pg_dump.sgml‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -981,9 +981,10 @@ PostgreSQL documentation
981981
<term><option>--no-unlogged-table-data</option></term>
982982
<listitem>
983983
<para>
984-
Do not dump the contents of unlogged tables. This option has no
985-
effect on whether or not the table definitions (schema) are dumped;
986-
it only suppresses dumping the table data. Data in unlogged tables
984+
Do not dump the contents of unlogged tables and sequences. This
985+
option has no effect on whether or not the table and sequence
986+
definitions (schema) are dumped; it only suppresses dumping the table
987+
and sequence data. Data in unlogged tables and sequences
987988
is always excluded when dumping from a standby server.
988989
</para>
989990
</listitem>

‎src/backend/commands/sequence.c‎

Lines changed: 50 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,7 @@
3030
#include"catalog/objectaccess.h"
3131
#include"catalog/pg_sequence.h"
3232
#include"catalog/pg_type.h"
33+
#include"catalog/storage_xlog.h"
3334
#include"commands/defrem.h"
3435
#include"commands/sequence.h"
3536
#include"commands/tablecmds.h"
@@ -95,6 +96,7 @@ static HTAB *seqhashtab = NULL; /* hash table for SeqTable items */
9596
staticSeqTableData*last_used_seq=NULL;
9697

9798
staticvoidfill_seq_with_data(Relationrel,HeapTupletuple);
99+
staticvoidfill_seq_fork_with_data(Relationrel,HeapTupletuple,ForkNumberforkNum);
98100
staticRelationlock_and_open_sequence(SeqTableseq);
99101
staticvoidcreate_seq_hashtable(void);
100102
staticvoidinit_sequence(Oidrelid,SeqTable*p_elm,Relation*p_rel);
@@ -133,12 +135,6 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
133135
boolpgs_nulls[Natts_pg_sequence];
134136
inti;
135137

136-
/* Unlogged sequences are not implemented -- not clear if useful. */
137-
if (seq->sequence->relpersistence==RELPERSISTENCE_UNLOGGED)
138-
ereport(ERROR,
139-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
140-
errmsg("unlogged sequences are not supported")));
141-
142138
/*
143139
* If if_not_exists was given and a relation with the same name already
144140
* 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,
492488

493489
/*
494490
* Initialize a sequence's relation with the specified tuple as content
491+
*
492+
* This handles unlogged sequences by writing to both the main and the init
493+
* fork as necessary.
495494
*/
496495
staticvoid
497496
fill_seq_with_data(Relationrel,HeapTupletuple)
497+
{
498+
fill_seq_fork_with_data(rel,tuple,MAIN_FORKNUM);
499+
500+
if (rel->rd_rel->relpersistence==RELPERSISTENCE_UNLOGGED)
501+
{
502+
SMgrRelationsrel;
503+
504+
srel=smgropen(rel->rd_node,InvalidBackendId);
505+
smgrcreate(srel,INIT_FORKNUM, false);
506+
log_smgrcreate(&rel->rd_node,INIT_FORKNUM);
507+
fill_seq_fork_with_data(rel,tuple,INIT_FORKNUM);
508+
FlushRelationBuffers(rel);
509+
smgrclose(srel);
510+
}
511+
}
512+
513+
/*
514+
* Initialize a sequence's relation fork with the specified tuple as content
515+
*/
516+
staticvoid
517+
fill_seq_fork_with_data(Relationrel,HeapTupletuple,ForkNumberforkNum)
498518
{
499519
Bufferbuf;
500520
Pagepage;
@@ -503,7 +523,7 @@ fill_seq_with_data(Relation rel, HeapTuple tuple)
503523

504524
/* Initialize first page of relation with special magic number */
505525

506-
buf=ReadBuffer(rel,P_NEW);
526+
buf=ReadBufferExtended(rel,forkNum,P_NEW,RBM_NORMAL,NULL);
507527
Assert(BufferGetBlockNumber(buf)==0);
508528

509529
page=BufferGetPage(buf);
@@ -549,7 +569,7 @@ fill_seq_with_data(Relation rel, HeapTuple tuple)
549569
elog(ERROR,"failed to add sequence tuple to page");
550570

551571
/* XLOG stuff */
552-
if (RelationNeedsWAL(rel))
572+
if (RelationNeedsWAL(rel)||forkNum==INIT_FORKNUM)
553573
{
554574
xl_seq_recxlrec;
555575
XLogRecPtrrecptr;
@@ -682,6 +702,28 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)
682702
returnaddress;
683703
}
684704

705+
void
706+
SequenceChangePersistence(Oidrelid,charnewrelpersistence)
707+
{
708+
SeqTableelm;
709+
Relationseqrel;
710+
Bufferbuf;
711+
HeapTupleDataseqdatatuple;
712+
713+
init_sequence(relid,&elm,&seqrel);
714+
715+
/* check the comment above nextval_internal()'s equivalent call. */
716+
if (RelationNeedsWAL(seqrel))
717+
GetTopTransactionId();
718+
719+
(void)read_seq_tuple(seqrel,&buf,&seqdatatuple);
720+
RelationSetNewRelfilenode(seqrel,newrelpersistence);
721+
fill_seq_with_data(seqrel,&seqdatatuple);
722+
UnlockReleaseBuffer(buf);
723+
724+
relation_close(seqrel,NoLock);
725+
}
726+
685727
void
686728
DeleteSequenceTuple(Oidrelid)
687729
{

‎src/backend/commands/tablecmds.c‎

Lines changed: 29 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -319,6 +319,7 @@ struct DropRelationCallbackState
319319
#defineATT_COMPOSITE_TYPE0x0010
320320
#defineATT_FOREIGN_TABLE0x0020
321321
#defineATT_PARTITIONED_INDEX0x0040
322+
#defineATT_SEQUENCE0x0080
322323

323324
/*
324325
* ForeignTruncateInfo
@@ -4660,7 +4661,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
46604661
pass = AT_PASS_MISC;
46614662
break;
46624663
case AT_SetLogged:/* SET LOGGED */
4663-
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
4664+
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_SEQUENCE);
46644665
if (tab->chgPersistence)
46654666
ereport(ERROR,
46664667
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4675,7 +4676,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
46754676
pass = AT_PASS_MISC;
46764677
break;
46774678
case AT_SetUnLogged:/* SET UNLOGGED */
4678-
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE);
4679+
ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_SEQUENCE);
46794680
if (tab->chgPersistence)
46804681
ereport(ERROR,
46814682
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -5425,7 +5426,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
54255426
* and assigns a new relfilenode, we automatically create or drop an
54265427
* init fork for the relation as appropriate.
54275428
*/
5428-
if (tab->rewrite > 0)
5429+
if (tab->rewrite > 0 && tab->relkind != RELKIND_SEQUENCE)
54295430
{
54305431
/* Build a temporary relation and copy data */
54315432
RelationOldHeap;
@@ -5546,6 +5547,11 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
55465547

55475548
InvokeObjectPostAlterHook(RelationRelationId, tab->relid, 0);
55485549
}
5550+
else if (tab->rewrite > 0 && tab->relkind == RELKIND_SEQUENCE)
5551+
{
5552+
if (tab->chgPersistence)
5553+
SequenceChangePersistence(tab->relid, tab->newrelpersistence);
5554+
}
55495555
else
55505556
{
55515557
/*
@@ -5564,6 +5570,23 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
55645570
if (tab->newTableSpace)
55655571
ATExecSetTableSpace(tab->relid, tab->newTableSpace, lockmode);
55665572
}
5573+
5574+
/*
5575+
* Also change persistence of owned sequences, so that it matches the
5576+
* table persistence.
5577+
*/
5578+
if (tab->chgPersistence)
5579+
{
5580+
List *seqlist = getOwnedSequences(tab->relid);
5581+
ListCell *lc;
5582+
5583+
foreach(lc, seqlist)
5584+
{
5585+
Oid seq_relid = lfirst_oid(lc);
5586+
5587+
SequenceChangePersistence(seq_relid, tab->newrelpersistence);
5588+
}
5589+
}
55675590
}
55685591

55695592
/*
@@ -6224,6 +6247,9 @@ ATSimplePermissions(AlterTableType cmdtype, Relation rel, int allowed_targets)
62246247
case RELKIND_FOREIGN_TABLE:
62256248
actual_target = ATT_FOREIGN_TABLE;
62266249
break;
6250+
case RELKIND_SEQUENCE:
6251+
actual_target = ATT_SEQUENCE;
6252+
break;
62276253
default:
62286254
actual_target = 0;
62296255
break;

‎src/backend/parser/parse_utilcmd.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -445,6 +445,7 @@ generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column,
445445
seqstmt=makeNode(CreateSeqStmt);
446446
seqstmt->for_identity=for_identity;
447447
seqstmt->sequence=makeRangeVar(snamespace,sname,-1);
448+
seqstmt->sequence->relpersistence=cxt->relation->relpersistence;
448449
seqstmt->options=seqoptions;
449450

450451
/*

‎src/bin/pg_dump/pg_dump.c‎

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16570,6 +16570,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1657016570
PQExpBuffer query = createPQExpBuffer();
1657116571
PQExpBuffer delqry = createPQExpBuffer();
1657216572
char *qseqname;
16573+
TableInfo *owning_tab = NULL;
1657316574

1657416575
qseqname = pg_strdup(fmtId(tbinfo->dobj.name));
1657516576

@@ -16678,7 +16679,7 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1667816679

1667916680
if (tbinfo->is_identity_sequence)
1668016681
{
16681-
TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab);
16682+
owning_tab = findTableByOid(tbinfo->owning_tab);
1668216683

1668316684
appendPQExpBuffer(query,
1668416685
"ALTER TABLE %s ",
@@ -16696,7 +16697,9 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1669616697
else
1669716698
{
1669816699
appendPQExpBuffer(query,
16699-
"CREATE SEQUENCE %s\n",
16700+
"CREATE %sSEQUENCE %s\n",
16701+
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
16702+
"UNLOGGED " : "",
1670016703
fmtQualifiedDumpable(tbinfo));
1670116704

1670216705
if (strcmp(seqtype, "bigint") != 0)
@@ -16722,7 +16725,15 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
1672216725
cache, (cycled ? "\n CYCLE" : ""));
1672316726

1672416727
if (tbinfo->is_identity_sequence)
16728+
{
1672516729
appendPQExpBufferStr(query, "\n);\n");
16730+
if (tbinfo->relpersistence != owning_tab->relpersistence)
16731+
appendPQExpBuffer(query,
16732+
"ALTER SEQUENCE %s SET %s;\n",
16733+
fmtQualifiedDumpable(tbinfo),
16734+
tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
16735+
"UNLOGGED" : "LOGGED");
16736+
}
1672616737
else
1672716738
appendPQExpBufferStr(query, ";\n");
1672816739

‎src/bin/psql/describe.c‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1681,8 +1681,12 @@ describeOneTableDetails(const char *schemaname,
16811681
printTableInit(&cont,&myopt,title.data,7,numrows);
16821682
printTableInitialized= true;
16831683

1684-
printfPQExpBuffer(&title,_("Sequence \"%s.%s\""),
1685-
schemaname,relationname);
1684+
if (tableinfo.relpersistence=='u')
1685+
printfPQExpBuffer(&title,_("Unlogged sequence \"%s.%s\""),
1686+
schemaname,relationname);
1687+
else
1688+
printfPQExpBuffer(&title,_("Sequence \"%s.%s\""),
1689+
schemaname,relationname);
16861690

16871691
printTableAddHeader(&cont,gettext_noop("Type"), true,'l');
16881692
printTableAddHeader(&cont,gettext_noop("Start"), true,'r');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp