Menu
▾
▴
postgres-xc-general
|
From: Aris S. <ari...@gm...> - 2012-07-26 06:09:39
|
Hi All, Is "Additional Supplied Modules" supported in XC cluster (hstore, pgcrypto, etc..)? -thanks |
|
From: Michael P. <mic...@gm...> - 2012-07-26 06:23:42
|
On Thu, Jul 26, 2012 at 3:09 PM, Aris Setyawan <ari...@gm...> wrote: > Hi All, > > Is "Additional Supplied Modules" supported in XC cluster (hstore, > pgcrypto, etc..)? > Normally yes. I tested some of them and they should work. There might be exceptions though. -- Michael Paquier http://michael.otacoo.com |
|
From: Koichi S. <koi...@gm...> - 2012-07-26 06:37:24
|
Yes, namely yes. May need to share the test. Here's my test of hstore. It looks to run. The example was from http://d.hatena.ne.jp/rudi/20120330/1333120115 (originally in Japanese). Of course, "products" table is distributed by hash on id. Regards; --- Koichi --------------8<-------------------------8<----------------- [koichi@willey:pgxc]$ psql -p 20004 postgres psql (PGXC 1.1devel, based on PG 9.2devel) Type "help" for help. postgres=# create extension hstore; WARNING: => is deprecated as an operator name DETAIL: This name may be disallowed altogether in future versions of PostgreSQL. CREATE EXTENSION postgres=# CREATE TABLE products (id serial PRIMARY KEY, name varchar, attrubutes hstore); NOTICE: CREATE TABLE will create implicit sequence "products_id_seq" for serial column "products.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "products_pkey" for table "products" CREATE TABLE postgres=# INSERT INTO products (name, attributes) VALUES postgres-# ('Geel Love: A Novel', postgres(# 'author => "Katherine Dunn", postgres'# pages => 368, postgres'# category => fiction' postgres(# ); ERROR: column "attributes" of relation "products" does not exist LINE 1: INSERT INTO products (name, attributes) VALUES ^ postgres=# drop table products postgres-# ; DROP TABLE postgres=# CREATE TABLE products ( postgres(# id serial PRIMARY KEY, postgres(# name varchar, postgres(# attributes hstore postgres(# ); NOTICE: CREATE TABLE will create implicit sequence "products_id_seq" for serial column "products.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "products_pkey" for table "products" CREATE TABLE postgres=# INSERT INTO products (name, attributes) VALUES ( postgres(# 'Geek Love: A Novel', postgres(# 'author => "Katherine Dunn", postgres'# pages => 368, postgres'# category => fiction' postgres(# ); INSERT 0 1 postgres=# SELECT name as device postgres-# FROM products postgres-# WHERE attributes->'category' = 'fiction'; device -------------------- Geek Love: A Novel (1 row) postgres=# SELECT name, attributes->'pages' postgres-# FROM products postgres-# WHERE attributes ? 'pages'; name | ?column? --------------------+---------- Geek Love: A Novel | 368 (1 row) postgres=# CREATE INDEX product_manufacturer postgres-# ON products ((products.attributes->'manufacturer')); CREATE INDEX postgres=# --------->8--------------------------->8------------------------- 2012/7/26 Michael Paquier <mic...@gm...>: > > > On Thu, Jul 26, 2012 at 3:09 PM, Aris Setyawan <ari...@gm...> wrote: >> >> Hi All, >> >> Is "Additional Supplied Modules" supported in XC cluster (hstore, >> pgcrypto, etc..)? > > Normally yes. I tested some of them and they should work. There might be > exceptions though. > -- > Michael Paquier > http://michael.otacoo.com > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Postgres-xc-general mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-general > |
|
From: Michael P. <mic...@gm...> - 2012-07-26 06:34:05
|
On Thu, Jul 26, 2012 at 3:30 PM, Aris Setyawan <ari...@gm...> wrote: > > Normally yes. I tested some of them and they should work. There might be > exceptions though. > > Can I rely on the current 1.0 XC's documentation to find out if one > module is supported? > Yes you can. They may be some mistakes in the documentation as there are not so many people maintaining and correcting it. So if you find anything that does not correspond, feel free to report that on this thread. Thanks, -- Michael Paquier http://michael.otacoo.com |
|
From: Koichi S. <koi...@gm...> - 2012-07-26 06:56:08
|
pgcrypto looks to work fine. Obviously, it is not recommended to
encrypt distribution key, where we don't have good optimization.
Here's my result.
Enjoy;
----
Koichi
----8<------------------------8<------------
postgres=# create extension pgcrypto;
CREATE EXTENSION
postgres=# create table test (id int, name bytea);
CREATE TABLE
postgres=# insert into test select 1, encrypt(convert_to('Suzuki',
'UTF8'), 'pass'::bytea, 'aes');
INSERT 0 1
postgres=# insert into test select 2, pgp_sym_encrypt('Sato', 'pass');
INSERT 0 1
postgres=# insert into test select 3, pgp_sym_encrypt('Sato', 'pass');
INSERT 0 1
postgres=# select * from test;
id |
name
----+------------------------------------------------------------------------------------------------------------------------------------------------
1 | \x42a2c9e81526133e031336b8a09a3ae8
2 | \xc30d0407030285360772296ea94e78d2350126b86572a89a4f054fb95eb0b8ab86b214e66571469460a469b740f26c3465e19f17500703e75e06378bc4b75b37f053befad44a
3 | \xc30d040703024a9931a54cde6b6f60d235011bdbf887c8a0cb2e122ed034101ef64b3d5fb7d25cbff91dd82661de665651d92c0c1ee7d6672139ef7e59b767bb90612ab71bef
(3 rows)
postgres=# SELECT id, convert_from(decrypt(name, 'pass'::bytea,
'aes'), 'UTF8') FROM test where id = 1;
id | convert_from
----+--------------
1 | Suzuki
(1 row)
postgres=# SELECT id, pgp_sym_decrypt(name,'pass') FROM test WHERE id IN (2,3);
id | pgp_sym_decrypt
----+-----------------
3 | Sato
2 | Sato
(2 rows)
postgres=# create table test1 (id varchar, name bytea);
CREATE TABLE
postgres=# insert into test1 values (pgp_sym_encrypt('1', 'pass'),
pgp_sym_encrypt('Suzuki', 'pass'));
INSERT 0 1
postgres=# select * from test1;
id
|
name
------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------
\xc30d0407030233ffc6b915c7908d62d23201980e846bcfbf772c43ae7bb5c9476da716fe80dd2149e81aaa2353767bb5402af7fa54eb8b498ea21de58a58e8ca697229
| \xc30d040703020169a4462ef5f63d65d2370108b2e0dea578142f63e416989a95e19663605c7f035b54074d6f190e704e7bf75b651c6dfa6091667a8242fcd91ae60384bf0ec0c4c6
(1 row)
postgres=# select pgp_sym_decrypt(id::bytea, 'pass'),
pgp_sym_decrypt(name::bytea, 'pass') from test1;
pgp_sym_decrypt | pgp_sym_decrypt
-----------------+-----------------
1 | Suzuki
(1 row)
postgres=# select pgp_sym_decrypt(id::bytea, 'pass'),
pgp_sym_decrypt(name::bytea, 'pass') from test1 where
pgp_sym_decrypt(id::bytea, 'pass') = '1';
pgp_sym_decrypt | pgp_sym_decrypt
-----------------+-----------------
1 | Suzuki
(1 row)
postgres=#
---->8------------------------>8------------
2012/7/26 Aris Setyawan <ari...@gm...>:
> Hi All,
>
> Is "Additional Supplied Modules" supported in XC cluster (hstore,
> pgcrypto, etc..)?
>
> -thanks
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
|
Thanks for helping keep SourceForge clean.
X