Mojo::Pg and idle transactions issue

[avkhozov]

• Mojo::Pg version: 4.01
• Perl version: 5.24.1
• Operating system: ubuntu 17.04

Steps to reproduce the behavior

use Mojo::Pg;

my $pg = Mojo::Pg->new('postgresql://and@:5433/test')->max_connections(2);

$pg->db->query('create table if not exists tmp  (a int primary key, s text)');
$pg->db->query('insert into tmp (a) values (13) on conflict (a) do nothing');

# fill connections cache
my ($db1, $db2) = ($pg->db, $pg->db);
undef $db1; undef $db2;

my $tx = $pg->db->begin;
$pg->db->update('tmp',  {s => rand}, {a => 13});
$pg->db->update('tmp',  {s => rand}, {a => 13});
$pg->db->update('tmp',  {s => rand}, {a => 13});
$tx->commit;

Expected behavior

All update commands will be completed.

Actual behavior

The last update command is blocked by a previous update, because previous update was run in transaction with AutoCommit = 0.

I know that using expression $pg->db->begin incorrectly because of a weak db in Mojo::Pg::Transaction. Maybe it should be mention in the documentation? Or maybe Mojo::Pg should not return such connections to queue if their attributes has been modified?

[kraih]

Like you said, you're using transactions incorrectly here. I don't think this is a bug that needs to be fixed. But of course i'm open to ideas for making Mojo::Pg better.

[Grinnz]

To be clear, after starting a transaction it's your responsibility to make sure you only run queries using the same Mojo::Pg::Database object, or you will deadlock or have queries that get committed unexpectedly. Maybe there's a better way to document this somewhere.

[avkhozov]

I think Mojo::Pg should check connections that returns into cache. If connection has changed options or search_path then Mojo::Pg should drop connection. Otherwise, the cache can contain connections with different options or search_path and cause various unexpected behaviors.