Thread: Recovery conflict message lost in user session for 9.3

I noticed this while teaching a Postgres course.

I'm using 9.3.10 (but suspect all/most prev 9.3.x the same):

Suppose I have a primary/standby streaming replication setup.

Session 1 (standby):

bench=# BEGIN;
BEGIN
bench=# DECLARE hcur CURSOR FOR select * FROM pgbench_history;
DECLARE CURSOR
bench=# fetch hcur;
  tid | bid |  aid   | delta |           mtime            | filler
-----+-----+--------+-------+----------------------------+--------
   90 |  98 | 499159 |  4384 | 2015-11-26 20:28:19.502997 |
(1 row)


Session 2 (primary):
bench=# UPDATE pgbench_history SET filler = 'filled';
UPDATE 3159

Session 1 (standby - wait > 30s):

bench=# fetch hcur;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Now in the standby log I see:
FATAL:  terminating connection due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be
removed.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.

However this is not transmitted to the standby user's session, so
leaving an unhelpful message about why the session got killed. Testing
with 9.1, 9.4, 9.5, 9.6 (noted not tested 9.2) seems to show that these
versions get the more descriptive message to the standby user's session
ok. Might be good to have 9.3 behave the same.

regards

Mark

> bench=# fetch hcur;
> server closed the connection unexpectedly
>     This probably means the server terminated abnormally
>     before or while processing the request.
> The connection to the server was lost. Attempting reset: Succeeded.

> Now in the standby log I see:
> FATAL:  terminating connection due to conflict with recovery
> DETAIL:  User query might have needed to see row versions that must be
> removed.
> HINT:  In a moment you should be able to reconnect to the database and
> repeat your command.

> However this is not transmitted to the standby user's session, so
> leaving an unhelpful message about why the session got killed.

Please check to see if commit db6e8e162 fixes this.

            regards, tom lane

On 27/11/15 05:14, Tom Lane wrote:
>> bench=# fetch hcur;
>> server closed the connection unexpectedly
>>     This probably means the server terminated abnormally
>>     before or while processing the request.
>> The connection to the server was lost. Attempting reset: Succeeded.
>
>> Now in the standby log I see:
>> FATAL:  terminating connection due to conflict with recovery
>> DETAIL:  User query might have needed to see row versions that must be
>> removed.
>> HINT:  In a moment you should be able to reconnect to the database and
>> repeat your command.
>
>> However this is not transmitted to the standby user's session, so
>> leaving an unhelpful message about why the session got killed.
>
> Please check to see if commit db6e8e162 fixes this.
>

It does :-)

regards

Mark