Re: [GENERAL] 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?
> >>
> > 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general