pg-redis-fdw/redis_fdw

A PostgreSQL foreign data wrapper for Redis

redis_fdw's Issues

No license

Please add a license to License file. Example for PostgreSQL license for a FDW is here, about other see free-software licenses.

Use SCAN rather than KEYS

There is a cursor like implementation of KEYS, named SCAN, this might allow for better performances, see: http://redis.io/commands/scan

Cache expire?

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

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.

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);

cursor id copy not done in sufficiently long lived context

We need to set up a memory context to hold the cursor id that lives as long as the scan. This is the cause of a privately reported bug.

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

ERROR: failed to get the database size: 19081328

use redis4.0.2 and postgres10.0 , after create server/user mapping/foreign table (key,val) , execute sql statement 'select * from redis_db0 limit 1' ，then error occurred；

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?

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');

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}');

?..

Add support for writing tables

This is now supported by PostgreSQL as of version 9.3.

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.

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?

What about redis_fdw for Windows?

Does exist redis_fdw for PostgreSQL x64 windows ?
Or how I can build it in VS2013?

Thnx

Add singleton_key table option

see http://adpgtech.blogspot.com/2013/05/postgresql-tables-for-single-redis.html

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

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

Redis Clustering support

Hello, I had a need to use fdw with a redis cluster. I hacked up this fdw that has basic feature parity with redis_fdw. Is this something you guys would like to take over and maintain? https://github.com/jeffreydwalter/redis_cluster_fdw

Cheers,
Jeff

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
......

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:-

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:-

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

Thanks.

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.

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);

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

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.

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.