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
|
CREATE EXTENSION multicorn;
CREATE server multicorn_srv foreign data wrapper multicorn options (
wrapper 'multicorn.testfdw.TestForeignDataWrapper'
);
CREATE user mapping for postgres server multicorn_srv options (usermapping 'test');
CREATE foreign table testmulticorn (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1',
test_type 'nowrite',
tx_hook 'true'
);
insert into testmulticorn(test1, test2) VALUES ('test', 'test2');
NOTICE: [('option1', 'option1'), ('test_type', 'nowrite'), ('tx_hook', 'true'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: BEGIN
NOTICE: ROLLBACK
ERROR: Error in python: NotImplementedError
DETAIL: This FDW does not support the writable API
update testmulticorn set test1 = 'test';
NOTICE: BEGIN
NOTICE: []
NOTICE: ['test1', 'test2']
NOTICE: ROLLBACK
ERROR: Error in python: NotImplementedError
DETAIL: This FDW does not support the writable API
delete from testmulticorn where test2 = 'test2 2 0';
NOTICE: BEGIN
NOTICE: [test2 = test2 2 0]
NOTICE: ['test1', 'test2']
NOTICE: ROLLBACK
ERROR: Error in python: NotImplementedError
DETAIL: This FDW does not support the writable API
CREATE foreign table testmulticorn_write (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1',
row_id_column 'test1',
test_type 'returning',
tx_hook 'true'
);
insert into testmulticorn_write(test1, test2) VALUES ('test', 'test2');
NOTICE: [('option1', 'option1'), ('row_id_column', 'test1'), ('test_type', 'returning'), ('tx_hook', 'true'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: BEGIN
NOTICE: INSERTING: [('test1', 'test'), ('test2', 'test2')]
NOTICE: PRECOMMIT
NOTICE: COMMIT
update testmulticorn_write set test1 = 'test' where test1 ilike 'test1 3%';
NOTICE: BEGIN
NOTICE: [test1 ~~* test1 3%]
NOTICE: ['test1', 'test2']
NOTICE: UPDATING: test1 3 1 with [('test1', 'test'), ('test2', 'test2 1 1')]
NOTICE: UPDATING: test1 3 4 with [('test1', 'test'), ('test2', 'test2 1 4')]
NOTICE: UPDATING: test1 3 7 with [('test1', 'test'), ('test2', 'test2 1 7')]
NOTICE: UPDATING: test1 3 10 with [('test1', 'test'), ('test2', 'test2 1 10')]
NOTICE: UPDATING: test1 3 13 with [('test1', 'test'), ('test2', 'test2 1 13')]
NOTICE: UPDATING: test1 3 16 with [('test1', 'test'), ('test2', 'test2 1 16')]
NOTICE: UPDATING: test1 3 19 with [('test1', 'test'), ('test2', 'test2 1 19')]
NOTICE: PRECOMMIT
NOTICE: COMMIT
delete from testmulticorn_write where test2 = 'test2 2 0';
NOTICE: BEGIN
NOTICE: [test2 = test2 2 0]
NOTICE: ['test1', 'test2']
NOTICE: DELETING: test1 1 0
NOTICE: PRECOMMIT
NOTICE: COMMIT
-- Test returning
insert into testmulticorn_write(test1, test2) VALUES ('test', 'test2') RETURNING test1;
NOTICE: BEGIN
NOTICE: INSERTING: [('test1', 'test'), ('test2', 'test2')]
NOTICE: PRECOMMIT
NOTICE: COMMIT
test1
----------------
INSERTED: test
(1 row)
update testmulticorn_write set test1 = 'test' where test1 ilike 'test1 3%' RETURNING test1;
NOTICE: BEGIN
NOTICE: [test1 ~~* test1 3%]
NOTICE: ['test1', 'test2']
NOTICE: UPDATING: test1 3 1 with [('test1', 'test'), ('test2', 'test2 1 1')]
NOTICE: UPDATING: test1 3 4 with [('test1', 'test'), ('test2', 'test2 1 4')]
NOTICE: UPDATING: test1 3 7 with [('test1', 'test'), ('test2', 'test2 1 7')]
NOTICE: UPDATING: test1 3 10 with [('test1', 'test'), ('test2', 'test2 1 10')]
NOTICE: UPDATING: test1 3 13 with [('test1', 'test'), ('test2', 'test2 1 13')]
NOTICE: UPDATING: test1 3 16 with [('test1', 'test'), ('test2', 'test2 1 16')]
NOTICE: UPDATING: test1 3 19 with [('test1', 'test'), ('test2', 'test2 1 19')]
NOTICE: PRECOMMIT
NOTICE: COMMIT
test1
---------------
UPDATED: test
UPDATED: test
UPDATED: test
UPDATED: test
UPDATED: test
UPDATED: test
UPDATED: test
(7 rows)
delete from testmulticorn_write where test1 = 'test1 1 0' returning test2, test1;
NOTICE: BEGIN
NOTICE: [test1 = test1 1 0]
NOTICE: ['test1', 'test2']
NOTICE: DELETING: test1 1 0
NOTICE: PRECOMMIT
NOTICE: COMMIT
test2 | test1
-----------+-----------
test2 2 0 | test1 1 0
(1 row)
DROP foreign table testmulticorn_write;
-- Now test with another column
CREATE foreign table testmulticorn_write(
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1',
row_id_column 'test2'
);
insert into testmulticorn_write(test1, test2) VALUES ('test', 'test2');
NOTICE: [('option1', 'option1'), ('row_id_column', 'test2'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: INSERTING: [('test1', 'test'), ('test2', 'test2')]
update testmulticorn_write set test1 = 'test' where test1 ilike 'test1 3%';
NOTICE: [test1 ~~* test1 3%]
NOTICE: ['test1', 'test2']
NOTICE: UPDATING: test2 1 1 with [('test1', 'test'), ('test2', 'test2 1 1')]
NOTICE: UPDATING: test2 1 4 with [('test1', 'test'), ('test2', 'test2 1 4')]
NOTICE: UPDATING: test2 1 7 with [('test1', 'test'), ('test2', 'test2 1 7')]
NOTICE: UPDATING: test2 1 10 with [('test1', 'test'), ('test2', 'test2 1 10')]
NOTICE: UPDATING: test2 1 13 with [('test1', 'test'), ('test2', 'test2 1 13')]
NOTICE: UPDATING: test2 1 16 with [('test1', 'test'), ('test2', 'test2 1 16')]
NOTICE: UPDATING: test2 1 19 with [('test1', 'test'), ('test2', 'test2 1 19')]
delete from testmulticorn_write where test2 = 'test2 2 0';
NOTICE: [test2 = test2 2 0]
NOTICE: ['test2']
NOTICE: DELETING: test2 2 0
update testmulticorn_write set test2 = 'test' where test2 = 'test2 1 1';
NOTICE: [test2 = test2 1 1]
NOTICE: ['test1', 'test2']
NOTICE: UPDATING: test2 1 1 with [('test1', 'test1 3 1'), ('test2', 'test')]
DROP foreign table testmulticorn_write;
-- Now test with other types
CREATE foreign table testmulticorn_write(
test1 date,
test2 timestamp
) server multicorn_srv options (
option1 'option1',
row_id_column 'test2',
test_type 'date'
);
insert into testmulticorn_write(test1, test2) VALUES ('2012-01-01', '2012-01-01 00:00:00');
NOTICE: [('option1', 'option1'), ('row_id_column', 'test2'), ('test_type', 'date'), ('usermapping', 'test')]
NOTICE: [('test1', 'date'), ('test2', 'timestamp without time zone')]
NOTICE: INSERTING: [('test1', datetime.date(2012, 1, 1)), ('test2', datetime.datetime(2012, 1, 1, 0, 0))]
delete from testmulticorn_write where test2 > '2011-12-03';
NOTICE: [test2 > 2011-12-03 00:00:00]
NOTICE: ['test2']
NOTICE: DELETING: 2011-12-03 14:30:25
update testmulticorn_write set test1 = date_trunc('day', test1) where test2 = '2011-09-03 14:30:25';
NOTICE: [test2 = 2011-09-03 14:30:25]
NOTICE: ['test1', 'test2']
NOTICE: UPDATING: 2011-09-03 14:30:25 with [('test1', datetime.date(2011, 9, 2)), ('test2', datetime.datetime(2011, 9, 3, 14, 30, 25))]
DROP foreign table testmulticorn_write;
-- Test with unknown column
CREATE foreign table testmulticorn_write(
test1 date,
test2 timestamp
) server multicorn_srv options (
option1 'option1',
row_id_column 'teststuff',
test_type 'date'
);
delete from testmulticorn_write;
NOTICE: [('option1', 'option1'), ('row_id_column', 'teststuff'), ('test_type', 'date'), ('usermapping', 'test')]
NOTICE: [('test1', 'date'), ('test2', 'timestamp without time zone')]
ERROR: The rowid attribute does not exist
DROP USER MAPPING for postgres SERVER multicorn_srv;
DROP EXTENSION multicorn cascade;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to server multicorn_srv
drop cascades to foreign table testmulticorn
drop cascades to foreign table testmulticorn_write
|
