pypgsql-users

[Pypgsql-users] Transactions, cursors and timestamps

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

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.

Cheers in advance

[Pypgsql-users] Transactions, cursors and timestamps

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

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.

Cheers in advance

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...>

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?

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

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

On Sun, 2006-04-02 at 19:25 +0100, Terry Macdonald wrote:

> Billy G. Allie wrote:
> > On Mon, 2006-03-27 at 13:52 +0100, Terry Macdonald wrote:
> >> Hi,

    [ . . . ]

> >
> 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?
> 

Either would work (providing that the connection/cursor pair used for
inserts and updates is commited frequently enough to make the timestamps
reflect the current time.

Note that the restrictions of one active transaction per connection is a
PostgreSQL restriction, not a DB-API  2.0 compilant module restriction.

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

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

Billy G. Allie wrote:
> On Sun, 2006-04-02 at 19:25 +0100, Terry Macdonald wrote:
>> Billy G. Allie wrote:
>> > On Mon, 2006-03-27 at 13:52 +0100, Terry Macdonald wrote:
>> >> Hi,
>>     
>     [ . . . ]
>> >
>> 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?
>>
>>     
> Either would work (providing that the onnection/cursor pair used for 
> inserts and updates is commited frequently enough to make the 
> timestamps reflect the current time.
>
> /Note that the restrictions of one active transaction per connection 
> is a PostgreSQL restriction, not a DB-API  2.0 compilant module 
> restriction./ 
I will be using a hack for now whereby for a non-select query a commit 
is performed before and after execution as the cursor is also used for 
select queries so a transaction is in progress pretty much the whole time.

Has no one else experienced this issue before?  I'm surprised it doesn't 
show up in the mailing list more often. Or am I doing things completely 
arse about face!

Do people open a new connection for every request?! surely not

Thanks for your help Billy.

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

[Timothy S. <ti...@op...>]

Terry Macdonald wrote:
> Billy G. Allie wrote:
>> On Sun, 2006-04-02 at 19:25 +0100, Terry Macdonald wrote:
>>> Billy G. Allie wrote:
>>> > On Mon, 2006-03-27 at 13:52 +0100, Terry Macdonald wrote:
>>> >> Hi,
>>>     
>>     [ . . . ]
>>> >
>>> 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?
>>>
>>>     
>> Either would work (providing that the onnection/cursor pair used for 
>> inserts and updates is commited frequently enough to make the 
>> timestamps reflect the current time.
>>
>> /Note that the restrictions of one active transaction per connection 
>> is a PostgreSQL restriction, not a DB-API  2.0 compilant module 
>> restriction./ 
> I will be using a hack for now whereby for a non-select query a commit 
> is performed before and after execution as the cursor is also used for 
> select queries so a transaction is in progress pretty much the whole 
> time.
>
> Has no one else experienced this issue before?  I'm surprised it 
> doesn't show up in the mailing list more often. Or am I doing things 
> completely arse about face!
>
> Do people open a new connection for every request?! surely not
>
> Thanks for your help Billy.
>
>
the general useage of cursors i've seen is that they are short lived 
things you create for the purpose of one transaction and then closed. 
there is no reason really to leave them open when you can just leave the 
db connection open and create a cursor as you need it.
thats the explaination i've seen on the psycopg lists anyway, and thats 
how i use it and i use a similar transaction based application, in which 
timestamps play a big role.