Geoffrey <lists at serioustechnology.com> writes:
> I am revisiting this issue as it seems I may have left something out
> of my slony solution.  Note from below we are trying to set the slony
> triggers to 'fire always' so that we can take advantage of
> session_replication_role.

Note that this is only the case with Slony-I version 2.0, which hasn't
yet been officially released.  (We've had rc2; I should "bake" rc3
this afternoon so we can hopefully finalize 2.0.0 next week...)

With versions 1.0, 1.1, 1.2 (and sub-versions under that), it is
necessary to handle the "fire always" triggers via the slonik STORE
TRIGGER command (which disappears in v2.0).

So, if you're using the earlier versions of Slony-I, then
session_replication_role isn't of any use; it didn't exist then, so
isn't used in the earlier versions.

> That being said, I'm back to trying to figure out how I can be sure
> that I set the slony triggers to 'fire always' at the 'right time' so
> as to insure our replication is working.

> As I understand it, we need the creation of the slony triggers and the
> setting of those triggers to 'fire always' to reside in the same
> transaction.

Hmm.  Slony creates *its own* triggers inside the relevant
transaction, but that shouldn't be your concern (except insofar as
you'd expect it to handle its own internals properly!).

How you establish your own triggers is a function of more than just
the matter of in which transaction the triggers are created; it is
also affected by any other relevant initialization.

For instance, we have a custom trigger (well, several :-)) for
managing invalidation of an application cache; whenever updates take
place to relevant objects on the replica, the trigger causes the
management process to remove the invalidated data from the cache.

Whenever we "boot up" the cache manager, there's a brief period when
it's not totally consistent.  To get consistency, the initialization
process looks like:

    begin;
    truncate cache_manager_table, cache_contents;
    commit;

(Actually, Ops don't usually do it quite that way, but this *would* be
a good way to do it...)

If whatever it is that your triggers are managing has some more
sophisticated state, then your process to "make it consistent" may be
more complex.

If the "make it consistent" process is looking exceedingly
expensive/complex, then that may be a sign that you're doing something
not-entirely-wise.

But note that in that "initialization" process, I didn't do anything
about defining the triggers.  For our purposes, having an out-of-date
cache for a few minutes isn't disastrous.  

- On the one hand, the condition is unlikely to ever be noticed during
  the interim period during which it's a *little* broken.  

- And on the other hand, truncating the cached data cleans it up
  perfectly well.

Your milage may vary.  Mind you, if it varies by a LOT, I'd question
whether triggers are a good choice for the application.

Oracle Magazine had a pretty good article recently on the use and
abuse of triggers.  It has some good good questions worth asking one's
self.
<http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html>

"Some people, when confronted with a problem, think 'I know, I'll use
 regular expressions.'  Now they have two problems."
        -- Jamie Zawinski, on comp.lang.emacs

That is the traditional attribution of that quote.  I have also heard
it used as:
  'I know, I'll use sed.'  Now they have two problems.

I think it works pretty well for:
  'I know, I'll use triggers.'  Now they have two problems.

:-)
-- 
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/advocacy.html
Send  messages calling for fonts  not  available to the  recipient(s).
This can (in the case of Zmail) totally disable the user's machine and
mail system for up to a whole day in some circumstances.
-- from the Symbolics Guidelines for Sending Mail