Chinaunix首页 | 论坛 | 博客

小桥河西

首页　| 　博文目录　| 　关于我

skykiker

  • 博客访问： 2951352
  • 博文数量： 199
  • 博客积分： 1400
  • 博客等级： 上尉
  • 技术积分： 4126
  • 用 户 组： 普通用户
  • 注册时间： 2008-07-06 19:06
个人简介

半个PostgreSQL DBA，热衷于数据库相关的技术。我的ppt分享https://pan.baidu.com/s/1eRQsdAa https://github.com/chenhuajun https://chenhuajun.github.io

文章分类

全部博文（199）

文章存档

2020年（5）

2019年（1）

2018年（12）

2017年（23）

2016年（43）

2015年（51）

2014年（27）

2013年（21）

2011年（1）

2010年（4）

2009年（5）

2008年（6）

我的朋友
最近访客
推荐博文
相关博文
多CN citus发行命令记录

分类： Mysql/postgreSQL

2018-10-04 01:35:57

0. 准备

在citus 7.4上打开记录远程命令的日志开关 

create table tb1(id int primary key, c1 int);
select create_distributed_table('tb1','id');
insert into tb1 select id,random()*1000 from generate_series(1,100)id;

set client_min_messages = log;
set citus.log_remote_commands = on;

postgres=# select * from pg_dist_node;
 nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster 
--------+---------+----------+----------+----------+-------------+----------+----------+-------------
      1 |       1 | cituswk1 |     5432 | default  | f           | t        | primary  | default
      2 |       2 | cituswk2 |     5432 | default  | f           | t        | primary  | default
(2 rows)

1. startmetadatasynctonode 

postgres=# select start_metadata_sync_to_node('cituswk1',5432);
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 0, '2000-01-01 00:00:00+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing UPDATE pg_dist_local_group SET groupid = 1
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_drop_distributed_table(logicalrelid) FROM pg_dist_partition
DETAIL:  on server cituswk1:5432
LOG:  issuing TRUNCATE pg_dist_node
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_node (nodeid, groupid, nodename, nodeport, noderack, hasmetadata, isactive, noderole, nodecluster) VALUES (2, 2, 'cituswk2', 5432, 'default', FALSE, TRUE, 'primary'::noderole, 'default'),(1, 1, 'cituswk1', 5432, 'default', TRUE, TRUE, 'primary'::noderole, 'default')
DETAIL:  on server cituswk1:5432
LOG:  issuing CREATE TABLE public.tb1 (id integer NOT NULL, c1 integer)
DETAIL:  on server cituswk1:5432
LOG:  issuing ALTER TABLE public.tb1 ADD CONSTRAINT tb1_pkey PRIMARY KEY (id)
DETAIL:  on server cituswk1:5432
LOG:  issuing ALTER TABLE public.tb1 OWNER TO postgres
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_partition (logicalrelid, partmethod, partkey, colocationid, repmodel) VALUES ('public.tb1'::regclass, 'h', column_name_to_column('public.tb1','id'), 1, 's')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_create_truncate_trigger('public.tb1')
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_placement (shardid, shardstate, shardlength, groupid, placementid) VALUES (102008, 1, 0, 1, 1),(102009, 1, 0, 2, 2),(102010, 1, 0, 1, 3),(102011, 1, 0, 2, 4),(102012, 1, 0, 1, 5),(102013, 1, 0, 2, 6),(102014, 1, 0, 1, 7),(102015, 1, 0, 2, 8),(102016, 1, 0, 1, 9),(102017, 1, 0, 2, 10),(102018, 1, 0, 1, 11),(102019, 1, 0, 2, 12),(102020, 1, 0, 1, 13),(102021, 1, 0, 2, 14),(102022, 1, 0, 1, 15),(102023, 1, 0, 2, 16),(102024, 1, 0, 1, 17),(102025, 1, 0, 2, 18),(102026, 1, 0, 1, 19),(102027, 1, 0, 2, 20),(102028, 1, 0, 1, 21),(102029, 1, 0, 2, 22),(102030, 1, 0, 1, 23),(102031, 1, 0, 2, 24),(102032, 1, 0, 1, 25),(102033, 1, 0, 2, 26),(102034, 1, 0, 1, 27),(102035, 1, 0, 2, 28),(102036, 1, 0, 1, 29),(102037, 1, 0, 2, 30),(102038, 1, 0, 1, 31),(102039, 1, 0, 2, 32)
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_shard (logicalrelid, shardid, shardstorage, shardminvalue, shardmaxvalue) VALUES ('public.tb1'::regclass, 102008, 't', '-2147483648', '-2013265921'),('public.tb1'::regclass, 102009, 't', '-2013265920', '-1879048193'),('public.tb1'::regclass, 102010, 't', '-1879048192', '-1744830465'),('public.tb1'::regclass, 102011, 't', '-1744830464', '-1610612737'),('public.tb1'::regclass, 102012, 't', '-1610612736', '-1476395009'),('public.tb1'::regclass, 102013, 't', '-1476395008', '-1342177281'),('public.tb1'::regclass, 102014, 't', '-1342177280', '-1207959553'),('public.tb1'::regclass, 102015, 't', '-1207959552', '-1073741825'),('public.tb1'::regclass, 102016, 't', '-1073741824', '-939524097'),('public.tb1'::regclass, 102017, 't', '-939524096', '-805306369'),('public.tb1'::regclass, 102018, 't', '-805306368', '-671088641'),('public.tb1'::regclass, 102019, 't', '-671088640', '-536870913'),('public.tb1'::regclass, 102020, 't', '-536870912', '-402653185'),('public.tb1'::regclass, 102021, 't', '-402653184', '-268435457'),('public.tb1'::regclass, 102022, 't', '-268435456', '-134217729'),('public.tb1'::regclass, 102023, 't', '-134217728', '-1'),('public.tb1'::regclass, 102024, 't', '0', '134217727'),('public.tb1'::regclass, 102025, 't', '134217728', '268435455'),('public.tb1'::regclass, 102026, 't', '268435456', '402653183'),('public.tb1'::regclass, 102027, 't', '402653184', '536870911'),('public.tb1'::regclass, 102028, 't', '536870912', '671088639'),('public.tb1'::regclass, 102029, 't', '671088640', '805306367'),('public.tb1'::regclass, 102030, 't', '805306368', '939524095'),('public.tb1'::regclass, 102031, 't', '939524096', '1073741823'),('public.tb1'::regclass, 102032, 't', '1073741824', '1207959551'),('public.tb1'::regclass, 102033, 't', '1207959552', '1342177279'),('public.tb1'::regclass, 102034, 't', '1342177280', '1476395007'),('public.tb1'::regclass, 102035, 't', '1476395008', '1610612735'),('public.tb1'::regclass, 102036, 't', '1610612736', '1744830463'),('public.tb1'::regclass, 102037, 't', '1744830464', '1879048191'),('public.tb1'::regclass, 102038, 't', '1879048192', '2013265919'),('public.tb1'::regclass, 102039, 't', '2013265920', '2147483647')
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT
DETAIL:  on server cituswk1:5432
 start_metadata_sync_to_node 
-----------------------------

(1 row)

2. 扩展worker上执行DDL 

在扩展worker上执行DDL，会被拒绝
postgres=# create index tb1_id1 on tb1(id);
ERROR:  operation is not allowed on this node
HINT:  Connect to the coordinator and run it again.

3. MX表的分布式死锁检测 

WK1
postgres=# begin;
BEGIN
postgres=# update tb1 set c1=10 where id=5;
UPDATE 1
postgres=# update tb1 set c1=10 where id=1;
ERROR:  canceling the transaction since it was involved in a distributed deadlock

CN
postgres=# begin;
BEGIN
postgres=# update tb1 set c1=10 where id=1;
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 12, '2018-05-20 20:04:59.522356+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing UPDATE public.tb1_102009 tb1 SET c1 = 10 WHERE (id OPERATOR(pg_catalog.=) 1)
DETAIL:  on server cituswk2:5432
UPDATE 1
postgres=# update tb1 set c1=10 where id=5;
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 12, '2018-05-20 20:04:59.522356+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing UPDATE public.tb1_102014 tb1 SET c1 = 10 WHERE (id OPERATOR(pg_catalog.=) 5)
DETAIL:  on server cituswk1:5432
UPDATE 1

4.1 DDL-创建分片表 

set citus.shard_count=4;
create table tb3(id int primary key, c1 int);
select create_distributed_table('tb3','id','hash','none');

postgres=# set citus.shard_count=4;
SET
postgres=# create table tb3(id int primary key, c1 int);
CREATE TABLE
postgres=# select create_distributed_table('tb3','id','hash','none');
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 5, '2018-05-27 21:35:51.094949+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102290, 'CREATE TABLE public.tb3 (id integer NOT NULL, c1 integer)')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102290, 'ALTER TABLE public.tb3 ADD CONSTRAINT tb3_pkey1 PRIMARY KEY (id)')
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 5, '2018-05-27 21:35:51.094949+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102291, 'CREATE TABLE public.tb3 (id integer NOT NULL, c1 integer)')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102291, 'ALTER TABLE public.tb3 ADD CONSTRAINT tb3_pkey1 PRIMARY KEY (id)')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102292, 'CREATE TABLE public.tb3 (id integer NOT NULL, c1 integer)')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102292, 'ALTER TABLE public.tb3 ADD CONSTRAINT tb3_pkey1 PRIMARY KEY (id)')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102293, 'CREATE TABLE public.tb3 (id integer NOT NULL, c1 integer)')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102293, 'ALTER TABLE public.tb3 ADD CONSTRAINT tb3_pkey1 PRIMARY KEY (id)')
DETAIL:  on server cituswk2:5432
LOG:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server cituswk1:5432
LOG:  issuing CREATE TABLE public.tb3 (id integer NOT NULL, c1 integer)
DETAIL:  on server cituswk1:5432
LOG:  issuing ALTER TABLE public.tb3 ADD CONSTRAINT tb3_pkey1 PRIMARY KEY (id)
DETAIL:  on server cituswk1:5432
LOG:  issuing ALTER TABLE public.tb3 OWNER TO postgres
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_partition (logicalrelid, partmethod, partkey, colocationid, repmodel) VALUES ('public.tb3'::regclass, 'h', column_name_to_column('public.tb3','id'), 7, 's')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_create_truncate_trigger('public.tb3')
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_placement (shardid, shardstate, shardlength, groupid, placementid) VALUES (102290, 1, 0, 1, 289),(102291, 1, 0, 2, 290),(102292, 1, 0, 1, 291),(102293, 1, 0, 2, 292)
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_shard (logicalrelid, shardid, shardstorage, shardminvalue, shardmaxvalue) VALUES ('public.tb3'::regclass, 102290, 't', '-2147483648', '-1073741825'),('public.tb3'::regclass, 102291, 't', '-1073741824', '-1'),('public.tb3'::regclass, 102292, 't', '0', '1073741823'),('public.tb3'::regclass, 102293, 't', '1073741824', '2147483647')
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_5_10'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_5_11'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_5_10'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_5_11'
DETAIL:  on server cituswk2:5432
 create_distributed_table 
--------------------------

(1 row)

4.2 DDL-添加索引 

postgres=# create index tb3_idx on tb3(c1);
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 6, '2018-05-27 21:36:38.65541+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server cituswk1:5432
LOG:  issuing create index tb3_idx on tb3(c1);
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 6, '2018-05-27 21:36:38.65541+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 6, '2018-05-27 21:36:38.65541+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 6, '2018-05-27 21:36:38.65541+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing CREATE  INDEX   tb3_idx_102290 ON public.tb3_102290 USING btree (c1 ) 
DETAIL:  on server cituswk1:5432
LOG:  issuing CREATE  INDEX   tb3_idx_102291 ON public.tb3_102291 USING btree (c1 ) 
DETAIL:  on server cituswk2:5432
LOG:  issuing CREATE  INDEX   tb3_idx_102292 ON public.tb3_102292 USING btree (c1 ) 
DETAIL:  on server cituswk1:5432
LOG:  issuing CREATE  INDEX   tb3_idx_102293 ON public.tb3_102293 USING btree (c1 ) 
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_6_12'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_6_13'
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_6_14'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_6_15'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_6_12'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_6_13'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_6_14'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_6_15'
DETAIL:  on server cituswk2:5432
CREATE INDEX

4.3 DDL-修改索引 

postgres=# alter index tb3_idx set(fillfactor = 80);
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 7, '2018-05-27 21:40:14.253399+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server cituswk1:5432
LOG:  issuing alter index tb3_idx set(fillfactor = 80);
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 7, '2018-05-27 21:40:14.253399+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 7, '2018-05-27 21:40:14.253399+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 7, '2018-05-27 21:40:14.253399+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102290, 'public', 'alter index tb3_idx set(fillfactor = 80);')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102291, 'public', 'alter index tb3_idx set(fillfactor = 80);')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102292, 'public', 'alter index tb3_idx set(fillfactor = 80);')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102293, 'public', 'alter index tb3_idx set(fillfactor = 80);')
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_7_16'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_7_17'
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_7_18'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_7_19'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_7_16'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_7_17'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_7_18'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_7_19'
DETAIL:  on server cituswk2:5432
ALTER INDEX

4.3 DDL-修改表定义 

postgres=# alter table tb3 rename to tb3_new;
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server cituswk1:5432
LOG:  issuing alter table tb3 rename to tb3_new;
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102290, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102291, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102292, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102293, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_26'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_27'
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_28'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_29'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_26'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_27'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_28'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_29'
DETAIL:  on server cituswk2:5432
ALTER TABLE

4.3 DDL-修改表定义 

postgres=# alter table tb3 rename to tb3_new;
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server cituswk1:5432
LOG:  issuing alter table tb3 rename to tb3_new;
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 10, '2018-05-27 21:46:12.824311+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102290, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102291, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102292, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102293, 'public', 'alter table tb3 rename to tb3_new;')
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_26'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_27'
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_28'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_10_29'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_26'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_27'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_28'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_10_29'
DETAIL:  on server cituswk2:5432
ALTER TABLE

4.4 DDL-创建参考表 

postgres=# create table tb1_ref(id int primary key, c1 int);
CREATE TABLE
postgres=# select create_reference_table('tb1_ref');
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 11, '2018-05-27 21:50:20.695277+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102294, 'CREATE TABLE public.tb1_ref (id integer NOT NULL, c1 integer)')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102294, 'ALTER TABLE public.tb1_ref ADD CONSTRAINT tb1_ref_pkey PRIMARY KEY (id)')
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 11, '2018-05-27 21:50:20.695277+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102294, 'CREATE TABLE public.tb1_ref (id integer NOT NULL, c1 integer)')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT worker_apply_shard_ddl_command (102294, 'ALTER TABLE public.tb1_ref ADD CONSTRAINT tb1_ref_pkey PRIMARY KEY (id)')
DETAIL:  on server cituswk2:5432
LOG:  issuing SET citus.enable_ddl_propagation TO 'off'
DETAIL:  on server cituswk1:5432
LOG:  issuing CREATE TABLE public.tb1_ref (id integer NOT NULL, c1 integer)
DETAIL:  on server cituswk1:5432
LOG:  issuing ALTER TABLE public.tb1_ref ADD CONSTRAINT tb1_ref_pkey PRIMARY KEY (id)
DETAIL:  on server cituswk1:5432
LOG:  issuing ALTER TABLE public.tb1_ref OWNER TO postgres
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_partition (logicalrelid, partmethod, partkey, colocationid, repmodel) VALUES ('public.tb1_ref'::regclass, 'n', NULL, 3, 't')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT worker_create_truncate_trigger('public.tb1_ref')
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_placement (shardid, shardstate, shardlength, groupid, placementid) VALUES (102294, 1, 0, 2, 294),(102294, 1, 0, 1, 293)
DETAIL:  on server cituswk1:5432
LOG:  issuing INSERT INTO pg_dist_shard (logicalrelid, shardid, shardstorage, shardminvalue, shardmaxvalue) VALUES ('public.tb1_ref'::regclass, 102294, 't', NULL, NULL)
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_11_30'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_11_31'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_11_30'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_11_31'
DETAIL:  on server cituswk2:5432
 create_reference_table 
------------------------

(1 row)

5.1 DML-批量插入数据 

postgres=# insert into tb3 select id,random()*1000 from generate_series(1,100)id;
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 13, '2018-05-27 21:52:01.620765+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing COPY public.tb3_102290 (id, c1) FROM STDIN WITH (FORMAT BINARY)
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 13, '2018-05-27 21:52:01.620765+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY public.tb3_102293 (id, c1) FROM STDIN WITH (FORMAT BINARY)
DETAIL:  on server cituswk2:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 13, '2018-05-27 21:52:01.620765+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY public.tb3_102291 (id, c1) FROM STDIN WITH (FORMAT BINARY)
DETAIL:  on server cituswk2:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 13, '2018-05-27 21:52:01.620765+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing COPY public.tb3_102292 (id, c1) FROM STDIN WITH (FORMAT BINARY)
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_13_36'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_13_37'
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_13_38'
DETAIL:  on server cituswk2:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_59_13_39'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_13_36'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_13_37'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_13_38'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_59_13_39'
DETAIL:  on server cituswk1:5432
INSERT 0 100

6.1 DML-带分片字段 

postgres=# select * from tb3 where id in(1);
LOG:  issuing SELECT id, c1 FROM public.tb3_102290 tb3 WHERE (id OPERATOR(pg_catalog.=) 1)
DETAIL:  on server cituswk1:5432
 id | c1  
----+-----
  1 | 633
(1 row)

postgres=# select * from tb3 where id in(1,5);
LOG:  issuing SELECT id, c1 FROM public.tb3_102290 tb3 WHERE (id OPERATOR(pg_catalog.=) ANY (ARRAY[1, 5]))
DETAIL:  on server cituswk1:5432
 id | c1  
----+-----
  1 | 633
  5 | 841
(2 rows)

postgres=# explain select * from tb3 where id in(1,5);
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 14, '2018-05-27 22:05:31.098819+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SAVEPOINT citus_explain_savepoint
DETAIL:  on server cituswk1:5432
LOG:  issuing EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, TIMING FALSE, FORMAT TEXT) SELECT id, c1 FROM public.tb3_102290 tb3 WHERE (id OPERATOR(pg_catalog.=) ANY (ARRAY[1, 5]))
DETAIL:  on server cituswk1:5432
LOG:  issuing ROLLBACK TO SAVEPOINT citus_explain_savepoint
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT
DETAIL:  on server cituswk1:5432
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Custom Scan (Citus Router)  (cost=0.00..0.00 rows=0 width=0)
   Task Count: 1
   Tasks Shown: All
   ->  Task
         Node: host=cituswk1 port=5432 dbname=postgres
         ->  Bitmap Heap Scan on tb3_102290 tb3  (cost=8.33..13.67 rows=2 width=8)
               Recheck Cond: (id = ANY ('{1,5}'::integer[]))
               ->  Bitmap Index Scan on tb3_pkey1_102290  (cost=0.00..8.33 rows=2 width=0)
                     Index Cond: (id = ANY ('{1,5}'::integer[]))
(9 rows)

postgres=# select * from tb3 where id in(1,4);
LOG:  issuing COPY (SELECT id, c1 FROM tb3_102290 tb3 WHERE (id OPERATOR(pg_catalog.=) ANY ('{1,4}'::integer[]))) TO STDOUT
DETAIL:  on server cituswk1:5432
LOG:  issuing COPY (SELECT id, c1 FROM tb3_102291 tb3 WHERE (id OPERATOR(pg_catalog.=) ANY ('{1,4}'::integer[]))) TO STDOUT
DETAIL:  on server cituswk2:5432
 id | c1  
----+-----
  1 | 633
  4 | 186
(2 rows)

postgres=# explain select * from tb3 where id in(1,4);
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 16, '2018-05-27 22:06:25.945043+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing SAVEPOINT citus_explain_savepoint
DETAIL:  on server cituswk1:5432
LOG:  issuing EXPLAIN (ANALYZE FALSE, VERBOSE FALSE, COSTS TRUE, BUFFERS FALSE, TIMING FALSE, FORMAT TEXT) SELECT id, c1 FROM tb3_102290 tb3 WHERE (id OPERATOR(pg_catalog.=) ANY ('{1,4}'::integer[]))
DETAIL:  on server cituswk1:5432
LOG:  issuing ROLLBACK TO SAVEPOINT citus_explain_savepoint
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT
DETAIL:  on server cituswk1:5432
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Custom Scan (Citus Real-Time)  (cost=0.00..0.00 rows=0 width=0)
   Task Count: 2
   Tasks Shown: One of 2
   ->  Task
         Node: host=cituswk1 port=5432 dbname=postgres
         ->  Bitmap Heap Scan on tb3_102290 tb3  (cost=8.33..13.67 rows=2 width=8)
               Recheck Cond: (id = ANY ('{1,4}'::integer[]))
               ->  Bitmap Index Scan on tb3_pkey1_102290  (cost=0.00..8.33 rows=2 width=0)
                     Index Cond: (id = ANY ('{1,4}'::integer[]))
(9 rows)


注:涉及单个分片时，采用router执行器，即短连接;涉及多个分片时，采用COPY命令和短连接。

6.2 DML-不带分片字段 

postgres=# select * from tb3 where c1=1;
LOG:  issuing COPY (SELECT id, c1 FROM tb3_102291 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO STDOUT
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY (SELECT id, c1 FROM tb3_102290 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO STDOUT
DETAIL:  on server cituswk1:5432
LOG:  issuing COPY (SELECT id, c1 FROM tb3_102292 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO STDOUT
DETAIL:  on server cituswk1:5432
LOG:  issuing COPY (SELECT id, c1 FROM tb3_102293 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO STDOUT
DETAIL:  on server cituswk2:5432
 id | c1 
----+----
 81 |  1
(1 row)

Time: 45.370 ms

6.2 DML-带分片字段(task-tracker) 

postgres=# set citus.task_executor_type='task-tracker';
SET
Time: 0.736 ms
postgres=# select * from tb3 where id=1;
LOG:  issuing SELECT id, c1 FROM public.tb3_102290 tb3 WHERE (id OPERATOR(pg_catalog.=) 1)
DETAIL:  on server cituswk1:5432
 id | c1  
----+-----
  1 | 633
(1 row)

Time: 15.135 ms
postgres=# select * from tb3 where id in(1,4);
LOG:  issuing SELECT task_tracker_assign_task  (989855766, 1, 'COPY (SELECT id, c1 FROM tb3_102290 tb3 WHERE (id OPERATOR(pg_catalog.=) ANY (''{1,4}''::integer[]))) TO ''base/pgsql_job_cache/job_989855766/task_000001''');
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_assign_task  (989855766, 2, 'COPY (SELECT id, c1 FROM tb3_102291 tb3 WHERE (id OPERATOR(pg_catalog.=) ANY (''{1,4}''::integer[]))) TO ''base/pgsql_job_cache/job_989855766/task_000002''');
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY "base/pgsql_job_cache/job_989855766/task_000001" TO STDOUT WITH (format 'transmit')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855766, 2);
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY "base/pgsql_job_cache/job_989855766/task_000002" TO STDOUT WITH (format 'transmit')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_assign_task  (989855766, 2147483647, 'SELECT task_tracker_cleanup_job(989855766)');
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_assign_task  (989855766, 2147483647, 'SELECT task_tracker_cleanup_job(989855766)');
DETAIL:  on server cituswk2:5432
 id | c1  
----+-----
  1 | 633
  4 | 186
(2 rows)

Time: 563.812 ms

注:task-tracker执行器下每个worker并发执行的任务数由参数citus.maxrunningtaskspernode控制，如果应用不方便在会话级上设置citus.taskexecutortype，可以设置到业务账号上。 

alter user jobuser set citus.task_executor_type='task-tracker';

6.2 DML-不带分片字段(task-tracker) 

postgres=# select * from tb3 where c1=1;
LOG:  issuing SELECT task_tracker_assign_task  (989855768, 3, 'COPY (SELECT id, c1 FROM tb3_102292 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO ''base/pgsql_job_cache/job_989855768/task_000003''');SELECT task_tracker_assign_task  (989855768, 1, 'COPY (SELECT id, c1 FROM tb3_102290 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO ''base/pgsql_job_cache/job_989855768/task_000001''');
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_assign_task  (989855768, 2, 'COPY (SELECT id, c1 FROM tb3_102291 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO ''base/pgsql_job_cache/job_989855768/task_000002''');SELECT task_tracker_assign_task  (989855768, 4, 'COPY (SELECT id, c1 FROM tb3_102293 tb3 WHERE (c1 OPERATOR(pg_catalog.=) 1)) TO ''base/pgsql_job_cache/job_989855768/task_000004''');
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 3);SELECT task_tracker_task_status(989855768, 1);
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY "base/pgsql_job_cache/job_989855768/task_000003" TO STDOUT WITH (format 'transmit')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY "base/pgsql_job_cache/job_989855768/task_000001" TO STDOUT WITH (format 'transmit')
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_task_status(989855768, 2);SELECT task_tracker_task_status(989855768, 4);
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY "base/pgsql_job_cache/job_989855768/task_000002" TO STDOUT WITH (format 'transmit')
DETAIL:  on server cituswk2:5432
LOG:  issuing COPY "base/pgsql_job_cache/job_989855768/task_000004" TO STDOUT WITH (format 'transmit')
DETAIL:  on server cituswk2:5432
LOG:  issuing SELECT task_tracker_assign_task  (989855768, 2147483647, 'SELECT task_tracker_cleanup_job(989855768)');
DETAIL:  on server cituswk1:5432
LOG:  issuing SELECT task_tracker_assign_task  (989855768, 2147483647, 'SELECT task_tracker_cleanup_job(989855768)');
DETAIL:  on server cituswk2:5432
 id | c1 
----+----
 81 |  1
(1 row)

Time: 637.999 ms

7.1 带事务的SQL(单分片字段的更新) 

postgres=# begin;
BEGIN
postgres=# update tb3 set c1=0 where id=1;
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 21, '2018-05-27 23:24:29.415197+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing UPDATE public.tb3_102290 tb3 SET c1 = 0 WHERE (id OPERATOR(pg_catalog.=) 1)
DETAIL:  on server cituswk1:5432
UPDATE 1
postgres=# update tb3 set c1=0 where id=5;
LOG:  issuing UPDATE public.tb3_102290 tb3 SET c1 = 0 WHERE (id OPERATOR(pg_catalog.=) 5)
DETAIL:  on server cituswk1:5432
UPDATE 1
postgres=# update tb3 set c1=0 where id=4;
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 21, '2018-05-27 23:24:29.415197+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing UPDATE public.tb3_102291 tb3 SET c1 = 0 WHERE (id OPERATOR(pg_catalog.=) 4)
DETAIL:  on server cituswk2:5432
UPDATE 1
postgres=# commit;
LOG:  issuing PREPARE TRANSACTION 'citus_0_176_21_2'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_176_21_3'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_176_21_2'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_176_21_3'
DETAIL:  on server cituswk2:5432
COMMIT

postgres=# select * from pg_locks where locktype='advisory';
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid |   mode    | granted | fastpath 
----------+----------+----------+------+-------+------------+---------------+---------+--------+----------+--------------------+-----+-----------+---------+----------
 advisory |    12953 |          |      |       |            |               |       0 | 102291 |        4 | 3/119              | 176 | ShareLock | t       | f
 advisory |    12953 |          |      |       |            |               |       0 | 102290 |        4 | 3/119              | 176 | ShareLock | t       | f
(2 rows)

注:对分片加ShareLock

7.1 带事务的SQL(多分片字段的更新) 

postgres=# update tb3 set c1=0 where id in(1,2);
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 19, '2018-05-27 23:16:59.815288+00');
DETAIL:  on server cituswk1:5432
LOG:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(0, 19, '2018-05-27 23:16:59.815288+00');
DETAIL:  on server cituswk2:5432
LOG:  issuing UPDATE public.tb3_102290 tb3 SET c1 = 0 WHERE (id OPERATOR(pg_catalog.=) ANY (ARRAY[1, 2]))
DETAIL:  on server cituswk1:5432
LOG:  issuing UPDATE public.tb3_102293 tb3 SET c1 = 0 WHERE (id OPERATOR(pg_catalog.=) ANY (ARRAY[1, 2]))
DETAIL:  on server cituswk2:5432
UPDATE 2
postgres=# commit;
LOG:  issuing PREPARE TRANSACTION 'citus_0_176_19_0'
DETAIL:  on server cituswk1:5432
LOG:  issuing PREPARE TRANSACTION 'citus_0_176_19_1'
DETAIL:  on server cituswk2:5432
LOG:  issuing COMMIT PREPARED 'citus_0_176_19_0'
DETAIL:  on server cituswk1:5432
LOG:  issuing COMMIT PREPARED 'citus_0_176_19_1'
DETAIL:  on server cituswk2:5432
COMMIT

postgres=# select * from pg_locks where locktype='advisory';
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid |           mode           | granted | fastpath 
----------+----------+----------+------+-------+------------+---------------+---------+--------+----------+--------------------+-----+--------------------------+---------+----------
 advisory |    12953 |          |      |       |            |               |       0 | 102290 |        5 | 3/118              | 176 | ShareUpdateExclusiveLock | t       | f
 advisory |    12953 |          |      |       |            |               |       0 | 102293 |        4 | 3/118              | 176 | ShareLock                | t       | f
 advisory |    12953 |          |      |       |            |               |       0 | 102293 |        5 | 3/118              | 176 | ShareUpdateExclusiveLock | t       | f
 advisory |    12953 |          |      |       |            |               |       0 | 102290 |        4 | 3/118              | 176 | ShareLock                | t       | f
(4 rows)

注:对分片加ShareLock和ShareUpdateExclusiveLock锁，锁整个分片（可能为了减少不必要的并发和死锁）。


阅读(2274) | 评论(0) | 转发(0) |
0

上一篇：citus实战系列之四多CN部署

下一篇：关于citus.limit_clause_row_fetch_count优化参数

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6