Billy G. Allie wrote:
> On Mon, 2006-03-27 at 13:52 +0100, Terry Macdonald wrote:
>> 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
> 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.
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