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;