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

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(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)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

On 1/25/2007 6:55 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)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(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)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 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.

regards, tom lane

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(at)Yahoo(dot)com #

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 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'.

Regards

Markus

markus(at)bluegap(dot)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/>

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(at)nasby(dot)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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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(at)Yahoo(dot)com #

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(at)Yahoo(dot)com #

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(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)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

Jan Wieck <JanWieck(at)Yahoo(dot)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

On 1/26/2007 5:09 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)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(at)Yahoo(dot)com #

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

On Jan 26, 2007, at 5:09 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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(at)Yahoo(dot)com #

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/

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 #