pyPgSql there is already a transaction in progres

[Paul Boddie]

On 2 Jun, 15:32, someone <petshm... at googlemail.com> wrote:
> Hi,
> I'm using pyPgSQL for accessing Postgres and do some update and select
> queries.
> and getting WARNING:  there is already a transaction in progress if I
> run runUpdate more than once.

I think this is because you're using explicit transaction statements
amongst the SQL statements you're sending to the database system,
whereas pyPgSQL probably starts transactions on your behalf if you've
not enabled autocommit.

> So, what happens is following:
>
> 1. SELECT address FROM address WHERE LOWER(address) = LOWER(%s); --
> __existRecord
> 2. BEGIN;DELETE FROM failed WHERE uquery = %s;COMMIT; -- __delQuery
> 3. SELECT address FROM address WHERE LOWER(address) = LOWER(%s); --
> again __existRecord
> and here I'm getting the warning.

Here, statement #3 may well start a new transaction - a convenience
introduced by pyPgSQL in order to provide DB-API compliance and
automatic transactions - and when __delQuery is invoked, PostgreSQL
will complain that you are trying to start another transaction.

Really, you should use the commit method on the cursor object
(self.db, I presume) and the rollback method when you want to start a
new transaction without changing anything. Alternatively, you could
set autocommit to true on the connection object and be sure to always
use transaction statements (BEGIN, COMMIT, ROLLBACK) where
appropriate.

Paul