Skip to content

Commit c13454e

Browse files
vadim4ovadim4o
committed
Timetravel tests.
1 parent 7627154 commit c13454e

File tree

4 files changed

+209
-3
lines changed

4 files changed

+209
-3
lines changed

‎src/test/regress/expected/triggers.out

Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,3 +159,122 @@ count
159159
(1 row)
160160

161161
QUERY: DROP TABLE dup17;
162+
QUERY: create sequence ttdummy_seq increment 10 start 0 minvalue 0;
163+
QUERY: create table tttest (
164+
price_id int4,
165+
price_val int4,
166+
price_on int4 default nextval('ttdummy_seq'),
167+
price_off int4 default 999999
168+
);
169+
QUERY: insert into tttest values (1, 1, null, null);
170+
QUERY: insert into tttest values (2, 2, null, null);
171+
QUERY: insert into tttest values (3, 3, null, null);
172+
QUERY: create trigger ttdummy
173+
before delete or update on tttest
174+
for each row
175+
execute procedure
176+
ttdummy (price_on, price_off);
177+
QUERY: select * from tttest;
178+
price_id|price_val|price_on|price_off
179+
--------+---------+--------+---------
180+
1| 1| 0| 999999
181+
2| 2| 10| 999999
182+
3| 3| 20| 999999
183+
(3 rows)
184+
185+
QUERY: delete from tttest where price_id = 2;
186+
QUERY: select * from tttest;
187+
price_id|price_val|price_on|price_off
188+
--------+---------+--------+---------
189+
1| 1| 0| 999999
190+
3| 3| 20| 999999
191+
2| 2| 10| 30
192+
(3 rows)
193+
194+
QUERY: select * from tttest where price_off = 999999;
195+
price_id|price_val|price_on|price_off
196+
--------+---------+--------+---------
197+
1| 1| 0| 999999
198+
3| 3| 20| 999999
199+
(2 rows)
200+
201+
QUERY: update tttest set price_val = 30 where price_id = 3;
202+
QUERY: select * from tttest;
203+
price_id|price_val|price_on|price_off
204+
--------+---------+--------+---------
205+
1| 1| 0| 999999
206+
2| 2| 10| 30
207+
3| 30| 40| 999999
208+
3| 3| 20| 40
209+
(4 rows)
210+
211+
QUERY: update tttest set price_id = 5 where price_id = 3;
212+
QUERY: select * from tttest;
213+
price_id|price_val|price_on|price_off
214+
--------+---------+--------+---------
215+
1| 1| 0| 999999
216+
2| 2| 10| 30
217+
3| 3| 20| 40
218+
5| 30| 50| 999999
219+
3| 30| 40| 50
220+
(5 rows)
221+
222+
QUERY: select set_ttdummy(0);
223+
set_ttdummy
224+
-----------
225+
1
226+
(1 row)
227+
228+
QUERY: delete from tttest where price_id = 5;
229+
QUERY: update tttest set price_off = 999999 where price_val = 30;
230+
QUERY: select * from tttest;
231+
price_id|price_val|price_on|price_off
232+
--------+---------+--------+---------
233+
1| 1| 0| 999999
234+
2| 2| 10| 30
235+
3| 3| 20| 40
236+
3| 30| 40| 999999
237+
(4 rows)
238+
239+
QUERY: update tttest set price_id = 5 where price_id = 3;
240+
QUERY: select * from tttest;
241+
price_id|price_val|price_on|price_off
242+
--------+---------+--------+---------
243+
1| 1| 0| 999999
244+
2| 2| 10| 30
245+
5| 3| 20| 40
246+
5| 30| 40| 999999
247+
(4 rows)
248+
249+
QUERY: select set_ttdummy(1);
250+
set_ttdummy
251+
-----------
252+
0
253+
(1 row)
254+
255+
QUERY: update tttest set price_on = -1 where price_id = 1;
256+
WARN:ttdummy (tttest): you can't change price_on and/or price_off columns (use set_ttdummy)
257+
QUERY: select set_ttdummy(0);
258+
set_ttdummy
259+
-----------
260+
1
261+
(1 row)
262+
263+
QUERY: update tttest set price_on = -1 where price_id = 1;
264+
QUERY: select * from tttest;
265+
price_id|price_val|price_on|price_off
266+
--------+---------+--------+---------
267+
2| 2| 10| 30
268+
5| 3| 20| 40
269+
5| 30| 40| 999999
270+
1| 1| -1| 999999
271+
(4 rows)
272+
273+
QUERY: select * from tttest where price_on <= 25 and price_off > 25 and price_id = 5;
274+
price_id|price_val|price_on|price_off
275+
--------+---------+--------+---------
276+
5| 3| 20| 40
277+
(1 row)
278+
279+
QUERY: drop table tttest;
280+
QUERY: drop sequence ttdummy_seq;

‎src/test/regress/input/create_function_1.source

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,3 +30,15 @@ CREATE FUNCTION funny_dup17 ()
3030
AS '_OBJWD_/regress_DLSUFFIX_'
3131
LANGUAGE 'c'
3232
;
33+
34+
CREATE FUNCTION ttdummy ()
35+
RETURNS opaque
36+
AS '_OBJWD_/regress_DLSUFFIX_'
37+
LANGUAGE 'c'
38+
;
39+
40+
CREATE FUNCTION set_ttdummy (int4)
41+
RETURNS int4
42+
AS '_OBJWD_/regress_DLSUFFIX_'
43+
LANGUAGE 'c'
44+
;

‎src/test/regress/output/create_function_1.source

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,16 +9,26 @@ QUERY: CREATE FUNCTION widget_out(opaque)
99
LANGUAGE 'c';
1010
QUERY: CREATE FUNCTION check_primary_key ()
1111
RETURNS opaque
12-
AS '_OBJWD_/../../../contrib/spi/refint.so'
12+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
1313
LANGUAGE 'c'
1414
;
1515
QUERY: CREATE FUNCTION check_foreign_key ()
1616
RETURNS opaque
17-
AS '_OBJWD_/../../../contrib/spi/refint.so'
17+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
1818
LANGUAGE 'c'
1919
;
2020
QUERY: CREATE FUNCTION funny_dup17 ()
2121
RETURNS opaque
22-
AS '_OBJWD_/regress.so'
22+
AS '_OBJWD_/regress_DLSUFFIX_'
23+
LANGUAGE 'c'
24+
;
25+
QUERY: CREATE FUNCTION ttdummy ()
26+
RETURNS opaque
27+
AS '_OBJWD_/regress_DLSUFFIX_'
28+
LANGUAGE 'c'
29+
;
30+
QUERY: CREATE FUNCTION set_ttdummy (int4)
31+
RETURNS int4
32+
AS '_OBJWD_/regress_DLSUFFIX_'
2333
LANGUAGE 'c'
2434
;

‎src/test/regress/sql/triggers.sql

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -116,3 +116,68 @@ insert into dup17 values (13);
116116
select count(*) from dup17 where x = 13;
117117

118118
DROP TABLE dup17;
119+
120+
create sequence ttdummy_seq increment 10 start 0 minvalue 0;
121+
122+
create table tttest (
123+
price_id int4,
124+
price_val int4,
125+
price_on int4 default nextval('ttdummy_seq'),
126+
price_off int4 default 999999
127+
);
128+
129+
insert into tttest values (1, 1, null, null);
130+
insert into tttest values (2, 2, null, null);
131+
insert into tttest values (3, 3, null, null);
132+
133+
create trigger ttdummy
134+
before delete or update on tttest
135+
for each row
136+
execute procedure
137+
ttdummy (price_on, price_off);
138+
139+
select * from tttest;
140+
delete from tttest where price_id = 2;
141+
select * from tttest;
142+
-- what do we see ?
143+
144+
-- get current prices
145+
select * from tttest where price_off = 999999;
146+
147+
-- change price for price_id == 3
148+
update tttest set price_val = 30 where price_id = 3;
149+
select * from tttest;
150+
151+
-- now we want to change pric_id in ALL tuples
152+
-- this gets us not what we need
153+
update tttest set price_id = 5 where price_id = 3;
154+
select * from tttest;
155+
156+
-- restore data as before last update:
157+
select set_ttdummy(0);
158+
delete from tttest where price_id = 5;
159+
update tttest set price_off = 999999 where price_val = 30;
160+
select * from tttest;
161+
162+
-- and try change price_id now!
163+
update tttest set price_id = 5 where price_id = 3;
164+
select * from tttest;
165+
-- isn't it what we need ?
166+
167+
select set_ttdummy(1);
168+
169+
-- we want to correct some "date"
170+
update tttest set price_on = -1 where price_id = 1;
171+
-- but this doesn't work
172+
173+
-- try in this way
174+
select set_ttdummy(0);
175+
update tttest set price_on = -1 where price_id = 1;
176+
select * from tttest;
177+
-- isn't it what we need ?
178+
179+
-- get price for price_id == 5 as it was @ "date" 25
180+
select * from tttest where price_on <= 25 and price_off > 25 and price_id = 5;
181+
182+
drop table tttest;
183+
drop sequence ttdummy_seq;

0 commit comments

Comments
 (0)