localtimestamp 始终返回相同的值
我最近将 Perl 应用程序从 Oracle 移植到 Postgres（我第一次使用 Postgres），我被一个小问题难住了，我能够将其简化为以下示例代码：
use strict;
use warnings;
use DBI;
my @db_params = qw (dbi:Pg:host=127.0.0.1;database=test test test);
my $dbh = DBI->connect(@db_params, {AutoCommit => 0 } ) or die ;
while (1) {
my $sth = $dbh->prepare_cached('SELECT localtimestamp ') or die;
$sth->execute() or die;
my $result = $sth->fetchall_arrayref();
print $result->[0][0] , "\n";
sleep(5);
}
此输出：
2011-11-27 16:46:25.94291
2011-11-27 16:46:25.94291
2011-11-27 16:46:25.94291
我得到了相同的时间戳除非我断开连接并重新连接到数据库。
如何确保每次从数据库中选择 localtimestamp 时都能获得与执行 sql 的时间相对应的值？
I have been recently porting a Perl application form Oracle to Postgres (my first time with Postgres) and I am stumped by a small issue which I was able to reduce to the following sample code:
use strict;
use warnings;
use DBI;
my @db_params = qw (dbi:Pg:host=127.0.0.1;database=test test test);
my $dbh = DBI->connect(@db_params, {AutoCommit => 0 } ) or die ;
while (1) {
my $sth = $dbh->prepare_cached('SELECT localtimestamp ') or die;
$sth->execute() or die;
my $result = $sth->fetchall_arrayref();
print $result->[0][0] , "\n";
sleep(5);
}
This outputs :
2011-11-27 16:46:25.94291
2011-11-27 16:46:25.94291
2011-11-27 16:46:25.94291
I am getting the same time stamp all the time unless I disconnect and reconnect to the database.
How can I make sure that ever time I select localtimestamp from the database I get the value corresponding to the time when the sql is executed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
另请参阅手册中的
statement_timestamp和clock_timestamp函数。Also take a look in the manual for the
statement_timestampandclock_timestampfunctions.因为您没有使用自动提交，所以您始终处于同一个事务中，并且 localtimestamp 获取 Postgres 中该事务开始时的时间戳。即，您需要使用
$dbh->begin_work和$dbh->commit为查询创建单独的事务。Because you are not using autocommit, you are always in the same transaction, and localtimestamp gets the timestamp at the start of that transaction in Postgres. I.e. you will need to create individual transactions for your queries, using
$dbh->begin_workand$dbh->commit.