Thread: Proposal: Change of pg_trigger.tg_enabled and adding pg_rewrite.ev_enabled

Proposal: Change of pg_trigger.tg_enabled and adding pg_rewrite.ev_enabled

From
Jan Wieck
Date:
The experience with Slony-I has shown that
  a) different behavior of triggers and rules on a transactions origin     and a replica is essential;
  b) mucking around with the system catalog to achieve this is futile.

This would be even more catastrophic in a multimaster environment, where 
regular transaction origin and replica behavior are required on a per 
session level concurrently.

To achieve the required flexibility, we need to change the definition of 
the pg_trigger attribute tg_enabled. It currently is a boolean. I would 
like to change it into a char along with the syntax of ALTER TRIGGER. 
The value definitions of tg_enabled would be
    A   fires always    N   fires never    O   fires on transaction origin only    R   fires on replica only

Anyone preferences how to map that to ALTER TRIGGER?

A new per session GUC variable, restricted to superusers, will define if 
the session is in origin or replica mode.

Likewise the system catalog pg_rewrite is extended with an attribute 
ev_enabled. It will have the same possible values and a new command, 
ALTER RULE, will match the functionality of ALTER TRIGGER.


Jan

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


Jan Wieck <JanWieck@Yahoo.com> writes:
> The value definitions of tg_enabled would be

>      A   fires always
>      N   fires never
>      O   fires on transaction origin only
>      R   fires on replica only

> A new per session GUC variable, restricted to superusers, will define if 
> the session is in origin or replica mode.

Are you sure two states are enough?

No particular objection, but now would be the time to think if a boolean
is sufficient.

> Likewise the system catalog pg_rewrite is extended with an attribute 
> ev_enabled. It will have the same possible values and a new command, 

I assume there'd be no intention of supporting on-the-fly changes of
this setting (ie, you'd set the GUC variable once at session startup
and not change thereafter)?  Otherwise you'd have a problem with cached
plans.
        regards, tom lane


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
On 1/25/2007 6:55 PM, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>> The value definitions of tg_enabled would be
> 
>>      A   fires always
>>      N   fires never
>>      O   fires on transaction origin only
>>      R   fires on replica only
> 
>> A new per session GUC variable, restricted to superusers, will define if 
>> the session is in origin or replica mode.
> 
> Are you sure two states are enough?

Good question. I don't know. I'd rather error on the safe side and make 
it multiple states, for now I only have Normal and Replica mode.

> 
> No particular objection, but now would be the time to think if a boolean
> is sufficient.
> 
>> Likewise the system catalog pg_rewrite is extended with an attribute 
>> ev_enabled. It will have the same possible values and a new command, 
> 
> I assume there'd be no intention of supporting on-the-fly changes of
> this setting (ie, you'd set the GUC variable once at session startup
> and not change thereafter)?  Otherwise you'd have a problem with cached
> plans.

This is indeed the intended use pattern. Since it is restricted to 
superusers, I don't see a particular reason why to enforce it in the 
system though.


Jan

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


Jan Wieck <JanWieck@Yahoo.com> writes:
>>> A   fires always
>>> N   fires never
>>> O   fires on transaction origin only
>>> R   fires on replica only

> Good question. I don't know. I'd rather error on the safe side and make 
> it multiple states, for now I only have Normal and Replica mode.

Hm, "N" up there seems easily confused with "Normal".  Perhaps a less
mistake-prone coding would be
1    fires always0    fires neverN    fires in "Normal" modeR    fires in "Replica" modeother letters available for
otherfuture mode values?
 

If you consistently think of "origin" and "replica" modes then the
original proposal is better (using both 0 and O would be Real Bad),
but your use of "normal" and "replica" in the followup makes me wonder
which terminology is more common.
        regards, tom lane


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
On 1/25/2007 7:33 PM, Tom Lane wrote:
>     1    fires always
>     0    fires never
>     N    fires in "Normal" mode
>     R    fires in "Replica" mode
>     other letters available for other future mode values?
> 
> If you consistently think of "origin" and "replica" modes then the
> original proposal is better (using both 0 and O would be Real Bad),
> but your use of "normal" and "replica" in the followup makes me wonder
> which terminology is more common.

Yeah, I tried for a long time to stay away from terms like master and 
slave ... but in the end people don't understand you if you talk about 
origin and subscriber or replica. That's how this inconsistent 
terminology slipped into my vocabulary.

I personally don't care about the particular values. I could live with 
A, B, C, D. If people find 1, 0, N, R more explanatory, fine.


Jan

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


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Markus Schiltknecht
Date:
Hi,

Nice proposal. I'd support that enhancement and could make use of such 
triggers in Postgres-R as well, at least to provide these triggers to 
the user.

Jan Wieck wrote:
> Good question. I don't know. I'd rather error on the safe side and make 
> it multiple states, for now I only have Normal and Replica mode.

Are these triggers intended to help implement async replication or are 
these for users to be able to take action on remote replay of a 
transaction (i.e. on the replica)? Does that give a further distinction?

In Postgres-R, I mostly use the terms 'local' and 'remote'. Also, 
"normal mode" can easily be confused with "non-replicated" mode, thus 
I'd not mix that with replicated, local transaction mode (even if it's 
mostly equal, as in this case). My naming proposal would thus be:
    A   fires always (i.e. fires N times, where N = nr of nodes)    L   fires on the transaction local node (i.e. only
exactlyonce)    R   fires on the remote nodes only (i.e. (N - 1) times)    0   fires never
 

'1' for "fires on both nodes" seems confusing as well, because it's not 
like in single node DB operation, in that one event can fire the trigger 
multiple times (on different nodes). The current, single node PostgreSQL 
should thus use '0' or 'L'.

Regards

Markus



Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Chris Browne
Date:
markus@bluegap.ch (Markus Schiltknecht) writes:
> Nice proposal. I'd support that enhancement and could make use of such
> triggers in Postgres-R as well, at least to provide these triggers to
> the user.
>
> Jan Wieck wrote:
>> Good question. I don't know. I'd rather error on the safe side and
>> make it multiple states, for now I only have Normal and Replica mode.
>
> Are these triggers intended to help implement async replication or are
> these for users to be able to take action on remote replay of a
> transaction (i.e. on the replica)? Does that give a further
> distinction?

Well, there's specific intent, and then there's general intent...  

If I understand correctly (and I think I do), the various threads that
Jan has been starting do have *specific* intent in that he's got an
implementation in mind that would specifically use the features he's
asking about.

But there is also the "general intent" that the features be usable
more widely than that.  If some generalization makes this particular
feature useful for Postgres-R as well as Jan's work, that's better
still.

> In Postgres-R, I mostly use the terms 'local' and 'remote'. Also,
> "normal mode" can easily be confused with "non-replicated" mode, thus
> I'd not mix that with replicated, local transaction mode (even if it's
> mostly equal, as in this case). My naming proposal would thus be:
>
>     A   fires always (i.e. fires N times, where N = nr of nodes)
>     L   fires on the transaction local node (i.e. only exactly once)
>     R   fires on the remote nodes only (i.e. (N - 1) times)
>     0   fires never
>
> '1' for "fires on both nodes" seems confusing as well, because it's
> not like in single node DB operation, in that one event can fire the
> trigger multiple times (on different nodes). The current, single node
> PostgreSQL should thus use '0' or 'L'.

I rather like your "L" for "local" and "R" for "remote."

An alternative to "A" for "always" would be "B", standing for "runs
[B]oth on local and remote nodes".

Of course, this is picking at nits; the important question is not what
to call the names of the states, but rather whether the set of states
is both desirable and complete...
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/x.html
Rules  of  the Evil  Overlord  #97.  "My  dungeon  cells  will not  be
furnished with  objects that  contain reflective surfaces  or anything
that can be unravelled." <http://www.eviloverlord.com/>


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jim Nasby
Date:
On Jan 26, 2007, at 5:13 AM, Markus Schiltknecht wrote:
> In Postgres-R, I mostly use the terms 'local' and 'remote'.

Note that those terms only make sense if you limit yourself to  
thinking the master is pushing data out to the slave...

I think it'd make the most sense if the name reflected whether the  
trigger should be fired by a replication process or not; that way it  
doesn't really matter if it's a master or a slave... if the data in  
the table is being modified by a replication process then you don't  
fire the trigger/rule, according to the setting. But maybe there is  
some need to discern between origin and target...

Also, if enums will be in 8.3, perhaps they can be used instead of  
"char"?
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




On Jan 25, 2007, at 5:33 PM, Jan Wieck wrote:
> A new per session GUC variable, restricted to superusers, will  
> define if the session is in origin or replica mode.

It would be nice if we had a separate role for replication services  
so that we weren't exposing superuser so much. IIRC Oracle even uses  
2 roles; one for administration of replication and one that the  
replication code actually runs under.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
On 1/26/2007 4:39 PM, Jim Nasby wrote:
> On Jan 26, 2007, at 5:13 AM, Markus Schiltknecht wrote:
>> In Postgres-R, I mostly use the terms 'local' and 'remote'.
> 
> Note that those terms only make sense if you limit yourself to  
> thinking the master is pushing data out to the slave...
> 
> I think it'd make the most sense if the name reflected whether the  
> trigger should be fired by a replication process or not; that way it  
> doesn't really matter if it's a master or a slave... if the data in  
> the table is being modified by a replication process then you don't  
> fire the trigger/rule, according to the setting. But maybe there is  
> some need to discern between origin and target...

That's why I prefer "origin" and "replica". I want to use the same terms 
in the sessions mode GUC, and there "local" could be misinterpreted as 
"doesn't replicate at all".

> 
> Also, if enums will be in 8.3, perhaps they can be used instead of  
> "char"?

I don't like this one. It makes it impossible to provide patches, 
enabling this replication system on older Postgres releases. And you 
know that your customers will want them.


Jan

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


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
On 1/26/2007 4:40 PM, Jim Nasby wrote:
> On Jan 25, 2007, at 5:33 PM, Jan Wieck wrote:
>> A new per session GUC variable, restricted to superusers, will  
>> define if the session is in origin or replica mode.
> 
> It would be nice if we had a separate role for replication services  
> so that we weren't exposing superuser so much. IIRC Oracle even uses  
> 2 roles; one for administration of replication and one that the  
> replication code actually runs under.

So you think about another flag in pg_shadow? Would work for me.


Jan

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


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
On 1/26/2007 4:47 PM, Jan Wieck wrote:
> On 1/26/2007 4:39 PM, Jim Nasby wrote:
>> On Jan 26, 2007, at 5:13 AM, Markus Schiltknecht wrote:
>>> In Postgres-R, I mostly use the terms 'local' and 'remote'.
>> 
>> Note that those terms only make sense if you limit yourself to  
>> thinking the master is pushing data out to the slave...
>> 
>> I think it'd make the most sense if the name reflected whether the  
>> trigger should be fired by a replication process or not; that way it  
>> doesn't really matter if it's a master or a slave... if the data in  
>> the table is being modified by a replication process then you don't  
>> fire the trigger/rule, according to the setting. But maybe there is  
>> some need to discern between origin and target...
> 
> That's why I prefer "origin" and "replica". I want to use the same terms 
> in the sessions mode GUC, and there "local" could be misinterpreted as 
> "doesn't replicate at all".

I will need that "local" mode anyway for some conflict resolutions. 
Think of a duplicate key (yeah, yeah, what comes now sounds bad ...) 
conflict, where you need to delete one of the entries without causing 
that delete to replicate.

Before people panic, the final system is supposed to have something 
smarter than deleting a dupkey in its repertoire. But I'll rather go 
with this cheap shot first and add a group communication based advisory 
locking system later, you know?


Jan

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


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
> On 1/26/2007 4:39 PM, Jim Nasby wrote:
>> Also, if enums will be in 8.3, perhaps they can be used instead of  
>> "char"?

> I don't like this one. It makes it impossible to provide patches, 
> enabling this replication system on older Postgres releases. And you 
> know that your customers will want them.

Also, at the level of C code enums will not be terribly easy to work
with.  We use the char-as-poor-mans-enum trick in all the other system
catalogs, so I feel no desire to do it differently here.
        regards, tom lane


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
> On 1/26/2007 4:40 PM, Jim Nasby wrote:
>> It would be nice if we had a separate role for replication services  
>> so that we weren't exposing superuser so much.

> So you think about another flag in pg_shadow? Would work for me.

How exactly would such a role differ from a "regular" superuser?  It
would still need an awful lot of privilege bypassing ability.  I'm
pretty dubious that you could lock it down enough to make it worth the
trouble of supporting an additional concept.
        regards, tom lane


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
On 1/26/2007 5:09 PM, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>> On 1/26/2007 4:40 PM, Jim Nasby wrote:
>>> It would be nice if we had a separate role for replication services  
>>> so that we weren't exposing superuser so much.
> 
>> So you think about another flag in pg_shadow? Would work for me.
> 
> How exactly would such a role differ from a "regular" superuser?  It
> would still need an awful lot of privilege bypassing ability.  I'm
> pretty dubious that you could lock it down enough to make it worth the
> trouble of supporting an additional concept.

As already said in the other mail, conflict resolution means that at 
some point you will be in the situation where you need a third role. The 
one of the replication admin that can do things that don't replicate. 
Polluting the system catalogs with flags for one specific external 
system isn't my thing. The different trigger modes as well as the 
snapshot cloning and the commit timestamp are all features, not 
exclusively useful for the one replication system I have in mind. They 
would have made my life developing Slony-I a lot easier to begin with. I 
would never have needed the stupid xxid or the poking around in the 
system catalog.


Jan

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


Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Markus Schiltknecht
Date:
Hi,

Jim Nasby wrote:
> Note that those terms only make sense if you limit yourself to thinking 
> the master is pushing data out to the slave...

I don't really get the "limitation" here. It's all about distinguishing 
between master/slave, origin/replica, local/remote - however you want to 
call it.

> I think it'd make the most sense if the name reflected whether the 
> trigger should be fired by a replication process or not; that way it 
> doesn't really matter if it's a master or a slave...

I think you are mixing the meaning of multi-master replication vs. a 
per-transaction 'master' (local transaction / origin node of the txn), 
which then propagates this transaction to the 'slaves' (remote/replica) 
of that transaction. This does not have anything to do with the more 
general multi-master vs. single-master replication distinction, as even 
in multi-master replication, each transaction must have a 'local' or 
'origin' node.

Regards

Markus



Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jim Nasby
Date:
On Jan 26, 2007, at 5:09 PM, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>> On 1/26/2007 4:40 PM, Jim Nasby wrote:
>>> It would be nice if we had a separate role for replication services
>>> so that we weren't exposing superuser so much.
>
>> So you think about another flag in pg_shadow? Would work for me.

Not really sure if that's necessary or not... there might be better  
ways to do it.

> How exactly would such a role differ from a "regular" superuser?  It
> would still need an awful lot of privilege bypassing ability.  I'm
> pretty dubious that you could lock it down enough to make it worth the
> trouble of supporting an additional concept.

There's two cases...

First is the role that actually sets up replication. It's going to  
need a decent amount of privileges... on the origin, it will need to  
add triggers to tables. Possibly create a schema as well (though, I'd  
argue that that should happen when you install replication, which is  
different than just adding a new table to a replication set, or  
adding a new node).

On the replica, it's going to need to be able to alter tables to  
disable triggers. If we want to be fancy and replicate DDL, it'd need  
to be able to do that as well.

But it's important to note that we could require the user to grant  
those abilities specifically to the replication admin role. Maybe not  
what we actually want, but it's something to consider.

The second case is the role that's actually replicating data. It will  
need to INSERT/UPDATE/DELETE on replica tables. Presumably it will  
need some rights on objects that actually implement the replication  
(think objects in the _cluster_name schema in slony), but when the  
node is added the replication admin role should be able to handle that.

Both of those are much more limited than a superuser is... they can't  
create databases, they can't run admin functions such as  
pg_cancel_backend, etc, etc.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)




Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
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)

A sessions current role is controlled by the PG_SUSET GUC
session_replication_role. The possible states are origin, replica and
local. The local state is identical to origin with respect to trigger
firing. It is intended to be used to issue statements that do not get
replicated at all.

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?


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.213
diff -u -r1.213 tablecmds.c
--- src/backend/commands/tablecmds.c    2 Feb 2007 00:07:02 -0000    1.213
+++ src/backend/commands/tablecmds.c    2 Feb 2007 20:27:47 -0000
@@ -3,7 +3,7 @@
  * tablecmds.c
  *      Commands for creating and altering table structures and settings
  *
- * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1996-2007, PostgreSQL Global DevelopmEnt Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  *
@@ -252,7 +252,7 @@
 static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
 static void ATExecSetRelOptions(Relation rel, List *defList, bool isReset);
 static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
-                           bool enable, bool skip_system);
+                           char fires_when, bool skip_system);
 static void ATExecAddInherit(Relation rel, RangeVar *parent);
 static void ATExecDropInherit(Relation rel, RangeVar *parent);
 static void copy_relation_data(Relation rel, SMgrRelation dst);
@@ -2192,6 +2192,8 @@
             pass = AT_PASS_MISC;
             break;
         case AT_EnableTrig:        /* ENABLE TRIGGER variants */
+        case AT_EnableAlwaysTrig:
+        case AT_EnableReplicaTrig:
         case AT_EnableTrigAll:
         case AT_EnableTrigUser:
         case AT_DisableTrig:    /* DISABLE TRIGGER variants */
@@ -2364,24 +2366,40 @@
         case AT_ResetRelOptions:        /* RESET (...) */
             ATExecSetRelOptions(rel, (List *) cmd->def, true);
             break;
-        case AT_EnableTrig:        /* ENABLE TRIGGER name */
-            ATExecEnableDisableTrigger(rel, cmd->name, true, false);
+
+        case AT_EnableTrig:            /* ENABLE TRIGGER name */
+            ATExecEnableDisableTrigger(rel, cmd->name,
+                    TRIGGER_FIRES_ON_ORIGIN, false);
+            break;
+        case AT_EnableAlwaysTrig:    /* ENABLE ALWAYS TRIGGER name */
+            ATExecEnableDisableTrigger(rel, cmd->name,
+                    TRIGGER_FIRES_ALWAYS, false);
+            break;
+        case AT_EnableReplicaTrig:    /* ENABLE REPLICA TRIGGER name */
+            ATExecEnableDisableTrigger(rel, cmd->name,
+                    TRIGGER_FIRES_ON_REPLICA, false);
             break;
         case AT_DisableTrig:    /* DISABLE TRIGGER name */
-            ATExecEnableDisableTrigger(rel, cmd->name, false, false);
+            ATExecEnableDisableTrigger(rel, cmd->name,
+                    TRIGGER_DISABLED, false);
             break;
         case AT_EnableTrigAll:    /* ENABLE TRIGGER ALL */
-            ATExecEnableDisableTrigger(rel, NULL, true, false);
+            ATExecEnableDisableTrigger(rel, NULL,
+                    TRIGGER_FIRES_ON_ORIGIN, false);
             break;
         case AT_DisableTrigAll:    /* DISABLE TRIGGER ALL */
-            ATExecEnableDisableTrigger(rel, NULL, false, false);
+            ATExecEnableDisableTrigger(rel, NULL,
+                    TRIGGER_DISABLED, false);
             break;
         case AT_EnableTrigUser:    /* ENABLE TRIGGER USER */
-            ATExecEnableDisableTrigger(rel, NULL, true, true);
+            ATExecEnableDisableTrigger(rel, NULL,
+                    TRIGGER_FIRES_ON_ORIGIN, true);
             break;
         case AT_DisableTrigUser:        /* DISABLE TRIGGER USER */
-            ATExecEnableDisableTrigger(rel, NULL, false, true);
+            ATExecEnableDisableTrigger(rel, NULL,
+                    TRIGGER_DISABLED, true);
             break;
+
         case AT_AddInherit:
             ATExecAddInherit(rel, (RangeVar *) cmd->def);
             break;
@@ -4549,7 +4567,7 @@
     MemSet(&trig, 0, sizeof(trig));
     trig.tgoid = InvalidOid;
     trig.tgname = fkconstraint->constr_name;
-    trig.tgenabled = TRUE;
+    trig.tgenabled = TRIGGER_FIRES_ON_ORIGIN;
     trig.tgisconstraint = TRUE;
     trig.tgconstrrelid = RelationGetRelid(pkrel);
     trig.tgdeferrable = FALSE;
@@ -6157,9 +6175,9 @@
  */
 static void
 ATExecEnableDisableTrigger(Relation rel, char *trigname,
-                           bool enable, bool skip_system)
+                           char fires_when, bool skip_system)
 {
-    EnableDisableTrigger(rel, trigname, enable, skip_system);
+    EnableDisableTrigger(rel, trigname, fires_when, skip_system);
 }

 /*
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.212
diff -u -r1.212 trigger.c
--- src/backend/commands/trigger.c    25 Jan 2007 04:17:46 -0000    1.212
+++ src/backend/commands/trigger.c    2 Feb 2007 19:04:58 -0000
@@ -53,6 +53,13 @@
 static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event,
                       bool row_trigger, HeapTuple oldtup, HeapTuple newtup);

+/*
+ * SessionReplicationRole -
+ *
+ *    Global variable that controls the trigger firing behaviour based
+ *    on pg_trigger.tgenabled. This is maintained from misc/guc.c.
+ */
+int    SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;

 /*
  * Create a trigger.  Returns the OID of the created trigger.
@@ -305,7 +312,7 @@
                                                   CStringGetDatum(trigname));
     values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
     values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
-    values[Anum_pg_trigger_tgenabled - 1] = BoolGetDatum(true);
+    values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
     values[Anum_pg_trigger_tgisconstraint - 1] = BoolGetDatum(stmt->isconstraint);
     values[Anum_pg_trigger_tgconstrname - 1] = DirectFunctionCall1(namein,
                                                 CStringGetDatum(constrname));
@@ -723,11 +730,11 @@
  * EnableDisableTrigger()
  *
  *    Called by ALTER TABLE ENABLE/DISABLE TRIGGER
- *    to change 'tgenabled' flag for the specified trigger(s)
+ *    to change 'tgenabled' field for the specified trigger(s)
  *
  * rel: relation to process (caller must hold suitable lock on it)
  * tgname: trigger to process, or NULL to scan all triggers
- * enable: new value for tgenabled flag
+ * enable: new value for tgenabled field
  * skip_system: if true, skip "system" triggers (constraint triggers)
  *
  * Caller should have checked permissions for the table; here we also
@@ -736,7 +743,7 @@
  */
 void
 EnableDisableTrigger(Relation rel, const char *tgname,
-                     bool enable, bool skip_system)
+                     char fires_when, bool skip_system)
 {
     Relation    tgrel;
     int            nkeys;
@@ -787,13 +794,13 @@

         found = true;

-        if (oldtrig->tgenabled != enable)
+        if (oldtrig->tgenabled != fires_when)
         {
             /* need to change this one ... make a copy to scribble on */
             HeapTuple    newtup = heap_copytuple(tuple);
             Form_pg_trigger newtrig = (Form_pg_trigger) GETSTRUCT(newtup);

-            newtrig->tgenabled = enable;
+            newtrig->tgenabled = fires_when;

             simple_heap_update(tgrel, &newtup->t_self, newtup);

@@ -1352,8 +1359,18 @@
         Trigger    *trigger = &trigdesc->triggers[tgindx[i]];
         HeapTuple    newtuple;

-        if (!trigger->tgenabled)
-            continue;
+        if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
+        else /* ORIGIN or LOCAL role */
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
         LocTriggerData.tg_trigger = trigger;
         newtuple = ExecCallTriggerFunc(&LocTriggerData,
                                        tgindx[i],
@@ -1401,8 +1418,18 @@
     {
         Trigger    *trigger = &trigdesc->triggers[tgindx[i]];

-        if (!trigger->tgenabled)
-            continue;
+        if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
+        else /* ORIGIN or LOCAL role */
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
         LocTriggerData.tg_trigtuple = oldtuple = newtuple;
         LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
         LocTriggerData.tg_trigger = trigger;
@@ -1463,8 +1490,18 @@
         Trigger    *trigger = &trigdesc->triggers[tgindx[i]];
         HeapTuple    newtuple;

-        if (!trigger->tgenabled)
-            continue;
+        if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
+        else /* ORIGIN or LOCAL role */
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
         LocTriggerData.tg_trigger = trigger;
         newtuple = ExecCallTriggerFunc(&LocTriggerData,
                                        tgindx[i],
@@ -1519,8 +1556,18 @@
     {
         Trigger    *trigger = &trigdesc->triggers[tgindx[i]];

-        if (!trigger->tgenabled)
-            continue;
+        if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
+        else /* ORIGIN or LOCAL role */
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
         LocTriggerData.tg_trigtuple = trigtuple;
         LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
         LocTriggerData.tg_trigger = trigger;
@@ -1594,8 +1641,18 @@
         Trigger    *trigger = &trigdesc->triggers[tgindx[i]];
         HeapTuple    newtuple;

-        if (!trigger->tgenabled)
-            continue;
+        if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
+        else /* ORIGIN or LOCAL role */
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
         LocTriggerData.tg_trigger = trigger;
         newtuple = ExecCallTriggerFunc(&LocTriggerData,
                                        tgindx[i],
@@ -1655,8 +1712,18 @@
     {
         Trigger    *trigger = &trigdesc->triggers[tgindx[i]];

-        if (!trigger->tgenabled)
-            continue;
+        if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
+        else /* ORIGIN or LOCAL role */
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
         LocTriggerData.tg_trigtuple = trigtuple;
         LocTriggerData.tg_newtuple = oldtuple = newtuple;
         LocTriggerData.tg_trigtuplebuf = InvalidBuffer;
@@ -3286,8 +3353,18 @@
         Trigger    *trigger = &trigdesc->triggers[tgindx[i]];

         /* Ignore disabled triggers */
-        if (!trigger->tgenabled)
-            continue;
+        if (SessionReplicationRole == SESSION_REPLICATION_ROLE_REPLICA)
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_ORIGIN ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }
+        else /* ORIGIN or LOCAL role */
+        {
+            if (trigger->tgenabled == TRIGGER_FIRES_ON_REPLICA ||
+                trigger->tgenabled == TRIGGER_DISABLED)
+                continue;
+        }

         /*
          * If this is an UPDATE of a PK table or FK table that does not change
Index: src/backend/parser/gram.y
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.579
diff -u -r2.579 gram.y
--- src/backend/parser/gram.y    3 Feb 2007 14:06:54 -0000    2.579
+++ src/backend/parser/gram.y    3 Feb 2007 20:53:24 -0000
@@ -363,7 +363,7 @@

 /* ordinary key words in alphabetical order */
 %token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
-    AGGREGATE ALL ALSO ALTER ANALYSE ANALYZE AND ANY ARRAY AS ASC
+    AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
     ASSERTION ASSIGNMENT ASYMMETRIC AT AUTHORIZATION

     BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
@@ -420,8 +420,8 @@
     QUOTE

     READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
-    REPEATABLE REPLACE RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT
-    ROLE ROLLBACK ROW ROWS RULE
+    REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
+    RIGHT ROLE ROLLBACK ROW ROWS RULE

     SAVEPOINT SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE
     SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE
@@ -1478,6 +1478,22 @@
                     n->name = $3;
                     $$ = (Node *)n;
                 }
+            /* ALTER TABLE <name> ENABLE ALWAYS TRIGGER <trig> */
+            | ENABLE_P ALWAYS TRIGGER name
+                {
+                    AlterTableCmd *n = makeNode(AlterTableCmd);
+                    n->subtype = AT_EnableAlwaysTrig;
+                    n->name = $4;
+                    $$ = (Node *)n;
+                }
+            /* ALTER TABLE <name> ENABLE REPLICA TRIGGER <trig> */
+            | ENABLE_P REPLICA TRIGGER name
+                {
+                    AlterTableCmd *n = makeNode(AlterTableCmd);
+                    n->subtype = AT_EnableReplicaTrig;
+                    n->name = $4;
+                    $$ = (Node *)n;
+                }
             /* ALTER TABLE <name> ENABLE TRIGGER ALL */
             | ENABLE_P TRIGGER ALL
                 {
@@ -8654,6 +8670,7 @@
             | AGGREGATE
             | ALSO
             | ALTER
+            | ALWAYS
             | ASSERTION
             | ASSIGNMENT
             | AT
@@ -8799,6 +8816,7 @@
             | RENAME
             | REPEATABLE
             | REPLACE
+            | REPLICA
             | RESET
             | RESTART
             | RESTRICT
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.184
diff -u -r1.184 keywords.c
--- src/backend/parser/keywords.c    25 Jan 2007 11:53:51 -0000    1.184
+++ src/backend/parser/keywords.c    2 Feb 2007 20:22:24 -0000
@@ -42,6 +42,7 @@
     {"all", ALL},
     {"also", ALSO},
     {"alter", ALTER},
+    {"always", ALWAYS},
     {"analyse", ANALYSE},        /* British spelling */
     {"analyze", ANALYZE},
     {"and", AND},
@@ -289,6 +290,7 @@
     {"rename", RENAME},
     {"repeatable", REPEATABLE},
     {"replace", REPLACE},
+    {"replica", REPLICA},
     {"reset", RESET},
     {"restart", RESTART},
     {"restrict", RESTRICT},
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.372
diff -u -r1.372 guc.c
--- src/backend/utils/misc/guc.c    1 Feb 2007 19:10:28 -0000    1.372
+++ src/backend/utils/misc/guc.c    2 Feb 2007 18:03:25 -0000
@@ -34,6 +34,7 @@
 #include "commands/async.h"
 #include "commands/vacuum.h"
 #include "commands/variable.h"
+#include "commands/trigger.h"
 #include "funcapi.h"
 #include "libpq/auth.h"
 #include "libpq/pqformat.h"
@@ -120,6 +121,8 @@

 static const char *assign_defaultxactisolevel(const char *newval, bool doit,
                            GucSource source);
+static const char *assign_session_replication_role(const char *newval, bool doit,
+                           GucSource source);
 static const char *assign_log_min_messages(const char *newval, bool doit,
                         GucSource source);
 static const char *assign_client_min_messages(const char *newval,
@@ -222,6 +225,7 @@
 static char *client_encoding_string;
 static char *datestyle_string;
 static char *default_iso_level_string;
+static char *session_replication_role_string;
 static char *locale_collate;
 static char *locale_ctype;
 static char *regex_flavor_string;
@@ -1917,6 +1921,16 @@
     },

     {
+        {"session_replication_role", PGC_SUSET, CLIENT_CONN_STATEMENT,
+            gettext_noop("Sets the sessions behaviour for triggers and rewrite rules."),
+            gettext_noop("Each session can be either"
+                         " \"origin\", \"replica\" or \"local\".")
+        },
+        &session_replication_role_string,
+        "origin", assign_session_replication_role, NULL
+    },
+
+    {
         {"dynamic_library_path", PGC_SUSET, CLIENT_CONN_OTHER,
             gettext_noop("Sets the path for dynamically loadable modules."),
             gettext_noop("If a dynamically loadable module needs to be opened and "
@@ -6099,6 +6113,29 @@
 }

 static const char *
+assign_session_replication_role(const char *newval, bool doit, GucSource source)
+{
+    if (pg_strcasecmp(newval, "origin") == 0)
+    {
+        if (doit)
+            SessionReplicationRole = SESSION_REPLICATION_ROLE_ORIGIN;
+    }
+    else if (pg_strcasecmp(newval, "replica") == 0)
+    {
+        if (doit)
+            SessionReplicationRole = SESSION_REPLICATION_ROLE_REPLICA;
+    }
+    else if (pg_strcasecmp(newval, "local") == 0)
+    {
+        if (doit)
+            SessionReplicationRole = SESSION_REPLICATION_ROLE_LOCAL;
+    }
+    else
+        return NULL;
+    return newval;
+}
+
+static const char *
 assign_log_min_messages(const char *newval,
                         bool doit, GucSource source)
 {
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.459
diff -u -r1.459 pg_dump.c
--- src/bin/pg_dump/pg_dump.c    25 Jan 2007 03:30:43 -0000    1.459
+++ src/bin/pg_dump/pg_dump.c    2 Feb 2007 22:34:05 -0000
@@ -3937,7 +3937,7 @@
             tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
             tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
             tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
-            tginfo[j].tgenabled = *(PQgetvalue(res, j, i_tgenabled)) == 't';
+            tginfo[j].tgenabled = *(PQgetvalue(res, j, i_tgenabled));
             tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
             tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';

@@ -8805,11 +8805,27 @@
     }
     appendPQExpBuffer(query, ");\n");

-    if (!tginfo->tgenabled)
+    if (tginfo->tgenabled != 't' && tginfo->tgenabled != 'O')
     {
         appendPQExpBuffer(query, "\nALTER TABLE %s ",
                           fmtId(tbinfo->dobj.name));
-        appendPQExpBuffer(query, "DISABLE TRIGGER %s;\n",
+        switch (tginfo->tgenabled)
+        {
+            case 'D':
+            case 'f':
+                appendPQExpBuffer(query, "DISABLE");
+                break;
+            case 'A':
+                appendPQExpBuffer(query, "ENABLE ALWAYS");
+                break;
+            case 'R':
+                appendPQExpBuffer(query, "ENABLE REPLICA");
+                break;
+            default:
+                appendPQExpBuffer(query, "ENABLE");
+                break;
+        }
+        appendPQExpBuffer(query, " TRIGGER %s;\n",
                           fmtId(tginfo->dobj.name));
     }

Index: src/bin/pg_dump/pg_dump.h
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/bin/pg_dump/pg_dump.h,v
retrieving revision 1.132
diff -u -r1.132 pg_dump.h
--- src/bin/pg_dump/pg_dump.h    23 Jan 2007 17:54:50 -0000    1.132
+++ src/bin/pg_dump/pg_dump.h    2 Feb 2007 22:27:21 -0000
@@ -312,7 +312,7 @@
     char       *tgconstrname;
     Oid            tgconstrrelid;
     char       *tgconstrrelname;
-    bool        tgenabled;
+    char        tgenabled;
     bool        tgdeferrable;
     bool        tginitdeferred;
 } TriggerInfo;
Index: src/bin/psql/describe.c
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.150
diff -u -r1.150 describe.c
--- src/bin/psql/describe.c    20 Jan 2007 21:17:30 -0000    1.150
+++ src/bin/psql/describe.c    2 Feb 2007 22:04:52 -0000
@@ -1054,14 +1054,12 @@
                    *result3 = NULL,
                    *result4 = NULL,
                    *result5 = NULL,
-                   *result6 = NULL,
-                   *result7 = NULL;
+                   *result6 = NULL;
         int            check_count = 0,
                     index_count = 0,
                     foreignkey_count = 0,
                     rule_count = 0,
                     trigger_count = 0,
-                    disabled_trigger_count = 0,
                     inherits_count = 0;
         int            count_footers = 0;

@@ -1124,10 +1122,10 @@
         if (tableinfo.triggers)
         {
             printfPQExpBuffer(&buf,
-                     "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
+                     "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid), "
+                            "t.tgenabled\n"
                               "FROM pg_catalog.pg_trigger t\n"
                               "WHERE t.tgrelid = '%s' "
-                              "AND t.tgenabled "
                               "AND (NOT t.tgisconstraint "
                               " OR NOT EXISTS"
                               "  (SELECT 1 FROM pg_catalog.pg_depend d "
@@ -1145,31 +1143,6 @@
             }
             else
                 trigger_count = PQntuples(result4);
-
-            /* acquire disabled triggers as a separate list */
-            printfPQExpBuffer(&buf,
-                     "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
-                              "FROM pg_catalog.pg_trigger t\n"
-                              "WHERE t.tgrelid = '%s' "
-                              "AND NOT t.tgenabled "
-                              "AND (NOT t.tgisconstraint "
-                              " OR NOT EXISTS"
-                              "  (SELECT 1 FROM pg_catalog.pg_depend d "
-                              "   JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid =
c.oid)" 
-                              "   WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype =
'f'))"
-                              "   ORDER BY 1",
-                              oid);
-            result7 = PSQLexec(buf.data, false);
-            if (!result7)
-            {
-                PQclear(result1);
-                PQclear(result2);
-                PQclear(result3);
-                PQclear(result4);
-                goto error_return;
-            }
-            else
-                disabled_trigger_count = PQntuples(result7);
         }

         /* count foreign-key constraints (there are none if no triggers) */
@@ -1188,7 +1161,6 @@
                 PQclear(result2);
                 PQclear(result3);
                 PQclear(result4);
-                PQclear(result7);
                 goto error_return;
             }
             else
@@ -1206,7 +1178,6 @@
             PQclear(result3);
             PQclear(result4);
             PQclear(result5);
-            PQclear(result7);
             goto error_return;
         }
         else
@@ -1323,44 +1294,77 @@
         /* print triggers */
         if (trigger_count > 0)
         {
-            printfPQExpBuffer(&buf, _("Triggers:"));
-            footers[count_footers++] = pg_strdup(buf.data);
-            for (i = 0; i < trigger_count; i++)
-            {
-                const char *tgdef;
-                const char *usingpos;
-
-                /* Everything after "TRIGGER" is echoed verbatim */
-                tgdef = PQgetvalue(result4, i, 1);
-                usingpos = strstr(tgdef, " TRIGGER ");
-                if (usingpos)
-                    tgdef = usingpos + 9;
-
-                printfPQExpBuffer(&buf, "    %s", tgdef);
-
-                footers[count_footers++] = pg_strdup(buf.data);
-            }
-        }
+            bool    have_heading;
+            int        category;

-        /* print disabled triggers */
-        if (disabled_trigger_count > 0)
-        {
-            printfPQExpBuffer(&buf, _("Disabled triggers:"));
-            footers[count_footers++] = pg_strdup(buf.data);
-            for (i = 0; i < disabled_trigger_count; i++)
+            /* split the output into 4 different categories.
+             * Enabled triggers, disabled triggers and the two
+             * special ALWAYS and REPLICA configurations.
+             */
+            for (category = 0; category < 4; category++)
             {
-                const char *tgdef;
-                const char *usingpos;
-
-                /* Everything after "TRIGGER" is echoed verbatim */
-                tgdef = PQgetvalue(result7, i, 1);
-                usingpos = strstr(tgdef, " TRIGGER ");
-                if (usingpos)
-                    tgdef = usingpos + 9;
+                have_heading = false;
+                for (i = 0; i < trigger_count; i++)
+                {
+                    bool        list_trigger;
+                    const char *tgdef;
+                    const char *usingpos;
+                    const char *tgenabled;
+
+                    /* Check if this trigger falls into the current category */
+                    tgenabled = PQgetvalue(result4, i, 2);
+                    list_trigger = false;
+                    switch (category)
+                    {
+                        case 0:        if (*tgenabled == 'O' || *tgenabled == 't')
+                                        list_trigger = true;
+                                    break;
+                        case 1:        if (*tgenabled == 'D' || *tgenabled == 'f')
+                                        list_trigger = true;
+                                    break;
+                        case 2:        if (*tgenabled == 'A')
+                                        list_trigger = true;
+                                    break;
+                        case 3:        if (*tgenabled == 'R')
+                                        list_trigger = true;
+                                    break;
+                    }
+                    if (list_trigger == false)
+                        continue;
+
+                    /* Print the category heading once */
+                    if (have_heading == false)
+                    {
+                        switch (category)
+                        {
+                            case 0:
+                                printfPQExpBuffer(&buf, _("Triggers:"));
+                                break;
+                            case 1:
+                                printfPQExpBuffer(&buf, _("Disabled Triggers:"));
+                                break;
+                            case 2:
+                                printfPQExpBuffer(&buf, _("Triggers firing always:"));
+                                break;
+                            case 3:
+                                printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
+                                break;
+
+                        }
+                        footers[count_footers++] = pg_strdup(buf.data);
+                        have_heading = true;
+                    }
+
+                    /* Everything after "TRIGGER" is echoed verbatim */
+                    tgdef = PQgetvalue(result4, i, 1);
+                    usingpos = strstr(tgdef, " TRIGGER ");
+                    if (usingpos)
+                        tgdef = usingpos + 9;

-                printfPQExpBuffer(&buf, "    %s", tgdef);
+                    printfPQExpBuffer(&buf, "    %s", tgdef);

-                footers[count_footers++] = pg_strdup(buf.data);
+                    footers[count_footers++] = pg_strdup(buf.data);
+                }
             }
         }

@@ -1399,7 +1403,6 @@
         PQclear(result4);
         PQclear(result5);
         PQclear(result6);
-        PQclear(result7);
     }

     printTable(title.data, headers,
Index: src/include/catalog/pg_trigger.h
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/include/catalog/pg_trigger.h,v
retrieving revision 1.26
diff -u -r1.26 pg_trigger.h
--- src/include/catalog/pg_trigger.h    5 Jan 2007 22:19:53 -0000    1.26
+++ src/include/catalog/pg_trigger.h    2 Feb 2007 18:15:45 -0000
@@ -37,7 +37,8 @@
     Oid            tgfoid;            /* OID of function to be called */
     int2        tgtype;            /* BEFORE/AFTER UPDATE/DELETE/INSERT
                                  * ROW/STATEMENT */
-    bool        tgenabled;        /* trigger is enabled/disabled */
+    char        tgenabled;        /* trigger's firing configuration
+                                 * WRT session_replication_role */
     bool        tgisconstraint; /* trigger is a RI constraint */
     NameData    tgconstrname;    /* RI constraint name */
     Oid            tgconstrrelid;    /* RI table of foreign key definition */
Index: src/include/commands/trigger.h
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/include/commands/trigger.h,v
retrieving revision 1.60
diff -u -r1.60 trigger.h
--- src/include/commands/trigger.h    5 Jan 2007 22:19:54 -0000    1.60
+++ src/include/commands/trigger.h    2 Feb 2007 18:54:50 -0000
@@ -104,6 +104,15 @@
 #define RI_MAX_NUMKEYS                    INDEX_MAX_KEYS
 #define RI_MAX_ARGUMENTS        (RI_FIRST_ATTNAME_ARGNO + (RI_MAX_NUMKEYS * 2))

+#define SESSION_REPLICATION_ROLE_ORIGIN        0
+#define SESSION_REPLICATION_ROLE_REPLICA    1
+#define SESSION_REPLICATION_ROLE_LOCAL        2
+extern int    SessionReplicationRole;
+
+#define    TRIGGER_FIRES_ON_ORIGIN                'O'
+#define    TRIGGER_FIRES_ALWAYS                'A'
+#define    TRIGGER_FIRES_ON_REPLICA            'R'
+#define    TRIGGER_DISABLED                    'D'

 extern Oid    CreateTrigger(CreateTrigStmt *stmt, bool forConstraint);

@@ -114,7 +123,7 @@
 extern void renametrig(Oid relid, const char *oldname, const char *newname);

 extern void EnableDisableTrigger(Relation rel, const char *tgname,
-                     bool enable, bool skip_system);
+                     char fires_when, bool skip_system);

 extern void RelationBuildTriggers(Relation relation);

Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.340
diff -u -r1.340 parsenodes.h
--- src/include/nodes/parsenodes.h    3 Feb 2007 14:06:55 -0000    1.340
+++ src/include/nodes/parsenodes.h    3 Feb 2007 20:55:11 -0000
@@ -936,6 +936,8 @@
     AT_SetRelOptions,            /* SET (...) -- AM specific parameters */
     AT_ResetRelOptions,            /* RESET (...) -- AM specific parameters */
     AT_EnableTrig,                /* ENABLE TRIGGER name */
+    AT_EnableAlwaysTrig,        /* ENABLE ALWAYS TRIGGER name */
+    AT_EnableReplicaTrig,        /* ENABLE REPLICA TRIGGER name */
     AT_DisableTrig,                /* DISABLE TRIGGER name */
     AT_EnableTrigAll,            /* ENABLE TRIGGER ALL */
     AT_DisableTrigAll,            /* DISABLE TRIGGER ALL */
Index: src/include/utils/rel.h
===================================================================
RCS file: /usr/local/pgsql/CvsRoot/pgsql/src/include/utils/rel.h,v
retrieving revision 1.96
diff -u -r1.96 rel.h
--- src/include/utils/rel.h    25 Jan 2007 02:17:26 -0000    1.96
+++ src/include/utils/rel.h    2 Feb 2007 22:13:59 -0000
@@ -53,7 +53,7 @@
     char       *tgname;
     Oid            tgfoid;
     int16        tgtype;
-    bool        tgenabled;
+    char        tgenabled;
     bool        tgisconstraint;
     Oid            tgconstrrelid;
     bool        tgdeferrable;

Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
"Joshua D. Drake"
Date:
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?

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?

Sincerely,

Joshua D. Drake





-- 
     === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/



Re: Proposal: Change of pg_trigger.tg_enabled and adding

From
Jan Wieck
Date:
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@Yahoo.com #