diff options
author | Daniele Varrazzo <daniele.varrazzo@gmail.com> | 2017-02-04 15:55:59 +0000 |
---|---|---|
committer | Daniele Varrazzo <daniele.varrazzo@gmail.com> | 2017-02-04 15:55:59 +0000 |
commit | c54a614c6e479e64d1819e2122a5b8972adc7a40 (patch) | |
tree | 7caa23a445d3f4dab23b45a073ae93fe090e4783 | |
parent | 9054eeccc0460908865e5462973fd82c148a9315 (diff) | |
download | psycopg2-c54a614c6e479e64d1819e2122a5b8972adc7a40.tar.gz |
Added documentation about the changes in transaction control
-rw-r--r-- | NEWS | 9 | ||||
-rw-r--r-- | doc/src/connection.rst | 64 | ||||
-rw-r--r-- | doc/src/extensions.rst | 24 | ||||
-rw-r--r-- | doc/src/usage.rst | 3 |
4 files changed, 79 insertions, 21 deletions
@@ -33,6 +33,10 @@ New features: (:ticket:`#491`). - Added ``async_`` as an alias for ``async`` to support Python 3.7 where ``async`` will become a keyword (:ticket:`#495`). +- Unless in autocommit, do not use :sql:`default_transaction_*` settings to + control the session characteristics as it may create problems with external + connection pools such as pgbouncer; use :sql:`BEGIN` options instead + (:ticket:`#503`). Bug fixes: @@ -44,6 +48,11 @@ Other changes: - Dropped support for Python 2.5. - Dropped support for client library older than PostgreSQL 9.1 (but older server versions are still supported). +- `~connection.isolation_level` doesn't read from the database but will return + `~psycopg2.extensions.ISOLATION_LEVEL_DEFAULT` if no value was set on the + connection. +- `~connection.set_isolation_level()` will throw an exception if executed + inside a transaction; previously it would have silently rolled it back. What's new in psycopg 2.6.3 diff --git a/doc/src/connection.rst b/doc/src/connection.rst index 0bc584c..2adad59 100644 --- a/doc/src/connection.rst +++ b/doc/src/connection.rst @@ -400,6 +400,32 @@ The ``connection`` class .. versionadded:: 2.4.2 + .. versionchanged:: 2.7 + Before this version, the function would have set + :sql:`default_transaction_*` attribute in the current session; + this implementation has the problem of not playing well with + external connection pooling working at transaction level and not + resetting the state of the session: changing the default + transaction would pollute the connections in the pool and create + problems to other applications using the same pool. + + Starting from 2.7, if the connection is not autocommit, the + transaction characteristics are issued together with :sql:`BEGIN` + and will leave the :sql:`default_transaction_*` settings untouched. + For example:: + + conn.set_session(readonly=True) + + will not change :sql:`default_transaction_read_only`, but + following transaction will start with a :sql:`BEGIN READ ONLY`. + Conversely, using:: + + conn.set_session(readonly=True, autocommit=True) + + will set :sql:`default_transaction_read_only` to :sql:`on` and + rely on the server to apply the read only state to whatever + transaction, implicit or explicit, is executed in the connection. + .. attribute:: autocommit @@ -428,32 +454,54 @@ The ``connection`` class .. versionadded:: 2.4.2 - .. attribute:: isolation_level .. method:: set_isolation_level(level) .. note:: - From version 2.4.2, `set_session()` and `autocommit`, offer + From version 2.4.2, `set_session()` and `autocommit` offer finer control on the transaction characteristics. - Read or set the `transaction isolation level`_ for the current session. + Set the `transaction isolation level`_ for the current session. The level defines the different phenomena that can happen in the database between concurrent transactions. - The value set or read is an integer: symbolic constants are defined in + The value set is an integer: symbolic constants are defined in the module `psycopg2.extensions`: see :ref:`isolation-level-constants` for the available values. - The default level is :sql:`READ COMMITTED`: at this level a - transaction is automatically started the first time a database command - is executed. If you want an *autocommit* mode, switch to - `~psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT` before + The default level is `~psycopg2.extensions.ISOLATION_LEVEL_DEFAULT`: + at this level a transaction is automatically started the first time a + database command is executed. If you want an *autocommit* mode, + switch to `~psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT` before executing any command:: >>> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) See also :ref:`transactions-control`. + .. versionchanged:: 2.7 + + the function must be called outside a transaction; previously it + would have executed an implicit :sql:`ROLLBACK`; it will now raise + an exception. + + + .. attribute:: isolation_level + + Read the `transaction isolation level`_ for the current session. The + value is one of the :ref:`isolation-level-constants` defined in the + `psycopg2.extensions` module. + + .. versionchanged:: 2.7 + + the default value for `!isolation_level` is + `~psycopg2.extensions.ISOLATION_LEVEL_DEFAULT`; previously the + property would have queried the server and returned the real value + applied. To know this value you can run a query such as :sql:`show + transaction_isolation`. Usually the default value is `READ + COMMITTED`, but this may be changed in the server configuration. + + .. index:: pair: Client; Encoding diff --git a/doc/src/extensions.rst b/doc/src/extensions.rst index 2475d7b..ae40b72 100644 --- a/doc/src/extensions.rst +++ b/doc/src/extensions.rst @@ -567,15 +567,16 @@ Isolation level constants ------------------------- Psycopg2 `connection` objects hold informations about the PostgreSQL -`transaction isolation level`_. The current transaction level can be read -from the `~connection.isolation_level` attribute. The default isolation -level is :sql:`READ COMMITTED`. A different isolation level con be set -through the `~connection.set_isolation_level()` method. The level can be -set to one of the following constants: +`transaction isolation level`_. By default Psycopg doesn't change the default +configuration of the server (`ISOLATION_LEVEL_DEFAULT`); the default for +PostgreSQL servers is typically :sql:`READ COMMITTED`, but this may be changed +in the server configuration files. A different isolation level can be set +through the `~connection.set_isolation_level()` or `~connection.set_session()` +methods. The level can be set to one of the following constants: .. data:: ISOLATION_LEVEL_AUTOCOMMIT - No transaction is started when command are issued and no + No transaction is started when commands are executed and no `~connection.commit()` or `~connection.rollback()` is required. Some PostgreSQL command such as :sql:`CREATE DATABASE` or :sql:`VACUUM` can't run into a transaction: to run such command use:: @@ -653,11 +654,12 @@ set to one of the following constants: .. data:: ISOLATION_LEVEL_DEFAULT - Whatever is defined by the server, either via server configuration or by - statements executed within the session outside Pyscopg control; Psycopg - will not force an isolation level of its own. If you want to know what the - value is you can use a query such as :sql:`show transaction_isolation` or - :sql:`show default_transaction_isolation`. + A new transaction is started at the first `~cursor.execute()` command, but + the isolation level is not explicitly selected by Psycopg: the server will + use whatever level is defined in its configuration or by statements + executed within the session outside Pyscopg control. If you want to know + what the value is you can use a query such as :sql:`show + transaction_isolation`. .. versionadded:: 2.7 diff --git a/doc/src/usage.rst b/doc/src/usage.rst index 1366485..6cb038b 100644 --- a/doc/src/usage.rst +++ b/doc/src/usage.rst @@ -676,8 +676,7 @@ commands executed will be immediately committed and no rollback is possible. A few commands (e.g. :sql:`CREATE DATABASE`, :sql:`VACUUM`...) require to be run outside any transaction: in order to be able to run these commands from Psycopg, the connection must be in autocommit mode: you can use the -`~connection.autocommit` property (`~connection.set_isolation_level()` in -older versions). +`~connection.autocommit` property. .. warning:: |