Request for review of new redis-fdw module

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:

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; 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&gt;; 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&gt;*

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Great. Congratulations.

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

On Wed, Jan 28, 2015 at 5:19 PM, 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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
George R. C. Silva
SIGMA Consultoria
----------------------------
http://www.consultoriasigma.com.br/

George Silva wrote on 01/28/2015 11:49 AM:

Great. Congratulations.

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

# explain analyze insert into rstr values ('k4434', '3234234');
QUERY PLAN
--------------------------------------------------------------------------------------------
Insert on rstr (cost=0.00..0.01 rows=1 width=0) (actual
time=0.288..0.288 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.001..0.002 rows=1 loops=1)
Planning time: 0.092 ms
Execution time: 0.582 ms

# explain analyze select * from rstr where key = 'k4434';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Foreign Scan on rstr (cost=10.00..11.00 rows=1 width=68) (actual
time=0.541..0.595 rows=1 loops=1)
Planning time: 0.382 ms
Execution time: 0.642 ms

I did do a benchmark in Golang to see the difference between redis-fdw,
temp-table, and table:

SELECT:
Redis FDW: 240663 ns/op
TEMP TABLE: 1130329 ns/op
TABLE: 764774 ns/op

INSERT:
Redis FDW: 187788 ns/op
TEMP TABLE: 106843 ns/op
TABLE: 3093156 ns/op

redis-fdw is currently unoptimized (no table option caching, etc) so
there is room for improvement. But so far so good.

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�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Please remove me from mailing list. I have deleted my account from pgsql
forum and I still get emails!

Thanks for your understanding.

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: George Silva <georger.silva@gmail.com>
Kopie: Andreas Kretschmer <akretschmer@spamfence.net>,
pgsql-general@postgresql.org
Datum: 28.01.2015 23:02
Betreff: Re: [GENERAL] Request for review of new redis-fdw module
Gesendet von: pgsql-general-owner@postgresql.org

George Silva wrote on 01/28/2015 11:49 AM:
Great. Congratulations.

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

# explain analyze insert into rstr values ('k4434', '3234234');
QUERY
PLAN
--------------------------------------------------------------------------------------------
Insert on rstr (cost=0.00..0.01 rows=1 width=0) (actual
time=0.288..0.288 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002
rows=1 loops=1)
Planning time: 0.092 ms
Execution time: 0.582 ms

# explain analyze select * from rstr where key = 'k4434';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------
Foreign Scan on rstr (cost=10.00..11.00 rows=1 width=68) (actual
time=0.541..0.595 rows=1 loops=1)
Planning time: 0.382 ms
Execution time: 0.642 ms

I did do a benchmark in Golang to see the difference between redis-fdw,
temp-table, and table:

SELECT:
Redis FDW: 240663 ns/op
TEMP TABLE: 1130329 ns/op
TABLE: 764774 ns/op

INSERT:
Redis FDW: 187788 ns/op
TEMP TABLE: 106843 ns/op
TABLE: 3093156 ns/op

redis-fdw is currently unoptimized (no table option caching, etc) so there
is room for improvement. But so far so good.

Leon

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
_________________________________________________________________________________

Viessmann Faulquemont SAS

Avenue André Gouy
57380 FAULQUEMONT
www.viessmann.fr

On Thu, Jan 29, 2015 at 9:36 PM, Quirin Hamp <HamQ@viessmann.com> wrote:

Please remove me from mailing list. I have deleted my account from pgsql
forum and I still get emails!

Here is an entry point to DIY:
http://www.postgresql.org/community/lists/subscribe/
--
Michael

Redis PUBLISH is now supported so you can send messages to subscribers on a
channel.

Leon

On Wed, Jan 28, 2015 at 12:23 AM, Leon Dang <ldang@nahannisys.com> wrote: