Skip site navigation (1) Skip section navigation (2)

Re: Proposal: Change of pg_trigger.tg_enabled and adding

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jim Nasby <decibel(at)decibel(dot)org>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal: Change of pg_trigger.tg_enabled and adding
Date: 2007-02-04 01:02:26
Message-ID: 45C530A2.6090805@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 2/3/2007 5:25 PM, Joshua D. Drake wrote:
> Jan Wieck wrote:
>> Attached is the implementation of the proposed changes as a patch for
>> discussion.
>> 
>> The chosen syntax is backward compatible and uses
>> 
>> ALTER TABLE <tab> ENABLE TRIGGER <trig> (fires on origin - default)
>> ALTER TABLE <tab> DISABLE TRIGGER <trig> (disabled)
>> ALTER TABLE <tab> ENABLE REPLICA TRIGGER <trig> (fires on replica only)
>> ALTER TABLE <tab> ENABLE ALWAYS TRIGGER <trig> (fires always)
>> 
> 
> <snip>
> 
>> 
>> The commands psql and pg_dump are adjusted in a backward compatible
>> manner. Although I noticed that psql currently is incompatible with at
>> least 8.1 databases due to querying indisvalid on \d.
>> 
>> Comments?
> 
> This is interesting. If I understand correctly the idea here is to be
> able to determine which triggers will get fired based on the role the
> database plays?

Not the database, the session actually has a role, which defaults to 
"origin". The default configuration for triggers (including RI triggers) 
is O (fires on origin). If the session does

     SET session_replication_role = replica;

only triggers configured A (always) or R (replica) will fire. Not those 
configured O (origin) or D (disabled). This means that a row based 
replication system like Slony only has to set the replication role of 
the session in order to disable triggers. It does not need to touch the 
system catalog or even ALTER TABLE to do its work. This would even 
suppress Slony-I's deny-access-trigger, that is in place on subscribers 
to prevent accidental updates on a replica.

Doing it on the session level is even more important for row based 
multimaster. At the same time where a user session does an update that 
needs to be added to the replication log, the replication engine in 
another session must be able to apply a remote transactions updates 
without firing the log trigger.


> 
> E.g; I have a REPLICA TRIGGER and thus I can use that on a
> subscriber/slave to take replicated data and create reports automatically.
> 
> How do we deal with other problems such as a PROMOTED state?

Promoted as in "transfer of origin to a replica"? In the case of a 
master-slave system like Slony-I, the origin of a table has the log 
trigger, that captures row changes, while a replica has a trigger that 
simply bails out with an error. Transfer of ownership is done by 
dropping one and creating the other trigger.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck(at)Yahoo(dot)com #

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-02-04 01:37:33
Subject: Remove log segment and log_id fields from pg_controldata
Previous:From: Bruce MomjianDate: 2007-02-03 23:58:14
Subject: Re: Dead code in _bt_split?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group