Re: Multiple result sets

From: Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com>
To: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
Cc: "PostgreSQL - General ML" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Multiple result sets
Date: 2008-06-01 21:11:17
Message-ID: a595de7a0806011411p457f9cf5s3e510be2215e51f1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adam Rich 2008-06-01 21:19:16 Re: Multiple result sets
Previous Message Ivan Sergio Borgonovo 2008-06-01 21:05:12 Re: Multiple result sets