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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
\set VERBOSITY 'terse'
set client_min_messages = 'warning';
select 1 from (select set_config(name, 'escape', false) as ignore
from pg_settings where name = 'bytea_output') x
where x.ignore = 'foo';
?column?
----------
(0 rows)
create or replace function pgq.insert_event(queue_name text, ev_type text, ev_data text, ev_extra1 text, ev_extra2 text, ev_extra3 text, ev_extra4 text)
returns bigint as $$
begin
raise warning 'insert_event(%, %, %, %)', queue_name, ev_type, ev_data, ev_extra1;
return 1;
end;
$$ language plpgsql;
create table udata (
id serial primary key,
txt text,
bin bytea
);
create trigger utest AFTER insert or update or delete ON udata
for each row execute procedure pgq.logutriga('udata_que');
insert into udata (txt) values ('text1');
WARNING: insert_event(udata_que, I:id, id=1&txt=text1&bin, public.udata)
insert into udata (bin) values (E'bi\tn\\000bin');
WARNING: insert_event(udata_que, I:id, id=2&txt&bin=bi%5c011n%5c000bin, public.udata)
-- test ignore
drop trigger utest on udata;
truncate udata;
create trigger utest after insert or update or delete on udata
for each row execute procedure pgq.logutriga('udata_que', 'ignore=bin');
insert into udata values (1, 'txt', 'bin');
WARNING: insert_event(udata_que, I:id, id=1&txt=txt, public.udata)
update udata set txt = 'txt';
WARNING: insert_event(udata_que, U:id, id=1&txt=txt, public.udata)
update udata set txt = 'txt2', bin = 'bin2';
WARNING: insert_event(udata_que, U:id, id=1&txt=txt2, public.udata)
update udata set bin = 'bin3';
delete from udata;
WARNING: insert_event(udata_que, D:id, id=1&txt=txt2, public.udata)
-- test missing pkey
create table nopkey2 (dat text);
create trigger nopkey_triga2 after insert or update or delete on nopkey2
for each row execute procedure pgq.logutriga('que3');
insert into nopkey2 values ('foo');
WARNING: insert_event(que3, I:, dat=foo, public.nopkey2)
update nopkey2 set dat = 'bat';
ERROR: Update/Delete on table without pkey
delete from nopkey2;
ERROR: Update/Delete on table without pkey
-- test custom pkey
create table ucustom_pkey (dat1 text not null, dat2 int2 not null, dat3 text);
create trigger ucustom_triga after insert or update or delete on ucustom_pkey
--for each row execute procedure pgq.logutriga('que3', 'pkey=dat1,dat2');
for each row execute procedure pgq.logutriga('que3');
insert into ucustom_pkey values ('foo', '2');
WARNING: insert_event(que3, I:, dat1=foo&dat2=2&dat3, public.ucustom_pkey)
update ucustom_pkey set dat3 = 'bat';
ERROR: Update/Delete on table without pkey
delete from ucustom_pkey;
ERROR: Update/Delete on table without pkey
-- test custom fields
create table custom_fields2 (
dat1 text not null primary key,
dat2 int2 not null,
dat3 text,
_pgq_ev_type text default 'my_type',
_pgq_ev_extra1 text default 'e1',
_pgq_ev_extra2 text default 'e2',
_pgq_ev_extra3 text default 'e3',
_pgq_ev_extra4 text default 'e4'
);
create trigger customf2_triga after insert or update or delete on custom_fields2
for each row execute procedure pgq.logutriga('que3');
insert into custom_fields2 values ('foo', '2');
WARNING: insert_event(que3, my_type, dat1=foo&dat2=2&dat3, e1)
update custom_fields2 set dat3 = 'bat';
WARNING: insert_event(que3, my_type, dat1=foo&dat2=2&dat3=bat, e1)
delete from custom_fields2;
WARNING: insert_event(que3, my_type, dat1=foo&dat2=2&dat3=bat, e1)
-- test custom expression
create table custom_expr2 (
dat1 text not null primary key,
dat2 int2 not null,
dat3 text
);
create trigger customex2_triga after insert or update or delete on custom_expr2
for each row execute procedure pgq.logutriga('que3', 'ev_extra1=''test='' || dat1', 'ev_type=dat3');
insert into custom_expr2 values ('foo', '2');
WARNING: insert_event(que3, <NULL>, dat1=foo&dat2=2&dat3, test=foo)
update custom_expr2 set dat3 = 'bat';
WARNING: insert_event(que3, bat, dat1=foo&dat2=2&dat3=bat, test=foo)
delete from custom_expr2;
WARNING: insert_event(que3, bat, dat1=foo&dat2=2&dat3=bat, test=foo)
-- test when=
create table when_test (
dat1 text not null primary key,
dat2 int2 not null,
dat3 text
);
create trigger when_triga after insert or update or delete on when_test
for each row execute procedure pgq.logutriga('que3', 'when=dat1=''foo''');
insert into when_test values ('foo', '2');
WARNING: insert_event(que3, I:dat1, dat1=foo&dat2=2&dat3, public.when_test)
insert into when_test values ('bar', '2');
select * from when_test;
dat1 | dat2 | dat3
------+------+------
foo | 2 |
bar | 2 |
(2 rows)
update when_test set dat3 = 'bat';
WARNING: insert_event(que3, U:dat1, dat1=foo&dat2=2&dat3=bat, public.when_test)
delete from when_test;
WARNING: insert_event(que3, D:dat1, dat1=foo&dat2=2&dat3=bat, public.when_test)
drop trigger when_triga on when_test;
create trigger when_triga after insert or update or delete on when_test
for each row execute procedure pgq.logutriga('que3', 'when=current_user=''random''');
insert into when_test values ('foo', '2');
select * from when_test;
dat1 | dat2 | dat3
------+------+------
foo | 2 |
(1 row)
-- test deny
create table deny_test (
dat1 text not null primary key,
dat2 text
);
create trigger deny_triga after insert or update or delete on deny_test
for each row execute procedure pgq.logutriga('noqueue', 'deny');
insert into deny_test values ('1', '2');
ERROR: Table 'public.deny_test' to queue 'noqueue': change not allowed (I)
-- test pk update
insert into udata (id, txt) values (1, 'txt');
WARNING: insert_event(udata_que, I:id, id=1&txt=txt, public.udata)
update udata set id = 2;
ERROR: primary key update not allowed
|
