Re: 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: =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