niwibe/djorm-ext-core — pull request #5 by scibi (2013-12-11 04:15:04+00:00)
Title: Server side cursors should be used only for SELECT queries

Hi!

Right now within `with server_side_cursors()` block all queries are made with server side cursors. Unfortunately they are valid only for SELECTs (see [PostgreSQL documentation] [1]).

So something like this doesn't work:

from djorm_core.postgresql import server_side_cursors

with server_side_cursors():
for item in Model.objects.all():
print item.value

m=AnotherModel(sth=item.x)
m.save()

It will produce queries like this:

DECLARE "curc51db20dcc4e4816abcbc8ad143a4d10" CURSOR WITHOUT HOLD FOR SELECT [..];
DECLARE "cur1c304e46a434453f9be72cf259403dab" CURSOR WITHOUT HOLD FOR INSERT INTO [..];

which will not work - PostgreSQL will report syntax error.

I've fixed it by saving original client side cursor and choosing correct cursor to use based on query. I also added withhold parameter which allows me to make commits inside for loop.

Notes:

* Maybe query type check could be made better (eg. case insensitive)?
* Provided solution is only for Django<1.6. This is what I needed right now. I can prepare fix for 1.6 but in few days.
* I've tested it only with execute(), not executemany()

[1]: http://www.postgresql.org/docs/9.1/static/sql-declare.html#AEN69659 "PostgreSQL documentation"