Christopher B. Browne's Home Page
cbbrowne@acm.org

4.9. Slony-I Trigger Handling

In PostgreSQL version 8.3, new functionality was added where triggers and rules may have their behaviour altered via ALTER TABLE, to specify the following alterations:

A new GUC variable, session_replication_role controls whether the session is in origin, replica, or local mode, which then, in combination with the above enabling/disabling options, controls whether or not the trigger function actually runs.

We may characterize when triggers fire, under Slony-I replication, based on the following table; the same rules apply to PostgreSQL rules.

Table 4-1. Trigger Behaviour

Trigger FormWhen EstablishedLog Triggerdenyaccess TriggerAction - originAction - replica Action - local
DISABLE TRIGGERUser requestdisabled on subscriberenabled on subscriberdoes not firedoes not firedoes not fire
ENABLE TRIGGERDefaultenabled on subscriberdisabled on subscriberfiresdoes not firefires
ENABLE REPLICA TRIGGERUser requestinappropriateinappropriatedoes not firefiresdoes not fire
ENABLE ALWAYS TRIGGERUser requestinappropriateinappropriatefiresfiresfires

There are, correspondingly, now, several ways in which Slony-I interacts with this. Let us outline those times that are interesting:

4.9.1. TRUNCATE in PostgreSQL 8.4+

In PostgreSQL 8.4, triggers were augmented to support the TRUNCATE event. Thus, one may create a trigger which runs when one requests TRUNCATE on a table, as follows:

    create trigger "_@CLUSTERNAME@_truncatetrigger" 
       before truncate on my_table 
       for each statement 
         execute procedure @NAMESPACE@.log_truncate(22);

Slony-I supports this on nodes running PostgreSQL 8.4 and above, as follows:

Google

If this was useful, let others know by an Affero rating

Contact me at cbbrowne@acm.org