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

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com