Re: Proposal: Change of pg_trigger.tg_enabled and adding

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 #