DBIO-PostgreSQL-EV

 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/EV/Storage.pm  view on Meta::CPAN

  # two in flight at once. on_connect sets _listen_connected and calls
  # us; listen/unlisten push onto _listen_pending and call us; we drain
  # one at a time and re-enter from the in-flight query's callback.
  $self->{_listen_pg} ||= do {
    require EV::Pg;
    $self->{_listen_pending}     = [];
    $self->{_listen_dispatching} = 0;
    $self->{_listen_connected}   = 0;
    my $pg = EV::Pg->new(
      conninfo   => $self->_conninfo_string,
      keep_alive => 1,
      on_connect => sub {
        $self->{_listen_connected} = 1;
        $self->_dispatch_listen_queue;
      },
      on_error   => sub { warn "LISTEN connection error: $_[0]\n" },
      on_notify  => sub {
        my ($ch, $payload, $pid) = @_;
        if (my $handler = $self->{_listeners}{$ch}) {
          $handler->($ch, $payload, $pid);
        }

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

# Split a DDL string and execute each statement on a pinned EV::Pg
# connection, one at a time. Returns a Future that resolves on the last
# successful statement or fails on the first libpq error — the surrounding
# txn_do_async then COMMITs or ROLLBACKs accordingly.
#
# The recursion pumps exactly one DDL at a time on the pinned connection:
# libpq never has two in flight on the same handle. Each step waits for
# CommandComplete + ReadyForQuery before dispatching the next. The
# surrounding txn_do_async retain()s the returned Then-Future, which keeps
# the whole chain alive until COMMIT/ROLLBACK fires.
sub _execute_ddl_async {
  my ($self, $pg, $ddl) = @_;

  my @stmts = grep { !/^\s*--/ } _split_statements($ddl);

  my $recur;
  $recur = sub {
    my $stmt;
    unless (defined($stmt = shift @stmts)) {
      return Future->done;

t/05-bind-release.t  view on Meta::CPAN

#
# WHY THIS TEST CAN ENCODE THAT INTENT WITHOUT A SERVER: the bind-retention
# question is a pure Perl reference-graph question. FakePg below faithfully
# emulates libpq's query_params contract -- it COPIES the params into private
# storage and retains ONLY the callback, never the $bind arrayref. We run the
# real _query_async / _query_async_pinned code through it, hold a weak ref to
# each bind arrayref, let the strong issuing-scope lexical drop, and assert the
# weak ref is gone. This FAILS the moment _query_async's closure starts closing
# over $bind, or the storage/pool starts stashing it anywhere keyed by query --
# i.e. exactly the leak shape the ticket describes. (Verified by hand: a variant
# whose completion closure captures $bind keeps every weak ref alive here.)

# --- FakePg: faithful EV::Pg->query_params contract -----------------------
#
# Stores ONLY the callback (like libpq keeping a pending-result slot), and
# copies the params into a private string list (like PQsendQueryParams
# serializing them onto the wire). It deliberately does NOT keep the $bind
# arrayref, so anything that survives must be held by DBIO's own code.
package FakePg;
sub new { bless { queue => [] }, shift }
sub query_params {

t/07-pool-connect-replay-unit.t  view on Meta::CPAN

  my $owner = FakeOwner07->new;
  $storage->_owner_storage($owner);   # held strongly by $owner lexical below

  my $conn = FakeConn07->new;
  $storage->_setup_pool_connection($conn);

  is_deeply $conn->{queries},
    [ q{SET myapp.a = 'do'}, q{SET myapp.b = 'call'} ],
    'both replayed actions executed on the freshly-spawned connection via the seam';

  # keep $owner alive to the end (the storage holds _owner_storage weakly)
  ok $owner, 'owner kept in scope';
}

done_testing;