Thread: [SQLObject] Re: [TurboGears] Re: AutoConnectHub and SQLObject caching
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Ian B. <ia...@co...> - 2005-09-30 15:36:37
|
Kevin Dangoor wrote: > Hi David, > > Sorry I hadn't gotten to your message on the SQLObject list. > > You're reading things correctly... Luckily, the fix should be simple. > Currently, the AutoConnectHub is specifically working around caching > the SQLObject does in connectionForURI, because that caching is not > compatible with sqlite. I'll just change the hub so that this > workaround is *only* there for sqlite. I've been thinking about this... but only lightly which is why I haven't done anything about it. Anyway, I think the bug is elsewhere; all your requests should share a single SQLObject connection, unless you are using transactions for each request (?). However, SQLObject may not be respecting the threadsafety of SQLite. It seems vaguely that SQLite doesn't want you to ever share connections between threads, even though SQLObject passes connections about and just doesn't use a connection simultaneously from two threads...? -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: David C. <da...@za...> - 2005-09-30 18:32:13
|
On Sep 30, 2005, at 9:57 AM, Kevin Dangoor wrote: > It's easier than that, even if the code doesn't seem that way. You > just need to remove this line: > TheURIOpener.cachedURIs = {} > > That's the hack for sqlite. > When connectionForURI is called with the same URI, it returns the same > DBConnection object. So, there are multiple underlying psycopg > connections but only one DBConnection object, and therefore, only one > cache. > > (Note that switching to use a transaction gives you a new DBConnection > object, new cache and all that... which is what you want.) > > Kevin > Ah cool -- I see that now. I *think* there's one problem left, when using a transaction -- the "classic" cacheValues=True one. If I change object Foo(id=1) within a Transaction ( and hence a new DB connection), then I can still have a stale object in the other shared DB connection cache. -- David |
From: Luke O. <lu...@me...> - 2005-10-02 18:07:12
|
Yep, that's a real problem. Here's the conceptual fix I have for it (code is against 0.5, so probably not much use): 1. Track deletions within the transaction (calls to Transaction._SO_delete), I store (clsName, id) tuples since that's what I use going back into the main cache. 2. When commit() is called, get clsName and ids from self.cache (Transaction's cache), plus the deleted values. 3. For each id, retrieve it from the main DBConnection cache (this is why you need clsName), and tell it to sync() if found. 4. If sync() fails with an SQLObjectNotFound (deleted object), expire() from the main cache and obsolete the found instance. Works for our needs, but a few potention problems: 1. Could be smarter about only sync'ing objects that were involved in a create/update. (Probably simple to improve.) 2. Doesn't deal with having multiple DBConnections. - Luke Quoting David Creemer <da...@za...>: > > On Sep 30, 2005, at 9:57 AM, Kevin Dangoor wrote: > >> It's easier than that, even if the code doesn't seem that way. You >> just need to remove this line: >> TheURIOpener.cachedURIs = {} >> >> That's the hack for sqlite. >> When connectionForURI is called with the same URI, it returns the same >> DBConnection object. So, there are multiple underlying psycopg >> connections but only one DBConnection object, and therefore, only one >> cache. >> >> (Note that switching to use a transaction gives you a new DBConnection >> object, new cache and all that... which is what you want.) >> >> Kevin >> > > > Ah cool -- I see that now. I *think* there's one problem left, when > using a transaction -- the "classic" cacheValues=True one. If I > change object Foo(id=1) within a Transaction ( and hence a new DB > connection), then I can still have a stale object in the other shared > DB connection cache. > > -- David > > > > ------------------------------------------------------- > This SF.Net email is sponsored by: > Power Architecture Resource Center: Free content, downloads, discussions, > and more. http://solutions.newsforge.com/ibmarch.tmpl > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > -- The Pursuit of Counterfactual Histories |
From: Kevin D. <da...@gm...> - 2005-10-02 20:44:34
|
On 9/30/05, David Creemer <da...@za...> wrote: > Ah cool -- I see that now. I *think* there's one problem left, when > using a transaction -- the "classic" cacheValues=3DTrue one. If I > change object Foo(id=3D1) within a Transaction ( and hence a new DB > connection), then I can still have a stale object in the other shared > DB connection cache. Yes, I believe that's true. It *would* be nice if that one was invalidated, and it doesn't seem entirely impossible either, since SQLObject code is running for each change anyhow. Kevin -- Kevin Dangoor Author of the Zesty News RSS newsreader email: ki...@bl... company: http://www.BlazingThings.com blog: http://www.BlueSkyOnMars.com |
From: David C. <da...@za...> - 2005-09-30 16:26:12
|
On Sep 30, 2005, at 8:36 AM, Ian Bicking wrote: > Kevin Dangoor wrote: > > >> Hi David, >> Sorry I hadn't gotten to your message on the SQLObject list. >> You're reading things correctly... Luckily, the fix should be simple. >> Currently, the AutoConnectHub is specifically working around caching >> the SQLObject does in connectionForURI, because that caching is not >> compatible with sqlite. I'll just change the hub so that this >> workaround is *only* there for sqlite. >> >> > > I've been thinking about this... but only lightly which is why I > haven't done anything about it. Anyway, I think the bug is > elsewhere; all your requests should share a single SQLObject > connection, unless you are using transactions for each request > (?). However, SQLObject may not be respecting the threadsafety of > SQLite. It seems vaguely that SQLite doesn't want you to ever > share connections between threads, even though SQLObject passes > connections about and just doesn't use a connection simultaneously > from two threads...? > > This sounds like what I've wound up doing, though I'm still not completely satisfied with the results. In my case I'm using PostgreSQL with psycopg, so multiple threads connecting to the database is OK. What I was worried about is the SQLObject CacheSet object caching. Since it's tied to a connection, if I establish a one- to-one thread to db connection policy then I have many CacheSets, and certainly cache a single object many times. At best this is inefficient, at worst it seems like I can run into data consistency problems. I'm experimenting now with a modified AutoConnectHub that always uses a shared connection, but will create a new thread-local one when a transaction is started (and dispose of it when the transaction is done). I don't fully grok how this will work out when touching object that have cacheValues = True, but I'll keep reading the code ;-) -- David |
From: Kevin D. <da...@gm...> - 2005-09-30 18:23:38
|
On 9/30/05, David Creemer <da...@za...> wrote: > This sounds like what I've wound up doing, though I'm still not > completely satisfied with the results. In my case I'm using > PostgreSQL with psycopg, so multiple threads connecting to the > database is OK. What I was worried about is the SQLObject CacheSet > object caching. Since it's tied to a connection, if I establish a one- > to-one thread to db connection policy then I have many CacheSets, and > certainly cache a single object many times. At best this is > inefficient, at worst it seems like I can run into data consistency > problems. > > I'm experimenting now with a modified AutoConnectHub that always uses > a shared connection, but will create a new thread-local one when a > transaction is started (and dispose of it when the transaction is > done). I don't fully grok how this will work out when touching object > that have cacheValues =3D True, but I'll keep reading the code ;-) It's easier than that, even if the code doesn't seem that way. You just need to remove this line: TheURIOpener.cachedURIs =3D {} That's the hack for sqlite. When connectionForURI is called with the same URI, it returns the same DBConnection object. So, there are multiple underlying psycopg connections but only one DBConnection object, and therefore, only one cache. (Note that switching to use a transaction gives you a new DBConnection object, new cache and all that... which is what you want.) Kevin |
From: Kevin D. <da...@gm...> - 2005-09-30 16:25:24
|
On 9/30/05, Ian Bicking <ia...@co...> wrote: > I've been thinking about this... but only lightly which is why I haven't > done anything about it. Anyway, I think the bug is elsewhere; all your > requests should share a single SQLObject connection, unless you are > using transactions for each request (?). Yes, I agree that a single SQLObject connection should be used. I did that particular workaround in the hub as an easy stopgap. Transactions aren't used for each request. (They're really only needed when you're updating data, after all...) > However, SQLObject may not be > respecting the threadsafety of SQLite. It seems vaguely that SQLite > doesn't want you to ever share connections between threads, even though > SQLObject passes connections about and just doesn't use a connection > simultaneously from two threads...? From: http://sqlite.org/faq.html#q8 An sqlite3 structure can only be used in the same thread that called sqlite3_open to create it. You cannot open a database in one thread then pass the handle off to another thread for it to use. This is due to limitations (bugs?) in many common threading implementations such as on RedHat9. pysqlite2 started raising exceptions for this, rather than silently letting it continue. It seems like the real solution for sqlite would be to keep one database connection per thread rather than using a pool. Kevin |
From: Ian B. <ia...@co...> - 2005-09-30 16:31:58
|
Kevin Dangoor wrote: > From: > http://sqlite.org/faq.html#q8 > > An sqlite3 structure can only be used in the same thread that called > sqlite3_open to create it. You cannot open a database in one thread > then pass the handle off to another thread for it to use. This is due > to limitations (bugs?) in many common threading implementations such > as on RedHat9. > > pysqlite2 started raising exceptions for this, rather than silently > letting it continue. > > It seems like the real solution for sqlite would be to keep one > database connection per thread rather than using a pool. OK, that's what it seemed like... I offer to fix this if someone writes up a failing test case. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |