On 11/30/2005 7:04 PM, Casey Duncan wrote:

> I've run up against the fairly well documented constraint in the  
> copy_set() function in remote_worker.c. The constraint delays copy_set  
> if there is a pending transaction that was opened prior to the one that  
> installed the slony triggers.
> 
> This entry in the faq describes the issue:
> http://developer.postgresql.org/~wieck/slony1/adminguide-1.1.rc1/ 
> faq.html#AEN2675
> 
> My question has to do with the last paragraph of that entry:
> 
> "By the way, if there is more than one database on the PostgreSQL  
> cluster, and activity is taking place on the OTHER database, that will  
> lead to there being "transactions earlier than XID whatever" being  
> found to be still in progress. The fact that it's a separate database  
> on the cluster is irrelevant; Slony-I will wait until those old  
> transactions terminate."
> 
> (I'm glad that was in there otherwise I'd have torn my hair out by now  
> btw ;^)
> 
> Looking at the code for copy_set(), it makes a query that calls the  
> getMinXid() stored func to check for pending transactions. getMinXid(),  
> as I understand it returns the transaction id of the oldest running  
> transaction in the entire postgres cluster, regardless of what database  
> it is in.
> 
> 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'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.


Jan

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