这篇文章主要介绍了Postgresql分布式插件plproxy的使用详解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧。

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;

 

补充：PostgreSQL 水平分库——plproxy

1、PL/Proxy安装

1、1 编译安装

tar -zxvf plproxy-2.7.tar.gz
cd plproxy-2.7
source /home/postgres/.bashrc
make
make install

 

1、2 创建pl/proxy扩展

itm_pg@pgs-> psql
psql (10.3)
Type "help" for help.
postgres=# create database proxy;
CREATE DATABASE
postgres=# \c proxy