FAQ
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

Search Discussions

  • Tom Lane at Jun 1, 2008 at 6:51 pm

    Clodoaldo 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
  • Adam Rich at Jun 1, 2008 at 6:53 pm

    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;
  • Clodoaldo at Jun 1, 2008 at 8:12 pm
    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
  • Adam Rich at Jun 1, 2008 at 8:38 pm

    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.
  • Ivan Sergio Borgonovo at Jun 1, 2008 at 9:05 pm

    On Sun, 1 Jun 2008 15:36:14 -0500 "Adam Rich" 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
  • Adam Rich at Jun 1, 2008 at 9:21 pm

    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.
  • Clodoaldo at Jun 1, 2008 at 9:11 pm
    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
  • Joe at Jun 1, 2008 at 10:44 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 1, '08 at 6:44p
activeJun 1, '08 at 10:44p
posts9
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase