multiple actions with models in one query

[starius]

In my application, I have two slow actions:

• create multiple instances of a model (~40 instances)
• update multiple instances of a model (~20 instances)

Both of them take about a second to complete, according to Firebug Network tab.

They can run much faster, if all 40 creations/updates are performed in one query. Of course, I can write queries manually, but in this case I can't use models, which is not good. I think, this can be done with the following API. Functions lapis.db.start_accumulation and lapis.db.apply_accumulated. They should work at client request level (ngx.ctx). Accumulated queries are sent as one query when lapis.db.apply_accumulated is called or when the request is closed. Returning model and database methods return nil if query accumulation is enabled.

[TangentFoxy]

I've come across the same issue. @leafo Any idea if something like this could be implemented or workarounds besides writing my own?

[leafo]

try:

db.query "begin"
-- all the inserts/updates
db.query "commit"

Note that this is a bit risky if there's an error after opening the transaction, since you don't want that connection to stay in a single transaction when getting reused for other requests. You'll want to add error condition that performsdb.query "rollback" on failure.

[TangentFoxy]

What would be your suggestion for where I should be handling an error? At first I was thinking Lapis's error handler, but then I realized that it would be called on requests that don't touch the database as well, so that would lead to a problem on its own.

Just catch anything between the begin and commit right where they're called?

[TangentFoxy]

Assuming I can use handle_error in sub-applications, I would assume I should do this limited to a sub-application that does the heavy lifting. Alternately, maybe setting a flag on the request that I check in the error handler (via @original_request ?) to know when to rollback?