Multiple result sets

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