Redis Publish PostgreSQL Extension
This PostgreSQL extension allows you to connect to Redis and publish messages on a Redis channel from PostgreSQL.
Requirements
To build you must have:
- PostgreSQL 9.1+
- HIREDIS C Client 13.0+
HIREDIS
The HIREDIS C client library is a minimalistic C client library for the Redis database.
On Ubuntu 12.04+, it can be installed using the apt-get package libhiredis-dev.
sudo apt-get install libhiredis-dev
On Centos 7.6 you should build HIREDIS from source, or search for recent RPMs as system repository only has version 12 available which is too old for pg_redis_pubsub
. For example, you can use hiredis-last-0.13.3-1.el7.remi.x86_64.rpm and hiredis-last-devel-0.13.3-1.el7.remi.x86_64.rpm. Download two RPMs and run:
sudo yum install \
hiredis-last-devel-0.13.3-1.el7.remi.x86_64.rpm \
hiredis-last-0.13.3-1.el7.remi.x86_64.rpm
Installation
This code is a standard PostgreSQL extension so all you need to run is:
make clean install
And then in the database:
CREATE EXTENSION redis;
Settings
Settings can be
- specified globally using
postgresql.conf
orALTER SYSTEM ... SET var = 'val'
- specified at the database level using
ALTER DATABASE ... SET var = 'val'
- specified at the role level using
ALTER ROLE ... SET var = 'val'
- specified at the session level using
SET var = 'val'
GUC settings
You can set/change the following variables with commands described above:
- redis.host - Redis client host setting.
- redis.port - Redis client port setting.
Usage
redis_status()
Returns the status of the Redis client.
redis_connect()
Connects the Redis client using the redis.host and redis.port configuratin settings.
redis_disconnect()
Disconnects the Redis client.
redis_publish(channel text, message text)
Publishes a message on the channel provided.
If the Redis client is not currently connected, it will first create a new connection before attempting to publish the message.
Roadmap
- Return record with columns host text, port int, connected boolean.
- Add a set of Redis connections to use?
- Add subscribe and psubscribe support that executes a PostgreSQL function?
Examples
Basic Example
CREATE EXTENSION IF NOT EXISTS redis;
SELECT redis_connect();
SELECT redis_publish('mychannel', 'Hello World');
SELECT redis_disconnect();
Trigger Example
CREATE EXTENSION IF NOT EXISTS redis;
CREATE TABLE IF NOT EXISTS products (
id serial,
name varchar(255)
);
CREATE OR REPLACE FUNCTION after_change()
RETURNS TRIGGER AS
$$
DECLARE
channel text;
message json;
BEGIN
channel = 'products:' || NEW.id::text;
message = to_jsonb(NEW);
PERFORM redis_publish(channel, message::text);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER products_after_change
AFTER INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE PROCEDURE after_change();
INSERT INTO products (name) VALUES ('Ale'), ('Beer'), ('Cider');