view release on metacpan or  search on metacpan

docs/adr/0003-dedicated-listen-connection-pooled-notify-via-pg-notify.md  view on Meta::CPAN

handle throws "not connected" (`Storage.pm:498-500`). And on the NOTIFY side, the
SQL `NOTIFY` statement takes no bind placeholders, so inlining a payload as a
string literal invites quoting bugs (`Storage.pm:576-577`).

## Decision

Split the two directions across two different connection strategies.

- **LISTEN on a dedicated, buffered, out-of-pool connection.** `listen` does not
  touch the pool. It lazily builds a single dedicated `EV::Pg` handle
  (`Storage.pm:501-523`) with `keep_alive => 1` and an `on_notify` callback that
  dispatches to the registered per-channel handler (`Storage.pm:515-520`).
  Because the socket is not connected yet, `LISTEN`/`UNLISTEN` statements are
  *buffered*: an `on_connect` callback flushes a pending queue once the socket is
  up (`Storage.pm:508-513`); until then statements are pushed onto
  `_listen_pending` (`Storage.pm:527-531`), and `unlisten` mirrors the same
  buffer-or-send logic (`Storage.pm:542-554`). The dedicated handle is torn down
  in `disconnect` (`Storage.pm:692-695`).
- **NOTIFY on a pooled connection via `pg_notify()`.** `notify` acquires a normal
  pooled connection (`Storage.pm:573`), runs
  `SELECT pg_notify($1, $2)` with the channel and payload as bind params, and
  releases the connection in the callback (`Storage.pm:578-586`). The method's POD
  states the contrast explicitly: "Unlike listen, this does not require a
  dedicated connection — it uses a pooled connection from the normal pool"
  (`Storage.pm:563-564`). Using the `pg_notify()` *function* rather than the bare
  `NOTIFY` statement is what makes binding the channel and payload possible.

## Rationale

The asymmetry of the feature dictates the asymmetry of the implementation. A
`LISTEN` must outlive any single checkout, so it cannot share the pool's
release-and-reuse lifecycle; a dedicated keep-alive connection with its own
`on_notify` pump is the only correct host. The connect-race buffering is not
optional polish — without it the first `LISTEN` issued right after
`EV::Pg->new` would reliably throw "not connected"; queuing until `on_connect`
and flushing is the documented fix (`Storage.pm:498-500`). On the NOTIFY side,
the statement is stateless, so paying for a dedicated connection would be waste —
the pool is correct — and routing through `pg_notify($1, $2)` instead of literal
`NOTIFY channel, 'payload'` lets libpq bind the values, sidestepping the
payload-quoting bugs that string interpolation into a `NOTIFY` statement invites
(`Storage.pm:576-577`).

lib/DBIO/PostgreSQL/Async/Storage.pm  view on Meta::CPAN

  # Use a dedicated connection for LISTEN (not from the pool).
  # EV::Pg->new returns before the socket is actually connected; query()
  # dispatched on a not-yet-connected handle throws "not connected".
  # We buffer LISTEN/UNLISTEN until on_connect fires and then flush.
  $self->{_listen_pg} ||= do {
    require EV::Pg;
    $self->{_listen_pending} = [];
    $self->{_listen_connected} = 0;
    my $pg = EV::Pg->new(
      conninfo   => $self->_conninfo_string,
      keep_alive => 1,
      on_connect => sub {
        $self->{_listen_connected} = 1;
        my $q = delete $self->{_listen_pending} || [];
        $self->{_listen_pending} = [];
        $self->{_listen_pg}->query($_, sub {}) for @$q;
      },
      on_error   => sub { warn "LISTEN connection error: $_[0]\n" },
      on_notify  => sub {
        my ($ch, $payload, $pid) = @_;
        if (my $handler = $self->{_listeners}{$ch}) {