1-- This file and its contents are licensed under the Apache License 2.0. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-APACHE for a copy of the license. 4 5CREATE TABLE hyper ( 6 time BIGINT NOT NULL, 7 device_id TEXT NOT NULL, 8 sensor_1 NUMERIC NULL DEFAULT 1 9); 10 11CREATE OR REPLACE FUNCTION test_trigger() 12 RETURNS TRIGGER LANGUAGE PLPGSQL AS 13$BODY$ 14DECLARE 15 cnt INTEGER; 16BEGIN 17 SELECT count(*) INTO cnt FROM hyper; 18 RAISE WARNING 'FIRING trigger when: % level: % op: % cnt: % trigger_name %', 19 tg_when, tg_level, tg_op, cnt, tg_name; 20 21 IF TG_OP = 'DELETE' THEN 22 RETURN OLD; 23 END IF; 24 RETURN NEW; 25END 26$BODY$; 27 28-- row triggers: BEFORE 29CREATE TRIGGER _0_test_trigger_insert 30 BEFORE INSERT ON hyper 31 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 32 33CREATE TRIGGER _0_test_trigger_update 34 BEFORE UPDATE ON hyper 35 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 36 37CREATE TRIGGER _0_test_trigger_delete 38 BEFORE delete ON hyper 39 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 40 41CREATE TRIGGER z_test_trigger_all 42 BEFORE INSERT OR UPDATE OR DELETE ON hyper 43 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 44 45-- row triggers: AFTER 46CREATE TRIGGER _0_test_trigger_insert_after 47 AFTER INSERT ON hyper 48 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 49 50CREATE TRIGGER _0_test_trigger_insert_after_when_dev1 51 AFTER INSERT ON hyper 52 FOR EACH ROW 53 WHEN (NEW.device_id = 'dev1') 54 EXECUTE FUNCTION test_trigger(); 55 56CREATE TRIGGER _0_test_trigger_update_after 57 AFTER UPDATE ON hyper 58 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 59 60CREATE TRIGGER _0_test_trigger_delete_after 61 AFTER delete ON hyper 62 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 63 64CREATE TRIGGER z_test_trigger_all_after 65 AFTER INSERT OR UPDATE OR DELETE ON hyper 66 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 67 68-- statement triggers: BEFORE 69CREATE TRIGGER _0_test_trigger_insert_s_before 70 BEFORE INSERT ON hyper 71 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 72 73CREATE TRIGGER _0_test_trigger_update_s_before 74 BEFORE UPDATE ON hyper 75 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 76 77CREATE TRIGGER _0_test_trigger_delete_s_before 78 BEFORE DELETE ON hyper 79 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 80 81-- statement triggers: AFTER 82CREATE TRIGGER _0_test_trigger_insert_s_after 83 AFTER INSERT ON hyper 84 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 85 86CREATE TRIGGER _0_test_trigger_update_s_after 87 AFTER UPDATE ON hyper 88 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 89 90CREATE TRIGGER _0_test_trigger_delete_s_after 91 AFTER DELETE ON hyper 92 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 93 94-- CONSTRAINT TRIGGER 95CREATE CONSTRAINT TRIGGER _0_test_trigger_constraint_insert 96 AFTER INSERT ON hyper FOR EACH ROW EXECUTE FUNCTION test_trigger(); 97 98CREATE CONSTRAINT TRIGGER _0_test_trigger_constraint_update 99 AFTER UPDATE ON hyper FOR EACH ROW EXECUTE FUNCTION test_trigger(); 100 101CREATE CONSTRAINT TRIGGER _0_test_trigger_constraint_delete 102 AFTER DELETE ON hyper FOR EACH ROW EXECUTE FUNCTION test_trigger(); 103 104SELECT * FROM create_hypertable('hyper', 'time', chunk_time_interval => 10); 105 106--test triggers before create_hypertable 107INSERT INTO hyper(time, device_id,sensor_1) VALUES 108(1257987600000000000, 'dev1', 1); 109 110INSERT INTO hyper(time, device_id,sensor_1) VALUES 111(1257987700000000000, 'dev2', 1), (1257987800000000000, 'dev2', 1); 112 113UPDATE hyper SET sensor_1 = 2; 114 115DELETE FROM hyper; 116 117--test drop trigger 118DROP TRIGGER _0_test_trigger_insert ON hyper; 119DROP TRIGGER _0_test_trigger_insert_s_before ON hyper; 120DROP TRIGGER _0_test_trigger_insert_after ON hyper; 121DROP TRIGGER _0_test_trigger_insert_s_after ON hyper; 122INSERT INTO hyper(time, device_id,sensor_1) VALUES 123(1257987600000000000, 'dev1', 1); 124INSERT INTO hyper(time, device_id,sensor_1) VALUES 125(1257987700000000000, 'dev2', 1), (1257987800000000000, 'dev2', 1); 126 127DROP TRIGGER _0_test_trigger_update ON hyper; 128DROP TRIGGER _0_test_trigger_update_s_before ON hyper; 129DROP TRIGGER _0_test_trigger_update_after ON hyper; 130DROP TRIGGER _0_test_trigger_update_s_after ON hyper; 131UPDATE hyper SET sensor_1 = 2; 132 133DROP TRIGGER _0_test_trigger_delete ON hyper; 134DROP TRIGGER _0_test_trigger_delete_s_before ON hyper; 135DROP TRIGGER _0_test_trigger_delete_after ON hyper; 136DROP TRIGGER _0_test_trigger_delete_s_after ON hyper; 137DELETE FROM hyper; 138 139DROP TRIGGER z_test_trigger_all ON hyper; 140DROP TRIGGER z_test_trigger_all_after ON hyper; 141 142--test create trigger on hypertable 143 144-- row triggers: BEFORE 145CREATE TRIGGER _0_test_trigger_insert 146 BEFORE INSERT ON hyper 147 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 148 149CREATE TRIGGER _0_test_trigger_update 150 BEFORE UPDATE ON hyper 151 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 152 153CREATE TRIGGER _0_test_trigger_delete 154 BEFORE delete ON hyper 155 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 156 157CREATE TRIGGER z_test_trigger_all 158 BEFORE INSERT OR UPDATE OR DELETE ON hyper 159 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 160 161-- row triggers: AFTER 162CREATE TRIGGER _0_test_trigger_insert_after 163 AFTER INSERT ON hyper 164 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 165 166CREATE TRIGGER _0_test_trigger_update_after 167 AFTER UPDATE ON hyper 168 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 169 170CREATE TRIGGER _0_test_trigger_delete_after 171 AFTER delete ON hyper 172 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 173 174CREATE TRIGGER z_test_trigger_all_after 175 AFTER INSERT OR UPDATE OR DELETE ON hyper 176 FOR EACH ROW EXECUTE FUNCTION test_trigger(); 177 178-- statement triggers: BEFORE 179CREATE TRIGGER _0_test_trigger_insert_s_before 180 BEFORE INSERT ON hyper 181 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 182 183CREATE TRIGGER _0_test_trigger_update_s_before 184 BEFORE UPDATE ON hyper 185 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 186 187CREATE TRIGGER _0_test_trigger_delete_s_before 188 BEFORE DELETE ON hyper 189 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 190 191-- statement triggers: AFTER 192CREATE TRIGGER _0_test_trigger_insert_s_after 193 AFTER INSERT ON hyper 194 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 195 196CREATE TRIGGER _0_test_trigger_update_s_after 197 AFTER UPDATE ON hyper 198 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 199 200CREATE TRIGGER _0_test_trigger_delete_s_after 201 AFTER DELETE ON hyper 202 FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 203 204 205INSERT INTO hyper(time, device_id,sensor_1) VALUES 206(1257987600000000000, 'dev1', 1); 207 208INSERT INTO hyper(time, device_id,sensor_1) VALUES 209(1257987700000000000, 'dev2', 1), (1257987800000000000, 'dev2', 1); 210 211UPDATE hyper SET sensor_1 = 2; 212 213DELETE FROM hyper; 214 215CREATE TABLE vehicles ( 216 vehicle_id INTEGER PRIMARY KEY, 217 vin_number CHAR(17), 218 last_checkup TIMESTAMP 219); 220 221CREATE TABLE color ( 222 color_id INTEGER PRIMARY KEY, 223 notes text 224); 225 226 227CREATE TABLE location ( 228 time TIMESTAMP NOT NULL, 229 vehicle_id INTEGER REFERENCES vehicles (vehicle_id), 230 color_id INTEGER, --no reference since gonna populate a hypertable 231 latitude FLOAT, 232 longitude FLOAT 233); 234 235CREATE OR REPLACE FUNCTION create_vehicle_trigger_fn() 236 RETURNS TRIGGER LANGUAGE PLPGSQL AS 237$BODY$ 238BEGIN 239 INSERT INTO vehicles VALUES(NEW.vehicle_id, NULL, NULL) ON CONFLICT DO NOTHING; 240 RETURN NEW; 241END 242$BODY$; 243 244 245CREATE OR REPLACE FUNCTION create_color_trigger_fn() 246 RETURNS TRIGGER LANGUAGE PLPGSQL AS 247$BODY$ 248BEGIN 249 --test subtxns within triggers 250 BEGIN 251 INSERT INTO color VALUES(NEW.color_id, 'n/a'); 252 EXCEPTION WHEN unique_violation THEN 253 -- Nothing to do, just continue 254 END; 255 RETURN NEW; 256END 257$BODY$; 258 259CREATE TRIGGER create_color_trigger 260 BEFORE INSERT OR UPDATE ON location 261 FOR EACH ROW EXECUTE FUNCTION create_color_trigger_fn(); 262 263SELECT create_hypertable('location', 'time'); 264 265--make color also a hypertable 266SELECT create_hypertable('color', 'color_id', chunk_time_interval=>10); 267 268-- Test that we can create and use triggers with another user 269GRANT TRIGGER, INSERT, SELECT, UPDATE ON location TO :ROLE_DEFAULT_PERM_USER_2; 270GRANT SELECT, INSERT, UPDATE ON color TO :ROLE_DEFAULT_PERM_USER_2; 271GRANT SELECT, INSERT, UPDATE ON vehicles TO :ROLE_DEFAULT_PERM_USER_2; 272\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2; 273 274CREATE TRIGGER create_vehicle_trigger 275 BEFORE INSERT OR UPDATE ON location 276 FOR EACH ROW EXECUTE FUNCTION create_vehicle_trigger_fn(); 277 278INSERT INTO location VALUES('2017-01-01 01:02:03', 1, 1, 40.7493226,-73.9771259); 279INSERT INTO location VALUES('2017-01-01 01:02:04', 1, 20, 24.7493226,-73.9771259); 280INSERT INTO location VALUES('2017-01-01 01:02:03', 23, 1, 40.7493226,-73.9771269); 281INSERT INTO location VALUES('2017-01-01 01:02:03', 53, 20, 40.7493226,-73.9771269); 282 283UPDATE location SET vehicle_id = 52 WHERE vehicle_id = 53; 284 285SELECT * FROM location; 286SELECT * FROM vehicles; 287SELECT * FROM color; 288 289-- switch back to default user to run some dropping tests 290\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER; 291 292\set ON_ERROR_STOP 0 293-- test that disable trigger is disallowed 294ALTER TABLE location DISABLE TRIGGER create_vehicle_trigger; 295\set ON_ERROR_STOP 1 296 297-- test that drop trigger works 298DROP TRIGGER create_color_trigger ON location; 299DROP TRIGGER create_vehicle_trigger ON location; 300 301-- test that drop trigger doesn't cause leftovers that mean that dropping chunks or hypertables no longer works 302SELECT count(1) FROM pg_depend d WHERE d.classid = 'pg_trigger'::regclass AND NOT EXISTS (SELECT 1 FROM pg_trigger WHERE oid = d.objid); 303DROP TABLE location; 304 305-- test triggers with transition tables 306-- test creating hypertable from table with triggers with transition tables 307CREATE TABLE transition_test(time timestamptz NOT NULL); 308CREATE TRIGGER t1 AFTER INSERT ON transition_test REFERENCING NEW TABLE AS new_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 309 310\set ON_ERROR_STOP 0 311SELECT create_hypertable('transition_test','time'); 312\set ON_ERROR_STOP 1 313DROP TRIGGER t1 ON transition_test; 314SELECT create_hypertable('transition_test','time'); 315 316-- test creating trigger with transition tables on existing hypertable 317\set ON_ERROR_STOP 0 318CREATE TRIGGER t2 AFTER INSERT ON transition_test REFERENCING NEW TABLE AS new_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 319CREATE TRIGGER t3 AFTER UPDATE ON transition_test REFERENCING NEW TABLE AS new_trans OLD TABLE AS old_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 320CREATE TRIGGER t4 AFTER DELETE ON transition_test REFERENCING OLD TABLE AS old_trans FOR EACH STATEMENT EXECUTE FUNCTION test_trigger(); 321 322CREATE TRIGGER t2 AFTER INSERT ON transition_test REFERENCING NEW TABLE AS new_trans FOR EACH ROW EXECUTE FUNCTION test_trigger(); 323CREATE TRIGGER t3 AFTER UPDATE ON transition_test REFERENCING NEW TABLE AS new_trans OLD TABLE AS old_trans FOR EACH ROW EXECUTE FUNCTION test_trigger(); 324CREATE TRIGGER t4 AFTER DELETE ON transition_test REFERENCING OLD TABLE AS old_trans FOR EACH ROW EXECUTE FUNCTION test_trigger(); 325\set ON_ERROR_STOP 1 326 327 328