Thread: [SQLObject] Bug? Spurious connection to postgresql
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Iwan V. <iw...@re...> - 2008-12-13 17:26:32
|
Hi there, We're experiencing what I suspect to be a nasty bug with SQLObject version 0.10.2 (and, it seems earlier) when using Postgresql. I have not tested it with later versions. Any pointers to a fix would be appreciated! Here's what happens, (I attach source below that illustrates the issue): When you call dropTable on a SQLObject class, _inside a transaction_, sqlobject creates a second connection to postgresql. This connection later deadlocks randomly with the first (which is sometimes busy inside a transaction). I can't figure out why the deadlock happens, but the second connection should never have been created to start with. If you run the code below, you'll see that createTable works correctly. This code does not illustrate the deadlock, only the extra connection. Using PDB, we've narrowed it to the following: In pgconnection.py, line: 163 if self.server_version[:3] <= "7.2": the attribute access to server_version results in a "poor mans aquisition" call to the method server_version on the Transaction's _dbConnection (a PostgresConnection). In there, on line: 304 server_version = self.queryOne("SELECT version()")[0] A query is executed on the underlying db connection. This is where the extra connection is created (the original is already in a transaction at this point). If this query is done manually on the Transaction object, instead of on its underlying connection, everything works as it should. So, it seems (from our limited understanding) that it should not have gone this far, and rather should have executed the query on the Transaction object itself. We'd be very grateful for a suggestion as to how we can fix this! Thanks - Iwan Vosloo -------------------------- The code: # The connections can be observed by running this in a window while # stepping through the code below with pdb: # # while true; do clear; ps ax | grep postgres: ; sleep 0.5; done # import sqlobject # Just make this point to your DB: connectionURI = 'postgres://rhug:rhug@localhost/rhug' conn = sqlobject.connectionForURI(connectionURI).transaction() class TestMe(sqlobject.SQLObject): pass import pdb; pdb.set_trace() TestMe.createTable(connection=conn) TestMe.dropTable(connection=conn) |
From: Oleg B. <ph...@ph...> - 2008-12-13 17:39:26
|
On Sat, Dec 13, 2008 at 07:26:14PM +0200, Iwan Vosloo wrote: > In pgconnection.py, line: > 163 if self.server_version[:3] <= "7.2": > > the attribute access to server_version results in a "poor mans > aquisition" call to the method server_version on the Transaction's > _dbConnection (a PostgresConnection). > > In there, on line: > 304 server_version = self.queryOne("SELECT version()")[0] > > A query is executed on the underlying db connection. > > This is where the extra connection is created (the original is already > in a transaction at this point). If this query is done manually on the > Transaction object, instead of on its underlying connection, everything > works as it should. > > So, it seems (from our limited understanding) that it should not have > gone this far, and rather should have executed the query on the > Transaction object itself. When the connection calls self.queryOne() - is 'self' the connection or the transaction? Transaction overrides queryOne method to pass the existing connection instead of creating a new one, but from you exploration it seems the overridden method is not called, instead the connection calls original queryOne and creates a new low-level connection. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Iwan V. <iw...@re...> - 2008-12-13 21:54:39
|
On Sat, 2008-12-13 at 20:39 +0300, Oleg Broytmann wrote: > On Sat, Dec 13, 2008 at 07:26:14PM +0200, Iwan Vosloo wrote: > When the connection calls self.queryOne() - is 'self' the connection or > the transaction? Transaction overrides queryOne method to pass the existing > connection instead of creating a new one, but from you exploration it seems > the overridden method is not called, instead the connection calls original > queryOne and creates a new low-level connection. Self is the connection. What happens is this line (dbconnection.py): 755 attr = getattr(self._dbConnection, attr) is called while self is still the Transaction, for attr 'server_version'. This returns the server_version (bound) method on the connection, and calls that with self == the connection. Inside there is where the self.queryOne is called: 304 server_version = self.queryOne("SELECT version()")[0] Maybe a simple workaround would be to implement a server_version method on the Transaction too? (Or to just set it to a value.) - Iwan |
From: Iwan V. <iw...@re...> - 2008-12-13 21:54:55
|
On Sat, 2008-12-13 at 23:37 +0200, Iwan Vosloo wrote: > On Sat, 2008-12-13 at 20:39 +0300, Oleg Broytmann wrote: > > On Sat, Dec 13, 2008 at 07:26:14PM +0200, Iwan Vosloo wrote: > Maybe a simple workaround would be to implement a server_version method > on the Transaction too? (Or to just set it to a value.) Yup, I have verified this as a workaround. The following code works correctly: import sqlobject connectionURI = 'postgres://rhug:rhug@localhost/rhug' import pdb; pdb.set_trace() conn = sqlobject.connectionForURI(connectionURI).transaction() # The workaround, so that __getattr__ would not get called: conn.server_version = conn.queryOne("SELECT version()")[0][1] class TestMe(sqlobject.SQLObject): pass TestMe.createTable(connection=conn) TestMe.dropTable(connection=conn) |
From: Oleg B. <ph...@ph...> - 2008-12-13 22:25:56
|
On Sat, Dec 13, 2008 at 11:36:55PM +0200, Iwan Vosloo wrote: > What happens is this line (dbconnection.py): > 755 attr = getattr(self._dbConnection, attr) > > is called while self is still the Transaction, for attr > 'server_version'. > > This returns the server_version (bound) method on the connection, and > calls that with self == the connection. Inside there is where the > self.queryOne is called: > > 304 server_version = self.queryOne("SELECT version()")[0] Thank you for the analysis. I have to think about it... Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2009-04-17 16:46:21
|
On Mon, Apr 13, 2009 at 09:28:36AM +0200, Iwan Vosloo wrote: > On Wed, 2009-04-08 at 00:37 +0400, Oleg Broytmann wrote: > > The problem with .server_version is that it's a property, so it is > > called at the very beginning of said __getattr__ and 'self' is the > > Connection, not the Transaction so server_version calls wrong > > self.queryOne(). I think, the simplest way to fix this would be to make the > > property a normal method. Then __getattr__ will wrap self and the > > .server_version() method will call .queryOne() on the Transaction, not > > Connection. > > This is an API change but I think it's a small evil 'cause it is a > > change in an API that is hardly used by many. > > What do you think? > > Sounds good to me. Even we do not really use server_version directly - > it is just being called as a side effect of doing other things. Fixed in the revisions 3853-3855 (branches 0.9, 0.10 and the trunk). Thank you! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |