-
Notifications
You must be signed in to change notification settings - Fork 68
/
modifications.out.tmpl
191 lines (177 loc) · 7.23 KB
/
modifications.out.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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
-- ===================================================================
-- test end-to-end modification functionality
-- ===================================================================
CREATE TYPE order_side AS ENUM ('buy', 'sell');
CREATE TABLE limit_orders (
id bigint PRIMARY KEY,
symbol text NOT NULL,
bidder_id bigint NOT NULL,
placed_at timestamp NOT NULL,
kind order_side NOT NULL,
limit_price decimal NOT NULL DEFAULT 0.00 CHECK (limit_price >= 0.00)
);
SELECT master_create_distributed_table('limit_orders', 'id');
master_create_distributed_table
---------------------------------
(1 row)
\set VERBOSITY terse
SELECT master_create_worker_shards('limit_orders', 2, 1);
WARNING: Connection failed to adeadhost:5432
WARNING: could not create shard on "adeadhost:5432"
master_create_worker_shards
-----------------------------
(1 row)
\set VERBOSITY default
-- basic single-row insert
INSERT INTO limit_orders VALUES (32743, 'AAPL', 9580, '2004-10-19 10:23:54', 'buy',
20.69);
SELECT COUNT(*) FROM limit_orders WHERE id = 32743;
count
-------
1
(1 row)
-- insert with DEFAULT in the target list
INSERT INTO limit_orders VALUES (12756, 'MSFT', 10959, '2013-05-08 07:29:23', 'sell',
DEFAULT);
SELECT COUNT(*) FROM limit_orders WHERE id = 12756;
count
-------
1
(1 row)
-- insert with expressions in target list
INSERT INTO limit_orders VALUES (430, upper('ibm'), 214, timestamp '2003-01-28 10:31:17' +
interval '5 hours', 'buy', sqrt(2));
SELECT COUNT(*) FROM limit_orders WHERE id = 430;
count
-------
1
(1 row)
-- insert without partition key
INSERT INTO limit_orders DEFAULT VALUES;
ERROR: cannot plan INSERT using row with NULL value in partition column
-- insert violating NOT NULL constraint
INSERT INTO limit_orders VALUES (NULL, 'T', 975234, DEFAULT);
ERROR: cannot plan INSERT using row with NULL value in partition column
-- insert violating column constraint
INSERT INTO limit_orders VALUES (18811, 'BUD', 14962, '2014-04-05 08:32:16', 'sell',
-5.00);
WARNING: Bad result from localhost:$PGPORT
DETAIL: Remote message: new row for relation "limit_orders_10034" violates check constraint "limit_orders_limit_price_check"
ERROR: could not modify any active placements
-- insert violating primary key constraint
INSERT INTO limit_orders VALUES (32743, 'LUV', 5994, '2001-04-16 03:37:28', 'buy', 0.58);
WARNING: Bad result from localhost:$PGPORT
DETAIL: Remote message: duplicate key value violates unique constraint "limit_orders_pkey_10035"
ERROR: could not modify any active placements
-- queries with non-constant partition values are unsupported
INSERT INTO limit_orders VALUES (random() * 100, 'ORCL', 152, '2011-08-25 11:50:45',
'sell', 0.58);
ERROR: cannot plan sharded modification containing values which are not constants or constant expressions
-- queries with expressions that cannot be collapsed are unsupported
INSERT INTO limit_orders VALUES (2036, 'GOOG', 5634, now(), 'buy', random());
ERROR: cannot plan sharded modification containing values which are not constants or constant expressions
-- queries with multiple rows are unsupported
INSERT INTO limit_orders VALUES (DEFAULT), (DEFAULT);
ERROR: multi-row INSERTs to distributed tables are not supported
-- insert from queries are unsupported
INSERT INTO limit_orders SELECT * FROM limit_orders;
ERROR: unsupported range table type: 1
-- queries with a returning clause are unsupported
INSERT INTO limit_orders VALUES (7285, 'AMZN', 3278, '2016-01-05 02:07:36', 'sell', 0.00)
RETURNING *;
ERROR: cannot plan sharded modification that uses a RETURNING clause
-- queries containing a CTE are unsupported
WITH deleted_orders AS (DELETE FROM limit_orders RETURNING *)
INSERT INTO limit_orders DEFAULT VALUES;
ERROR: cannot perform distributed planning on this query
DETAIL: Joins are currently unsupported
-- test simple delete
INSERT INTO limit_orders VALUES (246, 'TSLA', 162, '2007-07-02 16:32:15', 'sell', 20.69);
SELECT COUNT(*) FROM limit_orders WHERE id = 246;
count
-------
1
(1 row)
DELETE FROM limit_orders WHERE id = 246;
SELECT COUNT(*) FROM limit_orders WHERE id = 246;
count
-------
0
(1 row)
-- delete with expression in WHERE clause
INSERT INTO limit_orders VALUES (246, 'TSLA', 162, '2007-07-02 16:32:15', 'sell', 20.69);
SELECT COUNT(*) FROM limit_orders WHERE id = 246;
count
-------
1
(1 row)
DELETE FROM limit_orders WHERE id = (2 * 123);
SELECT COUNT(*) FROM limit_orders WHERE id = 246;
count
-------
0
(1 row)
-- queries with no constraints on the partition key are not supported
DELETE FROM limit_orders WHERE bidder_id = 162;
ERROR: cannot modify multiple shards during a single query
-- queries with a 'using' clause are unsupported
CREATE TABLE bidders ( name text, id bigint );
DELETE FROM limit_orders USING bidders WHERE limit_orders.id = 246 AND
limit_orders.bidder_id = bidders.id AND
bidders.name = 'Bernie Madoff';
ERROR: cannot perform distributed planning on this query
DETAIL: Joins are currently unsupported
-- queries with a returning clause are unsupported
DELETE FROM limit_orders WHERE id = 246 RETURNING *;
ERROR: cannot plan sharded modification that uses a RETURNING clause
-- queries containing a CTE are unsupported
WITH deleted_orders AS (INSERT INTO limit_orders DEFAULT VALUES RETURNING *)
DELETE FROM limit_orders;
ERROR: cannot perform distributed planning on this query
DETAIL: Joins are currently unsupported
-- cursors are not supported
DELETE FROM limit_orders WHERE CURRENT OF cursor_name;
ERROR: cannot modify multiple shards during a single query
INSERT INTO limit_orders VALUES (246, 'TSLA', 162, '2007-07-02 16:32:15', 'sell', 20.69);
-- simple update
UPDATE limit_orders SET symbol = 'GM' WHERE id = 246;
SELECT symbol FROM limit_orders WHERE id = 246;
symbol
--------
GM
(1 row)
-- expression update
UPDATE limit_orders SET bidder_id = 6 * 3 WHERE id = 246;
SELECT bidder_id FROM limit_orders WHERE id = 246;
bidder_id
-----------
18
(1 row)
-- multi-column update
UPDATE limit_orders SET (kind, limit_price) = ('buy', DEFAULT) WHERE id = 246;
SELECT kind, limit_price FROM limit_orders WHERE id = 246;
kind | limit_price
------+-------------
buy | 0.00
(1 row)
-- queries with no constraints on the partition key are not supported
UPDATE limit_orders SET limit_price = 0.00;
ERROR: cannot modify multiple shards during a single query
-- UPDATEs with a FROM clause are unsupported
UPDATE limit_orders SET limit_price = 0.00 FROM bidders
WHERE limit_orders.id = 246 AND
limit_orders.bidder_id = bidders.id AND
bidders.name = 'Bernie Madoff';
ERROR: cannot perform distributed planning on this query
DETAIL: Joins are currently unsupported
-- queries with a returning clause are unsupported
UPDATE limit_orders SET symbol = 'GM' WHERE id = 246 RETURNING *;
ERROR: cannot plan sharded modification that uses a RETURNING clause
-- queries containing a CTE are unsupported
WITH deleted_orders AS (INSERT INTO limit_orders DEFAULT VALUES RETURNING *)
UPDATE limit_orders SET symbol = 'GM';
ERROR: cannot perform distributed planning on this query
DETAIL: Joins are currently unsupported
-- cursors are not supported
UPDATE limit_orders SET symbol = 'GM' WHERE CURRENT OF cursor_name;
ERROR: cannot modify multiple shards during a single query