Allow to start transactions in autocommit mode

[anarazel]

Hi,

using autocommit mode can be quite useful for some long running applications, to make it less likely that transactions are accidentally left open, and to perform operations like VACUUM etc. psycopg provides for that by allowing to set autocommit to true.

Unfortunately I think that's too large a hammer in a lot of cases. What I think is commonly desired is being able to granularly choose the desired behaviour, but psycopg doesn't allow that currently.

E.g. even when in autocommit mode, one might want to explicitly use a transaction. But that doesn't really work well right now. Approaches I considered:

1. Use con.begin() - which doesn't exist
2. Just start a transaction using .execute() - but that breaks con.commit(), because:
   https://github.com/psycopg/psycopg2/blob/master/psycopg/pqpath.c#L396

    if (conn->autocommit || conn->status != CONN_STATUS_BEGIN) {
        Dprintf("pq_commit: no transaction to commit");
        retvalue = 0;
}

even though the connection knows that it's in a transaction, it ignores that fact.

While I obviously just could commit using .execute('COMMIT') that seems too likely to break assumptions of other code.

3. Disable autocommit for a single transaction:

psycopg2.ProgrammingError: set_session cannot be used inside a transaction

While I could work around that by resetting autocommit later, that requires exception handling etc.

What I think a sensible approach would be:

1. Have pq_commit() actually commit, even if autocommit mode is on, if there's a transaction in progress - the transaction status is known at the client side
2. Provide .begin(), to explicitly start a transaction (probably erroring out if there already is one in progress)
3. Have a context manager for transactions (rather than just connections and cursors). Probably just return one from con.begin().

Regards

Andres

[dvarrazzo]

Hello Andres.

I think there are a few things that can be done, yes. First a correction: it's a bit surprising but the connection context manager doesn't exit with close, but it terminates a transaction.

I don't have code handy but I guess entering the CM with an autocommit transaction is a no-op.

So I see two easy improvements:

• change __enter__ to begin a transaction even if the connection is autocommit. It is a change in behaviour, but I don't think anyone really does it. We could introduce a warning in the current maintenance branch and change behaviour in the next major release.

• change commit() and rollback() to terminate the transaction on autocommit too, so that the connection would behave as expected even if someone issued a manual BEGIN (I don't think there are breaking assumptions around in the code).

In case of autocommit it would be better tracking the status using libpq PQtransactionStatus instead of conn->status (even betterer would be to always use the libpq, probably...)

Would you like to work on these changes in a MR?

[and-semakin]

My current workaround (that relies on 3rd approach of @anarazel) to this issue is following:

from contextlib import contextmanager
from typing import Iterator

from psycopg2.extensions import connection as PgConnection


@contextmanager
def transaction_in_autocommit_mode(connection: PgConnection) -> Iterator[None]:
    connection.rollback()
    connection.autocommit = False
    try:
        with connection:
            yield
    finally:
        connection.autocommit = True

Seems to be working as expected:

In [43]: def get_txid(cur):
    ...:     cur.execute("SELECT txid_current()")
    ...:     txid, = cur.fetchone()
    ...:     return txid
    ...:

In [44]: with transaction_in_autocommit_mode(conn):
    ...:     with conn.cursor() as cur:
    ...:         print(get_txid(cur))
    ...:         print(get_txid(cur))
    ...:
654
654

[tometzky]

Based on @and-semakin's code I've created an elegant (IMHO) connection factory with autocommit and explicit transactions:

"""AutocommitConnection"""

import contextlib
import sys
import typing

import psycopg2  # type: ignore


class AutocommitConnection(
    psycopg2.extensions.connection
):  # pylint: disable=too-few-public-methods
    """Connection factory with autocommit and explicit transactions"""

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.set_session(autocommit=True)

    @contextlib.contextmanager
    def transaction(self) -> typing.Iterator[None]:
        """Context manager to encapsulate a transaction"""
        self.set_session(autocommit=False)
        try:
            with self:
                yield
        finally:
            self.set_session(autocommit=True)

# Example usage:
# Uses default connection or environmental variables like PGHOST PGDATABASE PGUSER PGPASSWORD
conn: AutocommitConnection = psycopg2.connect(
    "", connection_factory=AutocommitConnection
)
with conn.cursor() as cur:
    cur.execute("create temporary table test_autocommit(id int)")
    cur.execute("insert into test_autocommit values (1)")
    try:
        cur.execute("insert into test_autocommit values ('A')")
    except psycopg2.Error as err:
        print("Error:", err, file=sys.stderr)
    with conn.transaction():
        cur.execute("insert into test_autocommit values (2)")
        cur.execute("insert into test_autocommit values (3)")
        # Automatic commit
    with conn.transaction():
        cur.execute("insert into test_autocommit values (4)")
        try:
            cur.execute("insert into test_autocommit values ('A')")
        except psycopg2.Error as err:
            print("Error:", err, file=sys.stderr)
        # Automatic rollback
    with conn.transaction():
        cur.execute("insert into test_autocommit values (5)")
        conn.rollback()
        # Explicit rollback

# Also works in one line:
with conn.cursor() as cur, conn.transaction():
    cur.execute("insert into test_autocommit values (6)")
    cur.execute("insert into test_autocommit values (7)")
with conn.cursor() as cur, conn.transaction():
    cur.execute("insert into test_autocommit values (8)")
    cur.execute("insert into test_autocommit values (9)")
    conn.rollback()

with conn.cursor() as cur:
    cur.execute("select id from test_autocommit order by id")
    for (val,) in cur:
        print(val)
    # It should print 1, 2, 3, 6, 7
    # It should not print 4, 5, 8, nor 9 as they were rolled-back

[bortzmeyer]

In the mean time, it could be cool to document the fact that, in autocommit=True mode, conn.commit() is silently ignored and that you have to call cursor.execute("COMMIT") explicitely.

[asqui]

This might be of interest: https://github.com/asqui/psycopg-nestedtransactions

It's a Transaction context manager that abstracts away needing to manipulate autocommit.

Broadly similar to the AutocommitConnection above, but also supports nested transactions (implemented using savepoints) and forms the basis for the similar transaction functionality that is built-in to psycopg3.

[rachelcdavies]

We use conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) when working with Redshift and the new pyscopg2 release 2.9 that adds "with connection starts a transaction on autocommit transactions too" causes problems for us.

There are certain Redshift commands that cannot run in a transaction. For example,
"Amazon Invalid operation: ALTER TABLE ALTER COLUMN cannot run inside a transaction block;"
We've pinned our code to use 2.8 for now and will raise an issue.

[jimbojd72]

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

def database_setup(request, postgresql_process):
    with psycopg2.connect(
        dbname="postgres",
        user=postgresql_process.user,
        host=postgresql_process.host,
        port=postgresql_process.port,
    ) as conn:
        conn.autocommit = True
        conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        with conn.cursor() as cursor:
            cursor.execute(f'CREATE DATABASE "{TEST_DATABASE}";')

I reproduce what @rachelcdavies is having as well on 2.9.1. I added ISOLATION_LEVEL_AUTOCOMMIT part but I still received psycopg2.errors.ActiveSqlTransaction: CREATE DATABASE cannot run inside a transaction block

Any solutions? I will need to rollback as well or stop using a with statement I guess

Edit:

Reverting to the old way with a try/finally also did the trick for me, but not really clean.

@pytest.fixture(scope="session")
def database_setup(request, postgresql_process):

    conn = None
    try:
        conn = psycopg2.connect(
            dbname="postgres",
            user=postgresql_process.user,
            host=postgresql_process.host,
            port=postgresql_process.port,
        )
        conn.autocommit = True
        with conn.cursor() as cursor:
            cursor.execute(f'CREATE DATABASE "{TEST_DATABASE}";')
    finally:
        if conn:
            conn.close()

[dvarrazzo]

Yes, that's the way to do it: don't use a with block if you don't want a transaction to start.

[nicktimko]

@dvarrazzo what if you want your connection to be cleaned up automatically? I guess I get changing the default behavior as that's more common, but CREATE DATABASE ... queries or the like should also be acommodated

[dvarrazzo]

@nicktimko using with you are not cleaning any resource. Before 2.9 you are not doing anything at all. Now you are opening and closing a transaction. If you want the behaviour you expect (which is reasonable), freeing resources at the end of the with block, you have to move to psycopg 3.

[vsalvino]

Also just got bitten by this "feature" from psycopg2==2.9. I do not see any guidance in the documentation on how to turn off? For example, we are using:

with psycopg2.connect(...) as cnx:
    cnx.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    with cnx.cursor() as cursor:
        # Create database.
        try:
            cursor.execute('CREATE DATABASE "name" WITH OWNER="owner";')
        except psycopg2.errors.DuplicateDatabase as exc:
            print(exc)

Instead of psycopg2.errors.DuplicateDatabase it now throws the following error: psycopg2.errors.InFailedSqlTransaction

[dvarrazzo]

@vsalvino Just don't use with psycopg2.connect(...) as cnx, which is documented to start a transaction.

[vsalvino]

@dvarrazzo thanks for the quick reply... based on some further research I think we might take this as an opportunity to try out psycopg3, which seems to be able to disable the behavior in the connection.

Linking here for others watching the thread: https://www.psycopg.org/psycopg3/docs/basic/transactions.html#autocommit-transactions

[dvarrazzo]

@vsalvino Yes, unfortunately the psycopg2 connection context was not a happy design. Psycopg 3 might behave in a less surprising way.

[hoxu]

We ran into an exception caused by this changed psycopg2 behavior when upgrading from Python 2 to Python 3, because it seems the latest psycopg2 version for Python 2 is 2.8.6, and Python 3 has 2.9.2, with this problematic transaction change. In our case the exception was from Redshift as well:

psycopg2.errors.ActiveSqlTransaction: ALTER EXTERNAL TABLE cannot run inside a transaction block

And the affected code is:

    with psycopg2.connect(...) as conn:
        conn.set_session(autocommit=True)
        with conn.cursor() as curs:

Kind of confusing to see an exception about not being able to run inside a transaction, when the code clearly seems to be setting autocommits on.

I think it would be useful to have a list of workarounds with code examples on this issue (and perhaps changelog), because this is clearly a breaking change from 2.8.x to 2.9.

Workaround 1: pin psycopg2 dependency to 2.8.6.