From bb446b689b6681eb57a8a50605e119743190c4db Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 22 Oct 2011 18:22:45 -0400 Subject: Support synchronization of snapshots through an export/import procedure. A transaction can export a snapshot with pg_export_snapshot(), and then others can import it with SET TRANSACTION SNAPSHOT. The data does not leave the server so there are not security issues. A snapshot can only be imported while the exporting transaction is still running, and there are some other restrictions. I'm not totally convinced that we've covered all the bases for SSI (true serializable) mode, but it works fine for lesser isolation modes. Joachim Wieland, reviewed by Marko Tiikkaja, and rather heavily modified by Tom Lane --- doc/src/sgml/func.sgml | 130 ++++++++++++++++++++++++++++++++-- doc/src/sgml/ref/set_transaction.sgml | 79 ++++++++++++++++++--- doc/src/sgml/storage.sgml | 5 ++ 3 files changed, 198 insertions(+), 16 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 45b99566973..8dd69337f11 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -13802,6 +13802,14 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); System Administration Functions + + The functions described in this section are used to control and + monitor a PostgreSQL installation. + + + + Configuration Settings Functions + shows the functions available to query and alter run-time configuration parameters. @@ -13889,6 +13897,11 @@ SELECT set_config('log_statement_stats', 'off', false); + + + + Server Signalling Functions + pg_cancel_backend @@ -13985,6 +13998,11 @@ SELECT set_config('log_statement_stats', 'off', false); subprocess. + + + + Backup Control Functions + backup @@ -14181,6 +14199,11 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); . + + + + Recovery Control Functions + pg_is_in_recovery @@ -14198,7 +14221,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); The functions shown in provide information about the current status of the standby. - These functions may be executed during both recovery and in normal running. + These functions may be executed both during recovery and in normal running. @@ -14333,6 +14356,87 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); the pause, the rate of WAL generation and available disk space. + + + + Snapshot Synchronization Functions + + + pg_export_snapshot + + + + PostgreSQL allows database sessions to synchronize their + snapshots. A snapshot determines which data is visible to the + transaction that is using the snapshot. Synchronized snapshots are + necessary when two or more sessions need to see identical content in the + database. If two sessions just start their transactions independently, + there is always a possibility that some third transaction commits + between the executions of the two START TRANSACTION commands, + so that one session sees the effects of that transaction and the other + does not. + + + + To solve this problem, PostgreSQL allows a transaction to + export the snapshot it is using. As long as the exporting + transaction remains open, other transactions can import its + snapshot, and thereby be guaranteed that they see exactly the same view + of the database that the first transaction sees. But note that any + database changes made by any one of these transactions remain invisible + to the other transactions, as is usual for changes made by uncommitted + transactions. So the transactions are synchronized with respect to + pre-existing data, but act normally for changes they make themselves. + + + + Snapshots are exported with the pg_export_snapshot function, + shown in , and + imported with the command. + + +
+ Snapshot Synchronization Functions + + + Name Return Type Description + + + + + + + pg_export_snapshot() + + text + Save the current snapshot and return its identifier + + + +
+ + + The function pg_export_snapshot saves the current snapshot + and returns a text string identifying the snapshot. This string + must be passed (outside the database) to clients that want to import the + snapshot. The snapshot is available for import only until the end of the + transaction that exported it. A transaction can export more than one + snapshot, if needed. Note that doing so is only useful in READ + COMMITTED transactions, since in REPEATABLE READ and + higher isolation levels, transactions use the same snapshot throughout + their lifetime. Once a transaction has exported any snapshots, it cannot + be prepared with . + + + + See for details of how to use an + exported snapshot. + + + + + Database Object Management Functions + The functions shown in calculate the disk space usage of database objects. @@ -14591,9 +14695,14 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); the relation. + + + + Generic File Access Functions + The functions shown in provide native access to + linkend="functions-admin-genfile-table"> provide native access to files on the machine hosting the server. Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, @@ -14601,7 +14710,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); for log files. Use of these functions is restricted to superusers. - +
Generic File Access Functions @@ -14694,13 +14803,18 @@ SELECT (pg_stat_file('filename')).modification; + + + + Advisory Lock Functions + - The functions shown in manage - advisory locks. For details about proper use of these functions, see - . + The functions shown in + manage advisory locks. For details about proper use of these functions, + see . -
+
Advisory Lock Functions @@ -14972,6 +15086,8 @@ SELECT (pg_stat_file('filename')).modification; at session end, even if the client disconnects ungracefully.) + + diff --git a/doc/src/sgml/ref/set_transaction.sgml b/doc/src/sgml/ref/set_transaction.sgml index e28a7e1cde2..4327ca51a69 100644 --- a/doc/src/sgml/ref/set_transaction.sgml +++ b/doc/src/sgml/ref/set_transaction.sgml @@ -33,6 +33,7 @@ SET TRANSACTION transaction_mode [, ...] +SET TRANSACTION SNAPSHOT snapshot_id SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...] where transaction_mode is one of: @@ -60,6 +61,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTION transa The available transaction characteristics are the transaction isolation level, the transaction access mode (read/write or read-only), and the deferrable mode. + In addition, a snapshot can be selected, though only for the current + transaction, not as a session default. @@ -98,7 +101,7 @@ SET SESSION CHARACTERISTICS AS TRANSACTION transa serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a - serialization_failure SQLSTATE. + serialization_failure error. @@ -139,13 +142,41 @@ SET SESSION CHARACTERISTICS AS TRANSACTION transa The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and - READ ONLY. When all of these properties are set on a + READ ONLY. When all three of these properties are + selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups. + + + The SET TRANSACTION SNAPSHOT command allows a new + transaction to run with the same snapshot as an existing + transaction. The pre-existing transaction must have exported its snapshot + with the pg_export_snapshot function (see ). That function returns a + snapshot identifier, which must be given to SET TRANSACTION + SNAPSHOT to specify which snapshot is to be imported. The + identifier must be written as a string literal in this command, for example + '000003A1-1'. + SET TRANSACTION SNAPSHOT can only be executed at the + start of a transaction, before the first query or + data-modification statement (SELECT, + INSERT, DELETE, + UPDATE, FETCH, or + COPY) of the transaction. Furthermore, the transaction + must already be set to SERIALIZABLE or + REPEATABLE READ isolation level (otherwise, the snapshot + would be discarded immediately, since READ COMMITTED mode takes + a new snapshot for each command). If the importing transaction uses + SERIALIZABLE isolation level, then the transaction that + exported the snapshot must also use that isolation level. Also, a + non-read-only serializable transaction cannot import a snapshot from a + read-only transaction. + + @@ -163,6 +194,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTION transa by instead specifying the desired transaction_modes in BEGIN or START TRANSACTION. + But that option is not available for SET TRANSACTION + SNAPSHOT. @@ -178,11 +211,45 @@ SET SESSION CHARACTERISTICS AS TRANSACTION transa + + Examples + + + To begin a new transaction with the same snapshot as an already + existing transaction, first export the snapshot from the existing + transaction. That will return the snapshot identifier, for example: + + +BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT pg_export_snapshot(); + pg_export_snapshot +-------------------- + 000003A1-1 +(1 row) + + + Then give the snapshot identifier in a SET TRANSACTION + SNAPSHOT command at the beginning of the newly opened + transaction: + + +BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET TRANSACTION SNAPSHOT '000003A1-1'; + + + + Compatibility - Both commands are defined in the SQL standard. + These commands are defined in the SQL standard, + except for the DEFERRABLE transaction mode + and the SET TRANSACTION SNAPSHOT form, which are + PostgreSQL extensions. + + + SERIALIZABLE is the default transaction isolation level in the standard. In PostgreSQL the default is ordinarily @@ -197,12 +264,6 @@ SET SESSION CHARACTERISTICS AS TRANSACTION transa not implemented in the PostgreSQL server. - - The DEFERRABLE - transaction_mode - is a PostgreSQL language extension. - - The SQL standard requires commas between successive transaction_modes, but for historical diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml index 0a133bb7c7e..cb2f60e1eee 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -87,6 +87,11 @@ Item Subdirectory containing information about committed serializable transactions + + pg_snapshots + Subdirectory containing exported snapshots + + pg_stat_tmp Subdirectory containing temporary files for the statistics -- cgit v1.2.3