Move tests of contrib/spi/ out of the core regression tests - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Move tests of contrib/spi/ out of the core regression tests |
| Date | |
| Msg-id | 3872677.1744077559@sss.pgh.pa.us Whole thread Raw |
| Responses |
Re: Move tests of contrib/spi/ out of the core regression tests
|
| List | pgsql-hackers |
The attached patch removes test cases concerned with contrib/spi/
from the core regression tests and instead puts them into new
test files in contrib/spi/ itself.
My original motivation for looking at this was the discussion in
[1] about whether to remove contrib/spi/refint.c entirely, since
it's rather buggy and not a great example of our modern coding
practices. So I wondered whether the core test cases that use it
were contributing any significant amount of code coverage on the
core code. (Spoiler: nope.) But I think this is generally good
cleanup anyway, because it locates the test code for contrib/spi
where a person would expect to find that, and removes some rather
grotty coding in src/test/regress's Makefile and meson.build.
As a side benefit, it removes some small number of cycles from
the core tests, which seems like a good thing.
The tests for the refint module are just moved over verbatim,
except for using CREATE EXTENSION instead of manual declaration
of the C functions. Also, I kept the tests for COMMENT ON TRIGGER
in the core tests, by applying them to a different trigger.
The tests for autoinc were kind of messy, because the behavior of
autoinc itself was impossibly intertwined with the behavior of
"ttdummy", which is an undocumented C function in regress.c.
After some thought I decided we could just nuke ttdummy altogether,
so the new autoinc.sql test is much simpler and more straightforward.
(I realized while doing this that the description of autoinc in
the SGML docs is not a great description of what the function
actually does, so the patch includes some updates to those docs.)
So far as I can tell, the code coverage of the core regression
tests is unchanged by this patch: the removed test cases were
100% redundant with other cases, so far as the core is concerned.
This is too late for v18 of course, so I'll park it in the next CF.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/79755a2b18ed4fe5e29da6a87a1e00d1%40postgrespro.ru
diff --git a/contrib/spi/.gitignore b/contrib/spi/.gitignore
new file mode 100644
index 00000000000..5dcb3ff9723
--- /dev/null
+++ b/contrib/spi/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile
index c9c34ff3889..7ccbef8c926 100644
--- a/contrib/spi/Makefile
+++ b/contrib/spi/Makefile
@@ -10,6 +10,8 @@ DATA = autoinc--1.0.sql \
refint--1.0.sql
PGFILEDESC = "spi - examples of using SPI and triggers"
+REGRESS = autoinc refint
+
DOCS = $(addsuffix .example, $(MODULES))
# this is needed for the regression tests;
diff --git a/contrib/spi/expected/autoinc.out b/contrib/spi/expected/autoinc.out
new file mode 100644
index 00000000000..07ae8ad1f2b
--- /dev/null
+++ b/contrib/spi/expected/autoinc.out
@@ -0,0 +1,50 @@
+CREATE EXTENSION autoinc;
+create sequence aitest_seq increment 10 start 0 minvalue 0;
+create table aitest (
+ price_id int4,
+ price_val int4,
+ price_on int4
+);
+create trigger aiserial
+ before insert or update on aitest
+ for each row
+ execute procedure
+ autoinc (price_on, aitest_seq);
+insert into aitest values (1, 1, null);
+insert into aitest values (2, 2, 0);
+insert into aitest values (3, 3, 1);
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 10
+ 2 | 2 | 20
+ 3 | 3 | 1
+(3 rows)
+
+update aitest set price_on = 11;
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 11
+ 2 | 2 | 11
+ 3 | 3 | 11
+(3 rows)
+
+update aitest set price_on = 0;
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 30
+ 2 | 2 | 40
+ 3 | 3 | 50
+(3 rows)
+
+update aitest set price_on = null;
+select * from aitest;
+ price_id | price_val | price_on
+----------+-----------+----------
+ 1 | 1 | 60
+ 2 | 2 | 70
+ 3 | 3 | 80
+(3 rows)
+
diff --git a/contrib/spi/expected/refint.out b/contrib/spi/expected/refint.out
new file mode 100644
index 00000000000..79633603217
--- /dev/null
+++ b/contrib/spi/expected/refint.out
@@ -0,0 +1,113 @@
+CREATE EXTENSION refint;
+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
+ after insert or update on fkeys
+ for each row
+ execute function
+ check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
+create trigger check_fkeys_pkey2_exist
+ after insert or update on fkeys
+ for each row
+ execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
+--
+-- For fkeys2:
+-- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
+--
+create trigger check_fkeys2_pkey_exist
+ after 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
+ after 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
+ after 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);
+ERROR: tuple references non-existent key
+DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
+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);
+ERROR: tuple references non-existent key
+DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
+-- no key in fkeys2
+insert into fkeys values (60, '6', 4);
+ERROR: tuple references non-existent key
+DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
+delete from pkeys where pkey1 = 30 and pkey2 = '3';
+NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
+ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
+CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
+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
+update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
+NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
+NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
+update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
+ERROR: duplicate key value violates unique constraint "pkeys_i"
+DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
+SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers
+ WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
+ ORDER BY trigger_name COLLATE "C", 2;
+ trigger_name | event_manipulation | event_object_schema | event_object_table | action_order |
action_condition| action_orientation | action_timing | action_reference_old_table | action_reference_new_table
+----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
+ check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 |
| ROW | AFTER | |
+ check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 |
| ROW | AFTER | |
+ check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 |
| ROW | AFTER | |
+ check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 |
| ROW | AFTER | |
+ check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 |
| ROW | AFTER | |
+ check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 |
| ROW | AFTER | |
+ check_fkeys_pkey_exist | INSERT | public | fkeys | 2 |
| ROW | AFTER | |
+ check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 |
| ROW | AFTER | |
+ check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 |
| ROW | AFTER | |
+ check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 |
| ROW | AFTER | |
+(10 rows)
+
+DROP TABLE pkeys;
+DROP TABLE fkeys;
+DROP TABLE fkeys2;
diff --git a/contrib/spi/meson.build b/contrib/spi/meson.build
index eeab1ab210b..3832a91019a 100644
--- a/contrib/spi/meson.build
+++ b/contrib/spi/meson.build
@@ -107,3 +107,15 @@ install_data('refint.control', 'refint--1.0.sql',
install_data('refint.example',
kwargs: contrib_doc_args,
)
+
+tests += {
+ 'name': 'spi',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'regress': {
+ 'sql': [
+ 'autoinc',
+ 'refint',
+ ],
+ },
+}
diff --git a/contrib/spi/sql/autoinc.sql b/contrib/spi/sql/autoinc.sql
new file mode 100644
index 00000000000..b240dcdc082
--- /dev/null
+++ b/contrib/spi/sql/autoinc.sql
@@ -0,0 +1,33 @@
+CREATE EXTENSION autoinc;
+
+create sequence aitest_seq increment 10 start 0 minvalue 0;
+
+create table aitest (
+ price_id int4,
+ price_val int4,
+ price_on int4
+);
+
+create trigger aiserial
+ before insert or update on aitest
+ for each row
+ execute procedure
+ autoinc (price_on, aitest_seq);
+
+insert into aitest values (1, 1, null);
+insert into aitest values (2, 2, 0);
+insert into aitest values (3, 3, 1);
+
+select * from aitest;
+
+update aitest set price_on = 11;
+
+select * from aitest;
+
+update aitest set price_on = 0;
+
+select * from aitest;
+
+update aitest set price_on = null;
+
+select * from aitest;
diff --git a/contrib/spi/sql/refint.sql b/contrib/spi/sql/refint.sql
new file mode 100644
index 00000000000..63458127917
--- /dev/null
+++ b/contrib/spi/sql/refint.sql
@@ -0,0 +1,97 @@
+CREATE EXTENSION refint;
+
+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
+ after insert or update on fkeys
+ for each row
+ execute function
+ check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
+
+create trigger check_fkeys_pkey2_exist
+ after insert or update on fkeys
+ for each row
+ execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
+
+--
+-- For fkeys2:
+-- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
+--
+create trigger check_fkeys2_pkey_exist
+ after 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
+ after 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
+ after 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';
+
+SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
+ action_order, action_condition, action_orientation, action_timing,
+ action_reference_old_table, action_reference_new_table
+ FROM information_schema.triggers
+ WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
+ ORDER BY trigger_name COLLATE "C", 2;
+
+DROP TABLE pkeys;
+DROP TABLE fkeys;
+DROP TABLE fkeys2;
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 55d3fac7a69..6fa9479d1b9 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -81,10 +81,12 @@
<para>
<function>autoinc()</function> is a trigger that stores the next value of
a sequence into an integer field. This has some overlap with the
- built-in <quote>serial column</quote> feature, but it is not the same:
- <function>autoinc()</function> will override attempts to substitute a
- different field value during inserts, and optionally it can be
- used to increment the field during updates, too.
+ built-in <quote>serial column</quote> feature, but it is not the same.
+ The trigger will replace the field's value only if that value is
+ initially zero or null (after the action of the SQL statement that
+ inserted or updated the row). Also, if the sequence's next value is
+ zero, <function>nextval()</function> will be called a second time in
+ order to obtain a non-zero value.
</para>
<para>
diff --git a/src/test/regress/GNUmakefile b/src/test/regress/GNUmakefile
index 523be640424..ef2bddf42ca 100644
--- a/src/test/regress/GNUmakefile
+++ b/src/test/regress/GNUmakefile
@@ -87,26 +87,6 @@ installdirs-tests: installdirs
$(MKDIR_P) $(patsubst $(srcdir)/%/,'$(DESTDIR)$(pkglibdir)/regress/%',$(sort $(dir $(regress_data_files))))
-# Get some extra C modules from contrib/spi
-
-all: refint$(DLSUFFIX) autoinc$(DLSUFFIX)
-
-refint$(DLSUFFIX): $(top_builddir)/contrib/spi/refint$(DLSUFFIX)
- cp $< $@
-
-autoinc$(DLSUFFIX): $(top_builddir)/contrib/spi/autoinc$(DLSUFFIX)
- cp $< $@
-
-$(top_builddir)/contrib/spi/refint$(DLSUFFIX): | submake-contrib-spi ;
-
-$(top_builddir)/contrib/spi/autoinc$(DLSUFFIX): | submake-contrib-spi ;
-
-submake-contrib-spi: | submake-libpgport submake-generated-headers
- $(MAKE) -C $(top_builddir)/contrib/spi
-
-.PHONY: submake-contrib-spi
-
-
##
## Run tests
##
@@ -148,7 +128,7 @@ bigcheck: all | temp-install
clean distclean: clean-lib
# things built by `all' target
- rm -f $(OBJS) refint$(DLSUFFIX) autoinc$(DLSUFFIX)
+ rm -f $(OBJS)
rm -f pg_regress_main.o pg_regress.o pg_regress$(X)
# things created by various check targets
rm -rf $(pg_regress_clean_files)
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 8a44321034b..476266e3f4b 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2898,6 +2898,8 @@ select * from my_locks order by 1;
rollback;
begin;
+create function ttdummy () returns trigger language plpgsql as
+$$ begin return new; end $$;
create trigger ttdummy
before delete or update on alterlock
for each row
diff --git a/src/test/regress/expected/test_setup.out b/src/test/regress/expected/test_setup.out
index 3d0eeec9960..93a4c2691c1 100644
--- a/src/test/regress/expected/test_setup.out
+++ b/src/test/regress/expected/test_setup.out
@@ -205,10 +205,6 @@ CREATE FUNCTION binary_coercible(oid, oid)
RETURNS bool
AS :'regresslib', 'binary_coercible'
LANGUAGE C STRICT STABLE PARALLEL SAFE;
-CREATE FUNCTION ttdummy ()
- RETURNS trigger
- AS :'regresslib'
- LANGUAGE C;
-- Use hand-rolled hash functions and operator classes to get predictable
-- result on different machines. The hash function for int4 simply returns
-- the sum of the values passed to it and the one for text returns the length
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index e6f585d9740..c598dc78518 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -4,146 +4,11 @@
-- directory paths and dlsuffix are passed to us in environment variables
\getenv libdir PG_LIBDIR
\getenv dlsuffix PG_DLSUFFIX
-\set autoinclib :libdir '/autoinc' :dlsuffix
-\set refintlib :libdir '/refint' :dlsuffix
\set regresslib :libdir '/regress' :dlsuffix
-CREATE FUNCTION autoinc ()
- RETURNS trigger
- AS :'autoinclib'
- LANGUAGE C;
-CREATE FUNCTION check_primary_key ()
- RETURNS trigger
- AS :'refintlib'
- LANGUAGE C;
-CREATE FUNCTION check_foreign_key ()
- RETURNS trigger
- AS :'refintlib'
- LANGUAGE C;
CREATE FUNCTION trigger_return_old ()
RETURNS trigger
AS :'regresslib'
LANGUAGE C;
-CREATE FUNCTION set_ttdummy (int4)
- RETURNS int4
- AS :'regresslib'
- LANGUAGE C STRICT;
-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
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
--- Test comments
-COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong';
-ERROR: trigger "check_fkeys2_pkey_bad" for table "fkeys2" does not exist
-COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right';
-COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL;
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after 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
- after 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);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
-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);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
-CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
-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
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-ERROR: duplicate key value violates unique constraint "pkeys_i"
-DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
- trigger_name | event_manipulation | event_object_schema | event_object_table | action_order |
action_condition| action_orientation | action_timing | action_reference_old_table | action_reference_new_table
-----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
- check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 |
| ROW | AFTER | |
- check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 |
| ROW | AFTER | |
- check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 |
| ROW | AFTER | |
- check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 |
| ROW | AFTER | |
- check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 |
| ROW | AFTER | |
- check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 |
| ROW | AFTER | |
- check_fkeys_pkey_exist | INSERT | public | fkeys | 2 |
| ROW | AFTER | |
- check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 |
| ROW | AFTER | |
- check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 |
| ROW | AFTER | |
- check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 |
| ROW | AFTER | |
-(10 rows)
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
-- Check behavior when trigger returns unmodified trigtuple
create table trigtest (f1 int, f2 text);
create trigger trigger_return_old
@@ -294,143 +159,6 @@ select * from trigtest;
(1 row)
drop table trigtest;
-create sequence ttdummy_seq increment 10 start 0 minvalue 0;
-create table tttest (
- price_id int4,
- price_val int4,
- price_on int4,
- price_off int4 default 999999
-);
-create trigger ttdummy
- before delete or update on tttest
- for each row
- execute procedure
- ttdummy (price_on, price_off);
-create trigger ttserial
- before insert or update on tttest
- for each row
- execute procedure
- autoinc (price_on, ttdummy_seq);
-insert into tttest values (1, 1, null);
-insert into tttest values (2, 2, null);
-insert into tttest values (3, 3, 0);
-select * from tttest;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 1 | 1 | 10 | 999999
- 2 | 2 | 20 | 999999
- 3 | 3 | 30 | 999999
-(3 rows)
-
-delete from tttest where price_id = 2;
-select * from tttest;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 1 | 1 | 10 | 999999
- 3 | 3 | 30 | 999999
- 2 | 2 | 20 | 40
-(3 rows)
-
--- what do we see ?
--- get current prices
-select * from tttest where price_off = 999999;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 1 | 1 | 10 | 999999
- 3 | 3 | 30 | 999999
-(2 rows)
-
--- change price for price_id == 3
-update tttest set price_val = 30 where price_id = 3;
-select * from tttest;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 1 | 1 | 10 | 999999
- 2 | 2 | 20 | 40
- 3 | 30 | 50 | 999999
- 3 | 3 | 30 | 50
-(4 rows)
-
--- now we want to change pric_id in ALL tuples
--- this gets us not what we need
-update tttest set price_id = 5 where price_id = 3;
-select * from tttest;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 1 | 1 | 10 | 999999
- 2 | 2 | 20 | 40
- 3 | 3 | 30 | 50
- 5 | 30 | 60 | 999999
- 3 | 30 | 50 | 60
-(5 rows)
-
--- restore data as before last update:
-select set_ttdummy(0);
- set_ttdummy
--------------
- 1
-(1 row)
-
-delete from tttest where price_id = 5;
-update tttest set price_off = 999999 where price_val = 30;
-select * from tttest;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 1 | 1 | 10 | 999999
- 2 | 2 | 20 | 40
- 3 | 3 | 30 | 50
- 3 | 30 | 50 | 999999
-(4 rows)
-
--- and try change price_id now!
-update tttest set price_id = 5 where price_id = 3;
-select * from tttest;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 1 | 1 | 10 | 999999
- 2 | 2 | 20 | 40
- 5 | 3 | 30 | 50
- 5 | 30 | 50 | 999999
-(4 rows)
-
--- isn't it what we need ?
-select set_ttdummy(1);
- set_ttdummy
--------------
- 0
-(1 row)
-
--- we want to correct some "date"
-update tttest set price_on = -1 where price_id = 1;
-ERROR: ttdummy (tttest): you cannot change price_on and/or price_off columns (use set_ttdummy)
--- but this doesn't work
--- try in this way
-select set_ttdummy(0);
- set_ttdummy
--------------
- 1
-(1 row)
-
-update tttest set price_on = -1 where price_id = 1;
-select * from tttest;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 2 | 2 | 20 | 40
- 5 | 3 | 30 | 50
- 5 | 30 | 50 | 999999
- 1 | 1 | -1 | 999999
-(4 rows)
-
--- isn't it what we need ?
--- get price for price_id == 5 as it was @ "date" 35
-select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
- price_id | price_val | price_on | price_off
-----------+-----------+----------+-----------
- 5 | 3 | 30 | 50
-(1 row)
-
-drop table tttest;
-drop sequence ttdummy_seq;
--
-- tests for per-statement triggers
--
@@ -493,6 +221,11 @@ SELECT * FROM main_table ORDER BY a, b;
|
(8 rows)
+-- Test comments
+COMMENT ON TRIGGER no_such_trigger ON main_table IS 'wrong';
+ERROR: trigger "no_such_trigger" for table "main_table" does not exist
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
--
-- test triggers with WHEN clause
--
diff --git a/src/test/regress/meson.build b/src/test/regress/meson.build
index 87b26b4f7ff..1da9e9462a9 100644
--- a/src/test/regress/meson.build
+++ b/src/test/regress/meson.build
@@ -43,23 +43,6 @@ regress_module = shared_module('regress',
)
test_install_libs += regress_module
-# Get some extra C modules from contrib/spi but mark them as not to be
-# installed.
-# FIXME: avoid the duplication.
-
-autoinc_regress = shared_module('autoinc',
- ['../../../contrib/spi/autoinc.c'],
- kwargs: pg_test_mod_args,
-)
-test_install_libs += autoinc_regress
-
-refint_regress = shared_module('refint',
- ['../../../contrib/spi/refint.c'],
- c_args: refint_cflags,
- kwargs: pg_test_mod_args,
-)
-test_install_libs += refint_regress
-
tests += {
'name': 'regress',
diff --git a/src/test/regress/regress.c b/src/test/regress/regress.c
index 0bc0a9221de..837fab6b290 100644
--- a/src/test/regress/regress.c
+++ b/src/test/regress/regress.c
@@ -266,226 +266,6 @@ trigger_return_old(PG_FUNCTION_ARGS)
return PointerGetDatum(tuple);
}
-#define TTDUMMY_INFINITY 999999
-
-static SPIPlanPtr splan = NULL;
-static bool ttoff = false;
-
-PG_FUNCTION_INFO_V1(ttdummy);
-
-Datum
-ttdummy(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- char **args; /* arguments */
- int attnum[2]; /* fnumbers of start/stop columns */
- Datum oldon,
- oldoff;
- Datum newon,
- newoff;
- Datum *cvals; /* column values */
- char *cnulls; /* column nulls */
- char *relname; /* triggered relation name */
- Relation rel; /* triggered relation */
- HeapTuple trigtuple;
- HeapTuple newtuple = NULL;
- HeapTuple rettuple;
- TupleDesc tupdesc; /* tuple description */
- int natts; /* # of attributes */
- bool isnull; /* to know is some column NULL or not */
- int ret;
- int i;
-
- if (!CALLED_AS_TRIGGER(fcinfo))
- elog(ERROR, "ttdummy: not fired by trigger manager");
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- elog(ERROR, "ttdummy: must be fired for row");
- if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event))
- elog(ERROR, "ttdummy: must be fired before event");
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- elog(ERROR, "ttdummy: cannot process INSERT event");
- if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
- newtuple = trigdata->tg_newtuple;
-
- trigtuple = trigdata->tg_trigtuple;
-
- rel = trigdata->tg_relation;
- relname = SPI_getrelname(rel);
-
- /* check if TT is OFF for this relation */
- if (ttoff) /* OFF - nothing to do */
- {
- pfree(relname);
- return PointerGetDatum((newtuple != NULL) ? newtuple : trigtuple);
- }
-
- trigger = trigdata->tg_trigger;
-
- if (trigger->tgnargs != 2)
- elog(ERROR, "ttdummy (%s): invalid (!= 2) number of arguments %d",
- relname, trigger->tgnargs);
-
- args = trigger->tgargs;
- tupdesc = rel->rd_att;
- natts = tupdesc->natts;
-
- for (i = 0; i < 2; i++)
- {
- attnum[i] = SPI_fnumber(tupdesc, args[i]);
- if (attnum[i] <= 0)
- elog(ERROR, "ttdummy (%s): there is no attribute %s",
- relname, args[i]);
- if (SPI_gettypeid(tupdesc, attnum[i]) != INT4OID)
- elog(ERROR, "ttdummy (%s): attribute %s must be of integer type",
- relname, args[i]);
- }
-
- oldon = SPI_getbinval(trigtuple, tupdesc, attnum[0], &isnull);
- if (isnull)
- elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]);
-
- oldoff = SPI_getbinval(trigtuple, tupdesc, attnum[1], &isnull);
- if (isnull)
- elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]);
-
- if (newtuple != NULL) /* UPDATE */
- {
- newon = SPI_getbinval(newtuple, tupdesc, attnum[0], &isnull);
- if (isnull)
- elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[0]);
- newoff = SPI_getbinval(newtuple, tupdesc, attnum[1], &isnull);
- if (isnull)
- elog(ERROR, "ttdummy (%s): %s must be NOT NULL", relname, args[1]);
-
- if (oldon != newon || oldoff != newoff)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("ttdummy (%s): you cannot change %s and/or %s columns (use set_ttdummy)",
- relname, args[0], args[1])));
-
- if (newoff != TTDUMMY_INFINITY)
- {
- pfree(relname); /* allocated in upper executor context */
- return PointerGetDatum(NULL);
- }
- }
- else if (oldoff != TTDUMMY_INFINITY) /* DELETE */
- {
- pfree(relname);
- return PointerGetDatum(NULL);
- }
-
- newoff = DirectFunctionCall1(nextval, CStringGetTextDatum("ttdummy_seq"));
- /* nextval now returns int64; coerce down to int32 */
- newoff = Int32GetDatum((int32) DatumGetInt64(newoff));
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /* Fetch tuple values and nulls */
- cvals = (Datum *) palloc(natts * sizeof(Datum));
- cnulls = (char *) palloc(natts * sizeof(char));
- for (i = 0; i < natts; i++)
- {
- cvals[i] = SPI_getbinval((newtuple != NULL) ? newtuple : trigtuple,
- tupdesc, i + 1, &isnull);
- cnulls[i] = (isnull) ? 'n' : ' ';
- }
-
- /* change date column(s) */
- if (newtuple) /* UPDATE */
- {
- cvals[attnum[0] - 1] = newoff; /* start_date eq current date */
- cnulls[attnum[0] - 1] = ' ';
- cvals[attnum[1] - 1] = TTDUMMY_INFINITY; /* stop_date eq INFINITY */
- cnulls[attnum[1] - 1] = ' ';
- }
- else
- /* DELETE */
- {
- cvals[attnum[1] - 1] = newoff; /* stop_date eq current date */
- cnulls[attnum[1] - 1] = ' ';
- }
-
- /* if there is no plan ... */
- if (splan == NULL)
- {
- SPIPlanPtr pplan;
- Oid *ctypes;
- char *query;
-
- /* allocate space in preparation */
- ctypes = (Oid *) palloc(natts * sizeof(Oid));
- query = (char *) palloc(100 + 16 * natts);
-
- /*
- * Construct query: INSERT INTO _relation_ VALUES ($1, ...)
- */
- sprintf(query, "INSERT INTO %s VALUES (", relname);
- for (i = 1; i <= natts; i++)
- {
- sprintf(query + strlen(query), "$%d%s",
- i, (i < natts) ? ", " : ")");
- ctypes[i - 1] = SPI_gettypeid(tupdesc, i);
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(query, natts, ctypes);
- if (pplan == NULL)
- elog(ERROR, "ttdummy (%s): SPI_prepare returned %s", relname, SPI_result_code_string(SPI_result));
-
- if (SPI_keepplan(pplan))
- elog(ERROR, "ttdummy (%s): SPI_keepplan failed", relname);
-
- splan = pplan;
- }
-
- ret = SPI_execp(splan, cvals, cnulls, 0);
-
- if (ret < 0)
- elog(ERROR, "ttdummy (%s): SPI_execp returned %d", relname, ret);
-
- /* Tuple to return to upper Executor ... */
- if (newtuple) /* UPDATE */
- rettuple = SPI_modifytuple(rel, trigtuple, 1, &(attnum[1]), &newoff, NULL);
- else /* DELETE */
- rettuple = trigtuple;
-
- SPI_finish(); /* don't forget say Bye to SPI mgr */
-
- pfree(relname);
-
- return PointerGetDatum(rettuple);
-}
-
-PG_FUNCTION_INFO_V1(set_ttdummy);
-
-Datum
-set_ttdummy(PG_FUNCTION_ARGS)
-{
- int32 on = PG_GETARG_INT32(0);
-
- if (ttoff) /* OFF currently */
- {
- if (on == 0)
- PG_RETURN_INT32(0);
-
- /* turn ON */
- ttoff = false;
- PG_RETURN_INT32(0);
- }
-
- /* ON currently */
- if (on != 0)
- PG_RETURN_INT32(1);
-
- /* turn OFF */
- ttoff = true;
-
- PG_RETURN_INT32(1);
-}
-
/*
* Type int44 has no real-world use, but the regression tests use it
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 8432e8e3d54..5ce9d1e429f 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1810,6 +1810,8 @@ select * from my_locks order by 1;
rollback;
begin;
+create function ttdummy () returns trigger language plpgsql as
+$$ begin return new; end $$;
create trigger ttdummy
before delete or update on alterlock
for each row
diff --git a/src/test/regress/sql/test_setup.sql b/src/test/regress/sql/test_setup.sql
index 06b0e2121f8..5854399a028 100644
--- a/src/test/regress/sql/test_setup.sql
+++ b/src/test/regress/sql/test_setup.sql
@@ -252,11 +252,6 @@ CREATE FUNCTION binary_coercible(oid, oid)
AS :'regresslib', 'binary_coercible'
LANGUAGE C STRICT STABLE PARALLEL SAFE;
-CREATE FUNCTION ttdummy ()
- RETURNS trigger
- AS :'regresslib'
- LANGUAGE C;
-
-- Use hand-rolled hash functions and operator classes to get predictable
-- result on different machines. The hash function for int4 simply returns
-- the sum of the values passed to it and the one for text returns the length
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index e5a491be7ab..d3d242dd29b 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -6,135 +6,13 @@
\getenv libdir PG_LIBDIR
\getenv dlsuffix PG_DLSUFFIX
-\set autoinclib :libdir '/autoinc' :dlsuffix
-\set refintlib :libdir '/refint' :dlsuffix
\set regresslib :libdir '/regress' :dlsuffix
-CREATE FUNCTION autoinc ()
- RETURNS trigger
- AS :'autoinclib'
- LANGUAGE C;
-
-CREATE FUNCTION check_primary_key ()
- RETURNS trigger
- AS :'refintlib'
- LANGUAGE C;
-
-CREATE FUNCTION check_foreign_key ()
- RETURNS trigger
- AS :'refintlib'
- LANGUAGE C;
-
CREATE FUNCTION trigger_return_old ()
RETURNS trigger
AS :'regresslib'
LANGUAGE C;
-CREATE FUNCTION set_ttdummy (int4)
- RETURNS int4
- AS :'regresslib'
- LANGUAGE C STRICT;
-
-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
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
-
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
-
--- Test comments
-COMMENT ON TRIGGER check_fkeys2_pkey_bad ON fkeys2 IS 'wrong';
-COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS 'right';
-COMMENT ON TRIGGER check_fkeys2_pkey_exist ON fkeys2 IS NULL;
-
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after 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
- after 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';
-
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
-- Check behavior when trigger returns unmodified trigtuple
create table trigtest (f1 int, f2 text);
@@ -214,77 +92,6 @@ select * from trigtest;
drop table trigtest;
-create sequence ttdummy_seq increment 10 start 0 minvalue 0;
-
-create table tttest (
- price_id int4,
- price_val int4,
- price_on int4,
- price_off int4 default 999999
-);
-
-create trigger ttdummy
- before delete or update on tttest
- for each row
- execute procedure
- ttdummy (price_on, price_off);
-
-create trigger ttserial
- before insert or update on tttest
- for each row
- execute procedure
- autoinc (price_on, ttdummy_seq);
-
-insert into tttest values (1, 1, null);
-insert into tttest values (2, 2, null);
-insert into tttest values (3, 3, 0);
-
-select * from tttest;
-delete from tttest where price_id = 2;
-select * from tttest;
--- what do we see ?
-
--- get current prices
-select * from tttest where price_off = 999999;
-
--- change price for price_id == 3
-update tttest set price_val = 30 where price_id = 3;
-select * from tttest;
-
--- now we want to change pric_id in ALL tuples
--- this gets us not what we need
-update tttest set price_id = 5 where price_id = 3;
-select * from tttest;
-
--- restore data as before last update:
-select set_ttdummy(0);
-delete from tttest where price_id = 5;
-update tttest set price_off = 999999 where price_val = 30;
-select * from tttest;
-
--- and try change price_id now!
-update tttest set price_id = 5 where price_id = 3;
-select * from tttest;
--- isn't it what we need ?
-
-select set_ttdummy(1);
-
--- we want to correct some "date"
-update tttest set price_on = -1 where price_id = 1;
--- but this doesn't work
-
--- try in this way
-select set_ttdummy(0);
-update tttest set price_on = -1 where price_id = 1;
-select * from tttest;
--- isn't it what we need ?
-
--- get price for price_id == 5 as it was @ "date" 35
-select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5;
-
-drop table tttest;
-drop sequence ttdummy_seq;
-
--
-- tests for per-statement triggers
--
@@ -346,6 +153,11 @@ COPY main_table (a, b) FROM stdin;
SELECT * FROM main_table ORDER BY a, b;
+-- Test comments
+COMMENT ON TRIGGER no_such_trigger ON main_table IS 'wrong';
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'right';
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+
--
-- test triggers with WHEN clause
--
pgsql-hackers by date: