-
Notifications
You must be signed in to change notification settings - Fork 68
/
create_shards.sql.tmpl
110 lines (81 loc) · 3.65 KB
/
create_shards.sql.tmpl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
-- ===================================================================
-- create test functions
-- ===================================================================
CREATE FUNCTION sort_names(cstring, cstring, cstring)
RETURNS cstring
AS 'pg_shard'
LANGUAGE C STRICT;
CREATE FUNCTION create_table_then_fail(cstring, integer)
RETURNS bool
AS 'pg_shard'
LANGUAGE C STRICT;
-- ===================================================================
-- test shard creation functionality
-- ===================================================================
CREATE TABLE table_to_distribute (
name text,
id bigint PRIMARY KEY
);
-- use an index instead of table name
SELECT master_create_distributed_table('table_to_distribute_pkey', 'id');
-- use a bad column name
SELECT master_create_distributed_table('table_to_distribute', 'bad_column');
-- use unsupported partition type
SELECT master_create_distributed_table('table_to_distribute', 'name', 'r');
-- distribute table and inspect side effects
SELECT master_create_distributed_table('table_to_distribute', 'name');
SELECT partition_method, key FROM pgs_distribution_metadata.partition
WHERE relation_id = 'table_to_distribute'::regclass;
-- use a bad shard count
SELECT master_create_worker_shards('table_to_distribute', 0, 1);
-- use a bad replication factor
SELECT master_create_worker_shards('table_to_distribute', 16, 0);
-- use a replication factor higher than shard count
SELECT master_create_worker_shards('table_to_distribute', 16, 3);
\set VERBOSITY terse
-- use a replication factor higher than healthy node count
-- this will create a shard on the healthy node but fail right after
SELECT master_create_worker_shards('table_to_distribute', 16, 2);
-- finally, create shards and inspect metadata
SELECT master_create_worker_shards('table_to_distribute', 16, 1);
\set VERBOSITY default
SELECT storage, min_value, max_value FROM pgs_distribution_metadata.shard
WHERE relation_id = 'table_to_distribute'::regclass
ORDER BY (min_value COLLATE "C") ASC;
-- all shards should be on a single node
WITH unique_nodes AS (
SELECT DISTINCT ON (node_name, node_port) node_name, node_port
FROM pgs_distribution_metadata.shard_placement, pgs_distribution_metadata.shard
WHERE shard_placement.shard_id = shard.id
)
SELECT COUNT(*) FROM unique_nodes;
SELECT COUNT(*) FROM pg_class WHERE relname LIKE 'table_to_distribute%' AND relkind = 'r';
-- try to create them again
SELECT master_create_worker_shards('table_to_distribute', 16, 1);
-- test list sorting
SELECT sort_names('sumedh', 'jason', 'ozgun');
-- test remote command execution
SELECT create_table_then_fail('localhost', $PGPORT);
SELECT COUNT(*) FROM pg_class WHERE relname LIKE 'throwaway%' AND relkind = 'r';
\set VERBOSITY terse
-- test foreign table creation
CREATE FOREIGN TABLE foreign_table_to_distribute
(
name text,
id bigint
)
SERVER fake_fdw_server;
SELECT master_create_distributed_table('foreign_table_to_distribute', 'id');
SELECT master_create_worker_shards('foreign_table_to_distribute', 16, 1);
\set VERBOSITY default
SELECT storage, min_value, max_value FROM pgs_distribution_metadata.shard
WHERE relation_id = 'foreign_table_to_distribute'::regclass
ORDER BY (min_value COLLATE "C") ASC;
-- cleanup foreign table, related shards and shard placements
DELETE FROM pgs_distribution_metadata.shard_placement
WHERE shard_id IN (SELECT shard_id FROM pgs_distribution_metadata.shard
WHERE relation_id = 'foreign_table_to_distribute'::regclass);
DELETE FROM pgs_distribution_metadata.shard
WHERE relation_id = 'foreign_table_to_distribute'::regclass;
DELETE FROM pgs_distribution_metadata.partition
WHERE relation_id = 'foreign_table_to_distribute'::regclass;