Re: Transaction Snapshots and Hot Standby

Heikki Linnakangas wrote:
> Simon Riggs wrote:
>> Taking snapshots from primary has a few disadvantages
>>
>> ...
>> * snapshots on primary prevent row removal (but this was also an
>> advantage of this technique!)
>
> That makes it an awful solution for high availability. A backend hung in
> transaction-in-progress state in the slave will prevent row removal on
> the master. Isolating the master from queries done performed in the
> slave is exactly the reason why people use hot standby. And running long
> reporting queries in the standby is again a very typical use case.

I have to say I agree with Heikki here. Blocking the master based on
what the slave is doing seems to make host standby less useful than warm.

> I like the idea of acquiring snapshots locally in the slave much more.

It's the option that I can see people (well, me) understanding the
easiest. All the others sound like ways to get things wrong. As for
inconsistent query-results - that way madness lies. How on earth will
anyone be able to diagnose or report bugs when they occur?

> As you mentioned, the options there are to defer applying WAL, or cancel
> queries. I think both options need the same ability to detect when
> you're about to remove a tuple that's still visible to some snapshot,
> just the action is different. We should probably provide a GUC to
> control which you want.

I think there's only one value here: "hot standby wal delay time before
cancelling query". Might be a shorter name.

--
Richard Huxton
Archonet Ltd