Mojo::Pg::PubSub needs better connection management

[kraih]

Mojo::Pg::PubSub needs to be fixed to properly maintain a persistent connection, even if the PostgreSQL server has been unavailable for some time. The current reconnect code does not work properly in a few cases and we get regular complaints about it on IRC.

[kraih]

If we can't find a proper solution, i propose the removal of Mojo::Pg::PubSub for now, since it's not good enough for a Mojolicious spin-off project.

[kwakwaversal]

I'd be sad to see this removed. I'm using it in a few projects to provide real-time events. Leveraging the NOTIFY/LISTEN functions in combination with websockets is one of the things that makes using Mojo with PostgreSQL so great.

If I can reproduce some of the cases where the reconnect doesn't work I might be able to have a look at it. The way I deal with the db connection going away is code that looks like this.

my $notify = {};
$notify->{channel_foo} = sub { ... };
$notify->{channel_bar} = sub { ... };

my $pg = Mojo::Pg->new("...");
my $cb;
$cb = sub {
  my ($pg, $dbh) = @_;
  for my $chan (keys %{$notify}) {
    $pg->pubsub->unlisten($chan => $notify->{$chan})
      ->listen($chan => $notify->{$chan});
  }
  $pg->once(connection => $cb);
};
$pg->once(connection => $cb);

Then ping the db connection every second so that it raises the relevant event to resubscribe the subscriptions.

[kwakwaversal]

Thinking about it, the ping isn't a standard ping, it's eval'd to silently ignore an exception.

    unless (eval { $pg->db->ping; 1; }) {
      say 'Notification database not pinging...';
    }

[kraih]

Unfortunately there has been no proposal so far. That means we will have to deprecate Mojo::Pg::PubSub.

[jberger]

My apologies for not opening the PR on that branch. I consider it to be a proposal and now that I've opened a PR I welcome comment on it.

[kraih]

This might be resolved now with #51.