PostgreSQL Realtime Cache

PostgreSQL Realtime Cache

res_config_pgsql is the PostgreSQL backend for the GABPBX realtime configuration engine. It is used by extconfig.conf families such as sippeers, sipregs, voicemail, extensions, queues, queue_members and other dynamic configuration tables.

The public GABPBX driver uses plain realtime tables. Realtime lookups, updates, inserts, deletes and static config reads are generated directly against the table configured in extconfig.conf.

Build Requirements

Install PostgreSQL development files before building GABPBX:

apt install postgresql postgresql-contrib libpq-dev

Enable and build the module:

menuselect/menuselect --enable res_config_pgsql menuselect.makeopts
make -j$(nproc)
make install

Load the module:

; /etc/gabpbx/modules.conf
load => res_config_pgsql.so

Verify from the CLI:

*CLI> module show like res_config_pgsql
*CLI> realtime show pgsql status

Basic Configuration

res_pgsql.conf configures the PostgreSQL connection and optional cache:

[general]
dbhost=127.0.0.1
dbport=5432
dbname=gabpbx
dbuser=gabpbx
dbpass=change-this-password
requirements=warn

[failover]
;dbhost=192.0.2.20
;dbport=5432
;dbname=gabpbx
;dbuser=gabpbx
;dbpass=change-this-password

[cache]
max_items=8000
max_size=5120000

[networkupd]
port=3300

[selectfunc]
;sippeers=sippeers,gabpbx_realtime_select_sippeers
;sipregs=sipregs,gabpbx_realtime_select_sipregs

extconfig.conf maps realtime families to PostgreSQL tables:

[settings]
sippeers      => pgsql,general,sippeers
sipregs       => pgsql,general,sipregs
voicemail     => pgsql,general,voicemail
extensions    => pgsql,general,extensions
queues        => pgsql,general,queues
queue_members => pgsql,general,queue_members

The second field, general, selects the database connection section in res_pgsql.conf. The third field is the PostgreSQL table name.

Cache Model

The cache is local to each GABPBX process.

res_config_pgsql stores cached PGresult objects by full SQL text. If a future realtime request generates the same SQL string, the driver can return the cached result instead of querying PostgreSQL again.

The cache has two limits:

[cache]
max_items=8000
max_size=5120000

• max_items limits the number of cached SQL result sets.
• max_size limits approximate memory consumed by cached result sets.

When the cache is full, new result sets are not cached and the driver logs a warning. Existing cache entries remain available.

The cache can be cleared from the CLI:

*CLI> realtime show pgsql cache clear ram

SELECT Functions

[selectfunc] lets PostgreSQL receive the generated realtime SQL through a database function:

[selectfunc]
sippeers=sippeers,gabpbx_realtime_select_sippeers

For this mapping, GABPBX calls:

SELECT * FROM gabpbx_realtime_select_sippeers(E'<generated SQL>', '<systemname>');

This allows the database to:

• Track which SQL statements are cached.
• Track which table rows were returned by each cached SQL statement.
• Notify GABPBX when a relevant row changes.
• Add database-side routing or normalization while keeping the realtime API unchanged.

The public function signature should normally be:

function_name(psql varchar, psystemname varchar)
RETURNS SETOF target_table

Keep public selector functions to this two-argument form unless your local branch intentionally extends the driver.

Minimal SIP Peer Table

This is a compact sippeers table suitable for chan_sofia examples. Add columns as needed for your deployment.

CREATE TABLE sippeers (
    id serial PRIMARY KEY,
    name varchar(80) NOT NULL,
    username varchar(80),
    defaultuser varchar(80),
    secret varchar(80),
    md5secret varchar(80),
    type varchar(16) NOT NULL DEFAULT 'peer',
    host varchar(80) NOT NULL DEFAULT 'dynamic',
    context varchar(80),
    transport varchar(12) DEFAULT 'udp',
    port varchar(8),
    ipaddr varchar(45) DEFAULT '',
    fullcontact varchar(512) DEFAULT '',
    regseconds varchar(20) DEFAULT '0',
    regserver varchar(32),
    useragent varchar(120) DEFAULT '',
    nat varchar(64) DEFAULT 'force_rport,comedia',
    directmedia varchar(10) DEFAULT 'no',
    dtmfmode varchar(16) DEFAULT 'rfc2833',
    disallow varchar(100) DEFAULT 'all',
    allow varchar(100) DEFAULT 'alaw,ulaw,opus',
    qualify varchar(8) DEFAULT 'no',
    insecure varchar(32),
    call_limit varchar(16),
    mailbox varchar(80),
    language varchar(8),
    encryption varchar(8) DEFAULT 'no',
    lockuseragent smallint DEFAULT 0,
    callbackextension varchar(250),
    accountcode varchar(40),
    created_at timestamp DEFAULT now(),
    updated_at timestamp DEFAULT now(),
    UNIQUE (name)
);

CREATE INDEX sippeers_name_idx ON sippeers(name);
CREATE INDEX sippeers_host_idx ON sippeers(host);
CREATE INDEX sippeers_regexten_idx ON sippeers(regseconds);

For chan_sip compatibility, older schemas may use a column named call-limit. PostgreSQL accepts quoted identifiers:

ALTER TABLE sippeers ADD COLUMN "call-limit" varchar(16);

For new public schemas, prefer call_limit only if the consuming channel code is configured to read that name. Compatibility schemas should keep the legacy column spelling expected by SIP modules.

Cache Metadata Tables

The following tables are used by the database-side cache invalidation example.

CREATE TABLE gabpbx_systems (
    id serial PRIMARY KEY,
    name varchar(128) NOT NULL UNIQUE,
    host varchar(128) NOT NULL,
    udp_port integer NOT NULL DEFAULT 3300,
    created_at timestamp NOT NULL DEFAULT date_trunc('seconds', now())
);

CREATE TABLE gabpbx_cache_sql (
    id serial PRIMARY KEY,
    systemid integer NOT NULL REFERENCES gabpbx_systems(id) ON DELETE CASCADE,
    tablename varchar(64),
    sqlcmd varchar(1024) NOT NULL,
    update_count integer NOT NULL DEFAULT 0,
    dateins timestamp NOT NULL DEFAULT date_trunc('seconds', now()),
    datelastupd timestamp DEFAULT date_trunc('seconds', now()),
    UNIQUE (systemid, sqlcmd)
);

CREATE TABLE gabpbx_cache_row (
    id serial PRIMARY KEY,
    sqlid integer NOT NULL REFERENCES gabpbx_cache_sql(id) ON DELETE CASCADE,
    rid integer NOT NULL,
    UNIQUE (sqlid, rid)
);

CREATE INDEX gabpbx_cache_row_rid_idx ON gabpbx_cache_row(rid);
CREATE INDEX gabpbx_cache_row_sqlid_idx ON gabpbx_cache_row(sqlid);
CREATE INDEX gabpbx_cache_sql_sqlcmd_idx ON gabpbx_cache_sql(sqlcmd);
CREATE INDEX gabpbx_cache_sql_tablename_idx ON gabpbx_cache_sql(tablename);
CREATE INDEX gabpbx_cache_sql_update_idx ON gabpbx_cache_sql(update_count);

Register each PBX process that should receive UDP invalidations:

INSERT INTO gabpbx_systems (name, host, udp_port)
VALUES ('pbx-a', '127.0.0.1', 3300);

name must match systemname from gabpbx.conf when systemname is set. If autosystemname=yes is used, it must match the detected hostname.

Cache Functions

Record a returned row as belonging to a cached SQL statement:

CREATE OR REPLACE FUNCTION gabpbx_cache_update(psqlid integer, pid integer)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO gabpbx_cache_row (sqlid, rid)
    VALUES (psqlid, pid)
    ON CONFLICT (sqlid, rid) DO NOTHING;
END;
$$;

Example selector for sippeers:

CREATE OR REPLACE FUNCTION gabpbx_realtime_select_sippeers(
    psql varchar,
    psystemname varchar
)
RETURNS SETOF sippeers LANGUAGE plpgsql AS $$
DECLARE
    cache_row gabpbx_cache_sql%ROWTYPE;
    result sippeers;
    counter integer := 0;
    newsql boolean := false;
    vsystemid integer;
BEGIN
    SELECT id INTO vsystemid
      FROM gabpbx_systems
     WHERE name = psystemname;

    IF vsystemid IS NULL THEN
        RAISE EXCEPTION 'Unknown GABPBX system name: %', psystemname;
    END IF;

    SELECT * INTO cache_row
      FROM gabpbx_cache_sql
     WHERE systemid = vsystemid
       AND sqlcmd = psql;

    IF cache_row.id IS NULL THEN
        cache_row.id = nextval('gabpbx_cache_sql_id_seq');
        cache_row.systemid = vsystemid;
        cache_row.tablename = 'sippeers';
        cache_row.sqlcmd = psql;
        cache_row.update_count = 0;
        cache_row.dateins = date_trunc('seconds', now());
        cache_row.datelastupd = cache_row.dateins;
        newsql = true;
    END IF;

    FOR result IN EXECUTE psql LOOP
        counter = counter + 1;
        PERFORM gabpbx_cache_update(cache_row.id, result.id);
        RETURN NEXT result;
    END LOOP;

    IF counter <> 0 AND newsql THEN
        INSERT INTO gabpbx_cache_sql VALUES (cache_row.*);
    END IF;
END;
$$;

Invalidation Triggers

When a row changes, mark every cached SQL statement that previously returned that row:

CREATE OR REPLACE FUNCTION gabpbx_table_cache_bud()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    vsqlid integer;
BEGIN
    IF TG_OP = 'INSERT' THEN
        RETURN NEW;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        FOR vsqlid IN SELECT sqlid FROM gabpbx_cache_row WHERE rid = OLD.id LOOP
            UPDATE gabpbx_cache_sql
               SET update_count = update_count + 1,
                   datelastupd = date_trunc('seconds', now())
             WHERE id = vsqlid
               AND EXTRACT(epoch FROM (now() - datelastupd)::interval) > 10;
        END LOOP;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        FOR vsqlid IN SELECT sqlid FROM gabpbx_cache_row WHERE rid = OLD.id LOOP
            DELETE FROM gabpbx_cache_sql
             WHERE id = vsqlid
               AND EXTRACT(epoch FROM (now() - datelastupd)::interval) > 10;
        END LOOP;
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER sippeers_cache_bud
BEFORE INSERT OR UPDATE OR DELETE ON sippeers
FOR EACH ROW EXECUTE FUNCTION gabpbx_table_cache_bud();

When gabpbx_cache_sql changes, notify the PBX by sending the exact SQL text over UDP. The UDP payload must match the cached SQL string exactly.

This example assumes a PostgreSQL extension or C function named:

send_udp(msg text, ip text, port integer) RETURNS boolean

The notification trigger:

CREATE OR REPLACE FUNCTION gabpbx_cache_sql_bud()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
    vhost varchar;
    vport integer;
    vsql text;
BEGIN
    IF TG_OP = 'INSERT' THEN
        RETURN NEW;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        SELECT host, udp_port INTO vhost, vport
          FROM gabpbx_systems
         WHERE id = NEW.systemid;
        vsql = NEW.sqlcmd;
    ELSIF TG_OP = 'DELETE' THEN
        SELECT host, udp_port INTO vhost, vport
          FROM gabpbx_systems
         WHERE id = OLD.systemid;
        vsql = OLD.sqlcmd;
        DELETE FROM gabpbx_cache_row WHERE sqlid = OLD.id;
    END IF;

    IF vhost IS NOT NULL AND vport IS NOT NULL THEN
        PERFORM send_udp(vsql, vhost, vport);
    END IF;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER gabpbx_cache_sql_biud
BEFORE INSERT OR UPDATE OR DELETE ON gabpbx_cache_sql
FOR EACH ROW EXECUTE FUNCTION gabpbx_cache_sql_bud();

Manual UDP Invalidation

For testing, send an exact SQL cache key to the listener:

printf "%s" "SELECT * FROM sippeers WHERE (name = '100')" | nc -u -w1 127.0.0.1 3300

The next lookup for the same SQL string reloads from PostgreSQL.

How It Works

1. A SIP module asks realtime for a peer.
2. res_config_pgsql builds SQL such as:

SELECT * FROM sippeers WHERE (name = '100')

3. If [selectfunc] is configured, the driver calls the mapped function with the SQL string and the PBX systemname.
4. The selector function executes the SQL and records which row ids were returned.
5. The result is cached in GABPBX RAM using the exact SQL string as the key.
6. Later, an UPDATE or DELETE on a returned row marks affected SQL strings dirty in gabpbx_cache_sql.
7. The gabpbx_cache_sql trigger sends that SQL string to GABPBX over UDP.
8. The GABPBX cache listener marks the matching RAM cache item stale.
9. The next realtime read refreshes the cached item from PostgreSQL.

Verification

Check PostgreSQL driver status:

*CLI> realtime show pgsql status

Clear cache:

*CLI> realtime show pgsql cache clear ram

Verify a peer lookup:

*CLI> sip show peer 100

Inspect database cache metadata:

SELECT id, tablename, update_count, sqlcmd
  FROM gabpbx_cache_sql
 ORDER BY id DESC
 LIMIT 20;

SELECT sqlid, rid
  FROM gabpbx_cache_row
 ORDER BY id DESC
 LIMIT 20;

Update a peer and verify that the SQL cache item is invalidated:

UPDATE sippeers
   SET qualify = 'yes',
       updated_at = now()
 WHERE name = '100';

Then run the same sip show peer 100 again. The next realtime lookup should refresh the cached data.

Operational Notes

• The cache is per GABPBX process, not shared memory.
• Multi-node deployments must notify every PBX node.
• UDP invalidation is intentionally lightweight. It is not a durable queue.
• If in doubt, use realtime show pgsql cache clear ram.
• Keep generated SQL stable. The UDP payload must match the cached SQL text.
• Use requirements=warn in production unless schema auto-change is intended.
• Keep credentials out of public examples and use dedicated PostgreSQL roles with only the permissions required by realtime.