PostgreSQL’s LISTEN/NOTIFY mechanism provides asynchronous inter-process signaling, but it is transactional: notification delivery occurs only after the sending transaction commits, never earlier. This playbook drills into that commit-time contract and sets out a disciplined test fixture to verify it. Receiving a notification means its notifying transaction committed; it does not by itself prove that the application’s expected row exists or matches the payload. The durable row state must also match the fixture’s independent expectations. Specifically, we must confirm: (1) notifications are delivered only after commit, (2) duplicate notifies within one transaction (same channel+payload) are collapsed to a single event, (3) listeners must register (and re-register on reconnect) in a way that avoids race conditions, and (4) there is no hidden replay or extra delivery beyond the contract.
To do this, we define a precise acceptance criterion, build an isolated Docker fixture, and run controlled experiments with expected case and negative controls. For each scenario we capture raw notification output and check the underlying table state (our independent oracle). Each outcome is marked ACCEPTED if it meets the documented contract or REJECTED if not. Finally we apply an ACCEPT / REPAIR / RERUN / HOLD decision matrix to guide any needed action. This rigorous approach complements discussions of PostgreSQL queue designs by checking exactly what PostgreSQL guarantees and where defensive checks belong. All sample outcomes in this playbook are EXPECTED / NOT EXECUTED; they describe acceptance criteria, not completed laboratory results.
1. Define the acceptance contract
First, we must state precisely what “success” looks like for PostgreSQL LISTEN/NOTIFY. Based on the PostgreSQL NOTIFY documentation, we require:
Commit-time delivery: Any NOTIFY sent inside a transaction must only be delivered after that transaction commits. If the transaction rolls back, no notification should ever arrive. A listening session inside its own transaction will only see the event after both the sending and (if applicable) its own transaction have ended.
Duplicate suppression: If within a single transaction the same channel/payload is signaled more than once, the listener should see at most one notification for that (channel,payload) combo. Distinct payloads or channels should all be delivered as separate events, preserving send order. Notification ordering across transactions follows commit order.
Listener registration semantics: A listener’s LISTEN only takes effect at commit. If LISTEN is issued in a transaction that later rolls back, the prior registration state remains unchanged. More subtly, registration takes effect at an instant during LISTEN’s commit step. Commit LISTEN, inspect the database state in a new transaction, and then rely on notifications for later changes. If a listener disconnects and reconnects, it must re-establish its LISTEN; notifications generated while it was down are not queued for replay (the table state is the recovery oracle).
Notification versus durable row state: Receiving a notification is a hint to inspect application data, not proof that a particular row exists. The definitive oracle is the row state in the database. Within this fixture, we must check that the notification set and committed rows match the per-case manifest, using a fresh query of the target table. Explicit signals such as D1 and D2 are associated with row D by that manifest; their payloads are not separate row keys. Conversely, if the row is committed, we expect a notification (except in corner cases we document). In summary: notifications may be dropped (e.g. if the listener is not connected) but should never arrive for a non-committed transaction.
Acceptance criterion: We will design tests (with clear PASS/FAIL outcomes) to check each assertion above. The fixture should show that only committed transactions produce notifications, and that the notification set and corresponding row state match each case’s declared expectations. We’ll assert concrete conditions like “receiving exactly one notification per channel/payload per committed transaction”, and “no notification when an explicit rollback occurs”. Any deviation (e.g. a notification for a rolled-back change, or missing data after a notification) will be deemed a failure. This contract goes beyond simply “notification returns without error”; it ties the asynchronous signal to the durable state in a verifiable way.
2. Establish the documented baseline
Before testing, we review the official guarantees and record them as our baseline. The PostgreSQL docs state clearly that NOTIFY is transactional: “if a NOTIFY is executed inside a transaction, the notify events are not delivered until and unless the transaction is committed… if the transaction is aborted, all the commands within it have had no effect, including NOTIFY.” This implies the negative control: a rollback must produce no listener output. The docs also warn that “notification events are only delivered between transactions”: if a listener is itself in a transaction, it will hold off dispatch until it exits the transaction.
On duplicates, the docs note: “If the same channel name is signaled multiple times with identical payload strings within the same transaction, only one instance … is delivered to listeners.” Thus we expect any identical pg_notify calls in one transaction to collapse into one delivered event. Conversely, distinct payloads or separate transactions are always distinct notifications. These lines provide a benchmark: our test must show exactly one message in the duplicate case, and two (or more) when payloads differ.
For listener registration, the docs say LISTEN takes effect at commit. If a fresh, unregistered session issues LISTEN in a transaction and rolls back, it remains unregistered. A session that was already listening retains its existing registration. Also, the docs caution about a subtle race: “if concurrently-committing transactions are sending notify events, exactly which of those will the newly listening session receive? The session will receive all events committed after an instant during the transaction’s commit step.” The rule they suggest is: commit the LISTEN in its own transaction, then check state, then rely on notifications for later changes. We will mirror this advice in our fixture.
We also note that at session end PostgreSQL automatically executes UNLISTEN *; in practice our controlled tests will end sessions explicitly, so this just confirms no lingering listeners remain.
Finally, the Task Queues guide to Postgres LISTEN/NOTIFY for job wake-ups summarizes these semantics succinctly: “NOTIFY is transactional on the sending side… delivered only when that transaction commits… [and] notifications with identical channel and payload raised in the same transaction are collapsed into one.” Importantly, it adds: “a listener that is disconnected at that moment never receives [the notification]; there is no replay.” This warns us: if our listener reconnects, we should not expect to see notifications sent in the meantime. Instead, our independent manifest/oracle must cover any missed events.
Test boundaries and pass/fail: From this review, we define test boundaries. For commit-time delivery: a transaction that does NOTIFY then COMMIT should produce exactly one notification visible to a listener whose registration was committed before the writer transaction (PASS). If we do NOTIFY then ROLLBACK, the listener should see nothing (PASS if no output, FAIL if any notification appears). For duplicates: two identical NOTIFY calls in one transaction should still yield exactly one notification (PASS if one, FAIL otherwise). For distinct payloads in one tx: two notifications should yield two events in order (PASS if two, FAIL otherwise). For listener registration: a fresh session’s first LISTEN in a transaction that rolls back should leave it unregistered (so even a NOTIFY after would not be seen; PASS if silent). If a listener disconnects, we must see its manual LISTEN (after reconnect) doesn’t retroactively get old events (confirmed if only new commits trigger new notifies). Each of these criteria gives a concrete pass/fail check. We will now build a fixture to test them.
Pin software versions and environment
To make our tests reproducible, we fix PostgreSQL versions and environment. The fixture targets PostgreSQL 15.20 and PostgreSQL 16.16, both released on November 12, 2026, so the same cases can be checked across the two major versions. We assume a standard Linux x86_64 environment with Docker available. The commands we use (below) explicitly name images by tag (e.g. postgres:15.20). We also fix locale (LC_ALL=C.UTF-8) for consistent output if needed.
Our Docker Compose YAML will start a database with minimal but defined settings. For example:
services:
postgres:
image: postgres:15.20
environment:
POSTGRES_PASSWORD: example
command: postgres
ports:
- "127.0.0.1:5432:5432"This ensures we use a clean, throwaway DB instance for testing (no preexisting database), and we connect via psql through docker compose exec. We will confirm the server version with:
SHOW server_version;which should match 15.20 (or 16.16 in a second run).
We note the test boundary: all sessions (A, B, etc.) will share this same database. We will not rely on any external connectivity (no shared network, no PgBouncer); each listener uses a direct connection. We assume default isolation level READ COMMITTED (as documented for consistency). If a test fails under READ COMMITTED, diagnose that run against the stated contract. Any additional run under SERIALIZABLE is a separate case, not a substitute for passing the default-isolation case.
If any part of the environment fails (e.g. container won’t start, or a psql command syntax error), the fixture is broken; those are clear FAIL conditions. Otherwise all pass criteria are defined above.
3. Build a disposable controlled fixture
We create a disposable “lab” schema and tables for isolation. All tests run against newly created objects in a dedicated schema (e.g. lab_notify). At startup, we will ensure the schema does not exist, then create it and relevant tables. For this playbook, the fixture is simple: one table with a trigger that issues NOTIFY, plus a separate listener client. For example:
-- Setup in one committed transaction
BEGIN;
DO $$ BEGIN
IF EXISTS (
SELECT 1 FROM pg_namespace
WHERE nspname = 'lab_notify'
) THEN
RAISE EXCEPTION 'Schema already exists';
END IF;
END $$;
CREATE SCHEMA lab_notify;
CREATE TABLE lab_notify.events (
id SERIAL PRIMARY KEY,
payload TEXT NOT NULL
);
-- A trigger to notify on insert
CREATE OR REPLACE FUNCTION lab_notify.lab_notify_func()
RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('testchan', NEW.payload);
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_lab_notify
AFTER INSERT ON lab_notify.events
FOR EACH ROW
EXECUTE FUNCTION lab_notify.lab_notify_func();
COMMIT;This setup ensures every INSERT on events will pg_notify('testchan', payload), bundling changes with a notification. We commit the schema so it is visible to all sessions.
Next, we launch multiple psql sessions. We designate:
Session L (Listener): This session connects and issues LISTEN testchan in its own transaction or autocommit mode.
Session W (Writer): This session will do various transactions (commits and rollbacks) inserting into events, causing notifies.
Session R (Reader): Another session that may also LISTEN or just check the events table for verification.
For each session, open an interactive psql connection. In Session L, issue LISTEN testchan; in autocommit mode and keep the connection open:
docker compose exec postgres psql -X -U postgresSession L stays connected after LISTEN testchan; completes. A psql -c command would exit after executing its command and would not maintain this listener. Session W can then run:
BEGIN;
INSERT INTO lab_notify.events (payload) VALUES ('A');
COMMIT; -- triggers notify after commitAfter the writer commits, execute SELECT 1; in Session L so psql checks for pending notifications. The expected output is shown below (EXPECTED / NOT EXECUTED):
Asynchronous notification "testchan" with payload "A" received from server process with PID <pid>.
Capture the listener’s interactive psql transcript as evidence. If Session W executes ROLLBACK; instead, Session L should show no new notification. All sample listener output in this playbook is EXPECTED / NOT EXECUTED. Docker server logs can support diagnosis, but they do not replace the listener client’s transcript.
At the end of tests, we will tear down by dropping the lab_notify schema and its owned objects or removing the disposable container and its fixture-owned volumes. The fixture is fully disposable. We ensure each test starts from a clean state. For example, after each rollback test we could execute DELETE FROM lab_notify.events; COMMIT; to clear the table. This ensures isolation between tests.
Define the negative control
A key negative control is to explicitly rollback the transaction after NOTIFY and verify no notification arrives. For example:
-- Session W
BEGIN;
INSERT INTO lab_notify.events (payload) VALUES ('X');
ROLLBACK;Since the payload 'X' never persists, the contract dictates Session L gets nothing. We will label this expected silence PASS (no output). If, contrary to spec, we saw a notification for 'X', that would be a FAIL. This negative control defines a clear boundary: a NOTIFY inside an aborted transaction must not leak. The pass/fail criterion is that the listener’s output remains unchanged after the rollback.
Another negative control is to test duplicate suppression: doing two identical NOTIFY in one tx. We know doc says it should collapse to one. As a negative control, we could intentionally expect that extra call yields no second message. E.g.:
-- Session W
BEGIN;
INSERT INTO lab_notify.events (payload) VALUES ('Y');
SELECT pg_notify('testchan','Y');
SELECT pg_notify('testchan','Y');
COMMIT;Even though the INSERT trigger and two explicit calls request three identical notifications with 'Y', we expect only one line of output on the listener. If listener saw two, that violates the documented duplicate rule. Thus the criterion: exactly one output line for identical payloads is PASS; two lines would be FAIL. (We will also run Case D with distinct payloads, expecting three notification lines.)
Finally, a negative-control scenario is listener not listening: if Session L has not issued LISTEN, then writer’s NOTIFY should produce no output anywhere. This tests the obvious baseline. Since our plan will always listen explicitly, this just confirms that NOTIFY only reaches registered listeners.
4. Freeze an independent expected-case manifest
Before running tests, we declare exactly what outcomes we expect in each valid scenario. This independent manifest is our oracle for correctness. It lists, for each test case, the expected notifications and the expected final row state. All cases below are EXPECTED / NOT EXECUTED. Each case starts with a clean events table and the listener state shown in the table:
Case | Actions (summary) | Listener state | Expected notification | Expected row state |
A: commit | W: BEGIN; INSERT 'A'; COMMIT; | Registered before W commits | One event: A | One row: A |
B: rollback | W: BEGIN; INSERT 'B'; ROLLBACK; | Registered | No new event | No row B |
C: same payload | W: BEGIN; INSERT 'C'; two explicit C signals; COMMIT; | Registered | One event: C (three requests collapse) | One row: C |
D: distinct payloads | W: BEGIN; INSERT 'D'; signal D1; signal D2; COMMIT; | Registered | Three events in order: D, D1, D2 | One row: D |
E: listener transaction | L: committed LISTEN, then BEGIN; W: BEGIN; INSERT 'E'; COMMIT; | Registered; transaction remains open | No client delivery until L ends its transaction, then E | One row: E |
F: reconnect | W: BEGIN; INSERT 'F'; COMMIT while L is disconnected; L reconnects and commits LISTEN | Disconnected during W commit | No replay of F | One row: F |
This manifest illustrates the expected outcomes; the following sections explain the cases and their controls. It records predictions rather than measured results.
We commit this manifest in our plan. We will compare observed outputs and data against it. Importantly, our manifest is independent of the test runs: it is defined before we see results. For example, in Case A above, we will accept if we see exactly one “notification 'A'” and one row in events; if the row is missing or extra notifications appear, that case fails. In Case B, success is seeing nothing at the listener and no row 'B' in the table.
This manifest approach mirrors best practices (see Refonte’s guide to PostgreSQL ID watermarks and commit order, which uses an independent expected-event manifest). It ensures we have a clear completeness claim: that for each expected event, we either validate the notification and row, or we catch its absence. Our acceptance criterion (from Sec.1) is that the set of observed events exactly matches the manifest for committed transactions.
5. Reproduce the misleading success case
Now we demonstrate a scenario that might falsely appear to succeed if one only watches the NOTIFY command return. If Session W inserts a row and successfully commits, a registered listener should receive the trigger’s notification. At first glance, a successful INSERT or NOTIFY command might be mistaken for “transaction done”. A deferred constraint can still reject the transaction when COMMIT is attempted. A notification actually received by another session would imply that the notifying transaction committed; a successful command before COMMIT does not.
We simulate this case with a deferrable uniqueness constraint on a clean fixture. PostgreSQL does not support deferrable CHECK constraints, so two identical payloads provide the intended commit-time failure:
-- Session W: deferred constraint example
BEGIN;
ALTER TABLE lab_notify.events
ADD CONSTRAINT unique_payload
UNIQUE (payload)
DEFERRABLE INITIALLY DEFERRED;
INSERT INTO lab_notify.events (payload)
VALUES ('bad'), ('bad');
NOTIFY testchan, 'bad';
COMMIT;
-- EXPECTED / NOT EXECUTED:
-- COMMIT fails on the deferred uniqueness constraint.
-- Neither row persists; no notification is delivered.Here, the deferred uniqueness constraint is checked at commit time, causing the transaction to fail. The constraint addition and both inserts are rolled back. The NOTIFY was queued, but per contract it should not deliver, because the tx aborted. On Session L, one might expect that no notification appears (even though NOTIFY was called). Indeed, since the transaction aborts, the listener should see nothing, matching the row absence.
EXPECTED / NOT EXECUTED (misleading observation): If a tester only saw no notification and no row, they might think “nothing happened”. But in reality, the transaction executed the statements, then failed the constraint check when COMMIT was attempted. We must ensure our evidence ledger notes the raw command attempts and final state.
If (by contradiction) Session L did see “Asynchronous notification 'bad' …”, that would violate the commit-time rule. That contradictory outcome would be FAIL if observed. This case remains EXPECTED / NOT EXECUTED until an actual transcript and row query are captured. Our acceptance matrix (Sec.13) will mark this condition as failure of the release gate.
Additional “misleading success” scenarios can include a genuine storage failure during commit, but pausing a commit does not by itself simulate a disk failure. Such fault injection needs a separate controlled setup. The simpler deterministic case is NOTIFY followed by an explicit ROLLBACK, as in the manifest’s Case B. If any notification sneaks through there, that’s a critical bug. We will capture the listener’s output (or silence) and store it as evidence. For each such case, we record the raw psql output. For example, after running Case B above, the evidence ledger might have:
Case | Writer commands (summary) | Expected listener output | Evidence status |
B | BEGIN; INSERT 'B'; ROLLBACK; | No new notification; PASS only if the transcript and row query confirm absence | EXPECTED / NOT EXECUTED |
These are EXPECTED / NOT EXECUTED examples. Record any divergence only after executing the case and retaining its listener transcript, writer outcome, and row-state evidence.
6. Run the valid comparison case
Next we perform the valid or “normal” scenarios where everything should work per spec, and compare the listener’s result to the manifest. For example:
Case A (simple commit):
Commands (W): BEGIN; INSERT INTO lab_notify.events (payload) VALUES ('A'); COMMIT;
Commands (L): LISTEN testchan; (committed)
EXPECTED / NOT EXECUTED: Listener sees exactly one notification:
Asynchronous notification "testchan" with payload "A" received from server process with PID <pid>.
(This matches the example in the PostgreSQL documentation.) We then run a verification query (in a new session or after) SELECT * FROM lab_notify.events; expecting the row 'A' to be present. The listener’s output and table contents should match the manifest.
Case C (duplicates, identical payload):
W: BEGIN; INSERT INTO lab_notify.events (payload) VALUES ('C'); SELECT pg_notify('testchan','C'); SELECT pg_notify('testchan','C'); COMMIT;
L: same listening as Case A.
EXPECTED / NOT EXECUTED: One notification with payload “C” (the trigger and two explicit requests collapse into one event). We mark any second line as wrong. After commit, the events table has one row 'C'. The evidence ledger will compare actual vs expected: e.g. it should show one “C” notification, one row.Case D (duplicates, distinct payloads):
W: BEGIN; INSERT INTO lab_notify.events (payload) VALUES ('D'); NOTIFY testchan, 'D1'; NOTIFY testchan, 'D2'; COMMIT;
L: listening.
EXPECTED / NOT EXECUTED: Three notifications in order: payload “D” from the INSERT trigger, then “D1”, then “D2” from the explicit commands. Exactly three notification lines are expected. The table has one row with payload 'D'. The manifest associates all three signals with this case; it does not require separate rows for D1 and D2.Session behavior:
To verify registration timing, use a fresh, unregistered Session L and execute BEGIN; LISTEN testchan; ROLLBACK;. LISTEN takes effect at commit, so this rollback leaves the session unregistered. A later writer commit should not notify it. If Session L was already listening before BEGIN, rolling back another LISTEN would preserve that earlier registration; it would not unlisten. Test delayed client delivery separately by committing LISTEN first, then keeping a new listener transaction open while the writer commits.
Make failure deterministic
To avoid flakiness, we ensure each failure scenario is deterministic. For example, to force the duplicate drop behavior, we explicitly use identical string literals. To simulate “listener inside tx”, we perform LISTEN then BEGIN then notify outside that tx. We repeat tests a few times to confirm consistency. We also fix transaction isolation, disable autocommit where needed, and avoid timing issues (by explicitly completing each scheduled command before releasing the next step). Each scenario is run in a tight script so that the orders are reproducible. Any nondeterminism (e.g. random sleep) is avoided. We define each test’s boundary: if it requires a fresh session or certain state, we document that as part of the test description.
7. Exercise negative and boundary controls
Beyond the main cases, we stress-test edge conditions:
Listener reconnection: We test what happens if a notification is sent while the listener is disconnected. For example, Session L does UNLISTEN testchan; (or simply disconnects). Meanwhile Session W does an insert and commit. Then Session L reconnects and does LISTEN testchan; again. The documented behavior is that Session L should not receive the old notification; it is not replayed (there’s no replay queue). The test checks that the listener’s output shows nothing for that past event, and instead the application must rely on scanning the table for new rows. The manifest expects no output for that past event.
Listener partial transactions: We put Session L in a long transaction that does other work (BEGIN; in L, then a confirmed writer commit, followed by COMMIT; in L) while a NOTIFY is committed by Session W. The rules state the notification will only arrive after Session L’s transaction ends. We verify that behavior: no early output, then output after COMMIT. If notifications appeared inside the active transaction, that would violate the documented client-delivery boundary, so we must see none until after commit.
Row-reading fallback: As a boundary control, after catching any notification, we deliberately read the row with a separate query. For example, after noticing “Asynchronous notification 'A'…”, we run SELECT * FROM lab_notify.events WHERE payload='A'; to confirm the row exists. This double-check verifies the fixture’s declared relationship between the signal and the row. In the negative control (rollback case), we also check that SELECT finds nothing. This read is our independent oracle for durability. For Case A, both the notification and row must be present. For the rollback control, both must be absent. For the disconnected-listener case, the row is present without a replayed notification. A missing row cannot be explained merely by a missed notification.
We also define boundary variations (listener in-tx vs out-of-tx, identical payload vs empty payload, transaction while listener is offline, etc.). We define each control’s pass/fail. For example, “missing notify” is FAIL only when the manifest requires delivery to an established listener that stayed connected, ended any blocking transaction, and polled for notifications; identical requests in one transaction still count as one event. “Extra notify” is FAIL if notify appears on a rolled-back tx or as a duplicate. These criteria tighten our acceptance.
8. Capture raw per-case evidence
For every executed test case, capture the raw psql output and record it in an evidence ledger. Keep the writer outcome, listener transcript, and row query together. The table below is an unexecuted ledger template: its output cells contain predictions, and every verdict remains EXPECTED / NOT EXECUTED. Commands are summaries of the complete steps above, not standalone SQL scripts.
Case | Description | Commands (summary) | Expected listener output | Evidence status |
A | Commit produces a notification | W: BEGIN; INSERT 'A'; COMMIT; | Asynchronous notification "testchan" with payload "A" received from server process with PID <pid>. | EXPECTED / NOT EXECUTED |
B | Rollback suppresses notification | W: BEGIN; INSERT 'B'; ROLLBACK; | No new notification | EXPECTED / NOT EXECUTED |
C | Identical payloads collapse | W: BEGIN; INSERT 'C'; signal C twice; COMMIT; | Asynchronous notification "testchan" with payload "C" received from server process with PID <pid>. | EXPECTED / NOT EXECUTED |
D | Distinct payloads stay distinct | W: BEGIN; INSERT 'D'; signal D1; signal D2; COMMIT; | Asynchronous notification "testchan" with payload "D" received from server process with PID <pid>. | EXPECTED / NOT EXECUTED |
E | Delivery waits for listener transaction | L: commit LISTEN, then BEGIN; W: BEGIN; INSERT 'E'; COMMIT; L: COMMIT; poll | No delivery while L is in its transaction; then: | EXPECTED / NOT EXECUTED |
F | Reconnect has no replay | W: BEGIN; INSERT 'F'; COMMIT while L is disconnected; L: reconnect and commit LISTEN | No historical notification; a fresh row query finds F | EXPECTED / NOT EXECUTED |
Capture each actual listener output line exactly as psql prints it, including PID information. In the template, <pid> denotes the writer backend PID that must be recorded during execution.
All unexecuted outputs are labeled EXPECTED / NOT EXECUTED. For example, Case F predicts no replay after reconnect, but this is not measured silence. Replace a prediction with a PASS or FAIL verdict only after saving the transcript and independent row query. Every expected success or failure must be compared with captured evidence.
Reject missing or duplicate evidence
If our evidence ledger shows a test case where we expected an output or lack thereof, we reject the case if the evidence is not as expected. For instance, if Case D had only one line (it should have three), that’s a fail. If Case B (rollback) showed any “notification” line, that’s a fail. Each ledger entry will explicitly mark “PASS” or “FAIL” (or EXPECTED / NOT EXECUTED while execution evidence is missing). In the final decision matrix (Sec.13) any FAIL here would push us toward “REPAIR” or “HOLD”.
9. Reconcile byte, row or event identity
After collecting evidence, we reconcile the identity of notifications with actual data. Each notification event has a channel, a payload, and a source PID. We ensure these match the event identities and row state declared for the case. For example, if we see “payload ‘A’ from PID X”, we may query pg_stat_activity to confirm PID X was our writer. More importantly, we verify the row content: e.g. case A’s payload='A'. We also check the listener transcript for extraneous events. The pg_notification_queue_usage() function reports queue occupancy; it does not expose individual notification entries or establish event identity. Server or WAL diagnostics may support an investigation but do not replace the case evidence. In practice, the most direct check is reading the events table, which we treat as authoritative.
If the observed notification set or row state differs from the manifest, we flag it. For the identical-payload case, ensure only one row was inserted and only one notification was delivered. For Case D, reconcile the three event labels D, D1, and D2 with the single expected row D. To check ordering across separate transactions, record and control the commit schedule; SERIAL IDs and insertion timestamps do not establish commit order. This is the event equivalent of a byte or row equality check in a data pipeline: identities and multiplicities must match the independently declared expectations.
10. Distinguish documented behavior from observation
At this point, separate the documented promises from the evidence collected during an actual run. Commit-time delivery, duplicate collapse, and registration rules describe what should happen. The evidence ledger records what did happen only after the fixture is executed. Compare each observed transcript and row query with its expected case. For example, no notification before commit and one event after three identical requests would satisfy their respective criteria. A contradiction requires investigation. The sample outcomes in this playbook remain EXPECTED / NOT EXECUTED.
We also check whether an apparent exception belongs to the version under test. Historical improvements to notification deduplication changed how notifications merged from subtransactions were handled in 2019. That historical caveat is not an unresolved exception to the PostgreSQL 15/16 contract. Any new behavior outside the current documentation must be recorded as an observation with its version and evidence; subtransaction experiments remain outside this fixture’s scope.
Bound the repair: If we did find a mismatch (say hypothetically we saw two ‘C’ notifications in one transaction on an older PG), we would need a mitigation (like de-duplicating notifications in application code). The extent of repair is to the narrow boundary where the guarantee broke. But in well-behaved PostgreSQL, we expect no breaks. Any out-of-spec observation is a candidate for “REPAIR” in the matrix, rather than acceptance. Until execution evidence is collected, the official guarantee defines the expected outcome rather than an observed result.
11. Repair the smallest failing boundary
In this testing scenario, a failure is a disagreement between the independent expectation and the captured evidence. If an unexpected duplicate appears, first verify the transaction boundary, channel, payload, and listener transcript. Application-level deduplication may use a stable event identity where repeated delivery would cause repeated work. A unique payload marker serves a different purpose: it keeps intentionally distinct events from collapsing. Comparing the sender PID with the listener’s own backend PID identifies self-notifications; it is not a general duplicate detector. Apply any repair to the boundary that the evidence identifies.
Another potential boundary is the listener reconnect case: if our tests show missed notifications during disconnection, the only “repair” is to make sure our application checks table state on reconnect (the doc hint is “table is truth, notify is hint”). That means augmenting the process reading the events table to do a catch-up query after any reconnect. This operational fix is outside the DB, but we document it here as necessary.
For each failure type, propose the smallest supported repair. Keep data changes and their NOTIFY in the same transaction; moving NOTIFY into a separate transaction after the data commit would create another failure window. If registration is incomplete, commit LISTEN before relying on it. If the listener disconnects, re-register and reconcile against the table. An apparent notification from a rolled-back transaction requires identity and transcript investigation before blaming the database. These are planned responses, not repairs validated by completed runs.
12. Test recovery and safe reruns
We simulate recovery scenarios. For example, if the Postgres service crashed or was restarted, what happens to pending notifications and listeners? The documented guarantee is that notifications in uncommitted transactions are lost on crash, and even committed ones sent before the listener went down might be missed. Our safe-rerun test: after a crash, we restart Postgres, reconnect the listener (issuing LISTEN again) and then re-run the writer steps from the last commit boundary. We must handle idempotence carefully: if some notifies were lost, our process must be able to re-send or re-query.
Concretely, we might do: 1. Perform a NOTIFY + COMMIT so that notification should have been delivered. 2. Immediately kill and restart the Postgres container (simulates crash). 3. Reconnect listener and session, check for notification. We expect the listener will not see the pre-crash event (consistent with “no replay”). So it must re-scan table for the row. After checking the recovered table state, test a deliberate retry or continue from the reconciled state; do not blindly reinsert an already committed event.
We record that the recovery plan is: after any restart, ALWAYS verify table state against last known checkpoint before trusting notifications. That is, re-read rows. We test that logic: with the fixture as defined, reinserting the same payload normally creates a second row because SERIAL allocates a new primary key. A separate idempotency constraint would be needed to reject that duplicate, so the retry test must detect and handle the second row explicitly.
Assign operational ownership: the DBA or DevOps team should monitor service restarts and connection health, while application code must re-register and reconcile database state on startup or reconnect. Absence of notification output alone cannot identify every missed event. For this small fixture, compare the full events table with the expected manifest before deciding what to retry. A production checkpoint requires its own correctness argument: a simple id > last_checkpoint scan can skip a lower sequence ID that commits later. The REPAIR action must therefore include a reconciliation procedure that cannot silently omit committed work.
13. Apply the ACCEPT / REPAIR / RERUN / HOLD matrix
Finally, we apply the expected outcomes in a decision matrix. Once each condition has execution evidence, we decide:
ACCEPT: If the observed behavior matches the documented contract (and the manifest) exactly. Example: Case A should be ACCEPT (notification on commit). Case C should be ACCEPT (single collapse). Case B should be ACCEPT (no notify on rollback).
REPAIR: If the behavior deviated. For example, if a duplicate got through, we would consider some code fix. If a listener got an extra notification on rollback, we’d consider it unacceptable.
RERUN: If a test was inconclusive or flaky, we might need to rerun after correcting capture or fixture problems, while preserving the declared isolation level before a final decision.
HOLD: If some behavior is partly unspecified by docs or requires more investigation (unlikely here, but e.g. subtle subtransaction case might fall here).
We apply this to each crucial assertion. E.g. commit-time delivery tests: if all PASS, we ACCEPT that property. If any fail (which we expect they don’t, given the documented contract), we’d mark REPAIR or HOLD. Duplicate tests: if PASS, ACCEPT; if not, REPAIR. Listener reconnection: PASS (no replay) means ACCEPT that “missing notifications are normal, handle via table check.” We then record any necessary follow-up (like implementing the fallback re-scan).
The matrix below gives conditional decisions. All sample outcomes are EXPECTED / NOT EXECUTED; an ACCEPT decision requires the corresponding captured evidence.
Property / scenario | Required outcome | Decision if verified | Action |
NOTIFY on COMMIT | Notification delivered after the writer commits | ACCEPT | Retain transcript and row query |
NOTIFY on ROLLBACK | No notification and no committed row | ACCEPT | Retain the negative-control evidence |
Duplicate same payload | One notification for identical requests in one transaction | ACCEPT | Confirm expected row count |
Listener reconnect | No replay; missed event recovered by a table query | ACCEPT | Ensure the application re-queries the table |
Notification ordering | Order follows send order within a transaction and commit order across transactions | ACCEPT | Compare against the controlled command schedule |
Any deviation or missing evidence | Mismatch, incomplete capture, or unexecuted case | REPAIR / RERUN / HOLD | Diagnose, repair the failing boundary, and capture a new run |
If every required case passes with complete evidence, the overall judgment can be ACCEPT: the tested behavior aligns with the contract. Any failure requires a documented diagnosis, the smallest justified repair, and a RERUN. Until the fixture is executed and reconciled, the verification status is HOLD; expected outputs alone do not establish a successful test.
14. Connect the practice to Refonte Learning
This disciplined testing approach connects to the Refonte Learning Database Administrator Essentials program, a 3-month program with a 12–14-hour weekly workload covering database design, SQL optimization, backup and recovery, security, and monitoring. This playbook illustrates related operational skills: reproducible fixtures, version-pinned environments, independent row-state checks, and evidence-based release decisions. Related practice includes verifying replication or failover, as in Refonte’s guide to PostgreSQL logical replication failover readiness. Readers seeking structured training can explore the Database Administrator Essentials program, which offers hands-on projects, mentorship, and industry-recognized certificates.
Refonte’s approach encourages building test boundaries and failure cases into every database release cycle, so that engineers can say with confidence: “PostgreSQL’s NOTIFY did fire, and yes, our transaction truly committed.”
