所有文章是按github的markdown格式书写的，如此页面显示不正常，请跳转到github版
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. start_metadata_sync_to_node
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.max_running_tasks_per_node控制，如果应用不方便在会话级上设置citus.task_executor_type，可以设置到业务账号上。
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锁，锁整个分片（可能为了减少不必要的并发和死锁）。
October 4, 2018