Changes between Version 3 and Version 4 of replicating

Timestamp:

04/16/18 10:31:18 (7 years ago)

Author:

niles

Comment:

--

replicating

@@ -361 +361 @@
 }}}
 
+=== PART 4 : TRIGGERS ===
+
+If we want to keep track of what was inserted when into our new, replicated table, we might reasonably think that the following
+trigger setup would do it :
+
+{{{
+-- Make a table to hold what was inserted into the expireTimes table, and when it was inserted
+DROP TABLE insertTimes;
+CREATE TABLE insertTimes( insertTime TIMESTAMP, insertValue VARCHAR(255));
+
+-- Make a function that will update the insertTimes table
+CREATE OR REPLACE FUNCTION insert_trigger_fc() returns TRIGGER as $insert_trigger$
+ BEGIN
+ INSERT INTO insertTimes VALUES (LOCALTIMESTAMP, NEW.RETIREMENT);
+ DELETE FROM insertTimes WHERE insertTime <  LOCALTIMESTAMP - INTERVAL '1 HOUR';
+ RETURN NEW;
+END
+$insert_trigger$ LANGUAGE plpgsql;
+
+-- Make a trigger that will call the function when expireTimes is updated
+DROP TRIGGER IF EXISTS insert_trigger ON expireTimes;
+CREATE TRIGGER insert_trigger AFTER INSERT OR UPDATE ON expireTimes
+ FOR EACH ROW EXECUTE PROCEDURE insert_trigger_fc();
+}}}
+
+And that does work on the master node :
+
+{{{
+bladerunner=> SELECT * FROM  insertTimes;
+         inserttime         |             insertvalue             
+----------------------------+-------------------------------------
+ 2018-04-16 11:25:01.16447  | 2018/04/16 11:25:01 April Monday 16
+ 2018-04-16 11:26:01.849943 | 2018/04/16 11:26:01 April Monday 16
+}}}
+
+However on the slave node the trigger will not fire by default. You have to either :
+
+{{{
+ALTER TABLE expireTimes ENABLE REPLICA TRIGGER insert_trigger;
+}}}
+or (and I prefer this, because if I have a trigger I pretty much want it to fire) :
+{{{
+ALTER TABLE expireTimes ENABLE ALWAYS TRIGGER insert_trigger;
+}}}
+
 Niles Oien April 2018.