1--
2-- Hot Standby tests
3--
4-- hs_standby_allowed.sql
5--
6-- SELECT
7select count(*) as should_be_1 from hs1;
8 should_be_1
9-------------
10           1
11(1 row)
12
13select count(*) as should_be_2 from hs2;
14 should_be_2
15-------------
16           2
17(1 row)
18
19select count(*) as should_be_3 from hs3;
20 should_be_3
21-------------
22           3
23(1 row)
24
25COPY hs1 TO '/tmp/copy_test';
26\! cat /tmp/copy_test
271
28-- Access sequence directly
29select is_called from hsseq;
30 is_called
31-----------
32 f
33(1 row)
34
35-- Transactions
36begin;
37select count(*)  as should_be_1 from hs1;
38 should_be_1
39-------------
40           1
41(1 row)
42
43end;
44begin transaction read only;
45select count(*)  as should_be_1 from hs1;
46 should_be_1
47-------------
48           1
49(1 row)
50
51end;
52begin transaction isolation level repeatable read;
53select count(*) as should_be_1 from hs1;
54 should_be_1
55-------------
56           1
57(1 row)
58
59select count(*) as should_be_1 from hs1;
60 should_be_1
61-------------
62           1
63(1 row)
64
65select count(*) as should_be_1 from hs1;
66 should_be_1
67-------------
68           1
69(1 row)
70
71commit;
72begin;
73select count(*) as should_be_1 from hs1;
74 should_be_1
75-------------
76           1
77(1 row)
78
79commit;
80begin;
81select count(*) as should_be_1 from hs1;
82 should_be_1
83-------------
84           1
85(1 row)
86
87abort;
88start transaction;
89select count(*) as should_be_1 from hs1;
90 should_be_1
91-------------
92           1
93(1 row)
94
95commit;
96begin;
97select count(*) as should_be_1 from hs1;
98 should_be_1
99-------------
100           1
101(1 row)
102
103rollback;
104begin;
105select count(*) as should_be_1 from hs1;
106 should_be_1
107-------------
108           1
109(1 row)
110
111savepoint s;
112select count(*) as should_be_2 from hs2;
113 should_be_2
114-------------
115           2
116(1 row)
117
118commit;
119begin;
120select count(*) as should_be_1 from hs1;
121 should_be_1
122-------------
123           1
124(1 row)
125
126savepoint s;
127select count(*) as should_be_2 from hs2;
128 should_be_2
129-------------
130           2
131(1 row)
132
133release savepoint s;
134select count(*) as should_be_2 from hs2;
135 should_be_2
136-------------
137           2
138(1 row)
139
140savepoint s;
141select count(*) as should_be_3 from hs3;
142 should_be_3
143-------------
144           3
145(1 row)
146
147rollback to savepoint s;
148select count(*) as should_be_2 from hs2;
149 should_be_2
150-------------
151           2
152(1 row)
153
154commit;
155-- SET parameters
156-- has no effect on read only transactions, but we can still set it
157set synchronous_commit = on;
158show synchronous_commit;
159 synchronous_commit
160--------------------
161 on
162(1 row)
163
164reset synchronous_commit;
165discard temp;
166discard all;
167-- CURSOR commands
168BEGIN;
169DECLARE hsc CURSOR FOR select * from hs3;
170FETCH next from hsc;
171 col1
172------
173  113
174(1 row)
175
176fetch first from hsc;
177 col1
178------
179  113
180(1 row)
181
182fetch last from hsc;
183 col1
184------
185  115
186(1 row)
187
188fetch 1 from hsc;
189 col1
190------
191(0 rows)
192
193CLOSE hsc;
194COMMIT;
195-- Prepared plans
196PREPARE hsp AS select count(*) from hs1;
197PREPARE hsp_noexec (integer) AS insert into hs1 values ($1);
198EXECUTE hsp;
199 count
200-------
201     1
202(1 row)
203
204DEALLOCATE hsp;
205-- LOCK
206BEGIN;
207LOCK hs1 IN ACCESS SHARE MODE;
208LOCK hs1 IN ROW SHARE MODE;
209LOCK hs1 IN ROW EXCLUSIVE MODE;
210COMMIT;
211-- UNLISTEN
212UNLISTEN a;
213UNLISTEN *;
214-- LOAD
215-- should work, easier if there is no test for that...
216-- ALLOWED COMMANDS
217CHECKPOINT;
218discard all;
219