Re: Hot Standby query cancellation and Streaming Replication integration

On 26/02/10 14:45, Heikki Linnakangas wrote:
> 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.

That's a relief.

>> 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.

I see - we'd need to age the list of vacuumed tables too, so when the 
oldest transactions complete the correct flags get cleared.

Can we not wait to cancel the transaction until *any* new lock is 
attempted though? That should protect all the single-statement 
long-running transactions that are already underway. Aggregates etc.

--   Richard Huxton  Archonet Ltd