From ba247bd649cdee7c0b22f84b95deb7a7c0b3bb3c Mon Sep 17 00:00:00 2001 From: "Vadim B. Mikheev" Date: Thu, 11 Sep 1997 09:14:12 +0000 Subject: [PATCH] Tests for spi/triggers --- src/test/regress/GNUmakefile | 3 +- src/test/regress/expected/triggers.out | 161 ++++++++++++++++++ .../regress/input/create_function_1.source | 17 ++ .../regress/output/create_function_1.source | 15 ++ src/test/regress/regress.c | 115 ++++++++++++- src/test/regress/sql/tests | 1 + src/test/regress/sql/triggers.sql | 118 +++++++++++++ 7 files changed, 428 insertions(+), 2 deletions(-) create mode 100644 src/test/regress/expected/triggers.out create mode 100644 src/test/regress/sql/triggers.sql diff --git a/src/test/regress/GNUmakefile b/src/test/regress/GNUmakefile index 6cab363a6e..a4904b4119 100644 --- a/src/test/regress/GNUmakefile +++ b/src/test/regress/GNUmakefile @@ -7,7 +7,7 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.9 1997/07/29 14:09:11 momjian Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/GNUmakefile,v 1.10 1997/09/11 09:13:27 vadim Exp $ # #------------------------------------------------------------------------- @@ -43,6 +43,7 @@ endif all: $(INFILES) cd input; $(MAKE) all; cd .. cd output; $(MAKE) all; cd .. + $(MAKE) -C ../../../contrib/spi REFINT_VERBOSE=1 # # run the test diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out new file mode 100644 index 0000000000..158cb42767 --- /dev/null +++ b/src/test/regress/expected/triggers.out @@ -0,0 +1,161 @@ +QUERY: create table pkeys (pkey1 int4 not null, pkey2 text not null); +QUERY: create table fkeys (fkey1 int4, fkey2 text, fkey3 int); +QUERY: create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null); +QUERY: create index fkeys_i on fkeys (fkey1, fkey2); +QUERY: create index fkeys2_i on fkeys2 (fkey21, fkey22); +QUERY: create index fkeys2p_i on fkeys2 (pkey23); +QUERY: insert into pkeys values (10, '1'); +QUERY: insert into pkeys values (20, '2'); +QUERY: insert into pkeys values (30, '3'); +QUERY: insert into pkeys values (40, '4'); +QUERY: insert into pkeys values (50, '5'); +QUERY: insert into pkeys values (60, '6'); +QUERY: create unique index pkeys_i on pkeys (pkey1, pkey2); +QUERY: create trigger check_fkeys_pkey_exist + before insert or update on fkeys + for each row + execute procedure + check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); +QUERY: create trigger check_fkeys_pkey2_exist + before insert or update on fkeys + for each row + execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23'); +QUERY: create trigger check_fkeys2_pkey_exist + before insert or update on fkeys2 + for each row + execute procedure + check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); +QUERY: create trigger check_pkeys_fkey_cascade + before delete or update on pkeys + for each row + execute procedure + check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', + 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22'); +QUERY: create trigger check_fkeys2_fkey_restrict + before delete or update on fkeys2 + for each row + execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); +QUERY: insert into fkeys2 values (10, '1', 1); +QUERY: insert into fkeys2 values (30, '3', 2); +QUERY: insert into fkeys2 values (40, '4', 5); +QUERY: insert into fkeys2 values (50, '5', 3); +QUERY: insert into fkeys2 values (70, '5', 3); +WARN:check_fkeys2_pkey_exist: tuple references non-existing key in pkeys +QUERY: insert into fkeys values (10, '1', 2); +QUERY: insert into fkeys values (30, '3', 3); +QUERY: insert into fkeys values (40, '4', 2); +QUERY: insert into fkeys values (50, '5', 2); +QUERY: insert into fkeys values (70, '5', 1); +WARN:check_fkeys_pkey_exist: tuple references non-existing key in pkeys +QUERY: insert into fkeys values (60, '6', 4); +WARN:check_fkeys_pkey2_exist: tuple references non-existing key in fkeys2 +QUERY: delete from pkeys where pkey1 = 30 and pkey2 = '3'; +NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +WARN:check_fkeys2_fkey_restrict: tuple referenced in fkeys +QUERY: delete from pkeys where pkey1 = 40 and pkey2 = '4'; +NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted +QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; +NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +WARN:check_fkeys2_fkey_restrict: tuple referenced in fkeys +QUERY: update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; +NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted +NOTICE:check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted +QUERY: DROP TABLE pkeys; +QUERY: DROP TABLE fkeys; +QUERY: DROP TABLE fkeys2; +QUERY: create table dup17 (x int4); +QUERY: create trigger dup17_before + before insert on dup17 + for each row + execute procedure + funny_dup17 () +; +QUERY: insert into dup17 values (17); +NOTICE:funny_dup17 (fired BEFORE) on level 1: 0/0 tuples inserted/selected +QUERY: select count(*) from dup17; +count +----- + 1 +(1 row) + +QUERY: insert into dup17 values (17); +NOTICE:funny_dup17 (fired BEFORE) on level 17: 1/2 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 16: 1/3 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 15: 1/4 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 14: 1/5 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 13: 1/6 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 12: 1/7 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 11: 1/8 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 10: 1/9 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 9: 1/10 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 8: 1/11 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 7: 1/12 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 6: 1/13 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 5: 1/14 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 4: 1/15 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 3: 1/16 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 2: 1/17 tuples inserted/selected +NOTICE:funny_dup17 (fired BEFORE) on level 1: 1/18 tuples inserted/selected +QUERY: select count(*) from dup17; +count +----- + 19 +(1 row) + +QUERY: drop trigger dup17_before on dup17; +QUERY: create trigger dup17_after + after insert on dup17 + for each row + execute procedure + funny_dup17 () +; +QUERY: insert into dup17 values (13); +NOTICE:funny_dup17 (fired AFTER ) on level 17: 17/34 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 16: 16/49 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 15: 15/63 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 14: 14/76 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 13: 13/88 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 12: 12/99 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 11: 11/109 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 10: 10/118 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 9: 9/126 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 8: 8/133 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 7: 7/139 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 6: 6/144 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 5: 5/148 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 4: 4/151 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 3: 3/153 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 2: 2/154 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 1: 1/154 tuples inserted/selected +QUERY: select count(*) from dup17 where x = 13; +count +----- + 154 +(1 row) + +QUERY: insert into dup17 values (13); +NOTICE:funny_dup17 (fired AFTER ) on level 17: 171/342 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 16: 170/511 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 15: 169/679 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 14: 168/846 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 13: 167/1012 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 12: 166/1177 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 11: 165/1341 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 10: 164/1504 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 9: 163/1666 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 8: 162/1827 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 7: 161/1987 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 6: 160/2146 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 5: 159/2304 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 4: 158/2461 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 3: 157/2617 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 2: 156/2772 tuples inserted/selected +NOTICE:funny_dup17 (fired AFTER ) on level 1: 155/2926 tuples inserted/selected +QUERY: select count(*) from dup17 where x = 13; +count +----- + 2926 +(1 row) + +QUERY: DROP TABLE dup17; diff --git a/src/test/regress/input/create_function_1.source b/src/test/regress/input/create_function_1.source index 88c4127f7b..0031414fac 100644 --- a/src/test/regress/input/create_function_1.source +++ b/src/test/regress/input/create_function_1.source @@ -13,3 +13,20 @@ CREATE FUNCTION widget_out(opaque) AS '_OBJWD_/regress_DLSUFFIX_' LANGUAGE 'c'; +CREATE FUNCTION check_primary_key () + RETURNS opaque + AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_' + LANGUAGE 'c' +; + +CREATE FUNCTION check_foreign_key () + RETURNS opaque + AS '_OBJWD_/../../../contrib/spi/refint_DLSUFFIX_' + LANGUAGE 'c' +; + +CREATE FUNCTION funny_dup17 () + RETURNS opaque + AS '_OBJWD_/regress_DLSUFFIX_' + LANGUAGE 'c' +; diff --git a/src/test/regress/output/create_function_1.source b/src/test/regress/output/create_function_1.source index 94bc297157..65d3dd58f2 100644 --- a/src/test/regress/output/create_function_1.source +++ b/src/test/regress/output/create_function_1.source @@ -7,3 +7,18 @@ QUERY: CREATE FUNCTION widget_out(opaque) RETURNS opaque AS '_OBJWD_/regress_DLSUFFIX_' LANGUAGE 'c'; +QUERY: CREATE FUNCTION check_primary_key () + RETURNS opaque + AS '_OBJWD_/../../../contrib/spi/refint.so' + LANGUAGE 'c' +; +QUERY: CREATE FUNCTION check_foreign_key () + RETURNS opaque + AS '_OBJWD_/../../../contrib/spi/refint.so' + LANGUAGE 'c' +; +QUERY: CREATE FUNCTION funny_dup17 () + RETURNS opaque + AS '_OBJWD_/regress.so' + LANGUAGE 'c' +; diff --git a/src/test/regress/regress.c b/src/test/regress/regress.c index 020bf6685e..85af5dad69 100644 --- a/src/test/regress/regress.c +++ b/src/test/regress/regress.c @@ -1,5 +1,5 @@ /* - * $Header: /cvsroot/pgsql/src/test/regress/regress.c,v 1.13 1997/09/08 21:56:01 momjian Exp $ + * $Header: /cvsroot/pgsql/src/test/regress/regress.c,v 1.14 1997/09/11 09:13:28 vadim Exp $ */ #include /* faked on sunos */ @@ -298,3 +298,116 @@ char *string; new_string[len - i] = string[i]; return (new_string); } + +#include "executor/spi.h" /* this is what you need to work with SPI */ +#include "commands/trigger.h" /* -"- and triggers */ + +static TransactionId fd17b_xid = InvalidTransactionId; +static TransactionId fd17a_xid = InvalidTransactionId; +static int fd17b_level = 0; +static int fd17a_level = 0; +static bool fd17b_recursion = true; +static bool fd17a_recursion = true; +HeapTuple funny_dup17(void); + +HeapTuple /* have to return HeapTuple to Executor */ +funny_dup17() +{ + TransactionId *xid; + int *level; + bool *recursion; + Relation rel; + TupleDesc tupdesc; + HeapTuple tuple; + char sql[8192]; + char *when; + int inserted; + int selected = 0; + int ret; + + tuple = CurrentTriggerData->tg_trigtuple; + rel = CurrentTriggerData->tg_relation; + tupdesc = rel->rd_att; + if (TRIGGER_FIRED_BEFORE(CurrentTriggerData->tg_event)) + { + xid = &fd17b_xid; + level = &fd17b_level; + recursion = &fd17b_recursion; + when = "BEFORE"; + } + else + { + xid = &fd17a_xid; + level = &fd17a_level; + recursion = &fd17a_recursion; + when = "AFTER "; + } + + CurrentTriggerData = NULL; + + if (!TransactionIdIsCurrentTransactionId(*xid)) + { + *xid = GetCurrentTransactionId(); + *level = 0; + *recursion = true; + } + + if (*level == 17) + { + *recursion = false; + return (tuple); + } + + if (!(*recursion)) + return (tuple); + + (*level)++; + + SPI_connect(); + + sprintf(sql, "insert into %s select * from %s where %s = '%s'::%s", + SPI_getrelname(rel), SPI_getrelname(rel), + SPI_fname(tupdesc, 1), + SPI_getvalue(tuple, tupdesc, 1), + SPI_gettype(tupdesc, 1)); + + if ((ret = SPI_exec(sql, 0)) < 0) + elog(WARN, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d", + when, *level, ret); + + inserted = SPI_processed; + + sprintf(sql, "select count (*) from %s where %s = '%s'::%s", + SPI_getrelname(rel), + SPI_fname(tupdesc, 1), + SPI_getvalue(tuple, tupdesc, 1), + SPI_gettype(tupdesc, 1)); + + if ((ret = SPI_exec(sql, 0)) < 0) + elog(WARN, "funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d", + when, *level, ret); + + if (SPI_processed > 0) + { + selected = + int4in( + SPI_getvalue( + SPI_tuptable->vals[0], + SPI_tuptable->tupdesc, + 1 + ) + ); + } + + elog(NOTICE, "funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected", + when, *level, inserted, selected); + + SPI_finish(); + + (*level)--; + + if (*level == 0) + *xid = InvalidTransactionId; + + return (tuple); +} diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests index b2c3ddef55..30d2667625 100644 --- a/src/test/regress/sql/tests +++ b/src/test/regress/sql/tests @@ -32,6 +32,7 @@ create_type create_table create_function_2 constraints +triggers copy create_misc create_aggregate diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql new file mode 100644 index 0000000000..08fc689468 --- /dev/null +++ b/src/test/regress/sql/triggers.sql @@ -0,0 +1,118 @@ + +create table pkeys (pkey1 int4 not null, pkey2 text not null); +create table fkeys (fkey1 int4, fkey2 text, fkey3 int); +create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null); + +create index fkeys_i on fkeys (fkey1, fkey2); +create index fkeys2_i on fkeys2 (fkey21, fkey22); +create index fkeys2p_i on fkeys2 (pkey23); + +insert into pkeys values (10, '1'); +insert into pkeys values (20, '2'); +insert into pkeys values (30, '3'); +insert into pkeys values (40, '4'); +insert into pkeys values (50, '5'); +insert into pkeys values (60, '6'); +create unique index pkeys_i on pkeys (pkey1, pkey2); + +-- +-- For fkeys: +-- (fkey1, fkey2) --> pkeys (pkey1, pkey2) +-- (fkey3) --> fkeys2 (pkey23) +-- +create trigger check_fkeys_pkey_exist + before insert or update on fkeys + for each row + execute procedure + check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); + +create trigger check_fkeys_pkey2_exist + before insert or update on fkeys + for each row + execute procedure check_primary_key ('fkey3', 'fkeys2', 'pkey23'); + +-- +-- For fkeys2: +-- (fkey21, fkey22) --> pkeys (pkey1, pkey2) +-- +create trigger check_fkeys2_pkey_exist + before insert or update on fkeys2 + for each row + execute procedure + check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2'); + +-- +-- For pkeys: +-- ON DELETE/UPDATE (pkey1, pkey2) CASCADE: +-- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22) +-- +create trigger check_pkeys_fkey_cascade + before delete or update on pkeys + for each row + execute procedure + check_foreign_key (2, 'cascade', 'pkey1', 'pkey2', + 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22'); + +-- +-- For fkeys2: +-- ON DELETE/UPDATE (pkey23) RESTRICT: +-- fkeys (fkey3) +-- +create trigger check_fkeys2_fkey_restrict + before delete or update on fkeys2 + for each row + execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3'); + +insert into fkeys2 values (10, '1', 1); +insert into fkeys2 values (30, '3', 2); +insert into fkeys2 values (40, '4', 5); +insert into fkeys2 values (50, '5', 3); +-- no key in pkeys +insert into fkeys2 values (70, '5', 3); + +insert into fkeys values (10, '1', 2); +insert into fkeys values (30, '3', 3); +insert into fkeys values (40, '4', 2); +insert into fkeys values (50, '5', 2); +-- no key in pkeys +insert into fkeys values (70, '5', 1); +-- no key in fkeys2 +insert into fkeys values (60, '6', 4); + +delete from pkeys where pkey1 = 30 and pkey2 = '3'; +delete from pkeys where pkey1 = 40 and pkey2 = '4'; +update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5'; +update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1'; + +DROP TABLE pkeys; +DROP TABLE fkeys; +DROP TABLE fkeys2; + +create table dup17 (x int4); + +create trigger dup17_before + before insert on dup17 + for each row + execute procedure + funny_dup17 () +; + +insert into dup17 values (17); +select count(*) from dup17; +insert into dup17 values (17); +select count(*) from dup17; + +drop trigger dup17_before on dup17; + +create trigger dup17_after + after insert on dup17 + for each row + execute procedure + funny_dup17 () +; +insert into dup17 values (13); +select count(*) from dup17 where x = 13; +insert into dup17 values (13); +select count(*) from dup17 where x = 13; + +DROP TABLE dup17; -- 2.40.0