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
|
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'
);
-- No savepoints
BEGIN;
CREATE foreign table testmulticorn_write (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1',
row_id_column 'test1',
test_type 'returning'
);
insert into testmulticorn_write(test1, test2) VALUES ('0', 'A');
NOTICE: [('option1', 'option1'), ('row_id_column', 'test1'), ('test_type', 'returning'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: INSERTING: [('test1', '0'), ('test2', 'A')]
update testmulticorn_write set test2 = 'B' where test1 = '0';
NOTICE: [test1 = 0]
NOTICE: ['test1']
update testmulticorn_write set test2 = 'C' where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
delete from testmulticorn_write where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
DROP foreign table testmulticorn_write;
ROLLBACK;
-- One savepoint
BEGIN;
CREATE foreign table testmulticorn_write (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1',
row_id_column 'test1',
test_type 'returning'
);
SAVEPOINT A;
insert into testmulticorn_write(test1, test2) VALUES ('0', 'A');
NOTICE: [('option1', 'option1'), ('row_id_column', 'test1'), ('test_type', 'returning'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: INSERTING: [('test1', '0'), ('test2', 'A')]
update testmulticorn_write set test2 = 'B' where test1 = '0';
NOTICE: [test1 = 0]
NOTICE: ['test1']
update testmulticorn_write set test2 = 'C' where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
delete from testmulticorn_write where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
ROLLBACK TO A;
RELEASE A;
DROP foreign table testmulticorn_write;
COMMIT;
-- Multiple sequential savepoints
BEGIN;
CREATE foreign table testmulticorn_write (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1',
row_id_column 'test1',
test_type 'returning'
);
SAVEPOINT A;
insert into testmulticorn_write(test1, test2) VALUES ('0', 'A');
NOTICE: [('option1', 'option1'), ('row_id_column', 'test1'), ('test_type', 'returning'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: INSERTING: [('test1', '0'), ('test2', 'A')]
select * from testmulticorn LIMIT 1;
NOTICE: [('option1', 'option1'), ('test_type', 'nowrite'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: []
NOTICE: ['test1', 'test2']
test1 | test2
-----------+-----------
test1 1 0 | test2 2 0
(1 row)
ROLLBACK TO A;
RELEASE A;
SAVEPOINT B;
update testmulticorn_write set test2 = 'B' where test1 = '0';
NOTICE: [test1 = 0]
NOTICE: ['test1']
RELEASE B;
update testmulticorn_write set test2 = 'C' where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
delete from testmulticorn_write where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
DROP foreign table testmulticorn_write;
ROLLBACK;
-- Multiple nested savepoints
BEGIN;
CREATE foreign table testmulticorn_write (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1',
row_id_column 'test1',
test_type 'returning'
);
SAVEPOINT A;
insert into testmulticorn_write(test1, test2) VALUES ('0', 'A');
NOTICE: [('option1', 'option1'), ('row_id_column', 'test1'), ('test_type', 'returning'), ('usermapping', 'test')]
NOTICE: [('test1', 'character varying'), ('test2', 'character varying')]
NOTICE: INSERTING: [('test1', '0'), ('test2', 'A')]
select * from testmulticorn LIMIT 1;
NOTICE: []
NOTICE: ['test1', 'test2']
test1 | test2
-----------+-----------
test1 1 0 | test2 2 0
(1 row)
SAVEPOINT B;
update testmulticorn_write set test2 = 'B' where test1 = '0';
NOTICE: [test1 = 0]
NOTICE: ['test1']
RELEASE B;
update testmulticorn_write set test2 = 'C' where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
delete from testmulticorn_write where test1 = '1';
NOTICE: [test1 = 1]
NOTICE: ['test1']
ROLLBACK TO A;
RELEASE A;
DROP foreign table testmulticorn_write;
ROLLBACK;
-- Clean up
DROP USER MAPPING FOR postgres SERVER multicorn_srv;
DROP EXTENSION multicorn cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to server multicorn_srv
drop cascades to foreign table testmulticorn
|
