On Dec 1, 2005, at 1:20 PM, Jan Wieck wrote:

> On 11/30/2005 7:04 PM, Casey Duncan wrote:
> [..]
>> I wonder why this blanket limitation is necessary. could we not, at  
>> least in theory, limit this check to the subscribing node db (and 
>> maybe  the provider node, I'm not sure)? If so, to my naive eyes, it 
>> would  seem appropriate to query pg_locks or pg_stat_activity to 
>> perform this  check, limiting it to only the databases that were 
>> absolutely necessary  to check. Is this possible or is there some 
>> limitation or edge case  that makes this unsafe? Will long-running 
>> read transactions also block  copy_set?
>
> This "blanket limitation" is used because this information is readily 
> available in the transactions snapshot information. To the the fine 
> grained information you're looking for, one would have to traverse the 
> PGPROC array that's sitting in shared memory.

I'll have a look at that. If nothing else it gives me an excuse to poke 
around more in the internals ;^)

>> I'd like to develop a patch that loosens this restriction if 
>> possible.  I'm finding it quite troublesome especially on my dev and 
>> qa systems.
>
> If you have broken clients that leave open transactions around for 
> infinite times (I don't say you have, but I have seen that before), 
> then you have much bigger problems to worry about. One of them would 
> be that vacuum cannot remove rows, which in the Slony case will also 
> cause that pg_listener and other relations will clog up with dead 
> tuples.

Understood. This did flush out at least one case where starting an 
application and leaving it dormant would leave open transactions 
indefinitely. Since in production they aren't really ever dormant, I 
never noticed this, but it caused slony to complain in our development 
environment.

We do have regular transactions that take several minutes to complete 
(some reading only, others updating) on databases in the same postgres 
instance as the would-be provider node. Restricting this check to the 
provider db would make creating nodes much smoother for us, and I think 
rid slony of a rather unintuitive failure mode.

-Casey