Re: BUG #6571: Postgres Kills 'Select 1' query randomly on hot standby databases

The connection that is executing the SELECT 1 are generally open for 1-5
hours before they are killed. The specific connection only executes
SELECT 1. The transaction is simply BEGIN, and then SELECT 1's, no
other query is executed.

The updates we make to the primary database are quiet large, usually
several megabytes.

Alex

On 4/2/2012 11:49 PM, Heikki Linnakangas wrote:
> On 03.04.2012 02:23, amatzinger(at)experts-exchange(dot)com wrote:
>> On a hot standby database, while the primary is being updated,
>> Postgres will
>> randomly kill a process which is performing a "Select 1" command.
>>
>> The error is this:
>> 2012-04-02 13:36:13.269
>> PDT,"smxuser","smxprd1",39523,"127.0.0.1:57893",4f79ffad.9a63,1,"",2012-04-02
>>
>> 12:36:13 PDT,3/32,0,FATAL,40001,"terminating connection due to
>> conflict with
>> recovery","User query might have needed to see row versions that must be
>> removed.","In a moment you should be able to reconnect to the
>> database and
>> repeat your command.",,,,,,,""
>>
>> We have 5 hot standby's set up, which all preform this SELECT 1, and
>> postgres kills them across all standby's.
>>
>> There should never be a situation that SELECT 1 is in conflict with
>> data, as
>> it it never using any table in the database.
>
> The system doesn't make a difference between queries like "SELECT 1"
> that don't access any tables, and those that do. Even if "SELECT 1"
> doesn't access any tables, a subsequent statement in the same
> transaction might.
>
> I'm assuming that those "SELECT 1"s were issued in transactions that
> had been open for a long time, because you shouldn't get recovery
> conflicts with very short transactions, in practice anyway.
>