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

Commitbb446b6

Browse files
committed
Support synchronization of snapshots through an export/import procedure.
A transaction can export a snapshot with pg_export_snapshot(), and thenothers can import it with SET TRANSACTION SNAPSHOT. The data does notleave the server so there are not security issues. A snapshot can onlybe imported while the exporting transaction is still running, and thereare some other restrictions.I'm not totally convinced that we've covered all the bases for SSI (trueserializable) mode, but it works fine for lesser isolation modes.Joachim Wieland, reviewed by Marko Tiikkaja, and rather heavily modifiedby Tom Lane
1 parentb436c72 commitbb446b6

File tree

17 files changed

+1030
-34
lines changed

17 files changed

+1030
-34
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 123 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -13802,6 +13802,14 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
1380213802
<sect1 id="functions-admin">
1380313803
<title>System Administration Functions</title>
1380413804

13805+
<para>
13806+
The functions described in this section are used to control and
13807+
monitor a <productname>PostgreSQL</> installation.
13808+
</para>
13809+
13810+
<sect2 id="functions-admin-set">
13811+
<title>Configuration Settings Functions</title>
13812+
1380513813
<para>
1380613814
<xref linkend="functions-admin-set-table"> shows the functions
1380713815
available to query and alter run-time configuration parameters.
@@ -13889,6 +13897,11 @@ SELECT set_config('log_statement_stats', 'off', false);
1388913897
</programlisting>
1389013898
</para>
1389113899

13900+
</sect2>
13901+
13902+
<sect2 id="functions-admin-signal">
13903+
<title>Server Signalling Functions</title>
13904+
1389213905
<indexterm>
1389313906
<primary>pg_cancel_backend</primary>
1389413907
</indexterm>
@@ -13985,6 +13998,11 @@ SELECT set_config('log_statement_stats', 'off', false);
1398513998
subprocess.
1398613999
</para>
1398714000

14001+
</sect2>
14002+
14003+
<sect2 id="functions-admin-backup">
14004+
<title>Backup Control Functions</title>
14005+
1398814006
<indexterm>
1398914007
<primary>backup</primary>
1399014008
</indexterm>
@@ -14181,6 +14199,11 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1418114199
<xref linkend="continuous-archiving">.
1418214200
</para>
1418314201

14202+
</sect2>
14203+
14204+
<sect2 id="functions-recovery-control">
14205+
<title>Recovery Control Functions</title>
14206+
1418414207
<indexterm>
1418514208
<primary>pg_is_in_recovery</primary>
1418614209
</indexterm>
@@ -14198,7 +14221,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1419814221
The functions shown in <xref
1419914222
linkend="functions-recovery-info-table"> provide information
1420014223
about the current status of the standby.
14201-
These functions may be executedduringboth recovery and in normal running.
14224+
These functions may be executed both during recovery and in normal running.
1420214225
</para>
1420314226

1420414227
<table id="functions-recovery-info-table">
@@ -14333,6 +14356,87 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1433314356
the pause, the rate of WAL generation and available disk space.
1433414357
</para>
1433514358

14359+
</sect2>
14360+
14361+
<sect2 id="functions-snapshot-synchronization">
14362+
<title>Snapshot Synchronization Functions</title>
14363+
14364+
<indexterm>
14365+
<primary>pg_export_snapshot</primary>
14366+
</indexterm>
14367+
14368+
<para>
14369+
<productname>PostgreSQL</> allows database sessions to synchronize their
14370+
snapshots. A <firstterm>snapshot</> determines which data is visible to the
14371+
transaction that is using the snapshot. Synchronized snapshots are
14372+
necessary when two or more sessions need to see identical content in the
14373+
database. If two sessions just start their transactions independently,
14374+
there is always a possibility that some third transaction commits
14375+
between the executions of the two <command>START TRANSACTION</> commands,
14376+
so that one session sees the effects of that transaction and the other
14377+
does not.
14378+
</para>
14379+
14380+
<para>
14381+
To solve this problem, <productname>PostgreSQL</> allows a transaction to
14382+
<firstterm>export</> the snapshot it is using. As long as the exporting
14383+
transaction remains open, other transactions can <firstterm>import</> its
14384+
snapshot, and thereby be guaranteed that they see exactly the same view
14385+
of the database that the first transaction sees. But note that any
14386+
database changes made by any one of these transactions remain invisible
14387+
to the other transactions, as is usual for changes made by uncommitted
14388+
transactions. So the transactions are synchronized with respect to
14389+
pre-existing data, but act normally for changes they make themselves.
14390+
</para>
14391+
14392+
<para>
14393+
Snapshots are exported with the <function>pg_export_snapshot</> function,
14394+
shown in <xref linkend="functions-snapshot-synchronization-table">, and
14395+
imported with the <xref linkend="sql-set-transaction"> command.
14396+
</para>
14397+
14398+
<table id="functions-snapshot-synchronization-table">
14399+
<title>Snapshot Synchronization Functions</title>
14400+
<tgroup cols="3">
14401+
<thead>
14402+
<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
14403+
</row>
14404+
</thead>
14405+
14406+
<tbody>
14407+
<row>
14408+
<entry>
14409+
<literal><function>pg_export_snapshot()</function></literal>
14410+
</entry>
14411+
<entry><type>text</type></entry>
14412+
<entry>Save the current snapshot and return its identifier</entry>
14413+
</row>
14414+
</tbody>
14415+
</tgroup>
14416+
</table>
14417+
14418+
<para>
14419+
The function <function>pg_export_snapshot</> saves the current snapshot
14420+
and returns a <type>text</> string identifying the snapshot. This string
14421+
must be passed (outside the database) to clients that want to import the
14422+
snapshot. The snapshot is available for import only until the end of the
14423+
transaction that exported it. A transaction can export more than one
14424+
snapshot, if needed. Note that doing so is only useful in <literal>READ
14425+
COMMITTED</> transactions, since in <literal>REPEATABLE READ</> and
14426+
higher isolation levels, transactions use the same snapshot throughout
14427+
their lifetime. Once a transaction has exported any snapshots, it cannot
14428+
be prepared with <xref linkend="sql-prepare-transaction">.
14429+
</para>
14430+
14431+
<para>
14432+
See <xref linkend="sql-set-transaction"> for details of how to use an
14433+
exported snapshot.
14434+
</para>
14435+
</sect2>
14436+
14437+
<sect2 id="functions-admin-dbobject">
14438+
<title>Database Object Management Functions</title>
14439+
1433614440
<para>
1433714441
The functions shown in <xref linkend="functions-admin-dbsize"> calculate
1433814442
the disk space usage of database objects.
@@ -14591,17 +14695,22 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1459114695
the relation.
1459214696
</para>
1459314697

14698+
</sect2>
14699+
14700+
<sect2 id="functions-admin-genfile">
14701+
<title>Generic File Access Functions</title>
14702+
1459414703
<para>
1459514704
The functions shown in <xref
14596-
linkend="functions-admin-genfile"> provide native access to
14705+
linkend="functions-admin-genfile-table"> provide native access to
1459714706
files on the machine hosting the server. Only files within the
1459814707
database cluster directory and the <varname>log_directory</> can be
1459914708
accessed. Use a relative path for files in the cluster directory,
1460014709
and a path matching the <varname>log_directory</> configuration setting
1460114710
for log files. Use of these functions is restricted to superusers.
1460214711
</para>
1460314712

14604-
<table id="functions-admin-genfile">
14713+
<table id="functions-admin-genfile-table">
1460514714
<title>Generic File Access Functions</title>
1460614715
<tgroup cols="3">
1460714716
<thead>
@@ -14694,13 +14803,18 @@ SELECT (pg_stat_file('filename')).modification;
1469414803
</programlisting>
1469514804
</para>
1469614805

14806+
</sect2>
14807+
14808+
<sect2 id="functions-advisory-locks">
14809+
<title>Advisory Lock Functions</title>
14810+
1469714811
<para>
14698-
The functions shown in <xref linkend="functions-advisory-locks"> manage
14699-
advisory locks. For details about proper use of these functions, see
14700-
<xref linkend="advisory-locks">.
14812+
The functions shown in <xref linkend="functions-advisory-locks-table">
14813+
manageadvisory locks. For details about proper use of these functions,
14814+
see<xref linkend="advisory-locks">.
1470114815
</para>
1470214816

14703-
<table id="functions-advisory-locks">
14817+
<table id="functions-advisory-locks-table">
1470414818
<title>Advisory Lock Functions</title>
1470514819
<tgroup cols="3">
1470614820
<thead>
@@ -14972,6 +15086,8 @@ SELECT (pg_stat_file('filename')).modification;
1497215086
at session end, even if the client disconnects ungracefully.)
1497315087
</para>
1497415088

15089+
</sect2>
15090+
1497515091
</sect1>
1497615092

1497715093
<sect1 id="functions-trigger">

‎doc/src/sgml/ref/set_transaction.sgml

Lines changed: 70 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@
3333
<refsynopsisdiv>
3434
<synopsis>
3535
SET TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
36+
SET TRANSACTION SNAPSHOT <replaceable class="parameter">snapshot_id</replaceable>
3637
SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
3738

3839
<phrase>where <replaceable class="parameter">transaction_mode</replaceable> is one of:</phrase>
@@ -60,6 +61,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
6061
The available transaction characteristics are the transaction
6162
isolation level, the transaction access mode (read/write or
6263
read-only), and the deferrable mode.
64+
In addition, a snapshot can be selected, though only for the current
65+
transaction, not as a session default.
6366
</para>
6467

6568
<para>
@@ -98,7 +101,7 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
98101
serializable transactions would create a situation which could not
99102
have occurred for any serial (one-at-a-time) execution of those
100103
transactions, one of them will be rolled back with a
101-
<literal>serialization_failure</literal><literal>SQLSTATE</literal>.
104+
<literal>serialization_failure</literal>error.
102105
</para>
103106
</listitem>
104107
</varlistentry>
@@ -139,13 +142,41 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
139142
<para>
140143
The <literal>DEFERRABLE</literal> transaction property has no effect
141144
unless the transaction is also <literal>SERIALIZABLE</literal> and
142-
<literal>READ ONLY</literal>. When all of these properties are set on a
145+
<literal>READ ONLY</literal>. When all three of these properties are
146+
selected for a
143147
transaction, the transaction may block when first acquiring its snapshot,
144148
after which it is able to run without the normal overhead of a
145149
<literal>SERIALIZABLE</literal> transaction and without any risk of
146150
contributing to or being canceled by a serialization failure. This mode
147151
is well suited for long-running reports or backups.
148152
</para>
153+
154+
<para>
155+
The <literal>SET TRANSACTION SNAPSHOT</literal> command allows a new
156+
transaction to run with the same <firstterm>snapshot</> as an existing
157+
transaction. The pre-existing transaction must have exported its snapshot
158+
with the <literal>pg_export_snapshot</literal> function (see <xref
159+
linkend="functions-snapshot-synchronization">). That function returns a
160+
snapshot identifier, which must be given to <literal>SET TRANSACTION
161+
SNAPSHOT</literal> to specify which snapshot is to be imported. The
162+
identifier must be written as a string literal in this command, for example
163+
<literal>'000003A1-1'</>.
164+
<literal>SET TRANSACTION SNAPSHOT</literal> can only be executed at the
165+
start of a transaction, before the first query or
166+
data-modification statement (<command>SELECT</command>,
167+
<command>INSERT</command>, <command>DELETE</command>,
168+
<command>UPDATE</command>, <command>FETCH</command>, or
169+
<command>COPY</command>) of the transaction. Furthermore, the transaction
170+
must already be set to <literal>SERIALIZABLE</literal> or
171+
<literal>REPEATABLE READ</literal> isolation level (otherwise, the snapshot
172+
would be discarded immediately, since <literal>READ COMMITTED</> mode takes
173+
a new snapshot for each command). If the importing transaction uses
174+
<literal>SERIALIZABLE</literal> isolation level, then the transaction that
175+
exported the snapshot must also use that isolation level. Also, a
176+
non-read-only serializable transaction cannot import a snapshot from a
177+
read-only transaction.
178+
</para>
179+
149180
</refsect1>
150181

151182
<refsect1>
@@ -163,6 +194,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
163194
by instead specifying the desired <replaceable
164195
class="parameter">transaction_modes</replaceable> in
165196
<command>BEGIN</command> or <command>START TRANSACTION</command>.
197+
But that option is not available for <command>SET TRANSACTION
198+
SNAPSHOT</command>.
166199
</para>
167200

168201
<para>
@@ -178,11 +211,45 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
178211
</para>
179212
</refsect1>
180213

214+
<refsect1>
215+
<title>Examples</title>
216+
217+
<para>
218+
To begin a new transaction with the same snapshot as an already
219+
existing transaction, first export the snapshot from the existing
220+
transaction. That will return the snapshot identifier, for example:
221+
222+
<programlisting>
223+
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
224+
SELECT pg_export_snapshot();
225+
pg_export_snapshot
226+
--------------------
227+
000003A1-1
228+
(1 row)
229+
</programlisting>
230+
231+
Then give the snapshot identifier in a <command>SET TRANSACTION
232+
SNAPSHOT</command> command at the beginning of the newly opened
233+
transaction:
234+
235+
<programlisting>
236+
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
237+
SET TRANSACTION SNAPSHOT '000003A1-1';
238+
</programlisting>
239+
</para>
240+
</refsect1>
241+
181242
<refsect1 id="R1-SQL-SET-TRANSACTION-3">
182243
<title>Compatibility</title>
183244

184245
<para>
185-
Both commands are defined in the <acronym>SQL</acronym> standard.
246+
These commands are defined in the <acronym>SQL</acronym> standard,
247+
except for the <literal>DEFERRABLE</literal> transaction mode
248+
and the <command>SET TRANSACTION SNAPSHOT</> form, which are
249+
<productname>PostgreSQL</productname> extensions.
250+
</para>
251+
252+
<para>
186253
<literal>SERIALIZABLE</literal> is the default transaction
187254
isolation level in the standard. In
188255
<productname>PostgreSQL</productname> the default is ordinarily
@@ -197,12 +264,6 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
197264
not implemented in the <productname>PostgreSQL</productname> server.
198265
</para>
199266

200-
<para>
201-
The <literal>DEFERRABLE</literal>
202-
<replaceable class="parameter">transaction_mode</replaceable>
203-
is a <productname>PostgreSQL</productname> language extension.
204-
</para>
205-
206267
<para>
207268
The SQL standard requires commas between successive <replaceable
208269
class="parameter">transaction_modes</replaceable>, but for historical

‎doc/src/sgml/storage.sgml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,11 @@ Item
8787
<entry>Subdirectory containing information about committed serializable transactions</entry>
8888
</row>
8989

90+
<row>
91+
<entry><filename>pg_snapshots</></entry>
92+
<entry>Subdirectory containing exported snapshots</entry>
93+
</row>
94+
9095
<row>
9196
<entry><filename>pg_stat_tmp</></entry>
9297
<entry>Subdirectory containing temporary files for the statistics

‎src/backend/access/transam/xact.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2067,6 +2067,16 @@ PrepareTransaction(void)
20672067
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
20682068
errmsg("cannot PREPARE a transaction that has operated on temporary tables")));
20692069

2070+
/*
2071+
* Likewise, don't allow PREPARE after pg_export_snapshot. This could be
2072+
* supported if we added cleanup logic to twophase.c, but for now it
2073+
* doesn't seem worth the trouble.
2074+
*/
2075+
if (XactHasExportedSnapshots())
2076+
ereport(ERROR,
2077+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2078+
errmsg("cannot PREPARE a transaction that has exported snapshots")));
2079+
20702080
/* Prevent cancel/die interrupt while cleaning up */
20712081
HOLD_INTERRUPTS();
20722082

‎src/backend/access/transam/xlog.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@
5858
#include"utils/guc.h"
5959
#include"utils/ps_status.h"
6060
#include"utils/relmapper.h"
61+
#include"utils/snapmgr.h"
6162
#include"utils/timestamp.h"
6263
#include"pg_trace.h"
6364

@@ -6381,6 +6382,12 @@ StartupXLOG(void)
63816382
*/
63826383
ResetUnloggedRelations(UNLOGGED_RELATION_CLEANUP);
63836384

6385+
/*
6386+
* Likewise, delete any saved transaction snapshot files that got
6387+
* left behind by crashed backends.
6388+
*/
6389+
DeleteAllExportedSnapshotFiles();
6390+
63846391
/*
63856392
* Initialize for Hot Standby, if enabled. We won't let backends in
63866393
* yet, not until we've reached the min recovery point specified in

0 commit comments

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