Thread: Multiple result sets
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? All 5 queries return 2 columns but they are different types in each query. I could just do a union casting everything to text, adding one column identifying each query and then recast when receiving the result set. But that could be costly (i guess the union is) and not elegant at all. Those queries will fill select boxes and the ajax refresh should be very fast. Regards, Clodoaldo Pinto Neto
Clodoaldo <clodoaldo.pinto.neto@gmail.com> writes:
> I need that 5 queries, fired from the same ajax request to a web
> python application, see the same database snapshot.
serializable transaction?
regards, tom lane
> 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;
2008/6/1 Adam Rich <adam.r@sbcglobal.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.
Regards, Clodoaldo
> >> 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.
On Sun, 1 Jun 2008 15:36:14 -0500 "Adam Rich" <adam.r@sbcglobal.net> wrote: > 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. Which one will fail? the second query or the serializable transaction. My understanding was that the serializable transaction will fail. -- Ivan Sergio Borgonovo http://www.webthatworks.it
2008/6/1 Adam Rich <adam.r@sbcglobal.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
> > 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. > > Which one will fail? the second query or the serializable > transaction. > > My understanding was that the serializable transaction will fail. > Yes, serializable transactions fail if they attempt to modify data which has changed since the beginning of the transaction. If the OP's ajax call kicks off 5 queries in a serializable transaction, there is the potential for failure if two ajax calls initiate simultaneous serializable transactions modifying data.
> 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()
You will actually need to do this:
cursor.execute('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
cursor.execute(query_1, (param1,))
rs1 = cursor.fetchall()
cursor.execute(query_2, (param2,))
rs2 = cursor.fetchall()
cursor.execute('commit;');
Hope that helps,
Joe