PG_LISTEN/NOTIFY support?

[Karl Tiedt]

I am playing with the latest driver and postgres support and was curious about using this feature set. I noticed it wasn't implemented. I had antigravity take a swing at it and will be testing this throughout the day today, I tried to create a pull request to provide an easy place to discuss it around the code itself, but that seems to no longer work. (permissions to create pull request have been changed since my last code contribution).

The branch on my fork can be seen here https://github.com/ktiedt/ldmud/tree/pgsql-listen

Below is the Markdown walkthrough associated with the fork:

# PostgreSQL LISTEN / NOTIFY Support Implemented

I have successfully added PostgreSQL `LISTEN` and `NOTIFY` support to the LDMud driver!

## Changes Made

1. **New Efuns Added:**

- `int pg_listen(string channel, string/closure callback, ...)`

- `int pg_unlisten(string channel)`

2. **Driver Modifications in `pkg-pgsql.c`:**

- Introduced a `pg_listen_t` struct attached to the `dbconn_t` object to track all active subscriptions.

- Refactored `pg_process_query()` to call `PQnotifies` and process all pending notifications on the socket.

- Updated the main event loop (`pg_process_one()`) so `PG_IDLE` connections seamlessly poll the socket without blocking the MUD.

- Hooked `pg_process_connect_reset()` so that if the connection to PostgreSQL drops and resets, all registered `LISTEN` commands are quietly re-issued to the database to ensure no downtime.

3. **Internal `PG_SILENT_QUERY` Flag:**

- Implemented a silent queueing mechanism so internal queries (`LISTEN` / `UNLISTEN` generated by the efuns or the auto-reconnect layer) execute normally but do not trigger the LPC layer's normal query-result callback.

4. **Garbage Collection (GC) Support:**

- Updated `pg_count_refs()` and `pg_clear_refs()` to properly account for the closure callbacks and allocated strings in the `listens` list, preventing memory leaks during garbage collection cycles.

## How to Test

In your LPC code, you can now do:

```c

pg_listen("item_updates", (: wipe_item_cache :));

void wipe_item_cache(string channel, string payload) {

write("Received notification on " + channel + " with payload: " + payload + "\n");

}

```

Then in `psql` or your external tool, you can type:

```sql

NOTIFY item_updates, 'excalibur_sword';

```

And the MUD will immediately fire your closure.

-Karl Tiedt