Skip to content

popoto.backends.postgres.pubsub

popoto.backends.postgres.pubsub

Pub/sub on Postgres: PUBLISH as pg_notify, SUBSCRIBE as LISTEN (#759 M5).

One notification channel per schema (popoto_pubsub_<digest>) carries every popoto channel: the payload names the channel and carries the message, and each subscriber keeps what its subscriptions match. So a channel name is not bound by Postgres's 63-byte identifier limit, and pattern subscriptions (PSUBSCRIBE) are matched client-side, with Redis's glob rules (*, ?, [...], [^...], \ escapes) translated to a regular expression and matched byte-wise, as Redis's stringmatchlen matches (:func:glob_match). The cost: every subscriber of the schema receives every message and drops what it did not ask for.

Each payload starts with a per-publish nonce, stripped before delivery: Postgres folds identical notifications sent in one transaction into one, so without it a message published twice in a transaction would arrive once.

NOTIFY is transactional: a message published inside the backend's unit of work (pipeline=uow) is delivered when that transaction commits and never if it rolls back -- the Postgres reading of "publish atomically with the save". It is also bounded: a payload must be shorter than 8000 bytes. The message travels base64-encoded beside the channel name, so a message of roughly 5.9 KB is the most one notification holds; a larger one is refused with :class:PubSubPayloadTooLarge before anything is sent, rather than split (a split message could be half-delivered to a subscriber that joins between its parts). Publish a key and keep the body in a record.

publish returns the number of live subscriptions the message reaches, like PUBLISH: each subscriber registers its subscriptions in popoto_pubsub_listener under its session's backend pid, and the count keeps the rows whose pid is a live session (pg_stat_activity). A subscriber that closes deletes its rows; one that dies leaves rows that stop counting when its session ends and are swept by the next subscriber to connect. The LISTEN session is never pooled (ยง3 Topology): every subscriber in a process shares one per DSN (:mod:.listen, #799), and it must reach the same server as the backend's DSN.

NOTIFY_PAYLOAD_LIMIT = 8000 module-attribute

Postgres's NOTIFY payload must be shorter than this many bytes (the server's default build).

PubSubPayloadTooLarge

Bases: PublisherException, ValueError

A message too large for one NOTIFY on the Postgres backend.

Source code in src/popoto/backends/postgres/pubsub.py
class PubSubPayloadTooLarge(PublisherException, ValueError):
    """A message too large for one ``NOTIFY`` on the Postgres backend."""

PostgresPubSub

redis-py's PubSub on the process's shared LISTEN session.

subscribe/psubscribe/unsubscribe/punsubscribe, get_message(ignore_subscribe_messages=, timeout=) (0.0 polls, None waits), listen() and close(), with the same message dicts (type, pattern, channel, data; bytes values). A subscription is live when the call returns.

Every subscriber in a process shares one session per DSN (:mod:.listen, #799): each is a sink on the schema's channel, attached on its first subscription and detached when it has none left. If the session drops it is reopened at once, re-LISTEN\ s and re-registers every subscription under its new pid; messages published while it was down are lost, as on a Redis reconnect.

Source code in src/popoto/backends/postgres/pubsub.py
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
class PostgresPubSub:
    """redis-py's ``PubSub`` on the process's shared ``LISTEN`` session.

    ``subscribe``/``psubscribe``/``unsubscribe``/``punsubscribe``,
    ``get_message(ignore_subscribe_messages=, timeout=)`` (``0.0`` polls,
    ``None`` waits), ``listen()`` and ``close()``, with the same message
    dicts (``type``, ``pattern``, ``channel``, ``data``; ``bytes`` values).
    A subscription is live when the call returns.

    Every subscriber in a process shares one session per DSN
    (:mod:`.listen`, #799): each is a sink on the schema's channel, attached
    on its first subscription and detached when it has none left. If the
    session drops it is reopened at once, re-``LISTEN``\\ s and re-registers
    every subscription under its new pid; messages published while it was
    down are lost, as on a Redis reconnect."""

    def __init__(self, backend: Any, ignore_subscribe_messages: bool = False) -> None:
        self.backend = backend
        self.ignore_subscribe_messages = ignore_subscribe_messages
        self.channels: dict[bytes, Optional[Callable[[dict[str, Any]], Any]]] = {}
        self.patterns: dict[bytes, Optional[Callable[[dict[str, Any]], Any]]] = {}
        self._compiled: dict[bytes, re.Pattern[str]] = {}
        self._pending: collections.deque[dict[str, Any]] = collections.deque()
        self._sink = _PubSubSink()
        self._hub: Any = None
        self._finalizer: Any = None

    # -- the session ----------------------------------------------------------------

    @property
    def subscribed(self) -> bool:
        return bool(self.channels or self.patterns)

    @property
    def pid(self) -> Optional[int]:
        """The shared session's server pid this subscriber's
        ``popoto_pubsub_listener`` rows are registered under."""
        return self._sink.pid

    @property
    def token(self) -> str:
        return self._sink.token

    @property
    def dropped(self) -> int:
        """Messages discarded unread because this subscriber fell behind
        (``Defaults.PG_LISTEN_QUEUE_MAX_MESSAGES`` /
        ``PG_LISTEN_QUEUE_MAX_BYTES``). The process-wide total is the
        shared session's ``hub.dropped``."""
        return self._sink.dropped

    @property
    def dropped_bytes(self) -> int:
        return self._sink.dropped_bytes

    @property
    def queued_bytes(self) -> int:
        """Payload bytes received but not yet read."""
        return self._sink.queued_bytes

    @property
    def hub(self) -> Any:
        return self._hub

    def _channel(self) -> str:
        return pubsub_channel(self.backend.schema)

    def _attached(self) -> bool:
        return self._hub is not None and self._hub.pid == os.getpid()

    def _ensure(self) -> None:
        """Attach to the shared session (again in a forked child, which
        never shares the parent's socket, with a fresh token so it never
        touches the parent's registrations), then make sure the
        registrations name the session's current pid."""
        from .listen import hub_for

        if not self._attached():
            if self._hub is not None:  # inherited across a fork: start clean
                if self._finalizer is not None:
                    self._finalizer.detach()
                    self._finalizer = None
                # No lock: one another thread held at fork() stays held here.
                names = dict(self._sink.names)
                self._sink = _PubSubSink()
                self._sink.names = names
            self._sink.table = self.backend._events_ready()["popoto_pubsub_listener"]
            hub = hub_for(self.backend.listen_dsn)
            hub.attach(self._channel(), self._sink, barrier=True)
            self._hub = hub
            self._finalizer = weakref.finalize(
                self, hub.detach, self._channel(), self._sink
            )
        self._sync_registration()

    def _detach(self) -> None:
        if self._finalizer is not None:
            self._finalizer.detach()
            self._finalizer = None
        if self._attached():
            self._hub.detach(self._channel(), self._sink)
        self._hub = None
        if self._sink.inherited:
            # A forked child: start from a sink of its own rather than touch
            # the inherited one's lock (the parent's hub thread may have held
            # it at fork()). What it had queued was the parent's anyway.
            fresh = _PubSubSink()
            fresh.names = dict(self._sink.names)
            self._sink = fresh
        else:
            self._sink.take(0)

    @contextlib.contextmanager
    def _locked(self, sink: _PubSubSink) -> Iterator[None]:
        """``sink.lock`` for a registration write, then the sweep a
        reconnect's :meth:`_PubSubSink.resume` left to this subscriber
        because the lock was busy. ``resume`` marks ``stale`` before it
        tries the lock, so whenever its try fails the holder sees the mark
        on release (#803).

        A write that raises skips the sweep: the exception leaves at
        ``yield``, so nothing below it runs. That is deliberate -- the caller
        sees its own error, not a second one from the sweep -- and harmless:
        ``stale`` stays set, so the subscriber's next locked write sweeps,
        and every poll makes one (``_poll`` -> ``_ensure`` ->
        :meth:`_sync_registration`). Until then a superseded pid's row may
        be counted; the count is advisory."""
        with sink.lock:
            yield
        while sink.stale and not sink.inherited:
            try:
                with sink.lock:
                    sink.sweep(self._execute)
            except Exception as exc:  # the count is advisory; never fail on it
                logger.warning("pub/sub registration sweep failed: %s", exc)
                return

    def _execute(self, sql: str, params: list[Any]) -> None:
        self.backend._run(sql, params, write=True)

    def _sync_registration(self) -> None:
        """Register every subscription under the shared session's current
        pid when they are not (first use; a reconnect that this subscriber
        noticed before :meth:`_PubSubSink.resume` reached it), sweeping
        registrations whose session is gone.

        The re-registration itself is :meth:`_PubSubSink.sweep`, so it also
        deletes this subscriber's rows under the pid it replaces: that
        backend may still be listed in ``pg_stat_activity`` (a frozen or
        partitioned peer), where the dead-session DELETE below misses it.
        When a second reconnect overtakes these writes, its ``resume`` marks
        ``stale`` and :meth:`_locked` sweeps again under the newer pid."""
        sink = self._sink
        with self._locked(sink):
            with sink.state:
                current = None if self._hub is None else self._hub.backend_pid
                if current is None or current == sink.pid:
                    return
                sink.pid = current
                # Same block as the pid move: a raising write below leaves
                # the names marked, so the next locked operation re-sweeps.
                sink.stale.update(sink.names)
            t = self.backend._events_ready()
            self.backend._run(
                f"DELETE FROM {t['popoto_pubsub_listener']} WHERE pid NOT IN "
                "(SELECT pid FROM pg_stat_activity)",
                write=True,
            )
            sink.sweep(self._execute)

    def _insert(self, row: list[Any]) -> None:
        t = self.backend._events_ready()
        self.backend._run(
            f"INSERT INTO {t['popoto_pubsub_listener']} (pid, token, pattern, name, "
            "regex) VALUES (%s, %s, %s, %s, %s) ON CONFLICT DO NOTHING",
            row,
            write=True,
        )

    def _register(self, name: bytes, *, pattern: bool) -> None:
        sink = self._sink
        text = name.decode("utf-8")
        regex = glob_to_regex(as_bytes_text(text)) if pattern else None
        with self._locked(sink):
            with sink.state:
                sink.names[(pattern, text)] = regex
                pid = sink.pid
            if pid is not None:
                self._insert([pid, sink.token, pattern, text, regex])

    def _unregister(self, name: bytes, *, pattern: bool) -> None:
        sink = self._sink
        text = name.decode("utf-8")
        if sink.inherited:
            # A forked child: the row is the parent's, and the parent may
            # still be subscribed. Forget it locally, touch no lock (one held
            # by another thread at fork() stays held) and no row.
            sink.names.pop((pattern, text), None)
            return
        with self._locked(sink):
            with sink.state:
                sink.names.pop((pattern, text), None)
                pid = sink.pid
            if pid is None:
                return
            t = self.backend._events_ready()
            self.backend._run(
                f"DELETE FROM {t['popoto_pubsub_listener']} WHERE token = %s "
                "AND pattern = %s AND name = %s",
                [sink.token, pattern, text],
                write=True,
            )

    # -- subscriptions --------------------------------------------------------------

    @staticmethod
    def _key(name: Any) -> bytes:
        return _text(name, "channel").encode("utf-8")

    def _confirm(self, kind: str, name: Optional[bytes]) -> None:
        self._pending.append(
            {
                "type": kind,
                "pattern": None,
                "channel": name,
                "data": len(self.channels) + len(self.patterns),
            }
        )

    def _subscribe(
        self, args: tuple[Any, ...], kwargs: dict[str, Any], *, pattern: bool
    ) -> None:
        table = self.patterns if pattern else self.channels
        wanted: list[tuple[bytes, Optional[Callable[[dict[str, Any]], Any]]]] = [
            (self._key(a), None) for a in args
        ] + [(self._key(k), v) for k, v in kwargs.items()]
        self._ensure()
        for name, handler in wanted:
            fresh = name not in table
            table[name] = handler
            if pattern:
                self._compiled[name] = re.compile(
                    glob_to_regex(as_bytes_text(name)), re.DOTALL
                )
            if fresh:
                self._register(name, pattern=pattern)
            self._confirm("psubscribe" if pattern else "subscribe", name)

    def subscribe(self, *args: Any, **kwargs: Any) -> None:
        self._subscribe(args, kwargs, pattern=False)

    def psubscribe(self, *args: Any, **kwargs: Any) -> None:
        self._subscribe(args, kwargs, pattern=True)

    def _unsubscribe(self, args: tuple[Any, ...], *, pattern: bool) -> None:
        table = self.patterns if pattern else self.channels
        kind = "punsubscribe" if pattern else "unsubscribe"
        names = [self._key(a) for a in args] if args else list(table)
        if not names:
            self._confirm(kind, None)
            return
        for name in names:
            if name in table:
                del table[name]
                self._compiled.pop(name, None)
                try:
                    self._unregister(name, pattern=pattern)
                except Exception as exc:  # the count is advisory; never fail on it
                    logger.warning("pub/sub unregister of %r failed: %s", name, exc)
            self._confirm(kind, name)
        if not self.subscribed:
            # Nothing left to match: stop receiving (and queueing) the
            # schema's traffic until the next subscription.
            self._detach()

    def unsubscribe(self, *args: Any) -> None:
        self._unsubscribe(args, pattern=False)

    def punsubscribe(self, *args: Any) -> None:
        self._unsubscribe(args, pattern=True)

    # -- messages ---------------------------------------------------------------------

    def _deliver(self, payload: str) -> None:
        decoded = _decode_payload(payload)
        if decoded is None:
            return
        channel, data = decoded
        if channel in self.channels:
            self._pending.append(
                {"type": "message", "pattern": None, "channel": channel, "data": data}
            )
        if self.patterns:
            text = as_bytes_text(channel)
            for name, regex in list(self._compiled.items()):
                if regex.fullmatch(text):
                    self._pending.append(
                        {
                            "type": "pmessage",
                            "pattern": name,
                            "channel": channel,
                            "data": data,
                        }
                    )

    def _poll(self, timeout: Optional[float]) -> None:
        from . import _import_psycopg

        psycopg = _import_psycopg()
        try:
            self._ensure()
        except (psycopg.OperationalError, TimeoutError) as exc:
            logger.warning("popoto pub/sub LISTEN session unavailable (%s)", exc)
            return
        payloads, reconnected = self._sink.take(timeout)
        if reconnected:
            try:
                self._sync_registration()
            except Exception as exc:  # the count is advisory; never fail on it
                logger.warning("pub/sub re-registration failed: %s", exc)
        for payload in payloads:
            self._deliver(payload)

    def _handle(self, message: dict[str, Any]) -> Optional[dict[str, Any]]:
        kind = message["type"]
        if kind in ("message", "pmessage"):
            table = self.channels if kind == "message" else self.patterns
            key = message["channel"] if kind == "message" else message["pattern"]
            handler = table.get(key)
            if handler is not None:
                handler(message)
                return None
        return message

    def get_message(
        self, ignore_subscribe_messages: bool = False, timeout: Optional[float] = 0.0
    ) -> Optional[dict[str, Any]]:
        ignore = ignore_subscribe_messages or self.ignore_subscribe_messages
        while True:
            while self._pending:
                message = self._pending.popleft()
                if ignore and message["type"] not in ("message", "pmessage"):
                    continue
                return self._handle(message)
            if not self.subscribed:
                return None
            before = len(self._pending)
            self._poll(timeout)
            if len(self._pending) == before:
                if timeout is None:
                    continue
                return None
            timeout = 0.0

    def listen(self) -> Iterator[dict[str, Any]]:
        while self.subscribed or self._pending:
            message = self.get_message(timeout=None)
            if message is not None:
                yield message

    def close(self) -> None:
        sink = self._sink
        if sink.inherited:
            # A forked child: the rows are the parent's, and the parent may
            # still be subscribed (#803). Forget them locally, touching no
            # lock (one held by another thread at fork() stays held here).
            sink.names.clear()
            sink.pid = None
        else:
            self._close_registrations(sink)
        self._detach()
        self.channels.clear()
        self.patterns.clear()
        self._compiled.clear()

    def _close_registrations(self, sink: _PubSubSink) -> None:
        with self._locked(sink):
            # Forget first, then delete: a reconnect's resume() that inserted
            # meanwhile sees the names gone and deletes its rows itself.
            with sink.state:
                pid, sink.pid = sink.pid, None
                sink.names.clear()
            if pid is not None:
                try:
                    t = self.backend._events_ready()
                    self.backend._run(
                        f"DELETE FROM {t['popoto_pubsub_listener']} WHERE token = %s",
                        [sink.token],
                        write=True,
                    )
                except Exception as exc:
                    logger.warning(
                        "pub/sub close could not drop its registrations: %s", exc
                    )

    reset = close

    def __enter__(self) -> "PostgresPubSub":
        return self

    def __exit__(self, *exc: Any) -> None:
        self.close()

pid property

The shared session's server pid this subscriber's popoto_pubsub_listener rows are registered under.

dropped property

Messages discarded unread because this subscriber fell behind (Defaults.PG_LISTEN_QUEUE_MAX_MESSAGES / PG_LISTEN_QUEUE_MAX_BYTES). The process-wide total is the shared session's hub.dropped.

queued_bytes property

Payload bytes received but not yet read.

glob_to_regex(pattern)

Redis's stringmatchlen glob as a regular expression body that means the same in Python re (with DOTALL) and in Postgres's ~; the caller anchors it. * any run, ? one character, [abc]/[^abc]/[a-z] a class, \x a literal x.

Redis matches bytes. To mean what it means on a non-ASCII channel, translate and match the byte forms (:func:as_bytes_text: UTF-8, then each byte as one character) -- then ? and [^...] consume one byte, and a range compares bytes as stringmatchlen does (as signed char, :func:_range_items). :func:glob_match does both.

Source code in src/popoto/backends/postgres/pubsub.py
def glob_to_regex(pattern: str) -> str:
    """Redis's ``stringmatchlen`` glob as a regular expression body that
    means the same in Python ``re`` (with ``DOTALL``) and in Postgres's
    ``~``; the caller anchors it. ``*`` any run, ``?`` one character,
    ``[abc]``/``[^abc]``/``[a-z]`` a class, ``\\x`` a literal ``x``.

    Redis matches **bytes**. To mean what it means on a non-ASCII channel,
    translate and match the *byte* forms (:func:`as_bytes_text`: UTF-8,
    then each byte as one character) -- then ``?`` and ``[^...]`` consume
    one byte, and a range compares bytes as ``stringmatchlen`` does (as
    signed ``char``, :func:`_range_items`). :func:`glob_match` does both."""
    out: list[str] = []
    i, n = 0, len(pattern)
    special = set(".^$*+?()[]{}|\\-/")

    def lit(ch: str) -> str:
        return "\\" + ch if ch in special else ch

    while i < n:
        ch = pattern[i]
        if ch == "*":
            while i + 1 < n and pattern[i + 1] == "*":
                i += 1
            out.append(".*")
        elif ch == "?":
            out.append(".")
        elif ch == "\\" and i + 1 < n:
            i += 1
            out.append(lit(pattern[i]))
        elif ch == "[":
            j = i + 1
            negate = j < n and pattern[j] == "^"
            if negate:
                j += 1
            items: list[str] = []
            closed = False
            while j < n:
                c = pattern[j]
                if c == "\\" and j + 1 < n:
                    j += 1
                    items.append(lit(pattern[j]))
                elif c == "]":
                    closed = True
                    break
                elif j + 2 < n and pattern[j + 1] == "-":
                    # stringmatchlen: any "x-y" is a range, "]" included as
                    # its end ("[a-]" ranges a..] and leaves the class open).
                    items.extend(_range_items(c, pattern[j + 2], lit))
                    j += 2
                else:
                    items.append(lit(c))
                j += 1
            body = "".join(items)
            if not body:
                # "[]" matches nothing; "[^]" anything.
                out.append("." if negate else "(?!)")
            else:
                out.append(("[^" if negate else "[") + body + "]")
            i = j if closed else n - 1
        else:
            out.append(lit(ch))
        i += 1
    return "".join(out)

as_bytes_text(value)

value's UTF-8 bytes, one character per byte (latin-1): the form a glob and a channel are matched in, so the match is byte-wise like Redis's. Every byte maps to a character Postgres text can hold (U+0001..U+00FF; a channel has no NUL).

Source code in src/popoto/backends/postgres/pubsub.py
def as_bytes_text(value: Union[str, bytes]) -> str:
    """``value``'s UTF-8 bytes, one character per byte (``latin-1``): the
    form a glob and a channel are matched in, so the match is byte-wise like
    Redis's. Every byte maps to a character Postgres ``text`` can hold
    (U+0001..U+00FF; a channel has no NUL)."""
    raw = value.encode("utf-8") if isinstance(value, str) else bytes(value)
    return raw.decode("latin-1")

glob_match(pattern, channel)

Whether channel matches the PSUBSCRIBE glob pattern as Redis's stringmatchlen decides it, byte for byte.

Source code in src/popoto/backends/postgres/pubsub.py
def glob_match(pattern: Union[str, bytes], channel: Union[str, bytes]) -> bool:
    """Whether ``channel`` matches the ``PSUBSCRIBE`` glob ``pattern`` as
    Redis's ``stringmatchlen`` decides it, byte for byte."""
    regex = re.compile(glob_to_regex(as_bytes_text(pattern)), re.DOTALL)
    return regex.fullmatch(as_bytes_text(channel)) is not None

publish(backend, channel, message, *, uow=None)

PUBLISH channel message on backend: see the module docstring.

Source code in src/popoto/backends/postgres/pubsub.py
def publish(
    backend: Any,
    channel: Any,
    message: Union[bytes, str, int, float],
    *,
    uow: Optional[UnitOfWork] = None,
) -> int:
    """``PUBLISH channel message`` on ``backend``: see the module docstring."""
    ch = _text(channel, "channel")
    data = encode_value(message)
    payload = _encode_payload(ch, data)
    size = len(payload)
    if size >= NOTIFY_PAYLOAD_LIMIT:
        head = len(_encode_payload(ch, b"", "0" * _NONCE_WIDTH))
        room = max(((NOTIFY_PAYLOAD_LIMIT - 1 - head) // 4) * 3, 0)
        raise PubSubPayloadTooLarge(
            f"publish to {ch!r}: a Postgres NOTIFY payload must be shorter than "
            f"{NOTIFY_PAYLOAD_LIMIT} bytes, and this message encodes to {size} "
            f"(the channel name and the {len(data)}-byte message, base64-encoded; "
            f"at most {room} message bytes fit on this channel). Publish a smaller "
            "message, or the key of a record that holds it."
        )
    from . import _pg_uow

    pg = _pg_uow(uow)
    if pg is not None and _async_unit_outside_bridge(pg):
        # A popoto.batch() an async_* call opened (#784): its connection
        # belongs to the event loop, so this sync call cannot send now. The
        # NOTIFY is delivered at COMMIT either way, so it is sent then, from
        # inside the transaction that ``await pipe.async_execute()`` commits
        # (and never after a rollback). The count is not knowable yet: 0.
        pg.before_commit(lambda: publish(backend, ch, data, uow=pg))
        return 0
    t = backend._events_ready()
    rows, _ = backend._run(
        "SELECT pg_notify(%s, %s), (SELECT count(*) FROM "
        f"{t['popoto_pubsub_listener']} l WHERE l.pid IN (SELECT pid FROM "
        "pg_stat_activity) AND ((NOT l.pattern AND l.name = %s) OR (l.pattern AND "
        "%s ~ ('^(' || l.regex || ')$'))))",
        [pubsub_channel(backend.schema), payload, ch, as_bytes_text(ch)],
        uow=pg,
        write=True,
    )
    return int(rows[0][1])