On 14/09/14 16:24, Andy Colson wrote:
> I wonder if its a transaction thing? Maybe \watch is using a
> transaction for each (or isn't using transactions at all), whereas the
> plpgsql is one long transaction?
>
> Also if one of your replicas is far away, it doesn't really surprise me
> that it might loose connection every once and a while. On the other
> hand, if the box is on the same subnet, right next to the master, and it
> was loosing connection, that would be a bad thing.
>
> So, how far away is the replica? And does 'ps ax|grep postgr' show
> 'idle' or 'idle in transaction' on the \watch and the plpgsql?
The replicas are far away, intercontinental far. I am not complaining
that the replica looses the connection. What makes me wonder is that
within a transaction, pg_stat_replication can forget rows but cannot
acquire new ones. I'd think it should be either report the state at the
beginning of the transaction like now() or the current state like
clock_timestamp(). But currently it's reporting half the current state.
Torsten