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

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__

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: =A0 =A0 =A05191
> Logged by: =A0 =A0 =A0 =A0 =A0Clark Pearson
> Email address: =A0 =A0 =A0cloink_friggson@ntlworld.com
> PostgreSQL version: 8.3.7
> Operating system: =A0 Windows
> Description: =A0 =A0 =A0 =A0now() 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 databa=
se
> 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 =3D DBI->connect( 'DBI:Pg:dbname=3Dtc;host=3Dlocalhost;port=3D543=
2',
> =A0 =A0'user', 'pass',
> =A0 =A0{ =A0 RaiseError =3D> 1
> =A0 =A0, =A0 PrintError =3D> 1
> =A0 =A0, =A0 AutoCommit =3D> 0
> =A0 =A0}
> );
>
> my $qry =3D q(
> =A0 =A0select current_timestamp
> );
> $qry =3D $dbh->prepare($qry);
> my $tmp =3D $dbh->selectrow_array(
> =A0 =A0 =A0 =A0 =A0'select current_timestamp');
> warn $tmp;
>
> sleep 2;
>
> my $tm2 =3D $dbh->selectrow_array('SELECT 1');
> warn $tm2;
>
> my $tm3 =3D $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.h=
tml#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