Re: Hot Standby query cancellation and Streaming Replication integration

Richard Huxton wrote:
> On 26/02/10 08:33, Greg Smith wrote:
>> I'm not sure what you might be expecting from the above combination, but
>> what actually happens is that many of the SELECT statements on the table
>> *that isn't even being updated* are canceled. You see this in the logs:
>
> Hmm - this I'd already figured out for myself. It's just occurred to me
> that this could well be the case between databases too. Database A gets
> vacuumed, B gets its queries kicked off on the standby.

No, it's per-database already. Only queries in the same database are
canceled.

> Dumb non-hacker question: why do we cancel all transactions rather than
> just those with "ACCESS SHARE" on the vacuumed table in question? Is it
> the simple fact that we don't know what table this particular section of
> WAL affects, or is it the complexity of tracking all this info?

The problem is that even if transaction X doesn't have an (access share)
lock on the vacuumed table at the moment, it might take one in the
future. Simon proposed mechanisms for storing the information about
vacuumed tables in shared memory, so that if X takes the lock later on
it will get canceled at that point, but that's 9.1 material.

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