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

On Mon, Nov 16, 2009 at 12:59 PM, Clark Pearson
<cloink_friggson(at)ntlworld(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5191
> Logged by:          Clark Pearson
> Email address:      cloink_friggson(at)ntlworld(dot)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