开发者学堂课程【PostgreSQL快速入门：17PostgreSQL shared nothing分布式用法讲解】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/76
17PostgreSQL shared nothing分布式用法讲解
七、小测试
1、修改 foreign server
修改 foreign server 后，连接会中断，再重新连接。现在的连接为长链接，当重新发起请求时，backend_sta 不会发生改变。课程中当时的 backend_sta 时间为2014-01-11 11:29:49.461541+08。
digoal=# select *from pg_stat_activity;
再次发起请求，查询仍为2014-01-11 11:29:49.461541+08。由于 server 中配置的 lifetime 为1800秒，故在1800秒之内即使不发起任何请求，该连接都不会断开。
输入\q 断开连接，再输入 psql proxy proxy，则显示该链接已断开。这也就是短连接的不足之处。
重新建立连接，再次查询：
digoal=# select *from pg_stat_activity;
显示时间为2014-01-11 11:32:42.461541+08
另外一种会重新建立连接的情况是：当 server 配置发生改变时，也会重新建立连接。
proxy=# alter server cluster srvl options (set p1 'dbname db1 hostaddr=172.16.3.150 port-1921 application_name=abc'
为保存该节点的变化，在另一外节点改变，将 application_name 改为 abc，再次发起请求，显示时间为11:33:26，且所有节点的时间都发生了改变。换言之，当server的配置改变后，相当于所有连接全部断开，再重新建立，这样可以确保 server 在发生变更之后能够立刻生效。
proxy=> select sum(i) *from digoaldy(＇select count<*>from pg_class＇as t<i int8>);
-----
1124
该查询为聚合，即将所有子表加起来。
2、run on 的几种形式测试
proxy# \c db0 digoal
db0=> create table t(id int);
CREATE TABLE
db1=>\c db1
You are now connected to database "dbi" as user "digoal".
db1=> create table t(id int);
CREATE TABLE
db1=>\c db2
You are now connected to database "db2" as user "digoal"
db2=> create table t(id int);
CREATE TABLE
db2=> \c db3
You are now connected to database "db3" as user "digoal".
db3=> create table t(id int);
CREATE TABLE
//在其中创建测试表，并在数据节点里将t表里的数据 truncate（删除）。相当于创建了四个测试表，且每个库都t表作为测试表。
\c db0 digoal
db0=> createor replace function digoalf_test4)returns int as$$
declare
begin
insert into t(id values(1);
return 0;
end:
$$ language plpgsql strict
db1=> createor replace function digoalf_test4)returns int as $$
declare
begin
insert into t(id )values(1));
return 1;
...
//创建实体函数 f_test4，即在数据库插入 value 值。如在0号节点插入1，返回0；在1号节点插入2，返回1；......相当于是执行了插入值再返回值的命令。
接下来创建代理函数，注意只有超级用户才可以创建代理函数。
proxy>\c proxy postgres
create or replace function digoalf_test4)returns int as $$
cluster'cluster_sev1'
//f_test4()与数据节点的函数名一致
run on0;
//指在0号数据节点运行，可以设置为0到3
$$ language plproxy strict
//在调用函数时实际在0号节点执行
proxy> grant execute on function digoal.f_test4() to proxy
//把权限赋予普通用户，执行函数
GRANT
proxy=# \c proxy proxy
You are now connected to database "proxy" as user "proxy".
proxy=> select* from digoal.f_test4();
0
-------
(1 row)
//返回0
在另外的会话中修改执行的节点：
\c db0
select * from t
在0号节点可以看到些相关记录，其他节点则看不到，因为运行节点为0号节点。
proxy# create or replace function digoal.f_test4()returns int as $$
cluster 'cluster_sevl';
run on 4;
$$ language plproxy strict;
CREATE FUNCTION
proxy=# \c proxy proxy
You are now connected to database "proxy" as user "proxy".
proxy=> select" from digoal.f_test4();
ERROR: PL/Proxy function digoal.f_test4(0): part number out of range
//若将“0”改为1~3之外的其他数字，即超出了数据库的范围，系统报错。即此处运行的节点是必须落在0-（n-1）之间，n 为节点数目。
proxy>\c proxy postgres
You are now connected to database user"postgres"
proxy# create or replace function digoalf_test4()returns int as $$
cluster 'cluster_sevl';
run on any;
$$ language plproxy strict;
CREATE FUNCTION
proxy=# \c proxy proxy
You are now connected to database "proxy" as user "proxy".
proxy=> select" from digoal.f_test4();
f_test4();
------
0
(1 row)
//run on any 表示在随机的节点执行。
proxy=> select* from digoaldy(＇select from t＇as t<i int>);
i
-----
1
1
1
...
(12 rows)
显示共插入了12条。
接下来测试 run on+HASH 函数，指定返回int类型。
run on function
//使用比特计算得到运行节点。
proxy> create or replace function digoalf(int) returns int as $$
//创建函数返回int类型，由于该函数为普通函数，可以由普通用户使用sql语言创建，并返回指定的类型。
select $i
$$ language sol strict
CREATE FUNCTION
proxy>\c proxy postgres
You are now connected to database"proxy"as user"postares"
proxy# create or replace function digoal.f test4() returns int as $$
cluster 'cluster srvl';
run on digoal.f(10);
//通过比特微运算，10 转化为2，即在第二个节点执行
//若输入 run on digoal.f(11)，则在第三个节点执行；若输入 run on digoal.f(-11)，则在第一个节点执行
$$ language plproxy strict;
CREATE FUNCTION
proxy=> select digoal.f test4();
在所有节点同时去插入一条 sql 语句
proxy>\c proxy postgres
You are now connected to database user"postgres"
proxy# create or replace function digoalf_test4()returns int as $$
cluster 'cluster_sevl';
run on all;
$$ language plproxy strict;
ERROR: PL/Proxy function digoal.f_test4(0): RUN ON ALL requires set-returning function
//因为 run on all 不是仅对应一个返回值，故应改为 setof，且下面的函数也应改为 setof
DROP FUNCTION
proxy=# create or replace function digoalf_test4() returns setof int as $$
cluster 'cluster sevl';
run on all;
$$ language plproxy strict;
CREATE FUNCTION
proxy=# grant execute on function digoalf_test4() to proxy;
CREATE FUNCTION
proxy=# \c proxy proxy
You are now connected to database "proxy" as user "proxy".
proxy=> select" from digoal.f_test4();
f_test4();
------
0
(1 row)
将0-3号节点的返回值分别设为“return next0;return”“return next1;return”“return next2;return”“return next3;return”现在调用时，即可直接run on all，就会0、1、2、3全部返回，并在所有节点都插入了一条sql 语句，并调用了一个函数。
3、Postgre 优化测试
该案例后期也是在做数据拆分。
（1）测试表
此处用到的表有：用户的登录记录表、用户会话表及用户表 user_session 表。其登陆逻辑如下图：
查询；第二，插入登录表；第三，更新 user_session 表最后一次登录时间和登录的次数，当用户退出的时候，插入到用户的退出记录，更新用户的会话和最后一次的退出时间和在线时长。
create table user_info
(userid int,
engname text,
cnname text,
occupation text,
birthday date,
signname text,
email text,
qq numeric,
crt_time timestamp without time zone,
mod_time timestamp without time zone
);
create table user_session
(userid int,
logintime timestamp(0) without time zone,
login_count bigint default 0,
logouttime timestamp(0) without time zone,
online_interval interval default interval'0'
);
create table user_login_rec
(userid int,
login_time timestamp without time zone,
ip inet
);
create table user logout rec
(userid int,
logout_time timestamp without time zone,
ip inet
);
//在表在所有的数据节点上创建以上信息。
（2）初始化数据insert into user_info(userid, engname, cnname, occupation, birthday, signname, email , qq,crt_time, mod_time)
select generate_series(1,20000000),
//在代理节点中插入
'digoal.zhou',
'德哥',
'DBA',
'1970-01-01',
E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!',
'digoal@126.com',
276732431,
clock_timestamp(),
NULL；
insert into user_session (userid) select generate_series(1,20000000);
set work_mem='2048MB';
set maintenance_work_mem='2048MB';
alter table user_info add constraint pk_user_info primary key (userid);
alter table user_session add constraint pk_user_session primary key (userid);
//加入两个约束，分别为 user_info 和 user_session 表的主键，加到所有的数据节点中
（3）业务函数
①模拟用户登录的函数
create or replace function f_user_login
(i_userid int,
OUT o_userid int,
OUT o_engname text,
OUT o_cnname text,
OUT o_occupation text,
OUT o_birthday date,
OUT o_signname text,
OUT o_email text,
OUT o_qq numeric ，
)
//返回以上信息
as $BODY$
declare
begin
select userid, engname, cnname, occupation, birthday, signname, email, qq
into o_userid, o_engname, o_cnname, o_occupation, o_birthday, o_signname, o_email, o_qq
from user_info where userid=i_userid;insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session set logintime=now(), login_count =login_count +1 where userid=i_userid;
return;
$BODY$
language plpgsql;
② 模拟用户退出的函数
create or replace function f_user_logout
(i_userid int,
OUT o_result int
)
as $BODY$
declare
begininsert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());
update user_session set logouttime=now(), online_interval= online_interval+(now()-logintime) where userid=i_userid;
o_result := 0;
return;
exception
when others then
o_result := 1;
return;
end;
$BODY$
language plpgsql;
这些函数要加到所有的数据节点中，用户登录和退出时执行相应的函数。
（4）在代理节点创建对应的代理函数
要使用超级用户创建代理函数，修改代理的输入和输出，指定集群。
create or replace function digoal.f_user_login<
i_userid int,
OUT o_userid int,
OUT o_engnane text,
OUT o_cnname text,
OUT o_occupation text,
OUT o_birthday date,
OUT o_signname text,
OUT o_email text,
OUT o_qq numeric)
returns records as $$
cluster 'cluster srul';
run on pg_catalog.hashint4()
//使用 run on hash int4，查询其位置位于 pg.catalog
//由于 useid 是一个 pk，因而返回一个记录即 return 即可。run on 指定运行节点，可以以 hash.text 函数来作为运行的哈希分区，或直接就使用 useid，因为 useid 也是 int 类型。
$$ language plproxy strict;
将模拟用户登录的代理函数放到 function digoal_f_user_login 。
对于退出登录函数 logout，有两个参数，返回结果是1，表示存在问题，返回结果是0表示正常用户正常退出。注意run on 后面要加上 useid。
给予两个函数excute权限：
Grant excute on function to proxy;
Grant excute on function to logout
现在即可调用函数，如 select* from 1即用户1登录
在 user_session 和 user_info 表中插入用户数据，创建用户数据插入的函数。为了测试方便，插入一些非真实的数据:
create or replace function digoal.f_user_login<
i_userid int,
i_userid int,
i_engname text,
i_cnname text,
i_occupation text,
i_birthday date,
i_signname text,
i_email text,
i_qq numeric)
>returns records as $$
declare
begininsert into digoal.user_info <userid, engname, cnname, occupation, birthday date, signname, email, qq, crt_time> values <i_userid, i_engname, i_cnname, i_occupation, i_birthday date, i_signname, i_email, i_qq, now<>>
insert into digoal.user_session<userid> values<i_userid>，logintime， logouttime，online interval>
end
$$ language plpgsql strict;
将该段程序分别植入4个数据库中
proxyi create 01 replace Iuneclon User Inserc
proxy(# i_userid int,
proxy(# i_engname text,
proxy<# i_cnname text,
proxy(# i_occupation text,
proxy(# i birthday date,
proxy(# i_signname text,
proxy(# i_email text,
proxy<# t i_qq numeric
proxy(# ) as $$
proxy$# cluster 'cluster_sru1’;
proxy$# run on pg_catalog.hashint4(i_userid);
proxy$# $$ language plproxy strict;
ERROR: function result type must be specified
proxy=# grant execute on function digoal.f_user_insert to proxy;
ERROR: syntax error at or near "to"
LINE 1: grant execute on function digoal.f_user_insert to proxy;
proxy-f grant execute on function digoal.f user_insert(int.text.text.text,date.text.text.numeric) to proxy;
创建相应的插入数据的代理函数。里要改一下，不是out。
参数跟下面是一样的，跟函数是一样的，运行那么就算是创建好了。
那比如我们来调用插入数据的函数里面职业，年龄，我的签名，邮箱还有 qq，
那我们要往里面插很多条记录。
相当于是往指标里面会有20万条记录。数据节点里面有多少条记录，每个节点里面有多少条记录，我们到时候再来看。
20万条，全部刷完的。我们的设施数据就有了，到这里已经结束了。
我们来看一下优秀的绘画表还有没有更新。
我也就在等一那，我们看到已经登录仪式了，已经看到更新了
在线时长是19秒那我登就，总共登录一次那我再现在我再换id。比如，我换2的id，也正常的能够把数据取出来，在线时长17秒，换3，三也是正常，退出也正常。
通过代理代理的些登录退出，函数能够就来实现数据分区。
跨库的数据，当我物理设备不能够满足需求的时候，我把它分到四个物理设备。相当于是四个库都放在物理设备，那么在真实的环境当中，四个肯定就放到而不同的物理上。那样能够来实现，比如，我的用户数据非常庞大，已经达到几十个亿的时候。
那我节点放几十个亿的用户数据肯定就已经促销了，那我可以把几十个亿拆成每个节点，比如，放个亿，那拆成几十个节点话，就相当于是我不需要购买那种昂贵的，那种高端硬件。
我用种普通的叉86的服务器，就能够实现种一跨库的种数据分区，通过种代理pos代理来实现。我的用户比如，登录退出，都能够正常的去分发到不同的节点。
八、PL/Proxy 使用注意事项
1.plproxy 函数所在的 schema 尽量和数据节点上实际函数的 schema 一致，否则需要在 plproxy 函数中使用 target指定 schema.functionname。比如在课程中演示的在创建 plproxy 函数一般将 schema 存放在 digoal 下，因为实际的数据节点、函数等都存放在 digoal 下，就形成了一一对应，不需要去写 target，否则将会报错，显示不存在。
2.数据节点的个数请保持2^n
否则会在哈希运算时报错，因为在哈希运算时不允许创建非2^n个数据节点
如果业务为短连接的形式。那么需要1层连接池，且应在应用程序和 plproxy 数据库之间，而不是 plproxy 和数据节点之间。相当于应用程序连接3.39的，加的连接池应在3.39和 application 和 server 之间，而不是在3.39和3.150之间。连3.39是长连接，不会断掉，就不需要连接池，而如果是短连接，就需要连接池。
在应用程序和 plproxy 之间加连接池后，其实对于 plproxy 来说就是长连接了，所以在 plproxy 和数据节点之间也就不需要连接池了。
4.长连接不需要连接池,因为 plproxy 和数据节点之间的连接是长连接。
5.plproxy 语法:
connect. cluster, run, select, split target
cluster 指定运行的集群，run 在集群里面挑选连接,执行代理函数。
6.关于连接密码
出于安全考虑，建议在任何配置中不要出现明文密码，所以最好是plproxy服务器到数据节点是 trust 验证，保护好plproxy 即可.
假设 plproxy 在172.16.3.2上数据节点有4个，库名和用户名都为 digoal.那么在4个节点上配置 pghbaconf 如下 node0
host digoal digoal 172.16.3.2/32 trust nodel
host digoal digoal 172.16.3.2/32 trust
node2 577
host digoal digoal 172.16.3.2/32 trust node3
host digoal digoal 172.16.3.2/32 trust
7.run 详解:
run on<NR>,<NR>是数字常量，范围是0到 nodes-1;例如有4个节点 run on() ;(run on 4则报错)
run on ANY,
run on function(...)，即做比特微运算，这里用到的函数返回结果必须是 int2、int4或 int8
run on ALL,这种的 plproxy 函数必须是 returns setof...实体函数没有setof的要求
8.一个 plproxy 中只能出现一条connect 语句，否则报错.
digoal=# create or replace function f_test30returns setof int8 as $$
connect 'hostaddr=172.16.3.150 dbname=db0user=digoal port=1921;
connect hostaddr-172.16.3.150 dbname=db1 user=digoal port=1921;
select count(*) from pg_class;
$$ language plproxy strict;ERROR:PL/Proxy function postgres.ftest3(0):Compile error at line 2:Only one CONNECT statement allowed
不要把 plproxy 语言的权限赋予给普通用户，因为开放了trust 认证，如果再开放 plproxy 语言的权限是非常危险的正确的做法是使用超级用户创建 plproxy 函数,然后把函数的执行权限赋予给普通用户。
千万不要这样省事:
update pg language set lanpltrusted='t' where lanname=plproxy';
10.如果有全局唯一的序列需求，可以将序列的步调调整一下，每个数据节点使用不同的初始值。
例如 57
db0=# create sequence seq1 increment by 4 start with 1; CREATE SEOUENCE
db1=# create sequence seq1 increment by 4 start with 2:
