Geoffrey <lists at serioustechnology.com> writes:
> Christopher Browne wrote:
>> 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.
>
> So, I'm confused, but that's easy to do.  Are you saying that in the
> version of slony we are using (1.2.14), that session_replication_role
> does not exist, and therefore by trying to use that functionality
> within our code, we are not accomplishing what we are trying to do?

That's nearly it.

In the version of Slony that you are using, *Slony* does not use
session_replication_role, whether it exists in the PostgreSQL build or
not.

Everything that that you said following "therefore" is still true :-).

> The underlying issue here is that we have a process that transfers
> data from many databases to a master database on a regular basis.  In
> order to accomplish this, this process does:
>
>  $rvalue = session_role ($conn[$x], "session","replica");
>
> With the intent of turning off all triggers except for those that are
> set to 'enable always'?

This is what we might call a "temporal" confusion :-).

At present, when running Slony-I 1.2.anything,
session_replication_role is not used by Slony-I; it uses a pretty
gross hack to turn off triggers.  

Specifically, extracting from the function alterTableForReplication():

		-- ----
		-- Disable all existing triggers
		-- ----
		update "pg_catalog".pg_trigger
				set tgrelid = v_tab_row.indexrelid
				where tgrelid = v_tab_row.tab_reloid
				and not exists (
						select true from @NAMESPACE at .sl_table TAB,
								@NAMESPACE at .sl_trigger TRIG
								where TAB.tab_reloid = tgrelid
								and TAB.tab_id = TRIG.trig_tabid
								and TRIG.trig_tgname = tgname
					);

If you have a process that uses session_role to do something similar,
then your process will conflict with what Slony 1.2.14 (or earlier)
does.

What I'd expect to happen is that Slony will "hide" most triggers, as
shown, *irrespective* of what is done with session_role.

That's not "nice" behaviour, which is why Jan added the whole
"session_role" notion to PostgreSQL 8.3.

But Slony-I 1.2.14 doesn't use the nice new PostgreSQL feature,
because it needs to retain compatibility with versions 7.4, 8.0, 8.1,
and 8.2, which don't have session_role.

Slony-I version 2.0 will "play more nicely" as it uses session_role.
And I hope to have it released on Monday.  So, temporally, that answer
doesn't exist yet, but hopefully, next week, it becomes a new reality
:-).
-- 
(format nil "~S@~S" "cbbrowne" "linuxfinances.info")
http://cbbrowne.com/info/nonrdbms.html
"64-bit integers aren't silly.   For instance Bill Gates, of Microsoft
fame, is now worth more than  $4 billion.  You can't represent his net
worth in 32 bits in dollars, let alone cents."  -- Charlie Price