1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
SELECT setting::integer < 90600 AS pre_96
FROM pg_settings WHERE name = 'server_version_num';
pre_96
--------
t
(1 row)
/* Run tests as unprivileged user */
SET ROLE TO periods_unprivileged_user;
-- Unique keys are already pretty much guaranteed by the underlying features of
-- PostgreSQL, but test them anyway.
CREATE TABLE uk (id integer, s integer, e integer, CONSTRAINT uk_pkey PRIMARY KEY (id, s, e));
SELECT periods.add_period('uk', 'p', 's', 'e');
add_period
------------
t
(1 row)
SELECT periods.add_unique_key('uk', ARRAY['id'], 'p', key_name => 'uk_id_p', unique_constraint => 'uk_pkey');
add_unique_key
----------------
uk_id_p
(1 row)
TABLE periods.unique_keys;
key_name | table_name | column_names | period_name | unique_constraint | exclude_constraint
----------+------------+--------------+-------------+-------------------+----------------------
uk_id_p | uk | {id} | p | uk_pkey | uk_id_int4range_excl
(1 row)
INSERT INTO uk (id, s, e) VALUES (100, 1, 3), (100, 3, 4), (100, 4, 10); -- success
INSERT INTO uk (id, s, e) VALUES (200, 1, 3), (200, 3, 4), (200, 5, 10); -- success
INSERT INTO uk (id, s, e) VALUES (300, 1, 3), (300, 3, 5), (300, 4, 10); -- fail
ERROR: conflicting key value violates exclusion constraint "uk_id_int4range_excl"
DETAIL: Key (id, int4range(s, e, '[)'::text))=(300, [4,10)) conflicts with existing key (id, int4range(s, e, '[)'::text))=(300, [3,5)).
CREATE TABLE fk (id integer, uk_id integer, s integer, e integer, PRIMARY KEY (id));
SELECT periods.add_period('fk', 'q', 's', 'e');
add_period
------------
t
(1 row)
SELECT periods.add_foreign_key('fk', ARRAY['uk_id'], 'q', 'uk_id_p',
key_name => 'fk_uk_id_q',
fk_insert_trigger => 'fki',
fk_update_trigger => 'fku',
uk_update_trigger => 'uku',
uk_delete_trigger => 'ukd');
add_foreign_key
-----------------
fk_uk_id_q
(1 row)
TABLE periods.foreign_keys;
key_name | table_name | column_names | period_name | unique_key | match_type | delete_action | update_action | fk_insert_trigger | fk_update_trigger | uk_update_trigger | uk_delete_trigger
------------+------------+--------------+-------------+------------+------------+---------------+---------------+-------------------+-------------------+-------------------+-------------------
fk_uk_id_q | fk | {uk_id} | q | uk_id_p | SIMPLE | NO ACTION | NO ACTION | fki | fku | uku | ukd
(1 row)
SELECT periods.drop_foreign_key('fk', 'fk_uk_id_q');
drop_foreign_key
------------------
t
(1 row)
SELECT periods.add_foreign_key('fk', ARRAY['uk_id'], 'q', 'uk_id_p', key_name => 'fk_uk_id_q');
add_foreign_key
-----------------
fk_uk_id_q
(1 row)
TABLE periods.foreign_keys;
key_name | table_name | column_names | period_name | unique_key | match_type | delete_action | update_action | fk_insert_trigger | fk_update_trigger | uk_update_trigger | uk_delete_trigger
------------+------------+--------------+-------------+------------+------------+---------------+---------------+----------------------+----------------------+----------------------+----------------------
fk_uk_id_q | fk | {uk_id} | q | uk_id_p | SIMPLE | NO ACTION | NO ACTION | fk_uk_id_q_fk_insert | fk_uk_id_q_fk_update | fk_uk_id_q_uk_update | fk_uk_id_q_uk_delete
(1 row)
-- INSERT
INSERT INTO fk VALUES (0, 100, 0, 1); -- fail
ERROR: insert or update on table "fk" violates foreign key constraint "fk_uk_id_q"
CONTEXT: SQL statement "SELECT periods.validate_foreign_key_new_row(TG_ARGV[0], jnew)"
PL/pgSQL function periods.fk_insert_check() line 20 at PERFORM
INSERT INTO fk VALUES (0, 100, 0, 10); -- fail
ERROR: insert or update on table "fk" violates foreign key constraint "fk_uk_id_q"
CONTEXT: SQL statement "SELECT periods.validate_foreign_key_new_row(TG_ARGV[0], jnew)"
PL/pgSQL function periods.fk_insert_check() line 20 at PERFORM
INSERT INTO fk VALUES (0, 100, 1, 11); -- fail
ERROR: insert or update on table "fk" violates foreign key constraint "fk_uk_id_q"
CONTEXT: SQL statement "SELECT periods.validate_foreign_key_new_row(TG_ARGV[0], jnew)"
PL/pgSQL function periods.fk_insert_check() line 20 at PERFORM
INSERT INTO fk VALUES (1, 100, 1, 3); -- success
INSERT INTO fk VALUES (2, 100, 1, 10); -- success
-- UPDATE
UPDATE fk SET e = 20 WHERE id = 1; -- fail
ERROR: insert or update on table "fk" violates foreign key constraint "fk_uk_id_q"
CONTEXT: SQL statement "SELECT periods.validate_foreign_key_new_row(TG_ARGV[0], jnew)"
PL/pgSQL function periods.fk_update_check() line 19 at PERFORM
UPDATE fk SET e = 6 WHERE id = 1; -- success
UPDATE uk SET s = 2 WHERE (id, s, e) = (100, 1, 3); -- fail
ERROR: update or delete on table "uk" violates foreign key constraint "fk_uk_id_q" on table "fk"
CONTEXT: SQL statement "SELECT periods.validate_foreign_key_old_row(TG_ARGV[0], jold, true)"
PL/pgSQL function periods.uk_update_check() line 23 at PERFORM
UPDATE uk SET s = 0 WHERE (id, s, e) = (100, 1, 3); -- success
-- DELETE
DELETE FROM uk WHERE (id, s, e) = (100, 3, 4); -- fail
ERROR: update or delete on table "uk" violates foreign key constraint "fk_uk_id_q" on table "fk"
CONTEXT: SQL statement "SELECT periods.validate_foreign_key_old_row(TG_ARGV[0], jold, false)"
PL/pgSQL function periods.uk_delete_check() line 22 at PERFORM
DELETE FROM uk WHERE (id, s, e) = (200, 3, 5); -- success
DROP TABLE fk;
DROP TABLE uk;
|
