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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
|
\set MYSQL_HOST `echo \'"$MYSQL_HOST"\'`
\set MYSQL_PORT `echo \'"$MYSQL_PORT"\'`
\set MYSQL_USER_NAME `echo \'"$MYSQL_USER_NAME"\'`
\set MYSQL_PASS `echo \'"$MYSQL_PWD"\'`
-- Before running this file User must create database mysql_fdw_regress on
-- MySQL with all permission for MYSQL_USER_NAME user with MYSQL_PWD password
-- and ran mysql_init.sh file to create tables.
\c contrib_regression
CREATE EXTENSION IF NOT EXISTS mysql_fdw;
CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR public SERVER mysql_svr
OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
CREATE SERVER mysql_svr1 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR public SERVER mysql_svr1
OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
-- Create foreign tables and insert data.
CREATE FOREIGN TABLE fdw519_ft1(stu_id int, stu_name varchar(255), stu_dept int)
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress1', table_name 'student');
CREATE FOREIGN TABLE fdw519_ft2(c1 INTEGER, c2 VARCHAR(14), c3 VARCHAR(13))
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test_tbl2');
CREATE FOREIGN TABLE fdw519_ft3 (c1 INTEGER, c2 VARCHAR(10), c3 CHAR(9), c4 BIGINT, c5 pg_catalog.Date, c6 DECIMAL, c7 INTEGER, c8 SMALLINT)
SERVER mysql_svr1 OPTIONS (dbname 'mysql_fdw_regress', table_name 'test_tbl1');
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
INSERT INTO fdw519_ft2 VALUES(10, 'DEVELOPMENT', 'PUNE');
INSERT INTO fdw519_ft2 VALUES(20, 'ADMINISTRATION', 'BANGLORE');
INSERT INTO fdw519_ft3 VALUES (100, 'EMP1', 'ADMIN', 1300, '1980-12-17', 800.23, NULL, 20);
INSERT INTO fdw519_ft3 VALUES (200, 'EMP2', 'SALESMAN', 600, '1981-02-20', 1600.00, 300, 30);
-- Check truncatable option with invalid values.
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (ADD truncatable 'abc');
ERROR: truncatable requires a Boolean value
ALTER FOREIGN TABLE fdw519_ft1 OPTIONS (ADD truncatable 'abc');
ERROR: truncatable requires a Boolean value
-- Default behavior, should truncate.
TRUNCATE fdw519_ft1;
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
(0 rows)
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
-- Set truncatable to false
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (ADD truncatable 'false');
-- Truncate the table.
TRUNCATE fdw519_ft1;
ERROR: foreign table "fdw519_ft1" does not allow truncates
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- Set truncatable to true
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'true');
TRUNCATE fdw519_ft1;
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
(0 rows)
-- truncatable to true on Server but false on table level.
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'false');
ALTER TABLE fdw519_ft2 OPTIONS (ADD truncatable 'true');
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
TRUNCATE fdw519_ft2;
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----+----
(0 rows)
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
INSERT INTO fdw519_ft2 VALUES(10, 'DEVELOPMENT', 'PUNE');
INSERT INTO fdw519_ft2 VALUES(20, 'ADMINISTRATION', 'BANGLORE');
-- truncatable to true on Server but false on one table and true for other
-- table.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'true');
ALTER TABLE fdw519_ft1 OPTIONS (ADD truncatable 'false');
ALTER TABLE fdw519_ft2 OPTIONS (SET truncatable 'true');
TRUNCATE fdw519_ft1, fdw519_ft2;
ERROR: foreign table "fdw519_ft1" does not allow truncates
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
-- truncatable to false on Server but false on one table and true for other
-- table.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'false');
ALTER TABLE fdw519_ft1 OPTIONS (SET truncatable 'false');
ALTER TABLE fdw519_ft2 OPTIONS (SET truncatable 'true');
TRUNCATE fdw519_ft1, fdw519_ft2;
ERROR: foreign table "fdw519_ft1" does not allow truncates
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
-- Truncate from different servers.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'true');
ALTER SERVER mysql_svr1 OPTIONS (ADD truncatable 'true');
ALTER TABLE fdw519_ft1 OPTIONS (SET truncatable 'true');
TRUNCATE fdw519_ft1, fdw519_ft2, fdw519_ft3;
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
(0 rows)
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----+----
(0 rows)
SELECT * FROM fdw519_ft3 ORDER BY 1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
----+----+----+----+----+----+----+----
(0 rows)
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- Truncate with CASCADE is not supported.
TRUNCATE fdw519_ft1 CASCADE;
ERROR: CASCADE option in TRUNCATE is not supported by this FDW
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- Default is RESTRICT, so it is allowed.
TRUNCATE fdw519_ft1 RESTRICT;
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
(0 rows)
-- Should throw an error if primary key is referenced by foreign key.
CREATE FOREIGN TABLE fdw519_ft4(stu_id varchar(10), stu_name varchar(255), stu_dept int)
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress1', table_name 'student1');
CREATE FOREIGN TABLE fdw519_ft5(dept_id int, stu_id varchar(10))
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress1', table_name 'dept');
TRUNCATE fdw519_ft4;
ERROR: failed to execute the MySQL query:
Cannot truncate a table referenced in a foreign key constraint (`mysql_fdw_regress1`.`dept`, CONSTRAINT `dept_ibfk_1`)
-- FDW-520: Support generated columns in IMPORT FOREIGN SCHEMA command.
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520)
FROM SERVER mysql_svr INTO public OPTIONS (import_generated 'true');
\d fdw520
Foreign table "public.fdw520"
Column | Type | Collation | Nullable | Default | FDW options
---------+---------+-----------+----------+----------------------------------------------+-------------
c1 | integer | | not null | |
c `"" 2 | integer | | | |
c3 | integer | | | generated always as ("c `"""" 2" * 2) stored |
c4 | integer | | not null | generated always as ("c `"""" 2" * 4) stored |
Server: mysql_svr
FDW options: (dbname 'mysql_fdw_regress', table_name 'fdw520')
-- Generated column refers to another generated column, should throw an error:
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520_1)
FROM SERVER mysql_svr INTO public OPTIONS (import_generated 'true');
ERROR: cannot use generated column "c3" in column generation expression
LINE 5: c4 int GENERATED ALWAYS AS ("c3" * 4) STORED NOT NULL
^
DETAIL: A generated column cannot reference another generated column.
QUERY: CREATE FOREIGN TABLE fdw520_1 (
c1 int NOT NULL,
c2 int,
c3 int GENERATED ALWAYS AS ("c2" * 2) STORED,
c4 int GENERATED ALWAYS AS ("c3" * 4) STORED NOT NULL
) SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'fdw520_1');
CONTEXT: importing foreign table "fdw520_1"
-- import_generated as false.
DROP FOREIGN TABLE fdw520;
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520)
FROM SERVER mysql_svr INTO public OPTIONS (import_generated 'false');
\d fdw520
Foreign table "public.fdw520"
Column | Type | Collation | Nullable | Default | FDW options
---------+---------+-----------+----------+---------+-------------
c1 | integer | | not null | |
c `"" 2 | integer | | | |
c3 | integer | | | |
c4 | integer | | not null | |
Server: mysql_svr
FDW options: (dbname 'mysql_fdw_regress', table_name 'fdw520')
-- Without import_generated option, default is true.
DROP FOREIGN TABLE fdw520;
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520)
FROM SERVER mysql_svr INTO public;
\d fdw520
Foreign table "public.fdw520"
Column | Type | Collation | Nullable | Default | FDW options
---------+---------+-----------+----------+----------------------------------------------+-------------
c1 | integer | | not null | |
c `"" 2 | integer | | | |
c3 | integer | | | generated always as ("c `"""" 2" * 2) stored |
c4 | integer | | not null | generated always as ("c `"""" 2" * 4) stored |
Server: mysql_svr
FDW options: (dbname 'mysql_fdw_regress', table_name 'fdw520')
-- FDW-521: Insert and update operations on table having generated columns.
INSERT INTO fdw520(c1, "c `"""" 2") VALUES(1, 2);
INSERT INTO fdw520(c1, "c `"""" 2", c3, c4) VALUES(2, 4, DEFAULT, DEFAULT);
-- Should fail.
INSERT INTO fdw520 VALUES(1, 2, 3, 4);
ERROR: cannot insert a non-DEFAULT value into column "c3"
DETAIL: Column "c3" is a generated column.
SELECT * FROM fdw520 ORDER BY 1;
c1 | c `"" 2 | c3 | c4
----+---------+----+----
1 | 2 | 4 | 8
2 | 4 | 8 | 16
(2 rows)
UPDATE fdw520 SET "c `"""" 2" = 20 WHERE c1 = 2;
SELECT * FROM fdw520 ORDER BY 1;
c1 | c `"" 2 | c3 | c4
----+---------+----+----
1 | 2 | 4 | 8
2 | 20 | 40 | 80
(2 rows)
-- Should fail.
UPDATE fdw520 SET c4 = 20 WHERE c1 = 2;
ERROR: column "c4" can only be updated to DEFAULT
DETAIL: Column "c4" is a generated column.
UPDATE fdw520 SET c3 = 20 WHERE c1 = 2;
ERROR: column "c3" can only be updated to DEFAULT
DETAIL: Column "c3" is a generated column.
-- Cleanup
DELETE FROM fdw519_ft1;
DELETE FROM fdw519_ft2;
DELETE FROM fdw519_ft3;
DELETE FROM fdw520;
DROP FOREIGN TABLE fdw519_ft1;
DROP FOREIGN TABLE fdw519_ft2;
DROP FOREIGN TABLE fdw519_ft3;
DROP FOREIGN TABLE fdw519_ft4;
DROP FOREIGN TABLE fdw519_ft5;
DROP FOREIGN TABLE fdw520;
DROP USER MAPPING FOR public SERVER mysql_svr;
DROP SERVER mysql_svr;
DROP USER MAPPING FOR public SERVER mysql_svr1;
DROP SERVER mysql_svr1;
DROP EXTENSION mysql_fdw;
|
