rw_redis_fdw
Writable Foreign Data Wrapper for Redis
This PostgreSQL extension provides a Foreign Data Wrapper for read (SELECT) and write (INSERT, UPDATE, DELETE) access to Redis databases (http://redis.io). Supported Redis data types include: string, set, hash, list, zset, and pubsub.
Note that the output FDW module is called redis_fdw, even though this repository is called rw_redis_fdw and not be confused with https://github.com/pg-redis-fdw/redis_fdw (which was used as a basis for the table schema but with very different code), to enable existing users to migrate to this repo. Instructions hereon-in refer to this repository only.
redis_fdw (nahanni/rw_redis_fdw) was written by Leon Dang, sponsored by Nahanni Systems Inc.
This project is currently work in progress and may have experience significant changes until it becomes stable. Use it with caution and at your own risk!
PostgreSQL version compatibility
Currently tested against PostgreSQL 9.4+, 10, 11, 12, 13, 14 and 15. Other versions might work but unconfirmed.
Building
Dependencies:
- hiredis - usually part of the Redis installation on your system, or obtain from source at https://github.com/redis/hiredis.
- PostgreSQL pgxs - part of the PostgreSQL package or source SDK
Build
PATH=<pgsql_prefix>/bin:$PATH make
sudo PATH=<pgsql_prefix>/bin:$PATH make install
where pgsql_prefix is where you've installed PostgreSQL to, e.g. /usr/local/postgresql/9.4.0
Build RPM
For Centos 7 you should add Centos SCLO repo and install required packages:
yum -y install centos-release-scl-rhyum-config-manager --enable rhel-server-rhscl-7-rpmsyum -y install hiredis-devel postgresql11-devel llvm-toolset-7-clang
And build RPM by command:
rpmbuild -tb rw_redis_fdw-<tag>.tar.gz
Options
Server options
Server options are optional if using the defaults.
option | description | default |
---|---|---|
host | (or "address") Redis server's unix socket absolute path, hostname or IP address | localhost |
port | server network port | 6379 |
password | Redis-authentication | no default |
Example of how to add a Redis backend:
CREATE EXTENSION redis_fdw;
CREATE SERVER redis_server
FOREIGN DATA WRAPPER redis_fdw
OPTIONS (host '127.0.0.1', port '6379');
CREATE USER MAPPING FOR PUBLIC
SERVER redis_server
OPTIONS (password 'secret');
PostgreSQL Table Schema
redis_fdw expects the tables to have a particular structure to help it issue Redis commands and translate data. The default column names for each table are listed in the following sections; if they are relabeled then the column option redis <redis_fdw_original>
must be provided to map to the original redis_fdw expected name. For example:
CREATE FOREIGN TABLE rft_str (
key TEXT,
v TEXT,
...
)...
ALTER FOREIGN TABLE rft_str ALTER COLUMN v OPTIONS (ADD redis 'value');
Any extraneous columns defined are ignored and untested.
Table options
CREATE FOREIGN TABLE ftbl (
...
) ...
OPTIONS ( <options> )
option | description |
---|---|
tabletype | Mandatory option. Specifies the Redis data type: string, hash, mhash, set, zset, list, ttl, len, publish |
key | bind table to a specific key. Note: do not specify a "key" column in the table if this option is used. For a PUBLISH table, use channel instead of key. |
keyprefix | prefix all keys in the table with this value. One use is to enable namespace separation from other keys in Redis |
readonly | read-only table, no writes permitted |
database | for Redis database to use (an integer) |
tabletype, with column names, can be one of the following (refer to the subsections further below for operations that can be completed on them):
- string - key-value
- hash - key-field-value
- mhash or hmset - key-field[]-value[]. Read-only table
- set - key-member
- zset - key-member-score-index
- list - key-index-value
- publish - channel-message-len
- INSERT issues PUBLISH channel message,
- SELECT issues PUBSUB NUMSUB channel
tabletype for non-redis data types, but useful tables:
- ttl - key-expiry. Inspect or set/remove the expiry from a key.
- len - key-tabletype-len. Retrieve the number of items in a key or the entire database.
- keys - retreive all keys in the database
key must be either defined as a column or a table option, but not both.
Note that expiry (in seconds) is an optional column in all the tables. If it is specified, then redis_fdw will also fetch the key's expiry. This is only done once per unique key fetch, so it isn't too expensive.
String key-value data type
Read-Write
CREATE FOREIGN TABLE rft_str(
key TEXT,
value TEXT,
expiry INT
) SERVER xxx
OPTIONS (tabletype 'string');
Hash
Read-Write
Each row represents a field and value of the hash, so the key and expiry columns will be the same for all rows.
CREATE FOREIGN TABLE rft_hash(
key TEXT,
field TEXT,
value TEXT,
expiry INT
) SERVER xxx
OPTIONS (tabletype 'hash');
Multiple hash fields
Read-only
- this might be changed to being writable in the future
CREATE FOREIGN TABLE rft_mhash(
key TEXT,
field TEXT[],
value TEXT[],
expiry INT
) SERVER xxx
OPTIONS (tabletype 'mhash');
List
Read-Write
CREATE FOREIGN TABLE rft_list(
key TEXT,
value TEXT,
"index" INT,
expiry INT
) SERVER xxx
OPTIONS (tabletype 'list');
- INSERT is the equivalent of RPUSH (add to the tail of the list)
- UPDATE uses LSET to change the value of an item at the index
- DELETE of
index = 0
uses LPOP (remove first item), otherwise redis_fdw will rename the item at the specified index to a searchable string and delete that item. - DELETE of
value = x
usesLREM key 1 value
, ie deletes only the first left instance of the value. If you specify bothvalue
andindex
, then onlyvalue
is used for deletion (index
is ignored)
Set
Read-Write
CREATE FOREIGN TABLE rft_set(
key TEXT,
member TEXT,
expiry INT
) SERVER xxx
OPTIONS (tabletype 'set');
ZSet
Read-Write
CREATE FOREIGN TABLE rft_zset(
key TEXT,
member TEXT,
score INT,
"index" INT,
expiry INT
) SERVER localredis
OPTIONS (tabletype 'zset');
TTL
Read-Write
Get or set the time to live (in seconds) of a key.
- Set expiry = 0 to make the key persistent.
- DELETE will delete the entire key.
CREATE FOREIGN TABLE rft_ttl(
key TEXT,
expiry INT
) SERVER localredis
OPTIONS (tabletype 'ttl');
Publish
Read-Write
- SELECT performs
PUBSUB NUMSUB channel
to fetch the number of subscribers to the channel - INSERT performs
PUBLISH channel message
and places the number of subscribers who received the message on the RETURNING value of len
CREATE FOREIGN TABLE rft_pub(
channel TEXT,
message TEXT,
len INT
) SERVER localredis
OPTIONS (tabletype 'publish');
Len
Read-only
Retrieve the length of a key or the database (if SELECT does not have WHERE key = xxxx).
CREATE FOREIGN TABLE rft_len(
key TEXT,
tabletype TEXT,
len INT,
expiry INT
) SERVER localredis
OPTIONS (tabletype 'len');
Keys
Read-only
Retrieve all keys for the database. Use with special care and only on small key-spaces since it requires fetching all keys from redis which returns all keys as a single array which can hurt memory use significantly.
CREATE FOREIGN TABLE rft_keys(
key TEXT
) SERVER localredis
OPTIONS (tabletype 'keys');
Usage
redis_fdw is able to parse simple WHERE clauses containing the following operators.
Text Equality
- column = constant/parameter
- e.g.
WHERE key = "foo"
Index and Score integer comparisons
- index/score column < | <= | = | >= | > constant/parameter
- e.g.
WHERE index > 4 AND index < 9
Array contains
- array-column @> array-constant/array-parameter
- e.g.
WHERE field @> '{"a","b","c"}'
Refer to the test sql script for real examples.
Limitations
redis_fdw can handle most queries ok if the WHERE clause conditions are passed to it from PostgreSQL.
The module can't yet handle JOINs if the key is not provided in WHERE clause as a constant or parameter. The reasons are complex and have to do with PostgreSQL's query planner which doesn't (and may not be able to) provide the WHERE conditions to redis_fdw.
For example, the following will fail because r.key = u.key
isn't provided to redis_fdw.
SELECT u.*, r.value, r.expiry
FROM pgsql_users u
JOIN rft_sessions r ON r.key = u.key
WHERE r.key = u.key;
The workaround is something like where r.key = (<subquery>)
:
WITH u AS (SELECT * FROM pgsql_users WHERE userid = 1)
SELECT u.*, r.value, r.expiry
FROM rft_sessions r, u
WHERE r.key = (SELECT u.key FROM u);
PubSub
Only PUBSUB NUMSUB channel is implemented.
SUB clients will need to connect to Redis directly.
License:
Copyright 2015 Leon Dang, Nahanni Systems Inc. BSD-style license; see LICENSE.
Support
(this wiki section to be improved upon)
If you encounter an issue with the module, here are some ways that can assist in identifying root causes:
- If PostgreSQL crashes because of the module:
- enable core dumps on your system
- start postgres with
ulimit -c unlimited
to enable core dumps - compile this module with gdb enabled (it is enabled by default in the Makefile with the -g switch to PG_CPPFLAGS)
gdb <pgsql-prefix>/bin/postgresql <path-to-core>/<corefile>
- get gdb back trace so with
gdb> bt
- submit the backtrace
- Enable debug output from the module
make DEBUG=1
and reinstall (make install
)- configure postgres to write debug messages with log level INFO to the logs
- obtain and submit the log entries that were printed by this module
Authors
Leon Dang