Changes between Version 3 and Version 4 of replicating


Ignore:
Timestamp:
04/16/18 10:31:18 (5 months ago)
Author:
niles
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • replicating

    v3 v4  
    361361}}} 
    362362 
     363=== PART 4 : TRIGGERS === 
     364 
     365If we want to keep track of what was inserted when into our new, replicated table, we might reasonably think that the following 
     366trigger setup would do it : 
     367 
     368{{{ 
     369-- Make a table to hold what was inserted into the expireTimes table, and when it was inserted 
     370DROP TABLE insertTimes; 
     371CREATE TABLE insertTimes( insertTime TIMESTAMP, insertValue VARCHAR(255)); 
     372 
     373-- Make a function that will update the insertTimes table 
     374CREATE OR REPLACE FUNCTION insert_trigger_fc() returns TRIGGER as $insert_trigger$ 
     375 BEGIN 
     376 INSERT INTO insertTimes VALUES (LOCALTIMESTAMP, NEW.RETIREMENT); 
     377 DELETE FROM insertTimes WHERE insertTime <  LOCALTIMESTAMP - INTERVAL '1 HOUR'; 
     378 RETURN NEW; 
     379END 
     380$insert_trigger$ LANGUAGE plpgsql; 
     381 
     382-- Make a trigger that will call the function when expireTimes is updated 
     383DROP TRIGGER IF EXISTS insert_trigger ON expireTimes; 
     384CREATE TRIGGER insert_trigger AFTER INSERT OR UPDATE ON expireTimes 
     385 FOR EACH ROW EXECUTE PROCEDURE insert_trigger_fc(); 
     386}}} 
     387 
     388And that does work on the master node : 
     389 
     390{{{ 
     391bladerunner=> SELECT * FROM  insertTimes; 
     392         inserttime         |             insertvalue              
     393----------------------------+------------------------------------- 
     394 2018-04-16 11:25:01.16447  | 2018/04/16 11:25:01 April Monday 16 
     395 2018-04-16 11:26:01.849943 | 2018/04/16 11:26:01 April Monday 16 
     396}}} 
     397 
     398However on the slave node the trigger will not fire by default. You have to either : 
     399 
     400{{{ 
     401ALTER TABLE expireTimes ENABLE REPLICA TRIGGER insert_trigger; 
     402}}} 
     403or (and I prefer this, because if I have a trigger I pretty much want it to fire) : 
     404{{{ 
     405ALTER TABLE expireTimes ENABLE ALWAYS TRIGGER insert_trigger; 
     406}}} 
     407 
    363408Niles Oien April 2018.