How to remove the warning: "there is already a transaction in progress" when using lock table

[angeloulivieri]

Hi all,

I have searched a lot but couldn't find an answer to this. I am using the construct

$dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1, AutoCommit 
+=> 0 });
        #Here we open a locked access to the database 
        my $select_handle;
        eval{
            $dbh->do("BEGIN WORK");
            $dbh->do("LOCK TABLE $table");

            ###CODE
         $dbh->do("COMMIT WORK");  
        };

To access a Postgres db in a exclusive way. This construct works fine but each time it is used a Warning comes out

WARNING:  there is already a transaction in progress

Someone knows how can I avoid it? It is little annoying this print in the log file of my program which accesses tens of times to the database. Thanks in advance

[runrig]

With AutoCommit => 0 you are already in a transaction. When you want to commit or rollback, do $dbh->commit() or $dbh->rollback(). Either of those implicitly starts a new transaction. Do not explicity execute "BEGIN WORK" or "COMMIT WORK".

[Discipulus]

hello angeloulivieri,

i think you'll get answer from monks with great psql experience, but reading here and there i can guess it is not a Perl issue but a warning (yes just an annoying one) caused (if i understand correctly) by the driver: the transaction is already started and psql give back this warning (for you safety)

L*

[cavac]

Correct. With AutoCommit off, DBI/DBD::Pg automatically sends START TRANSACTION (or the identical BEGIN) when you do() or execute().

If you want explicit control over this, settings AutoCommit to true should work.

See http://search.cpan.org/~timb/DBI-1.634/DBI.pm#AutoCommit and http://search.cpan.org/~turnstep/DBD-Pg-3.5.3/Pg.pm#AutoCommit_(boolean).