Radio for DuckDB 2.0: listen again, and see what the queue lost

A listener that occasionally sleeps through an arriving message is difficult to trust. So is a prepared query that works once and then returns no rows because the extension remembered that it was done.

Those were two of the problems behind the Radio extension’s DuckDB 2.0 update. Radio connects SQL to WebSockets and Redis Pub/Sub, with a local loopback transport for testing. The new work fixes the lifetime of execution state, closes gaps in waiting for messages, and makes receive-queue losses visible.

Run the same prepared query twice

A table function has setup work it can do when DuckDB binds a statement, and work it must do for each execution. Radio had put some of the latter in the former. A done flag could survive the first execution of a prepared statement, making the second execution return nothing.

The update moves that state to the execution. This small example uses only the local loopback transport:

CALL radio_subscribe('loopback://article');

PREPARE received AS
    SELECT count(*) AS message_count FROM radio_received_messages();

EXECUTE received;

CALL radio_subscription_received_message_add(
    'loopback://article', NULL, 'first message'
);

EXECUTE received;

The prepared query sees the message that arrived after it was prepared. The same rule now applies to subscription inspection, transmit calls, flushing, sleeping and listening. A prepared radio_unsubscribe doesn’t merely repeat its old success: executing it after the subscription is gone reports that no subscription exists.

The prepared-statement tests exercise these cases across successive calls. This matters in applications that prepare a small set of SQL statements once and execute them for the life of a connection.

Check the queue before sleeping

radio_listen tells you which subscriptions have unseen messages. You can poll without waiting, or wait for a bounded interval:

SELECT * FROM radio_listen(false, INTERVAL '0 seconds');

After the message in the previous example has been read by radio_received_messages, this returns no rows: reading marks the message as seen. Add another message and the subscription becomes ready again:

CALL radio_subscription_received_message_add(
    'loopback://article', NULL, 'second message'
);

SELECT * FROM radio_listen(true, INTERVAL '1 second');

The listener checks whether messages are already waiting before it sleeps. The change also fixes lost wakeups around the transition into a wait, so a notification doesn’t disappear between checking the condition and waiting for it.

Listening and reading are separate operations. radio_listen reports readiness; the received-message functions retrieve the data and update the seen counts. An empty poll doesn’t mean the subscription has never received anything, only that it has nothing unseen at that point.

A full queue should tell you what happened

Radio’s receive queue has a capacity. Once full, it drops the oldest message to make room for a new one. The update exposes received_messages_dropped_unseen alongside the number processed and currently queued.

Here is an intentionally small queue:

CALL radio_subscribe('loopback://small', receive_message_capacity := 2);

CALL radio_subscription_received_message_add('loopback://small', NULL, 'one');
CALL radio_subscription_received_message_add('loopback://small', NULL, 'two');
CALL radio_subscription_received_message_add('loopback://small', NULL, 'three');

SELECT received_messages_queued, received_messages_dropped_unseen
FROM radio_subscriptions()
WHERE url = 'loopback://small';

The first message was evicted before it was read. Evicting an already-read message doesn’t increment the unread-drop counter. The distinction gives a consumer something actionable: it can detect when it isn’t keeping up, rather than treating the remaining queue as a complete history.

The queue is still bounded, and these counters don’t make Radio a durable message log. Use them to understand a live consumer’s behavior and choose an appropriate capacity.

Preserve the payload, and check network permission at execution

The 2.0 branch also includes the fix that sends VARCHAR WebSocket payloads as text frames and BLOB payloads as binary frames. Quotes and binary bytes are retained rather than being transformed through a display representation. That matters for a JSON acknowledgment just as much as for a binary message. A NULL payload is rejected with a user-facing error.

Network subscriptions and sends now check enable_external_access when they execute. Preparing a network operation while access is enabled doesn’t give it permission to run after access has been disabled. The loopback:// transport remains usable because it never opens a network connection; inspecting local subscription state remains available too.

Clean up the local example with:

CALL radio_unsubscribe('loopback://article');
CALL radio_unsubscribe('loopback://small');

These changes are on the reviewed v2.0 branch, with offline receive tests for queue behavior. The branch’s native build currently targets Linux and macOS; its Windows build remains disabled because of a TLS-library link conflict. The Radio reference covers transport URLs and function arguments, and the Query Farm repository guide explains installation for DuckDB 2.0.