Funguje v psql, nestabilne v produkcii: Ticha vrazda LISTEN/NOTIFY cez PgBouncer

LISTEN/NOTIFY v transaction poolingu ma naucil pokore. “Notifikacie jednoducho prestanu prichodit. Ziadna chyba, ziadne varovanie, jednoducho… prestanu.” Postavili sme real-time dashboard pouzivajuc PostgreSQL LISTEN/NOTIFY - jednoduchy, elegantny pub/sub mechanizmus zabudovany priamo v databaze. Fungovalo to bezchybne v developmente. Lokalne testy presli zakazdym. Ale v produkcii, po nejakom nahodnom intervale, notifikacie ticho prestali prichodit. Restart listener servisu to docasne opravil, kym sa to nestalo znova.

Debugging bol sileny pretoze nebola ziadna chyba ktoru by ste mohli sledovat. LISTEN prikaz uspesne prebehol. PostgreSQL logy ukazovali ze NOTIFYs sa posielaju. Listener bol pripojeny a zdravy. A predsa notifikacie zmizli do prazdna. Stravil som dva dni pridavanim logovani, kontrolovanim sietovych problemov a prezeranim naseho kodu, kym sa kolega nespytal: “Pripajate sa cez PgBouncer?”

Ta otazka vsetko zmenila. PgBouncer v transaction pooling mode neudrzuje session state medzi transakciami. Ked vasa LISTEN transakcia skonci, spojenie ide spat do poolu a moze byt pridelene uplne inemu klientovi. Vasa LISTEN registracia existuje na spojeni ktore teraz obsluhuje niekoho ineho dotazy. NOTIFYs su dorucovane - len nespravnemu klientovi.

Prostredie: PostgreSQL 16, PgBouncer 1.21 v transaction pooling mode, aplikacia pouzivajuca LISTEN/NOTIFY pre real-time updaty

Pochopenie problemu

Ako LISTEN/NOTIFY funguje (bez poolera)

Priame PostgreSQL spojenie:

Klientsky Proces              PostgreSQL Backend
     |                              |
     |  LISTEN events;              |
     |----------------------------->|
     |  OK (listener zaregistrovany)|
     |<-----------------------------|
     |                              |
     |  ... cas plynie ...          |
     |                              |
     |                         NOTIFY events, 'payload'
     |                         (z inej session)
     |                              |
     |  Async notifikacia           |
     |<-----------------------------|
     |  payload: 'payload'          |
     |                              |

Klient SI DRZI to iste backend spojenie.
LISTEN registracia pretrva az kym UNLISTEN alebo disconnect.

Ako sa to rozbije (s transaction poolingom)

PgBouncer v transaction pooling mode:

Klientsky Proces       PgBouncer              PostgreSQL Backendy
     |                    |                         |
     |  BEGIN; LISTEN;    |  → Backend A            |
     |  COMMIT;           |                         |
     |------------------->|---------+               |
     |                    |         |  LISTEN zaregistrovany na A
     |                    |         v               |
     |  (transakcia       |  Backend A uvolneny     |
     |   hotova)          |  spat do poolu!         |
     |                    |                         |
     |  ... cas ...       |                         |
     |                    |                         |
     |  Iny klient        |  → Backend A            |
     |  (SELECT * FROM)   |  (teraz pouzivany       |
     |                    |   inymi!)               |
     |                    |                         |
     |                    |                    NOTIFY events
     |                    |                         |
     |                    |  Notifikacia ide na     |
     |                    |  Backend A... ktory     |
     |                    |  teraz obsluhuje INEHO  |
     |                    |  klienta!               |
     |                    |                         |
     ❌ Povodny listener NIKDY nedostane notifikaciu!

Session State ktory sa rozbije s transaction poolingom

PostgreSQL session-level features ktore NEPREZIJU transaction pooling:

1. LISTEN/NOTIFY
   - LISTEN sa zaregistruje na spojenie
   - Transakcia skonci → spojenie uvolnene
   - Notifikacie prichadzaju na "vase" spojenie (teraz niekoho ineho)

2. Prepared Statements (server-side)
   - PREPARE stmt AS SELECT ...
   - Spojenie uvolnene po transakcii
   - EXECUTE stmt → "prepared statement does not exist"

3. Session Variables
   - SET search_path = 'myschema'
   - SET statement_timeout = '5s'
   - Dalsia transakcia moze byt na inom spojeni

4. Advisory Locks
   - pg_advisory_lock(key)
   - Lock drzany na spojeni, nie vasim klientom
   - Spojenie uvolnene → stratite lock

5. Temporary Tables
   - CREATE TEMP TABLE ...
   - Tabulka existuje na spojeni
   - Spojenie uvolnene → tabulka stratena (alebo konflikty)

Diagnostika problemu

Over PgBouncer mod

# Pripoj sa na PgBouncer admin konzolu
psql "host=localhost port=6432 user=admin dbname=pgbouncer"

# Skontroluj pool mode
SHOW CONFIG;
# Hladaj: pool_mode = transaction  ← TOTO JE PROBLEM!

SHOW POOLS;
# Ukazuje aktivne pooly a ich konfiguraciu

SHOW SERVERS;
# Ukazuje backend spojenia a ich stav

Sleduj preradenie spojenia

-- Na PgBouncer admin konzole
SHOW CLIENTS;
-- Zaznamenaj si client PID

SHOW SERVERS;
-- Zaznamenaj si ktory backend vase klient pouziva

-- Teraz v aplikacii:
-- Vykonaj transakciu, potom pockaj

-- Naspat na admin konzole:
SHOW SERVERS;
-- Vas backend uz moze obsluhovat ineho klienta!

Testuj LISTEN/NOTIFY priamo

# Terminal 1: Listener (cez PgBouncer)
psql "host=localhost port=6432 user=app dbname=mydb"
mydb=> LISTEN test_channel;
LISTEN
mydb=> -- len tu pockaj

# Terminal 2: Notifier (priamo na PostgreSQL)
psql "host=localhost port=5432 user=app dbname=mydb"
mydb=> NOTIFY test_channel, 'hello';
NOTIFY

# Terminal 1: Cez PgBouncer v transaction mode
# Nic sa nezobrazi! LISTEN bol na spojeni ktore je teraz uvolnene.

# Terminal 3: Listener (priamo na PostgreSQL)
psql "host=localhost port=5432 user=app dbname=mydb"
mydb=> LISTEN test_channel;
LISTEN
# Teraz skus NOTIFY z Terminal 2 znova
# Tentokrat to funguje!

Reprodukcny Lab

Docker Compose Setup

# docker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: pass
      POSTGRES_DB: testdb
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5

  pgbouncer:
    image: edoburu/pgbouncer:1.21.0
    environment:
      DB_HOST: postgres
      DB_PORT: "5432"
      DB_USER: postgres
      DB_PASSWORD: pass
      POOL_MODE: transaction  # PROBLEM!
      ADMIN_USERS: postgres
      AUTH_TYPE: plain
    ports:
      - "6432:6432"
    depends_on:
      postgres:
        condition: service_healthy

networks:
  default:
    driver: bridge

Reprodukuj bug

# Spusti stack
docker compose up -d

# Pockaj na servisy
sleep 5

# Terminal 1: Listen cez PgBouncer (ZLYHA)
docker compose exec postgres psql \
  "host=pgbouncer port=6432 user=postgres password=pass dbname=testdb" \
  -c "LISTEN my_channel;" -c "SELECT pg_sleep(60);"

# Terminal 2: Posli notifikaciu priamo na Postgres
docker compose exec postgres psql \
  "host=localhost port=5432 user=postgres password=pass dbname=testdb" \
  -c "NOTIFY my_channel, 'hello from postgres';"

# Terminal 1 by mal ukazat notifikaciu... ale NEUKAZE!

# Teraz dokazeme ze to funguje priamo na Postgres:
# Terminal 3: Listen priamo na Postgres (BUDE FUNGOVAT)
docker compose exec postgres psql \
  "host=localhost port=5432 user=postgres password=pass dbname=testdb" \
  -c "LISTEN my_channel; SELECT 1; \\watch 1"

# Terminal 2: Posli dalsiu notifikaciu
docker compose exec postgres psql \
  "host=localhost port=5432 user=postgres password=pass dbname=testdb" \
  -c "NOTIFY my_channel, 'hello direct';"

# Terminal 3 UKAZE notifikaciu!

Oprava

Moznost 1: Session Pooling pre LISTEN/NOTIFY klientov

# pgbouncer.ini - Viacero poolov s roznymi modmi

[databases]
# Bezny aplikacny traffic - transaction pooling (efektivne)
mydb = host=postgres dbname=mydb pool_mode=transaction

# LISTEN/NOTIFY klienti - session pooling (udrzuje state)
mydb_session = host=postgres dbname=mydb pool_mode=session

[pgbouncer]
pool_mode = transaction  # default pre neuvedene databazy

# Aplikacny kod - pouzi spravny connection string
# Pre bezne dotazy:
regular_conn = psycopg2.connect("host=pgbouncer port=6432 dbname=mydb")

# Pre LISTEN/NOTIFY:
listen_conn = psycopg2.connect("host=pgbouncer port=6432 dbname=mydb_session")
listen_conn.autocommit = True
listen_conn.cursor().execute("LISTEN events")

Moznost 2: Priame spojenie pre listenerov

# Obid PgBouncer uplne pre LISTEN spojenia

# Bezny traffic cez pooler
POOLER_DSN = "host=pgbouncer port=6432 dbname=mydb"

# LISTEN traffic priamo na Postgres
DIRECT_DSN = "host=postgres port=5432 dbname=mydb"

class NotificationListener:
    def __init__(self):
        # Pouzi priame spojenie, nie pooler
        self.conn = psycopg2.connect(DIRECT_DSN)
        self.conn.autocommit = True

    def subscribe(self, channel):
        cur = self.conn.cursor()
        cur.execute(f"LISTEN {channel}")

    def poll(self, timeout=5.0):
        if select.select([self.conn], [], [], timeout)[0]:
            self.conn.poll()
            return list(self.conn.notifies)
        return []

Moznost 3: Aplikacny Pub/Sub

# Nahrad LISTEN/NOTIFY pollingom alebo externou queue
# ked pouzihas transaction pooling

import redis
import json

class MessageBus:
    """Pouzi Redis pub/sub namiesto PostgreSQL LISTEN/NOTIFY"""

    def __init__(self):
        self.redis = redis.Redis(host='redis', port=6379)
        self.pubsub = self.redis.pubsub()

    def publish(self, channel: str, payload: dict):
        self.redis.publish(channel, json.dumps(payload))

    def subscribe(self, channel: str):
        self.pubsub.subscribe(channel)

    def listen(self):
        for message in self.pubsub.listen():
            if message['type'] == 'message':
                yield json.loads(message['data'])

Moznost 4: Vypni Server-Side Prepared Statements

// Pre JDBC - vypni server-side prepared statements
Properties props = new Properties();
props.setProperty("prepareThreshold", "0");  // Vypni server-side prepare
// ALEBO
props.setProperty("preferQueryMode", "simple");  // Pouzi simple query protocol

Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://pgbouncer:6432/mydb", props);

Testovanie opravy

Automatizovany LISTEN/NOTIFY Test

#!/usr/bin/env python3
"""Testuj ze LISTEN/NOTIFY funguje cez tvoju connection cestu."""

import psycopg2
import select
import threading
import time
import sys

LISTEN_DSN = sys.argv[1] if len(sys.argv) > 1 else "host=localhost port=6432 dbname=mydb"
NOTIFY_DSN = sys.argv[2] if len(sys.argv) > 2 else "host=localhost port=5432 dbname=mydb"

received = []

def listener():
    conn = psycopg2.connect(LISTEN_DSN)
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute("LISTEN test_channel")

    for _ in range(20):
        if select.select([conn], [], [], 0.5)[0]:
            conn.poll()
            while conn.notifies:
                notify = conn.notifies.pop(0)
                received.append(notify.payload)
        if received:
            break
    conn.close()

def notifier():
    time.sleep(1)
    conn = psycopg2.connect(NOTIFY_DSN)
    cur = conn.cursor()
    cur.execute("NOTIFY test_channel, 'test_payload'")
    conn.commit()
    conn.close()

t1 = threading.Thread(target=listener)
t2 = threading.Thread(target=notifier)
t1.start()
t2.start()
t1.join()
t2.join()

if received and received[0] == 'test_payload':
    print("USPECH: LISTEN/NOTIFY funguje!")
    sys.exit(0)
else:
    print(f"ZLYHANIE: Ocakavane ['test_payload'], dostali sme {received}")
    print("Toto zvycajne znamena ze PgBouncer je v transaction mode")
    sys.exit(1)

Checklist

## PgBouncer + LISTEN/NOTIFY Checklist

### Detekcia
- [ ] Over PgBouncer pool_mode (SHOW CONFIG na admin konzole)
- [ ] Testuj LISTEN/NOTIFY manualne (cez pooler vs priamo)
- [ ] Skontroluj "prepared statement does not exist" chyby
- [ ] Monitoruj mieru dorucenia notifikacii

### Architekturne rozhodnutie
- [ ] Naozaj potrebujem LISTEN/NOTIFY? Zvazte alternativy
- [ ] Mozem pouzit session pooling pre listener spojenia?
- [ ] Mozu listenery obist PgBouncer uplne?

### Ak pouzivas LISTEN/NOTIFY
- [ ] Vytvor separatny pool so session modom pre listenerov
- [ ] ALEBO: Pripajaj listenerov priamo na PostgreSQL
- [ ] Dokumentuj ktoru connection cestu pouzit na co

### Ak pouzivas Prepared Statements
- [ ] Nastav prepareThreshold=0 v JDBC
- [ ] Alebo pouzi session pooling pre postihnutych klientov
- [ ] Testuj prezitie prepared statements cez transakcie

Zaver

PgBouncer transaction pooling + LISTEN/NOTIFY pasca je perfektny priklad ako dve veci ktore funguju perfektne samostatne sa mozu rozbit ked sa kombinuju. PostgreSQL LISTEN/NOTIFY je elegantny a spolahlivy. PgBouncer transaction pooling je excelentny sposob ako skalovat pouzitie spojeni. Ale spolu vytvaraju tichy failure mode kde notifikacie miznii bez akejkolvek chyby.

Korenom priciny je fundamentalny nesulad: LISTEN/NOTIFY je session-level state (listener je zaregistrovany na konkretnom backend spojeni), ale transaction pooling uvolnuje spojenia medzi transakciami. Vasa LISTEN registracia zije na spojeni ktore PgBouncer s radostou odovzda inym klientom.

Klucove principy:

1. Transaction pooling uvolnuje spojenia medzi transakciami - vas session state neprezije
2. LISTEN je session-level, nie transaction-level - vyzaduje perzistentne spojenie
3. Ziadna chyba sa nevyvolava - LISTEN uspeje, notifikacie len idu na nespravne miesto
4. Session pooling alebo priame spojenia su potrebne - pre akekolvek session-state features
5. Testuj cestu notifikacii explicitne - nepredpokladaj ze to funguje len preto ze LISTEN uspel

Skontroluj PgBouncer mod hned teraz: SHOW CONFIG; na admin konzole. Ak ukazuje pool_mode = transaction a pouzivas LISTEN/NOTIFY, mas bug co caka na to kedy sa prejavi.

Suvisiace clanky

• PostgreSQL Prepared Statement Plan Cliff - Dalsia prepared statement pasca
• Connection Pool Sizing s Little’s Law - Zaklady konfiguracie poolov