mirror of
https://github.com/postgres/postgres.git
synced 2025-04-20 00:42:27 +03:00
b1720fe63f
It's weird to have the core regression tests depending on contrib code, and coverage testing shows that those test queries add nothing to the core-code coverage of the core tests. So pull those test bits out and put them into ordinary test scripts inside contrib/spi/, making that more like other contrib modules. Aside from being structurally nicer, anything we can take out of the core tests (which are executed multiple times per check-world run) and put into tests executed only once should be a win. It doesn't look like this change will buy a whole lot of milliseconds, but a cycle saved is a cycle earned. Also, there is some discussion around possibly removing refint and/or autoinc altogether. I don't know if that will happen, but we'd certainly need to decouple them from the core tests to do so. The tests for autoinc were quite intertwined with the undocumented "ttdummy" trigger in regress.c. That made the tests very hard to understand and contributed nothing to autoinc's testing either. So I just deleted ttdummy and rewrote the autoinc tests without it. 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. Author: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Heikki Linnakangas <hlinnaka@iki.fi> Discussion: https://postgr.es/m/3872677.1744077559@sss.pgh.pa.us
98 lines
3.0 KiB
SQL
98 lines
3.0 KiB
SQL
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;
|