Skip to content

Commit ba247bd

Browse files
vadim4ovadim4o
committed
Tests for spi/triggers
1 parent b09114b commit ba247bd

File tree

7 files changed

+428
-2
lines changed

7 files changed

+428
-2
lines changed

‎src/test/regress/GNUmakefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
#
88
#
99
# IDENTIFICATION
10-
# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.9 1997/07/29 14:09:11 momjian Exp $
10+
# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.10 1997/09/11 09:13:27 vadim Exp $
1111
#
1212
#-------------------------------------------------------------------------
1313

@@ -43,6 +43,7 @@ endif
4343
all: $(INFILES)
4444
cd input; $(MAKE) all; cd ..
4545
cd output; $(MAKE) all; cd ..
46+
$(MAKE) -C ../../../contrib/spi REFINT_VERBOSE=1
4647

4748
#
4849
# run the test

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

Lines changed: 161 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,161 @@
1+
QUERY: create table pkeys (pkey1 int4 not null, pkey2 text not null);
2+
QUERY: create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
3+
QUERY: create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
4+
QUERY: create index fkeys_i on fkeys (fkey1, fkey2);
5+
QUERY: create index fkeys2_i on fkeys2 (fkey21, fkey22);
6+
QUERY: create index fkeys2p_i on fkeys2 (pkey23);
7+
QUERY: insert into pkeys values (10, '1');
8+
QUERY: insert into pkeys values (20, '2');
9+
QUERY: insert into pkeys values (30, '3');
10+
QUERY: insert into pkeys values (40, '4');
11+
QUERY: insert into pkeys values (50, '5');
12+
QUERY: insert into pkeys values (60, '6');
13+
QUERY: create unique index pkeys_i on pkeys (pkey1, pkey2);
14+
QUERY: create trigger check_fkeys_pkey_exist
15+
before insert or update on fkeys
16+
for each row
17+
execute procedure
18+
check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
19+
QUERY: create trigger check_fkeys_pkey2_exist
20+
before insert or update on fkeys
21+
for each row
22+
execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23');
23+
QUERY: create trigger check_fkeys2_pkey_exist
24+
before insert or update on fkeys2
25+
for each row
26+
execute procedure
27+
check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
28+
QUERY: create trigger check_pkeys_fkey_cascade
29+
before delete or update on pkeys
30+
for each row
31+
execute procedure
32+
check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
33+
'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
34+
QUERY: create trigger check_fkeys2_fkey_restrict
35+
before delete or update on fkeys2
36+
for each row
37+
execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
38+
QUERY: insert into fkeys2 values (10, '1', 1);
39+
QUERY: insert into fkeys2 values (30, '3', 2);
40+
QUERY: insert into fkeys2 values (40, '4', 5);
41+
QUERY: insert into fkeys2 values (50, '5', 3);
42+
QUERY: insert into fkeys2 values (70, '5', 3);
43+
WARN:check_fkeys2_pkey_exist: tuple references non-existing key in pkeys
44+
QUERY: insert into fkeys values (10, '1', 2);
45+
QUERY: insert into fkeys values (30, '3', 3);
46+
QUERY: insert into fkeys values (40, '4', 2);
47+
QUERY: insert into fkeys values (50, '5', 2);
48+
QUERY: insert into fkeys values (70, '5', 1);
49+
WARN:check_fkeys_pkey_exist: tuple references non-existing key in pkeys
50+
QUERY: insert into fkeys values (60, '6', 4);
51+
WARN:check_fkeys_pkey2_exist: tuple references non-existing key in fkeys2
52+
QUERY: delete from pkeys where pkey1 = 30 and pkey2 = '3';
53+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
54+
WARN:check_fkeys2_fkey_restrict: tuple referenced in fkeys
55+
QUERY: delete from pkeys where pkey1 = 40 and pkey2 = '4';
56+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
57+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
58+
QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
59+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
60+
WARN:check_fkeys2_fkey_restrict: tuple referenced in fkeys
61+
QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
62+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
63+
NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
64+
QUERY: DROP TABLE pkeys;
65+
QUERY: DROP TABLE fkeys;
66+
QUERY: DROP TABLE fkeys2;
67+
QUERY: create table dup17 (x int4);
68+
QUERY: create trigger dup17_before
69+
before insert on dup17
70+
for each row
71+
execute procedure
72+
funny_dup17 ()
73+
;
74+
QUERY: insert into dup17 values (17);
75+
NOTICE:funny_dup17 (fired BEFORE) on level 1: 0/0 tuples inserted/selected
76+
QUERY: select count(*) from dup17;
77+
count
78+
-----
79+
1
80+
(1 row)
81+
82+
QUERY: insert into dup17 values (17);
83+
NOTICE:funny_dup17 (fired BEFORE) on level 17: 1/2 tuples inserted/selected
84+
NOTICE:funny_dup17 (fired BEFORE) on level 16: 1/3 tuples inserted/selected
85+
NOTICE:funny_dup17 (fired BEFORE) on level 15: 1/4 tuples inserted/selected
86+
NOTICE:funny_dup17 (fired BEFORE) on level 14: 1/5 tuples inserted/selected
87+
NOTICE:funny_dup17 (fired BEFORE) on level 13: 1/6 tuples inserted/selected
88+
NOTICE:funny_dup17 (fired BEFORE) on level 12: 1/7 tuples inserted/selected
89+
NOTICE:funny_dup17 (fired BEFORE) on level 11: 1/8 tuples inserted/selected
90+
NOTICE:funny_dup17 (fired BEFORE) on level 10: 1/9 tuples inserted/selected
91+
NOTICE:funny_dup17 (fired BEFORE) on level 9: 1/10 tuples inserted/selected
92+
NOTICE:funny_dup17 (fired BEFORE) on level 8: 1/11 tuples inserted/selected
93+
NOTICE:funny_dup17 (fired BEFORE) on level 7: 1/12 tuples inserted/selected
94+
NOTICE:funny_dup17 (fired BEFORE) on level 6: 1/13 tuples inserted/selected
95+
NOTICE:funny_dup17 (fired BEFORE) on level 5: 1/14 tuples inserted/selected
96+
NOTICE:funny_dup17 (fired BEFORE) on level 4: 1/15 tuples inserted/selected
97+
NOTICE:funny_dup17 (fired BEFORE) on level 3: 1/16 tuples inserted/selected
98+
NOTICE:funny_dup17 (fired BEFORE) on level 2: 1/17 tuples inserted/selected
99+
NOTICE:funny_dup17 (fired BEFORE) on level 1: 1/18 tuples inserted/selected
100+
QUERY: select count(*) from dup17;
101+
count
102+
-----
103+
19
104+
(1 row)
105+
106+
QUERY: drop trigger dup17_before on dup17;
107+
QUERY: create trigger dup17_after
108+
after insert on dup17
109+
for each row
110+
execute procedure
111+
funny_dup17 ()
112+
;
113+
QUERY: insert into dup17 values (13);
114+
NOTICE:funny_dup17 (fired AFTER ) on level 17: 17/34 tuples inserted/selected
115+
NOTICE:funny_dup17 (fired AFTER ) on level 16: 16/49 tuples inserted/selected
116+
NOTICE:funny_dup17 (fired AFTER ) on level 15: 15/63 tuples inserted/selected
117+
NOTICE:funny_dup17 (fired AFTER ) on level 14: 14/76 tuples inserted/selected
118+
NOTICE:funny_dup17 (fired AFTER ) on level 13: 13/88 tuples inserted/selected
119+
NOTICE:funny_dup17 (fired AFTER ) on level 12: 12/99 tuples inserted/selected
120+
NOTICE:funny_dup17 (fired AFTER ) on level 11: 11/109 tuples inserted/selected
121+
NOTICE:funny_dup17 (fired AFTER ) on level 10: 10/118 tuples inserted/selected
122+
NOTICE:funny_dup17 (fired AFTER ) on level 9: 9/126 tuples inserted/selected
123+
NOTICE:funny_dup17 (fired AFTER ) on level 8: 8/133 tuples inserted/selected
124+
NOTICE:funny_dup17 (fired AFTER ) on level 7: 7/139 tuples inserted/selected
125+
NOTICE:funny_dup17 (fired AFTER ) on level 6: 6/144 tuples inserted/selected
126+
NOTICE:funny_dup17 (fired AFTER ) on level 5: 5/148 tuples inserted/selected
127+
NOTICE:funny_dup17 (fired AFTER ) on level 4: 4/151 tuples inserted/selected
128+
NOTICE:funny_dup17 (fired AFTER ) on level 3: 3/153 tuples inserted/selected
129+
NOTICE:funny_dup17 (fired AFTER ) on level 2: 2/154 tuples inserted/selected
130+
NOTICE:funny_dup17 (fired AFTER ) on level 1: 1/154 tuples inserted/selected
131+
QUERY: select count(*) from dup17 where x = 13;
132+
count
133+
-----
134+
154
135+
(1 row)
136+
137+
QUERY: insert into dup17 values (13);
138+
NOTICE:funny_dup17 (fired AFTER ) on level 17: 171/342 tuples inserted/selected
139+
NOTICE:funny_dup17 (fired AFTER ) on level 16: 170/511 tuples inserted/selected
140+
NOTICE:funny_dup17 (fired AFTER ) on level 15: 169/679 tuples inserted/selected
141+
NOTICE:funny_dup17 (fired AFTER ) on level 14: 168/846 tuples inserted/selected
142+
NOTICE:funny_dup17 (fired AFTER ) on level 13: 167/1012 tuples inserted/selected
143+
NOTICE:funny_dup17 (fired AFTER ) on level 12: 166/1177 tuples inserted/selected
144+
NOTICE:funny_dup17 (fired AFTER ) on level 11: 165/1341 tuples inserted/selected
145+
NOTICE:funny_dup17 (fired AFTER ) on level 10: 164/1504 tuples inserted/selected
146+
NOTICE:funny_dup17 (fired AFTER ) on level 9: 163/1666 tuples inserted/selected
147+
NOTICE:funny_dup17 (fired AFTER ) on level 8: 162/1827 tuples inserted/selected
148+
NOTICE:funny_dup17 (fired AFTER ) on level 7: 161/1987 tuples inserted/selected
149+
NOTICE:funny_dup17 (fired AFTER ) on level 6: 160/2146 tuples inserted/selected
150+
NOTICE:funny_dup17 (fired AFTER ) on level 5: 159/2304 tuples inserted/selected
151+
NOTICE:funny_dup17 (fired AFTER ) on level 4: 158/2461 tuples inserted/selected
152+
NOTICE:funny_dup17 (fired AFTER ) on level 3: 157/2617 tuples inserted/selected
153+
NOTICE:funny_dup17 (fired AFTER ) on level 2: 156/2772 tuples inserted/selected
154+
NOTICE:funny_dup17 (fired AFTER ) on level 1: 155/2926 tuples inserted/selected
155+
QUERY: select count(*) from dup17 where x = 13;
156+
count
157+
-----
158+
2926
159+
(1 row)
160+
161+
QUERY: DROP TABLE dup17;

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,3 +13,20 @@ CREATE FUNCTION widget_out(opaque)
1313
AS '_OBJWD_/regress_DLSUFFIX_'
1414
LANGUAGE 'c';
1515

16+
CREATE FUNCTION check_primary_key ()
17+
RETURNS opaque
18+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
19+
LANGUAGE 'c'
20+
;
21+
22+
CREATE FUNCTION check_foreign_key ()
23+
RETURNS opaque
24+
AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_'
25+
LANGUAGE 'c'
26+
;
27+
28+
CREATE FUNCTION funny_dup17 ()
29+
RETURNS opaque
30+
AS '_OBJWD_/regress_DLSUFFIX_'
31+
LANGUAGE 'c'
32+
;

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,3 +7,18 @@ QUERY: CREATE FUNCTION widget_out(opaque)
77
RETURNS opaque
88
AS '_OBJWD_/regress_DLSUFFIX_'
99
LANGUAGE 'c';
10+
QUERY: CREATE FUNCTION check_primary_key ()
11+
RETURNS opaque
12+
AS '_OBJWD_/../../../contrib/spi/refint.so'
13+
LANGUAGE 'c'
14+
;
15+
QUERY: CREATE FUNCTION check_foreign_key ()
16+
RETURNS opaque
17+
AS '_OBJWD_/../../../contrib/spi/refint.so'
18+
LANGUAGE 'c'
19+
;
20+
QUERY: CREATE FUNCTION funny_dup17 ()
21+
RETURNS opaque
22+
AS '_OBJWD_/regress.so'
23+
LANGUAGE 'c'
24+
;

‎src/test/regress/regress.c

Lines changed: 114 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $Header: /cvsroot/pgsql/src/test/regress/regress.c,v 1.13 1997/09/08 21:56:01 momjian Exp $
2+
* $Header: /cvsroot/pgsql/src/test/regress/regress.c,v 1.14 1997/09/11 09:13:28 vadim Exp $
33
*/
44

55
#include <float.h> /* faked on sunos */
@@ -298,3 +298,116 @@ char *string;
298298
new_string[len - i] = string[i];
299299
return (new_string);
300300
}
301+
302+
#include "executor/spi.h" /* this is what you need to work with SPI */
303+
#include "commands/trigger.h" /* -"- and triggers */
304+
305+
static TransactionId fd17b_xid = InvalidTransactionId;
306+
static TransactionId fd17a_xid = InvalidTransactionId;
307+
static int fd17b_level = 0;
308+
static int fd17a_level = 0;
309+
static bool fd17b_recursion = true;
310+
static bool fd17a_recursion = true;
311+
HeapTuple funny_dup17(void);
312+
313+
HeapTuple /* have to return HeapTuple to Executor */
314+
funny_dup17()
315+
{
316+
TransactionId *xid;
317+
int *level;
318+
bool *recursion;
319+
Relation rel;
320+
TupleDesc tupdesc;
321+
HeapTuple tuple;
322+
char sql[8192];
323+
char *when;
324+
int inserted;
325+
int selected = 0;
326+
int ret;
327+
328+
tuple = CurrentTriggerData->tg_trigtuple;
329+
rel = CurrentTriggerData->tg_relation;
330+
tupdesc = rel->rd_att;
331+
if (TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event))
332+
{
333+
xid = &fd17b_xid;
334+
level = &fd17b_level;
335+
recursion = &fd17b_recursion;
336+
when = "BEFORE";
337+
}
338+
else
339+
{
340+
xid = &fd17a_xid;
341+
level = &fd17a_level;
342+
recursion = &fd17a_recursion;
343+
when = "AFTER ";
344+
}
345+
346+
CurrentTriggerData = NULL;
347+
348+
if (!TransactionIdIsCurrentTransactionId(*xid))
349+
{
350+
*xid = GetCurrentTransactionId();
351+
*level = 0;
352+
*recursion = true;
353+
}
354+
355+
if (*level == 17)
356+
{
357+
*recursion = false;
358+
return (tuple);
359+
}
360+
361+
if (!(*recursion))
362+
return (tuple);
363+
364+
(*level)++;
365+
366+
SPI_connect();
367+
368+
sprintf(sql, "insert into %s select * from %s where %s = '%s'::%s",
369+
SPI_getrelname(rel), SPI_getrelname(rel),
370+
SPI_fname(tupdesc, 1),
371+
SPI_getvalue(tuple, tupdesc, 1),
372+
SPI_gettype(tupdesc, 1));
373+
374+
if ((ret = SPI_exec(sql, 0)) < 0)
375+
elog(WARN, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d",
376+
when, *level, ret);
377+
378+
inserted = SPI_processed;
379+
380+
sprintf(sql, "select count (*) from %s where %s = '%s'::%s",
381+
SPI_getrelname(rel),
382+
SPI_fname(tupdesc, 1),
383+
SPI_getvalue(tuple, tupdesc, 1),
384+
SPI_gettype(tupdesc, 1));
385+
386+
if ((ret = SPI_exec(sql, 0)) < 0)
387+
elog(WARN, "funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d",
388+
when, *level, ret);
389+
390+
if (SPI_processed > 0)
391+
{
392+
selected =
393+
int4in(
394+
SPI_getvalue(
395+
SPI_tuptable->vals[0],
396+
SPI_tuptable->tupdesc,
397+
1
398+
)
399+
);
400+
}
401+
402+
elog(NOTICE, "funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected",
403+
when, *level, inserted, selected);
404+
405+
SPI_finish();
406+
407+
(*level)--;
408+
409+
if (*level == 0)
410+
*xid = InvalidTransactionId;
411+
412+
return (tuple);
413+
}

‎src/test/regress/sql/tests

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ create_type
3232
create_table
3333
create_function_2
3434
constraints
35+
triggers
3536
copy
3637
create_misc
3738
create_aggregate

0 commit comments

Comments
 (0)