Re: [Pypgsql-users] Transactions, cursors and timestamps

[Billy G. A. <bil...@de...>]

On Mon, 2006-03-27 at 13:52 +0100, Terry Macdonald wrote:

> Hi,
> 
> I don't now if I am implementing the DB-API via pypgsql properly but I 
> have a web app that opens a db connection and a couple of cursors at 
> application startup and I use the same cursors throughout the life of 
> the app.
> 
> Doing it this way I have noticed that transactions begin once a commit 
> had been done. If an SQL statement acts on a table with a timestamp 
> column using the now() function,  as it is in a transaction the time 
> used in the update/insert is when the transaction was started which 
> means that after my last commit if a significant time has elapsed before 
> the cursor is used again to update/insert a row the time stored is that 
> of when the transaction began and not when the row was inserted which 
> could easily be long after the transaction began.
> 
> What is the best practice in using cursors and how do I get the 
> timestamp to reflect when the row was created.

There are a number of things to consider:

     1. Transactions are at the Connection level.  This implies that all
        cursors that share a connection share the transaction.
     2. Transactions are started when the first cursor is opened on the
        connection.  Additional cursors created on the same connection
        do no start another transaction (there is only one active
        transaction per connection).  If you need multiple transactions
        at the same time, you will need multiple connections.
     3. After a Connection.commit() or .rollback(), another transaction
        is not started until a new cursor is created or until an query
        is executed on an existing cursor.  Remember - if there are
        multiple cursors on the connection, the transaction is started
        the first time .executeXXX or .callproc is executed on any one
        of them.

Given these thing, I would recommend:

     1. Using only one cursor per connection, unless you need to process
        through the results of multiple queries simotaineously.
     2. If you need to perform updates based on the results of
        processing the results of a query in progress, use two
        connections with 1 cursor each.  One for the query being
        processed, the other for the updates.
     3. Commit the updates as soon as passible, preferrably after each
        row if there is significant time between the updates.  This will
        delay the creation of a transaction until the next update
        occurs, ensuring that the timestamps reflect when the row was
        created.
     4. If creating a cursor ahead of time, issue a connection.rollback
        () after creating all the cursors for that connection.  This
        will delay the creation of the transaction until the first time
        a cursor for that connection is used.


-- 
Billy G. Allie <bil...@de...>