Re: Negative result with (now()-previously_inserted_timestamp)

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> On Wed, Jul 7, 2010 at 12:18 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
>>> I ran the following query, and got an unexpected negative value. Does
>>> this imply that SELECT-transaction was able to see a row created by
>>> INSERT-transaction which started after the SELECT-transaction?
>>
>> Was the SELECT inside a BEGIN block?

> Oh, I get it. You mean read-committed transaction mode's side-effect inside
> a transaction block!

> No, that's not the case. Just confirmed that by issuing a syntactically
> wrong statement in that session (resulting in ERROR), and then doing 'select
> 1'; it did not raise the error 'Current transaction is aborted...'.

Well, now() would be the time of receipt of the command message from the
client. The transaction snapshot would be taken a bit later than that.
It's theoretically possible for another transaction to start and
commit in between. That'd be more likely if now() had been set by a
separate transaction-starting command, but if your server was loaded
enough then maybe it could happen anyway.

We have seen at least one case where it appeared that the value of
gettimeofday() was significantly different on different CPUs of a
multiprocessor machine:
http://archives.postgresql.org/pgsql-hackers/2010-03/msg00985.php
It's possible you're dealing with something like that, too.

regards, tom lane