Thread: Request for review of new redis-fdw module

Hi

I've implemented a completely new Redis FDW module which has little to do with github.com/pg-redis-fdw/redis_fdw; although I did take some inspiration from in on how the tables were to be designed but most I got from looking at the oracle-fdw.

My redis-fdw implementation supports read and write to the Redis backend, so you can do insert, update, and delete. e.g. you can define a hash table as:

table rhash (

  key text,

  field text,

  value test,
  expiry int

) server redserver options(tabletype 'hash');

and do:
  select * from rhash where key = 'foo' and field = 'bar';

  update rhash set value = 'bahbah' where key = 'foo' and field = 'bar';
  delete from rhash where key = 'foo' and field = 'bar';

I need someone experienced with postgresql's internals for FDWs to review my code and let me know if I've done something wrong or where I can optimize it a little more. Once it has been reviewed, I'll post it up and announce it on my github account for public consumption. But for now I want to make sure that the code is correct.

There are some functions (Explain* and costs) which I know I haven't implemented properly so assistance with that is also appreciated.

Please email me if you can assist with reviewing.

thanks
Leon

Viessmann Faulquemont SAS
Avenue André Gouy
57380 FAULQUEMONT
www.viessmann.fr

Please use this http://www.postgresql.org/community/lists/subscribe/ link.

On 28.01.2015 12:01, Quirin Hamp wrote:
> Please unsubscribe me from mailing list. Thank you!
>
> Bien cordialement, / Mit freundlichen Grüßen / Yours sincerely,
>
> Quirin HAMP
> _____________________________
> Viessmann Faulquemont S.A.S
> Responsable développement systèmes thermiques
> Avenue André GOUY
> F 57380 Faulquemont
>
> Tel.: +33 (0)3.87.90.64.12
> Mob.: +33 (0)6.88.54.57.08
> e-mail: HamQ@viessmann.com
> web: www.viessmann.fr
>
>
>
> Von:        Leon Dang <ldang@nahannisys.com>
> An:        pgsql-general@postgresql.org
> Datum:        28.01.2015 09:25
> Betreff:        Re: [GENERAL] Request for review of new redis-fdw module
> Gesendet von:        pgsql-general-owner@postgresql.org
> ------------------------------------------------------------------------
>
>
>
> The code has been posted to _https://github.com/nahanni/rw_redis_fdw_
>
> Anyone interested can test it out or review it.
>
> cheers
> Leon
>
>
>
> Leon Dang wrote on 01/24/2015 04:17 PM:
> Hi
>
> I've implemented a completely new Redis FDW module which has little to
> do with _github.com/pg-redis-fdw/redis_fdw_
> <http://github.com/pg-redis-fdw/redis_fdw>; although I did take some
> inspiration from in on how the tables were to be designed but most I
> got from looking at the oracle-fdw.
>
> My redis-fdw implementation supports read and write to the Redis
> backend, so you can do insert, update, and delete. e.g. you can define
> a hash table as:
>
> table rhash (
>   key text,
>   field text,
>   value test,
>   expiry int
> ) server redserver options(tabletype 'hash');
>
> and do:
>   select * from rhash where key = 'foo' and field = 'bar';
>   update rhash set value = 'bahbah' where key = 'foo' and field = 'bar';
>   delete from rhash where key = 'foo' and field = 'bar';
>
> I need someone experienced with postgresql's internals for FDWs to
> review my code and let me know if I've done something wrong or where I
> can optimize it a little more. Once it has been reviewed, I'll post it
> up and announce it on my github account for public consumption. But
> for now I want to make sure that the code is correct.
>
> There are some functions (Explain* and costs) which I know I haven't
> implemented properly so assistance with that is also appreciated.
>
> Please email me if you can assist with reviewing.
>
> thanks
> Leon
>
>
> ------------------------------------------------------------------------
>
>
>
>  *Viessmann - climate of innovation <http://www.viessmann.fr>*
>
> *Viessmann Faulquemont SAS
> *Avenue André Gouy
> 57380 FAULQUEMONT
> www.viessmann.fr
>

Leon Dang <ldang@nahannisys.com> wrote:

> Hi
>
> I've implemented a completely new Redis FDW module which has little to do with
> github.com/pg-redis-fdw/redis_fdw; although I did take some inspiration from in
> on how the tables were to be designed but most I got from looking at the
> oracle-fdw.
>
> My redis-fdw implementation supports read and write to the Redis backend, so
> you can do insert, update, and delete. e.g. you can define a hash table as:

is it possible to write multiple row's into the redis? something like

insert into foreign_redis_table select * from big_table

?

Anyway, thx, compiled and installed (just for fun, i'm not familiar with
redis, and i'm not a coder)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andreas Kretschmer wrote on 01/28/2015 03:36 AM:
> > I've implemented a completely new Redis FDW module which has little to do with
> > github.com/pg-redis-fdw/redis_fdw; although I did take some inspiration from in
> > on how the tables were to be designed but most I got from looking at the
> > oracle-fdw.
> >
> > My redis-fdw implementation supports read and write to the Redis backend, so
> > you can do insert, update, and delete. e.g. you can define a hash table as:
>
> is it possible to write multiple row's into the redis? something like
>
> insert into foreign_redis_table select * from big_table
>

Thanks for pointing this out. I had a small bug which didn't let it
succeed, but now it's been fixed and committed; I've also added a
bulkdata.sql test script in the code to show an example.


> Anyway, thx, compiled and installed (just for fun, i'm not familiar with
> redis, and i'm not a coder)
>

Redis is great for session management as it allows you to set an expiry
for each key. So by using redis_fdw, you don't need to do multiple
queries to determine of the session is still valid. e.g.:

-- use a string (key-value) dataset for user sessions
CREATE FOREIGN TABLE rsessions(
     sessid TEXT,
     value  TEXT,
     expiry INT
) SERVER localredis
   OPTIONS (tabletype 'string');
ALTER FOREIGN TABLE rsessions ALTER COLUMN sessid OPTIONS (ADD param 'key');

-- a user table in postgres, can contain a whole bunch of other fields.
CREATE TEMP TABLE users (
     userid   INT,
     username TEXT,
     sessid   TEXT
);


--
-- get user's details at the same time as determining if they're session
is still valid
--
WITH u AS (SELECT * FROM users WHERE username = 'foo')
SELECT u.*, r.value, r.expiry
FROM rsessions r, u
WHERE r.sessid = (SELECT u.sessid FROM u);



If the user's session is still valid then a row will be returned (Redis
automatically destroys the key on expiry).


--
-- to reset the expiry timeout for the user
--
UPDATE rsessions SET expiry = 40 WHERE sessid = $1;



Leon

Great. Congratulations.

How big is the latency in the FDW? This opens up new possibilities using redis. Very cool.

> I've implemented a completely new Redis FDW module which has little to do with
> github.com/pg-redis-fdw/redis_fdw; although I did take some inspiration from in
> on how the tables were to be designed but most I got from looking at the
> oracle-fdw.
>
> My redis-fdw implementation supports read and write to the Redis backend, so
> you can do insert, update, and delete. e.g. you can define a hash table as:

is it possible to write multiple row's into the redis? something like

insert into foreign_redis_table select * from big_table

Thanks for pointing this out. I had a small bug which didn't let it succeed, but now it's been fixed and committed; I've also added a bulkdata.sql test script in the code to show an example.

Anyway, thx, compiled and installed (just for fun, i'm not familiar with
redis, and i'm not a coder)

Redis is great for session management as it allows you to set an expiry for each key. So by using redis_fdw, you don't need to do multiple queries to determine of the session is still valid. e.g.:

-- use a string (key-value) dataset for user sessions
CREATE FOREIGN TABLE rsessions(
    sessid TEXT,
    value  TEXT,
    expiry INT
) SERVER localredis
  OPTIONS (tabletype 'string');
ALTER FOREIGN TABLE rsessions ALTER COLUMN sessid OPTIONS (ADD param 'key');

-- a user table in postgres, can contain a whole bunch of other fields.
CREATE TEMP TABLE users (
    userid   INT,
    username TEXT,
    sessid   TEXT
);


--
-- get user's details at the same time as determining if they're session is still valid
--
WITH u AS (SELECT * FROM users WHERE username = 'foo')
SELECT u.*, r.value, r.expiry
FROM rsessions r, u
WHERE r.sessid = (SELECT u.sessid FROM u);



If the user's session is still valid then a row will be returned (Redis automatically destroys the key on expiry).


--
-- to reset the expiry timeout for the user
--
UPDATE rsessions SET expiry = 40 WHERE sessid = $1;



Leon

Leon Dang <ldang@nahannisys.com> wrote:

>
> Andreas Kretschmer wrote on 01/28/2015 03:36 AM:
>> > I've implemented a completely new Redis FDW module which has little to do with
>> > github.com/pg-redis-fdw/redis_fdw; although I did take some inspiration from in
>> > on how the tables were to be designed but most I got from looking at the
>> > oracle-fdw.
>> >
>> > My redis-fdw implementation supports read and write to the Redis backend, so
>> > you can do insert, update, and delete. e.g. you can define a hash table as:
>>
>> is it possible to write multiple row's into the redis? something like
>>
>> insert into foreign_redis_table select * from big_table
>>
>
> Thanks for pointing this out. I had a small bug which didn't let it
> succeed, but now it's been fixed and committed; I've also added a
> bulkdata.sql test script in the code to show an example.

Yeah, it's working now:

test=*# insert into test_redis select 'key'||s::text, 'value_xxx'||s::text from generate_Series(1,10) s;
INSERT 0 10
Time: 1,041 ms
test=*# select * from test_redis where key = 'key7';
 key  |   value
------+------------
 key7 | value_xxx7
(1 row)


>
>> Anyway, thx, compiled and installed (just for fun, i'm not familiar with
>> redis, and i'm not a coder)
>>
>
> Redis is great for session management as it allows you to set an expiry
> for each key. So by using redis_fdw, you don't need to do multiple
> queries to determine of the session is still valid. e.g.:

Yeah, or as a fast cache, and you can set or update the redis-cache via
trigger:

test=# create table redis_source (key text, val text);
CREATE TABLE
Time: 4,365 ms
test=*# create or replace function redis_update() returns trigger as
$$begin insert into test_redis values (new.key, new.val); return new;
end; $$language plpgsql;
CREATE FUNCTION
Time: 0,532 ms
test=*# create trigger trg_redis after insert or update on redis_source
for each row execute procedure redis_update();
CREATE TRIGGER
Time: 0,439 ms
test=*# insert into redis_source values ('new_key','new_value');
INSERT 0 1
Time: 0,809 ms
test=*# select * from test_redis where key = 'new_key';
   key   |   value
---------+-----------
 new_key | new_value
(1 row)

Time: 0,615 ms
test=*# update redis_source set val = 'hot new val' where key =
'new_key';
UPDATE 1
Time: 0,630 ms
test=*# select * from test_redis where key = 'new_key';
   key   |    value
---------+-------------
 new_key | hot new val
(1 row)


Nice!




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Viessmann Faulquemont SAS
Avenue André Gouy
57380 FAULQUEMONT
www.viessmann.fr