BUG #5191: now() returns same value from Perl.

On Mon, Nov 16, 2009 at 12:59 PM, Clark Pearson
<cloink_friggson@ntlworld.com> wrote:

The following bug has been logged online:

Bug reference:      5191
Logged by:          Clark Pearson
Email address:      cloink_friggson@ntlworld.com
PostgreSQL version: 8.3.7
Operating system:   Windows
Description:        now() returns same value from Perl.
Details:

Hi,

I don't know if this is a core database issue, or a Perl DBD::Pg one;
however the same thing occurs using the 'pure perl' DBD::PgPP as well, so
the evidence points to the db. Note that I do not observe this behaviour if
connected via psql from a Windows DOS terminal.

If I 'SELECT now()' repeatedly from a perl script connected to the database
via DBD::Pg, the returned value does not get updated to the new system
time.

I have tried a number of different tricks, but the only way I have managed
to make it return a value not the same as the first time is by
dis-/reconnecting.

Here is a simple perl script to test with

#!perl

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect( 'DBI:Pg:dbname=tc;host=localhost;port=5432',
   'user', 'pass',
   {   RaiseError => 1
   ,   PrintError => 1
   ,   AutoCommit => 0
   }
);

my $qry = q(
   select current_timestamp
);
$qry = $dbh->prepare($qry);
my $tmp = $dbh->selectrow_array(
         'select current_timestamp');
warn $tmp;

sleep 2;

my $tm2 = $dbh->selectrow_array('SELECT 1');
warn $tm2;

my $tm3 = $dbh->selectrow_array('SELECT now()');
warn $tm3;

$dbh->disconnect();
__END__

now() doesn't advance within a transaction. If you want a value that
does, use clock_timestamp().

See http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

...Robert

"Clark Pearson" <cloink_friggson@ntlworld.com> writes:

If I 'SELECT now()' repeatedly from a perl script connected to the database
via DBD::Pg, the returned value does not get updated to the new system
time.

This is the correct behavior if you're inside the same transaction all
along, which is to be expected if you set autocommit off and then don't
issue any commits. now() is defined as transaction start time.

regards, tom lane