ansaurus

Question

Answer 1

+2 A:

Try calling c.close() as described in the module documentation: http://tools.cherrypy.org/wiki/Databases

def add_timestamp(self):
        c = cherrypy.thread_data.db.cursor()
        now = datetime.datetime.now()
        c.execute("insert into test (given_time) values ('%s')" % now)
        c.connection.commit()
        c.close()
        raise cherrypy.HTTPRedirect('/')

def get_timestamps(self):
        c = cherrypy.thread_data.db.cursor()
        c.execute("select * from test order by given_time desc limit 10")
        records = c.fetchall()
        c.close()
        return records

jcoon 2009-03-17 17:12:27

I just made those changes and restarted the cherrypy server and I am still having the same issue.

adam 2009-03-17 18:10:01

Update: we moved from postgres 8.0 to 8.3 and this answer now works as well.

adam 2009-10-05 19:17:57

Answer 2

A:

I have added a commit to the method that selects the timestamps and that has solved the problem.

 def get_timestamps(self):
    c = cherrypy.thread_data.db.cursor()
    c.execute("select * from test order by given_time desc limit 10")
    records = c.fetchall()
    c.connection.commit()  # Adding this line fixes the timestamp issue
    c.close()
    return records

Can anyone explain why I would need to call cursor.connection.commit() when all I'm doing is a select?

adam 2009-03-19 17:43:58

Answer 3

+1 A:

To address the question posed by your most recent edits:

In PostgreSQL, NOW() is not the current time, but the time at the start of the current transaction. Psycopg2 is probably starting a transaction implicitly for you, and since the transaction is never closed (by a commit or otherwise), the timestamp gets 'stuck' and becomes stale.

Possible fixes:

Commit frequently (silly if you're only doing SELECTs)
Set up Psycopg2 to use different behavior for automatically creating transactions (probably tricky to get right, and will affect other parts of your app)
Use a different timestamp function, like statement_timestamp() (not SQL-standard-compliant, but otherwise perfect for this scenario)

From the manual, section 9.9.4, emphasis added:

PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_TIME(precision)

CURRENT_TIMESTAMP(precision)

LOCALTIME LOCALTIMESTAMP

LOCALTIME(precision)

LOCALTIMESTAMP(precision)

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally be given a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

...

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.

Note: Other database systems might advance these values more frequently.

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:

now()

transaction_timestamp()

statement_timestamp()

clock_timestamp()

timeofday()

now() is a traditional PostgreSQL equivalent to CURRENT_TIMESTAMP. transaction_timestamp() is likewise equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command. timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual current time, but as a formatted text string rather than a timestamp with time zone value.

kquinn 2009-03-20 07:24:44

Thank you for explaining this. I haven't tested your suggestions yet, but I've accepted your answer for doing the best job of explaining why the timestamp would be incorrect. However, now I am wondering if there is a way I can create a cursor without starting a transaction.

adam 2009-03-20 14:04:53

You can set Psycopg2 to the transaction isolation level `ISOLATION_LEVEL_AUTOCOMMIT`, which will not start transactions when commands are issued. I don't know how wide-ranging that change would be, though; doing so might break other queries that use transactions.

kquinn 2009-03-20 21:12:29

ansaurus

tags:

views:

answers:

python postgres cursor timestamp issue

related questions