| 00:00.19 | *** join/#storm wallflower (n=wallflow@ip-205-246-113-216.pool.grokthis.net) |
| 00:14.21 | *** join/#storm mwhudson (n=mwh@canonical/launchpad/mwhudson) |
| 01:05.13 | *** join/#storm mwhudson (n=mwh@canonical/launchpad/mwhudson) |
| 03:10.45 | thumper | hey |
| 03:10.55 | thumper | got a stormish question |
| 03:11.20 | thumper | jobs = store.find(Job, BranchJob.job == Job.id, BranchJob.branch == self) |
| 03:11.24 | thumper | jobs.remove() blows up |
| 03:11.53 | thumper | DELETE FROM Job WHERE BranchJob.job (blows up referencing BranchJob) |
| 03:12.01 | thumper | jamesh: got an idea? |
| 03:12.10 | thumper | jamesh: I feel it is a simple thing I'm missing |
| 03:13.13 | jamesh | thumper: what you are trying to do is not supported by standard SQL. Both PostgreSQL and MySQL have extensions to handle joins in delete and update though, and there is a bug open to add support. |
| 03:13.25 | jamesh | thumper: for now, using a subselect is probably best. |
| 03:13.39 | thumper | jamesh: do you have an example? |
| 03:13.51 | thumper | Select ? |
| 03:14.17 | thumper | store.find(Job, Select(BranchJob.job == ...)) ?? |
| 03:15.28 | jamesh | thumper: I guess something like: jobs = store.find(Job, Job.id.is_in(Select([BranchJob.job], BranchJob.branch == self))) |
| 03:16.01 | thumper | ah,ok |
| 03:16.10 | thumper | looks kinda hacky |
| 03:16.35 | thumper | jamesh: my idea works :) |
| 03:16.59 | thumper | what's the storm debugging thing to enable and disable around the statement? |
| 03:17.06 | jamesh | because they hate us, MySQL and PostgreSQL picked different syntaxes for their extensions |
| 03:17.07 | thumper | I'll check it is doing the correct sql |
| 03:17.37 | jamesh | "from storm.tracer import debug", then use debug(True) / debug(False) |
| 03:17.41 | thumper | ta |
| 03:18.21 | thumper | re-running test |
| 04:12.17 | jamesh | thumper: so I assume it all worked? |
| 04:13.33 | thumper | sub select needed :) |
| 04:13.40 | thumper | with id.is_in |
| 04:19.28 | jamesh | so, PostgreSQL and MySQL can do your original query with the syntax "DELETE FROM Job USING BranchJob WHERE BranchJob.job == Job.id AND BranchJob.branch = ?" |
| 04:19.39 | jamesh | it is the UPDATE statement where they have different syntax |
| 04:21.27 | jamesh | the fix is to pass the table info to the Update() and Delete() expression objects, have the default compiler for those objects raise an exception if joins are found, and specialise for PostgreSQL and MySQL. |
| 04:47.20 | *** join/#storm bigdog (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |
| 04:58.57 | *** join/#storm thumper (n=tim@canonical/launchpad/thumper) |
| 05:17.34 | *** join/#storm spats (n=spats@mail.fitness2live.com.au) |
| 05:25.09 | spats | hey stormers. I'm having an issue which looks like storm is caching stale data. I'm using the storm-twisted-integration branch (unfortunate, but historical) and I'm effectively doing a get-modify-commit with one store from the pool, then returning it to the pool. Later - as in minutes later - a find query with another store from the pool seems to be including stale objects in the result set, with data from before the commit. |
| 05:25.51 | spats | except that the objects are only being included in the results because of the modifications made by the commit - i.e. after the commit they begin to match the conditions in my query, but the object data does not! |
| 06:02.14 | *** join/#storm jukart (n=jukart@d91-128-122-97.cust.tele2.at) |
| 06:24.44 | jamesh | spats: I don't know much about the status of the twisted-integration branch, but are you ever doing commits/rollbacks behind Storm's back? |
| 06:29.50 | spats | jamesh: no - all access to this (postgres) db is via one pool of storm stores. I am doing explicit commits before returning stores to the pool though. basically twisted-storm maintains a thread pool of up to 10 storm stores, each one attached to its own worker thread, sharing the same db connection. you grab a store, do normal storm gets/finds, and the store wrapper defers them all to its private thread to do the work. |
| 06:30.50 | jamesh | spats: assuming the twisted-integration branch doesn't do anything too weird, both store.commit() and store.rollback() should invalidate the cache |
| 06:32.43 | jamesh | could it be a case of stores being returned to the pool with open transactions? |
| 06:33.25 | jamesh | i.e. do you have any "idle in transaction" connections when your app is supposed to be idle? |
| 06:34.24 | spats | I had that problem early on, with finds getting slower and slower as the transaction grew :) no, I explicitly commit or rollback the store before I give it back to the pool in each case afaik - but I'll do another review to make sure. |
| 06:36.03 | jamesh | so "select datname, usename, xact_start, waiting, current_query from pg_stat_activity" doesn't show any idle open transactions? |
| 06:36.55 | jamesh | It will show "<IDLE> in transaction" for the current_query column |
| 06:37.07 | *** join/#storm jukart (i=lovely@81.189.156.94) |
| 06:37.38 | spats | hm, no xact_start column.. sorry, not too familiar with the pg internals |
| 06:37.57 | spats | state? |
| 06:38.13 | jamesh | try leaving it out from the query then |
| 06:38.43 | jamesh | it is useful info to know when the transaction began, but might not be available if you've got a particularly old postgres |
| 06:39.08 | jamesh | it isn't necessary to see if you've got idle open transactions though |
| 06:39.34 | spats | it does show an <IDLE> entry for this databae |
| 06:39.49 | jamesh | <IDLE> by itself isn't a problem. |
| 06:40.21 | jamesh | that just means that there is a connection open with no transaction started, which is what you'd expect for an app using a pool of connections |
| 06:40.26 | spats | that's the only one, aside from the pg_stat_activity query itself. |
| 06:48.18 | spats | just reproduced the problem - I have a web page that performs the find, and refreshing it shows the stale, yet now visible data - there's still only the <IDLE> connection |
| 06:48.29 | jamesh | the DeferredStore code looks like it passes through commit and rollback fairly directly (other than deferring to a thread) |
| 06:49.13 | jamesh | are you using storm's trunk with twisted-integration merged in, or some old branch? |
| 06:59.16 | spats | jamesh: just the branch directly. I'll try merging it into the trunk code. also, looking at some of this code I suspect there are some corner cases where neither commit nor rollback are being called. thanks - this gives me plenty to look into! |
| 07:00.45 | spats | jamesh: as a quick work-around, is it safe and not horribly inefficient to call rollback on the store unconditionally when I return it to the pool? |
| 07:01.21 | spats | as in, is it going to throw some sort of 'no transaction started' error? |
| 07:01.51 | jamesh | spats: perhaps, but it looks like the StorePool has an unconditional rollback anyway ... |
| 07:02.34 | spats | ah, so it does. hmm. |
| 07:03.29 | jamesh | spats: you aren't keeping references to any Storm objects after returning the associated store to the pool, are you? |
| 07:08.15 | spats | jamesh: in fact, that's exactly what's happening in at least two different places - the resulting list from DeferredResultSet.all() and an object that was just created and committed - both are accessed later to read some data.. *sigh* |
| 07:08.55 | jamesh | spats: we've got a few twisted apps using Storm, but not through the twisted-integration branch. |
| 07:10.02 | jamesh | they use the storm.zope per-thread stores, and defer database access to threads in the reactor's thread pool |
| 07:10.59 | jamesh | the deferred function commits or rolls back the transaction before completing, and doesn't return any storm objects. |
| 07:11.49 | spats | jamesh: yes, I have one also - using a similar technique, but I have a threadpool where each thread owns a Store and I push whole transactions into the worker thread instead of wrangling across the parts bit by bit. |
| 07:11.57 | jamesh | essentially the standard way of integrating synchronous code into a twisted app |
| 07:12.01 | spats | might be a good idea to refactor this code along those lines |
| 07:12.15 | jamesh | spats: that sounds like what I just described :) |
| 07:12.31 | jamesh | an entire transaction is deferred |
| 07:12.52 | jamesh | and the storm.zope code keeps track of the per-thread stores |
| 07:13.15 | spats | jamesh: yes, except I have a private thread pool instead of the reactor one, because I didn't know how to attach Stores to the existing twisted pool threads ;) |
| 07:13.57 | spats | but point taken - I'll fix it properly :o |
| 07:16.08 | jamesh | you might want to look at the storm.zope.zstorm code then. |
| 07:16.43 | jamesh | it is usable outside of Zope 3 apps, and can be used with just the zope.interfaces and transaction modules (the first of which you'll already have if you're using twisted) |
| 07:19.16 | spats | jamesh: thanks, I'll look it up tomorrow.. time for me to head home :) |
| 07:42.25 | *** part/#storm bozz1 (n=bozzo@89.143.110.176) |
| 08:05.23 | *** join/#storm mcella (n=mcella@ip-143-157.sn3.eutelia.it) |
| 08:10.11 | *** join/#storm goschtl (n=goschtl@p5B0BC9C5.dip.t-dialin.net) |
| 09:04.42 | *** join/#storm sidnei (n=sidnei@plone/dreamcatcher) |
| 09:07.45 | *** join/#storm oubiwann (n=oubiwann@host217-45-210-54.in-addr.btopenworld.com) |
| 09:19.08 | *** join/#storm jkakar (n=jkakar@host217-45-210-54.in-addr.btopenworld.com) |
| 09:22.34 | *** join/#storm niemeyer (n=niemeyer@host217-45-210-54.in-addr.btopenworld.com) |
| 11:20.20 | *** join/#storm mcella_ (n=mcella@ip-142-248.sn3.eutelia.it) |
| 11:28.51 | *** join/#storm kov (n=kov@debian/developer/kov) |
| 11:36.54 | *** part/#storm kov (n=kov@debian/developer/kov) |
| 12:18.53 | *** join/#storm andrea-bs (n=andrea-b@ubuntu/member/beeseek.developer.andrea-bs) |
| 15:02.16 | *** join/#storm sidnei (n=sidnei@plone/dreamcatcher) |
| 15:13.35 | *** join/#storm andrea-bs (n=andrea-b@ubuntu/member/beeseek.developer.andrea-bs) |
| 15:22.38 | *** join/#storm goschtl_ (n=goschtl@ip-77-25-141-162.web.vodafone.de) |
| 15:24.55 | *** join/#storm goschtl__ (n=goschtl@p5B0BC9C5.dip.t-dialin.net) |
| 16:26.51 | *** join/#storm maze (i=wijnand@CAcert/Assurer/maze) |
| 18:37.36 | *** join/#storm pyCube_ (n=jmayfiel@c-24-5-148-105.hsd1.ca.comcast.net) |
| 18:38.52 | pyCube_ | How does storm deal with db connections/cleanup? LIke, if i create a store and run some query or whatever, will that connection to the db be closed at some point if i dont explicitly call close()? |
| 18:39.52 | pyCube_ | i mean, when whatever associated objs are cleaned up, the connection gets closed, right? |
| 18:47.35 | therve | yes the connection is closed |
| 18:56.24 | *** join/#storm pyCube__ (n=jmayfiel@12.87.213.242) |
| 19:03.22 | *** join/#storm jukart (n=jukart@d91-128-122-97.cust.tele2.at) |
| 19:29.37 | *** join/#storm maze (i=wijnand@CAcert/Assurer/maze) [NETSPLIT VICTIM] |
| 19:29.37 | *** join/#storm oubiwann (n=oubiwann@host217-45-210-54.in-addr.btopenworld.com) [NETSPLIT VICTIM] |
| 19:29.37 | *** join/#storm spats (n=spats@mail.fitness2live.com.au) [NETSPLIT VICTIM] |
| 21:53.34 | *** join/#storm mcella (n=mcella@ip-142-248.sn3.eutelia.it) |
| 22:40.26 | *** join/#storm sidnei (n=sidnei@plone/dreamcatcher) |
| 23:08.21 | *** part/#storm bigdog (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |
| 23:09.47 | *** join/#storm bigdog (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |
| 23:18.49 | *** part/#storm bigdog (n=scmikes@72-197-8-8-arpa.cust.cinci.current.net) |