Re: Multiple result sets

2008/6/1 Adam Rich <adam(dot)r(at)sbcglobal(dot)net>:
>
>> >> I need that 5 queries, fired from the same ajax request to a web
>> >> python application, see the same database snapshot. The driver is
>> >> psycopg2.
>> >>
>> >> Since postgresql 8.2 functions can't return multiple result sets
>> >> what would be the best aproach?
>> >>
>> > You want to set your transaction isolation to "Serializable".
>> > Then execute your 5 queries via the same connection, and the same
>> > Transaction.
>> >
>> > You can do that with this command:
>> >
>> > BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>>
>> I'm not sure i got it. You mean like this?:
>>
>> import psycopg2 as db
>> dsn = 'host=localhost dbname=dbname user=user password=passwd'
>> connection = db.connect(dsn)
>> cursor = connection.cursor()
>>
>> cursor.execute('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
>> rs1 = cursor.execute(query_1, (param1,))
>> rs2 = cursor.execute(query_2, (param2,))
>> cursor.execute('commit;');
>>
>> cursor.close()
>> connection.close()
>>
>> I tested it and it raises no exception. I just don't understand if a
>> transaction persists between execute() calls.
>>
>
> I am not familiar with the python library, but that looks correct to me.
> You can always test it by adding a sleep between your two queries and
> modifying the database from a console connection during the sleep.
>
> Note that I'm assuming your 5 queries are all read-only selects.
> If you're modifying data during your queries, and another concurrent
> database connection modifies the same data during your transaction,
> the later modifications will fail under serializable isolation.

The queries are read only.

Regards, Clodoaldo