Postgresql分布式插件plproxy

作者：高铭杰
邮箱：jackgo73@outlook.com
日期：2017年7月4日

Simple remote function call

节点61/62(datanode)

CREATE TABLE users (username text,email text);
insert into users values ('user0','user0@gmail.com');
insert into users values ('user1','user1@gmail.com');
insert into users values ('user2','user2@gmail.com');

节点60(proxy)

create or replace extension plproxy;

CREATE FUNCTION get_user_email(i_username text) RETURNS SETOF text AS $$ CONNECT 'host=localhost port=9461 dbname=postgres connect_timeout=10';
    SELECT email FROM users WHERE username = $1;
$$ LANGUAGE plproxy;

SELECT * from get_user_email('user0');

Configuring Pl/Proxy clusters with SQL/MED

节点60(proxy)

CREATE FOREIGN DATA WRAPPER plproxy;

CREATE SERVER usercluster FOREIGN DATA WRAPPER plproxy OPTIONS (connection_lifetime '1800',p0 'host=localhost port=9461 dbname=postgres connect_timeout=10',p1 'host=localhost port=9462 dbname=postgres connect_timeout=10' );

CREATE USER MAPPING FOR PUBLIC SERVER usercluster;

Partitioned remote call

节点60(proxy)

CREATE OR REPLACE FUNCTION insert_user(i_username text,i_emailaddress text)
RETURNS integer AS $$
    CLUSTER 'usercluster';
    RUN ON hashtext(i_username);
$$ LANGUAGE plproxy;

节点61/62(datanode)

CREATE OR REPLACE FUNCTION insert_user(i_username text,i_emailaddress text) RETURNS integer AS $$ INSERT INTO users (username,email) VALUES ($1,$2);
       SELECT 1;
$$ LANGUAGE SQL;

Putting it all together

节点60(proxy)

SELECT insert_user('Sven','sven@somewhere.com');
SELECT insert_user('Marko','marko@somewhere.com');
SELECT insert_user('Steve','steve@somewhere.cm');

plproxy–2.7.0.sql

-- handler function
CREATE FUNCTION plproxy_call_handler () RETURNS language_handler AS 'plproxy' LANGUAGE C;

-- validator function
CREATE FUNCTION plproxy_validator (oid) RETURNS void AS 'plproxy' LANGUAGE C;

-- language
CREATE LANGUAGE plproxy HANDLER plproxy_call_handler VALIDATOR plproxy_validator;

-- validator function
CREATE FUNCTION plproxy_fdw_validator (text[],oid) RETURNS boolean AS 'plproxy' LANGUAGE C;

-- foreign data wrapper
CREATE FOREIGN DATA WRAPPER plproxy VALIDATOR plproxy_fdw_validator;