Skip site navigation (1) Skip section navigation (2)

Re: Hot Standby query cancellation and Streaming Replication integration

From: Richard Huxton <dev(at)archonet(dot)com>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: Greg Smith <greg(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Hot Standby query cancellation and Streaming Replication integration
Date: 2010-02-26 14:57:16
Message-ID: 4B87E14C.9080709@archonet.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-02-26 15:07:00
Subject: Re: Avoiding bad prepared-statement plans.
Previous:From: Mark MielkeDate: 2010-02-26 14:49:35
Subject: Re: Avoiding bad prepared-statement plans.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group