twisted adbapi turn off autocommit (psycopg2)

Question

I am inserting a lot of rows and it seems that postgress can't keep up. I've googled a bit and it is suggested that you can turn off autocommit. I don't need to commit the values right away. It's data that I can fetch again if something goes wrong.

Now when I search for turning off autocommit I'm not finding what I'm looking for. I've tried supplying autocommit=False in the dbpool constructor:

dbpool = adbapi.ConnectionPool('psycopg2', user="xxx", password="xxx", database="postgres", host="localhost", autocommit=False)

2013-01-27 18:24:42,254 - collector.EventLogItemController - WARNING - [Failure instance: Traceback: : invalid connection option "autocommit"

there are [db.set_isolation_level(n)](http://stackoverflow.com/a/1219376/4279) and [`SET TRANSACTION`](http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html). It is unclear whether it can be used with `dbpool`. — jfs, Jan 28 '13 at 05:03

score 1 · Accepted Answer · answered Jan 27 '13 at 20:43

psycopg2 does not claim to support an autocommit keyword argument to connect:

connect(dsn=None, database=None, user=None, password=None, host=None, port=None, connection_factory=None, async=False, **kwargs)
    Create a new database connection.

    The connection parameters can be specified either as a string:

        conn = psycopg2.connect("dbname=test user=postgres password=secret")

    or using a set of keyword arguments:

        conn = psycopg2.connect(database="test", user="postgres", password="secret")

    The basic connection parameters are:

    - *dbname*: the database name (only in dsn string)
    - *database*: the database name (only as keyword argument)
    - *user*: user name used to authenticate
    - *password*: password used to authenticate
    - *host*: database host address (defaults to UNIX socket if not provided)
    - *port*: connection port number (defaults to 5432 if not provided)

    Using the *connection_factory* parameter a different class or connections
    factory can be specified. It should be a callable object taking a dsn
    argument.

    Using *async*=True an asynchronous connection will be created.

    Any other keyword parameter will be passed to the underlying client
    library: the list of supported parameter depends on the library version.

The current postgresql documentation doesn't discuss any "autocommit" parameter either:

http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING

So perhaps the problem is that this is not the correct way to disable autocommit for a psycopg2 connection. Apart from that, you won't find that turning off autocommit actually helps you at all. adbapi.ConnectionPool will begin and commit explicit transactions for you, side-stepping any behavior autocommit mode might give you.

score 0 · Answer 2 · answered Jan 27 '13 at 19:33

0

The problem with adbapi is that: 1) its missing features specific to the some of the database backends 2) its fake asynchronous api. Under the hoods its using the thread pool and to call the blocking methods.

For postgres I'd suggest using txpostgres library (source is here: https://github.com/wulczer/txpostgres). It is using asynchronous api of psycopg2 and it lets you specify the connection string. You can find an example here: http://txpostgres.readthedocs.org/en/latest/usage.html#customising-the-connection-and-cursor-factories

answered Jan 27 '13 at 19:33

Marek Kowalski

1,782
9
11

Your answer seems somewhat unrelated to the question being asked. – Jean-Paul Calderone Jan 27 '13 at 20:43
Quite the opposite. Since you are in control of the connection string you can pass autoCommit=false like in the example. – Marek Kowalski Jan 27 '13 at 21:28
Changing autocommit won't actually improve performance for the OP's case, and the first half of your answer has nothing to do with connection strings. – Jean-Paul Calderone Jan 27 '13 at 22:04
Even though this isn't the answer to my question this was the answer i needed. Thanks. I got a noticeable speedup. (somewhere between 4x to 10x I would guess) – Sjuul Janssen Jan 30 '13 at 15:43

score 0 · Answer 3 · answered Jun 20 '18 at 09:16

Use the cp_openfun option of the twisted.enterprise.adbapi.ConnectionPool constructor

This function is called with the connection as a parameter.

In case of psycopg2 you can then set the autocommit property of that connection to True or False as stated here

twisted adbapi turn off autocommit (psycopg2)

3 Answers3