Giter Site home page Giter Site logo

redis_fdw's Introduction

Redis FDW for PostgreSQL 9.1+

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for the Redis key/value database: http://redis.io/

This code was originally experimental, and largely intended as a pet project for Dave to experiment with and learn about FDWs in PostgreSQL. It has now been extended for production use by Andrew.

By all means use it, but do so entirely at your own risk! You have been warned!

Building

To build the code, you need the hiredis C interface to Redis installed on your system. You can checkout the hiredis from https://github.com/redis/hiredis or it might be available for your OS as it is for Fedora, for example.

Once that's done, the extension can be built with:

PATH=/usr/local/pgsql91/bin/:$PATH make USE_PGXS=1
sudo PATH=/usr/local/pgsql91/bin/:$PATH make USE_PGXS=1 install

(assuming you have PostgreSQL 9.1 in /usr/local/pgsql91).

Make necessary changes for 9.2 and later.

You will need to have the right branch checked out to match the PostgreSQL release you are building against, as the FDW API has changed from release to release.

Dave has tested the original on Mac OS X 10.6 only, and Andrew on Fedora and Suse. Other *nix's should also work. Neither of us have tested on Windows, but the code should be good on MinGW.

Limitations

  • There's no such thing as a cursor in Redis in the SQL sense, nor MVCC, which leaves us with no way to atomically query the database for the available keys and then fetch each value. So, we get a list of keys to begin with, and then fetch whatever records still exist as we build the tuples.

  • We can only push down a single qual to Redis, which must use the TEXTEQ operator, and must be on the 'key' column.

  • There is no support for non-scalar datatypes in Redis such as lists, for PostgreSQL 9.1. There is such support for later releases.

  • Redis has acquired cursors as of Release 2.8. This is used in all the mainline branches from REL9_2_STABLE on, for operations which would otherwise either scan the entire Redis database in a single sweep, or scan a single, possible large, keyset in a single sweep. Redis Releases prior to 2.8 are maintained on the REL9_x_STABLE_pre2.8 branches.

  • Redis cursors have some significant limitations. The Redis docs say:

    A given element may be returned multiple times. It is up to the application to handle the case of duplicated elements, for example only using the returned elements in order to perform operations that are safe when re-applied multiple times.

    The FDW makes no attempt to detect this situation. Users should be aware of the possibility.

Usage

The following parameters can be set on a Redis foreign server:

address: The address or hostname of the Redis server. Default: 127.0.0.1

port: The port number on which the Redis server is listening. Default: 6379

The following parameters can be set on a Redis foreign table:

database: The numeric ID of the Redis database to query. Default: 0

(9.2 and later) tabletype: can be 'hash', 'list', 'set' or 'zset' Default: none, meaning only look at scalar values.

(9.2 and later) tablekeyprefix: only get items whose names start with the prefix Default: none

(9.2 and later) tablekeyset: fetch item names from the named set Default: none

(9.2 and later) singleton_key: get all the values in the table from a single named object. Default: none, meaning don't just use a single object.

You can only have one of tablekeyset and tablekeyprefix, and if you use singleton_key you can't have either.

Structured items are returned as array text, or, if the value column is a text array as an array of values. In the case of hash objects this array is an array of key, value, key, value ...

Singleton key tables are returned as rows with a single column of text in the case of lists sets and scalars, rows with key and value text columns for hashes, and rows with a value text columns and an optional numeric score column for zsets.

The following parameter can be set on a user mapping for a Redis foreign server:

password: The password to authenticate to the Redis server with. Default:

Insert, Update and Delete

PostgreSQL acquired support for modifying foreign tables in release 9.3, and now the Redis Foreign Data Wrapper supports these too, for 9.3 and later PostgreSQL releases. There are a few restriction on this:

  • only INSERT works for singleton key list tables, due to limitations in the Redis API for lists.
  • INSERT and UPDATE only work for singleton key ZSET tables if they have the priority column
  • non-singleton non-scalar tables must have an array type for the second column

Example

CREATE EXTENSION redis_fdw;

CREATE SERVER redis_server
	FOREIGN DATA WRAPPER redis_fdw
	OPTIONS (address '127.0.0.1', port '6379');

CREATE FOREIGN TABLE redis_db0 (key text, val text)
	SERVER redis_server
	OPTIONS (database '0');

CREATE USER MAPPING FOR PUBLIC
	SERVER redis_server
	OPTIONS (password 'secret');

CREATE FOREIGN TABLE myredishash (key text, val text[])
	SERVER redis_server
	OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'mytable:');

INSERT INTO myredishash (key, val)
   VALUES ('mytable:r1','{prop1,val1,prop2,val2}');

UPDATE myredishash
    SET val = '{prop3,val3,prop4,val4}'
    WHERE key = 'mytable:r1';

DELETE from myredishash
    WHERE key = 'mytable:r1';

CREATE FOREIGN TABLE myredis_s_hash (key text, val text)
	SERVER redis_server
	OPTIONS (database '0', tabletype 'hash',  singleton_key 'mytable');

INSERT INTO myredis_s_hash (key, val)
   VALUES ('prop1','val1'),('prop2','val2');

UPDATE myredis_s_hash
    SET val = 'val23'
    WHERE key = 'prop1';

DELETE from myredis_s_hash
    WHERE key = 'prop2';

Testing

The tests for 9.2 and later assume that you have access to a redis server on the localmachine with no password, and uses database 15, which must be empty, and that the redis-cli program is in the PATH when it is run. The test script checks that the database is empty before it tries to populate it, and it cleans up afterwards.

Authors

Dave Page [email protected]

Andrew Dunstan [email protected]

redis_fdw's People

Contributors

adunstan avatar dpage avatar engobi avatar hasegeli avatar movermeyer avatar nodecarter avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

redis_fdw's Issues

Postgresql 11.3 crashed on CREATE EXTENSION redis_fdw compiled with DEBUG

I've faced some strange behavior of redis_fdw inside postgres transaction so I decided to look deeper and make redis_fdw module with '#define DEBUG'

Then I run
=# drop extension redis_fdw cascade;
NOTICE: drop cascades to 5 other objects
DETAIL: drop cascades to server redis_m0
drop cascades to user mapping for qp_kg_user on server redis_m0
drop cascades to user mapping for public on server redis_m0
drop cascades to foreign table redis_regions_future_reserve
drop cascades to foreign table redis_regions_reserve
DROP EXTENSION

And try to create debugger version

qp_kg=# create extension redis_fdw;

After few minutes of waiting client was aborted connection with message

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Database switched to recovery mode

2019-09-09 14:20:47 +06 [91660]: [16-1]:LOG: server process (PID 94608) was terminated by signal 11: Segmentation fault
2019-09-09 14:20:47 +06 [91660]: [17-1]:DETAIL: Failed process was running: create extension redis_fdw;
2019-09-09 14:20:47 +06 [91660]: [18-1]:LOG: terminating any other active server processes

2019-09-09 14:22:17 +06 [91660]: [21-1]:LOG: received fast shutdown request
2019-09-09 14:22:18 +06 [91660]: [22-1]:LOG: abnormal database system shutdown
2019-09-09 14:22:18 +06 [91660]: [23-1]:LOG: database system is shut down

2019-09-09 14:22:21 +06 [87363]: [1-1]:LOG: database system was interrupted while in recovery at 2019-09-09 14:21:00 +06
2019-09-09 14:22:21 +06 [87363]: [2-1]:HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery.

make: *** No rule to make target `make'. Stop

make: ** No rule to make target `make'. Stop*
I encountered the above problem while building the path using the following commands.
PATH=/usr/share/postgresql/9.4/:$PATH make USE_PGXS=1 make

I also tried the following commands but I faced the same error.
PATH=/usr/lib/postgresql/9.4/bin/:$PATH make USE_PGXS=1 make

Suggestion: use Lua to get data for a Redis set

If you use sets of keys to create your "tables" in Redis the client needs to send multiple requests to Redis, you can also use Lua to collect all the key/values of a set in one request.

Now I don't know if that fits the Foreign Data Wrapper model.

Does it support postgresql 10.0 or later release?

I installed postgresq1 10.0 and redis-3.2.5. After executing "make USE_PGXS=1" in folder "redis_fdw", problems occurred like
redis_fdw.c:103: 错误:expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘attribute’ before ‘*’ token
......

question about connection pooling for the fdw

I'm wondering if there is connecting pooling for redis fdw.

Also, I'd like to be able to run an EVAL call to redis with arguments (to get back a json in order to insert into a table), is there anyway to do this currently?

Make use of HINCRBY?

Is there any way to use this to access HINCRBY? I can't find anything in the docs or tests that would reflect it.

Cache expire?

Is there a way to set keys to expire in redis via this FDW?

Bug in tablekeyprefix check

You have a bug in line 998 of redis_fdw.c

else if (festate->keyprefix)
        {
            if (strncmp(qual_value, festate->keyprefix, 
                        strlen(festate->keyprefix)) == 0)
                festate->row = -1;
        }

strncmp must be !=0

With this bug foreign table with tablekeyprefix search for key equality doesn't work.

GCC 10.2.1 + PostgreSQL 13 warnings

redis_fdw.c: In function ‘redis_fdw_handler’:
redis_fdw.c:294:38: warning: assignment to ‘AddForeignUpdateTargets_function’ {aka ‘void (*)(Query *, RangeTblEntry *, struct RelationData *)’} from incompatible pointer type ‘void (*)(PlannerInfo *, Index,  RangeTblEntry *, struct RelationData *)’ {aka ‘void (*)(PlannerInfo *, unsigned int,  RangeTblEntry *, struct RelationData *)’} [-Wincompatible-pointer-types]
  294 |  fdwroutine->AddForeignUpdateTargets = redisAddForeignUpdateTargets; /* U D */
      |                                      ^
redis_fdw.c: In function ‘redisPlanForeignModify’:
redis_fdw.c:1801:24: warning: implicit declaration of function ‘get_rel_all_updated_cols’ [-Wimplicit-function-declaration]
 1801 |   Bitmapset  *tmpset = get_rel_all_updated_cols(root, rrel);
      |                        ^~~~~~~~~~~~~~~~~~~~~~~~
redis_fdw.c:1801:24: warning: initialization of ‘Bitmapset *’ from ‘int’ makes pointer from integer without a cast [-Wint-conversion]

redis_fdw.c:294:38: warning: incompatible function pointer types assigning to 'AddForeignUpdateTargets_function' (aka 'void (*)(struct Query *, struct RangeTblEntry *, struct RelationData *)') from 'void (PlannerInfo *, Index, RangeTblEntry *, Relation)' (aka 'void (struct PlannerInfo *, unsigned int, struct RangeTblEntry *, struct RelationData *)') [-Wincompatible-function-pointer-types]
        fdwroutine->AddForeignUpdateTargets = redisAddForeignUpdateTargets; /* U D */
                                            ^ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
redis_fdw.c:1801:24: warning: implicit declaration of function 'get_rel_all_updated_cols' is invalid in C99 [-Wimplicit-function-declaration]
                Bitmapset  *tmpset = get_rel_all_updated_cols(root, rrel);
                                     ^
redis_fdw.c:1801:15: warning: incompatible integer to pointer conversion initializing 'Bitmapset *' (aka 'struct Bitmapset *') with an expression of type 'int' [-Wint-conversion]
                Bitmapset  *tmpset = get_rel_all_updated_cols(root, rrel);
                            ^        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3 warnings generated.

In https://doxygen.postgresql.org/inherit_8h_source.html we have extern Bitmapset *get_rel_all_updated_cols(PlannerInfo *root, RelOptInfo *rel);

json and hstore types instead of just text[] is planned?

just text[] is not useful for hashes, currently I do something like

CREATE FOREIGN TABLE redis0_hash1 (key text, value text[])
    SERVER redis_server
   OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'hash:');
select key, hstore(value) as val from redis0_hash1 where defined(hstore(value),'var1');

Null bytes in redis bit strings

I use SETBIT https://redis.io/commands/SETBIT to efficiently store second-by-second availability data for many devices. These are ultimately strings from redis's point of view.

I am looking to access this from pg and found this extension. I can access the data but if there is a strings of zeroes in the bitstring such that a null byte is returned, the extension views this as a string terminator and I don't get any further data for that key. I've tried creating the foreign table with a bytea type rather than a text type but this makes no difference. I looked in the options documentation for a binary option but didn't see one.

Am I doing something wrong, or is this not currently supported?

question about redis_wrapper as foreign server

Hi, I want to comment redis_wrapper but the issue button is grey :(

I want to use it as a trigger to sync db data into redis as cache.

In such scenario, it will be great if the redis server could be implemented as foreign server instead of connect/disconnect functions.

Is there any plan to support this feature or is there any workaround to achieve it at the moment?

Why redis_fdw can't be used is that we have hashes like account:$id for each row in table account, using redis_fdw needs to create foreign table each time :(

Thanks

Geo as a tabletype

Now that Redis 3.2 is out, it would be cool to be able to update geospacial items based upon a singleton key. I'm thinking there'd have to be serious limitations to this because the table used to insert values based on the geo coordinates wouldn't be the same one that you'd use to read. Although, I think this would be inline w/ the zset functionality since that's what stores the data underlying the geo commands in redis.

Example Experience:

CREATE FOREIGN TABLE myredis_geo (value text, lat double, long double)
    SERVER redis_server
    OPTIONS (database '0', tabletype 'geo', singleton_key 'mygeo');

INSERT INTO myredis_geo (value, lat, long) VALUES ("Palermo", 13.361389, 38.115556);

Redis Secondary Index module

Hi guys.
I'm working on a new redis module for secondary indexing with a minimal query language.

It's not ready yet, but I wanted to share the design of the API and see if you think we can collaborate on integrating this with the FDW somehow. Also, as people involved with PGSQL your input on this would be very valuable.

https://gist.github.com/dvirsky/3ef73143a6d8212f2b50096a8eb68018

A few things not mentioned in the document:

  1. The underlying engine is already sort-of-working and I'll open it up soon. It currently uses a skiplist to model the index, but I'm also considering a b+btree for more cache efficiency.
  2. I haven't dealt with doing this over a distributed redis cluster, but it will require a proxy for sure, which is also a place where I thought integrating with PGSQL might be an interesting direction.
  3. Redis 4.0 with modules should be in RC in about a month or two.

Dvir

Failed to connect to Redis

Hi,
I'm currently facing this issue even if connection exists,not for all transactions but only for few.
Please provide some solution, because of which i'm not able to use the app.

Any solution will be appreciated.

running on rds postgres

Running create extension redis_fdw gives below error. Currently is there any alternative way to create foreign data wrapper for redis and achieve similar goals as this project on RDS ?

Extension "redis_fdw" is not supported by Amazon RDS

Thanks

Performance problem

Is it normal that inserting a single two-column line in a redis_fdw scalar/hash table lasts much longer than in a regular Postgres table (in average 0.6 ms vs 0.06 ms) while ram is nearly free? Almost the same situation with reading. I thought that read/write actions in in-memory database should take less (or equal) time than in Postgres (which is true for direct access) . But benchmarks show opposite!
What can cause such behavior?

My system is Ubuntu 14, Postgres 9.3, Redis 2.8.4
RAM 3GB,
Processor 4 core 2.3 GHz
HDD SATA2 with below average speed

missing single quote?..

in title page:

INSERT INTO myredishash (key, val)
   VALUES ('mytable:r1,'{prop1,val1,prop2,val2}');

should probably be

INSERT INTO myredishash (key, val)
   VALUES ('mytable:r1','{prop1,val1,prop2,val2}');

?..

libhiredis.so.0.13: cannot open shared object file

Hello,

I'm just getting started with integrating Redis and postgresql. I have hiredis - the C client library installed and also the redis fdw. When I try to create the extension though I get the following error:

ERROR: could not load library "/usr/lib/postgresql/9.3/lib/redis_fdw.so": libhiredis.so.0.13: cannot open shared object file: No such file or directory

libhiredis.so.0.13 is currently in the directory /usr/local/lib.

Any help will be greatly appreciated. Thank you

ERROR: invalid byte sequence for encoding "UTF8": 0x86

Hi, I am having a Redis Key "ial:imapapi123" which is actually in MessagePack format. While accessing in redis-cli, it is showing like below:-

2021-07-28 21_22_19-redis-cli

Regarding this I got an information like - read the value from Redis as a Buffer not String and then use a messagepack decoder on it.

But for FDW, I have used below method to create the foreign table:-

CREATE FOREIGN TABLE ial (key text, val text[]) SERVER redis_server OPTIONS (database '0', tabletype 'hash', tablekeyprefix 'ial:');

The foreign table was created , but while selecting getting this error:-

2021-07-29 13_36_41-su

Is there any method to decode from MessagePack format and show the content in foreign table?

Thanks.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.