pyPgSql there is already a transaction in progres

[someone]

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.
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.

Can anyone explain me please what the problem is? I do select, then
delete transaction and then select again which doesn't work

Regards, Pet








class Bla:

def __init__:
pass

def runUpdate(self, number=5):
data = {}
data = self.__getUpdateItems(number)
for row in data:
try:
if self.__existRecord(row['q']):
self.__delQuery(row['q'])
except Exception, e:
print "Exception", e


def __delQuery(self, name):
query = """
BEGIN;DELETE FROM failed WHERE uquery = %s;COMMIT;
"""
try:
self.db.execute(query, name)
except Exception, e:
print "Exception: ", e
return True

def __existRecord(self, search):
query = """
SELECT address FROM address WHERE LOWER(address) = LOWER
(%s);
"""
try:
self.db.execute(query, search)
except Exception, e:
print "Exception: ", e
return self.db.fetchall()

def __getUpdateItems(self,number=5):
values = [number]
query = """
SELECT * FROM failed
WHERE id IS NULL
ORDER BY up DESC
LIMIT %s;
"""
result = []
try:
self.db.execute(query, *values)
result = self.db.fetchall()
except Exception, e:
print "Exception getUpdateItems: ", e
return result

 

[Paul Boddie]

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

 

[Tep]

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.



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.

Ok, that make sense

Really, you should use the commit method on the cursor object

You mean connection object, do you?

I've tried that, but forgotten to remove BEGIN;COMMIT; statements from
my queries
Now, I do commit on connection object after _each_ query and it seems
to work

(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.

In that way it works too, which means, everything is clear now

Thanks for help!

 

[Paul Boddie]

You mean connection object, do you?

Yes, I meant the connection object.

I've tried that, but forgotten to remove BEGIN;COMMIT; statements from
my queries
Now, I do commit on connection object after _each_ query and it seems
to work

You should probably use rollback in order to close transactions just
in case you've issued a statement which changes the database. Think of
it as kind of a synchronisation operation.

[...]

In that way it works too, which means, everything is clear now

Great!

Paul