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

[Terry M. <ter...@ds...>]

Billy G. Allie wrote:
> 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.
>
If I have understood you then If I have a single cursor on a connection 
I would need to do a commit after every select so that a subsequent 
insert or update would have a correct timestamp.
Or...
I could have two connections each with a cursor; one connection/cursor 
pair would be used for non-select statements and the other 
connection/cursor pair would be used for selects

Yes?