On Fri, Aug 20, 2010 at 10:44 PM, Christopher Browne
<cbbrowne at ca.afilias.info> wrote:
> Stuart Bishop <stuart at stuartbishop.net> writes:
>> As a work around, maybe there should be some way for my application to
>> detect that Slony wants to open some locks and block? If slony grabbed
>> an exclusive advisory lock before grabbing its exclusive table locks,
>> and released it when the table locks are granted, my application could
>> simply attempt at the start of the transaction to grab the same
>> exclusive advisory lock and release it straight away.
>
> That's an interesting thought.
>
> I'll need to muse on it more.
>
> The alternative I was thinking of was to write a stored function to use
> in the "Lock section" of EXECUTE SCRIPT that would allow:
>   SELECT claim_locks_with_extreme_prejudice(tab_schema, tab_name);
>
> It would do something like the following (which isn't quite valid code)
>
>  create function claim_locks_with_extreme_prejudice(tab_schema, tab_name)

I think both would be needed to avoid scheduling outages. With the
lock approach, if my application cooperates and am careful with my
DDL, I can turn my scheduled downtime into a 20 second hiccup.
Terminating non-cooperating processes, such as autovacuum or that
pesky backup cronjob you forgot to disable, will ensure that what you
benchmarked as a 20 second hiccup on staging remains a 20 second
hiccup on production (when it counts). The advisory lock approach
makes it purely opt-in. I'd store the advisory lock number used as a
column on sl_set, default NULL,

(For the web application, we use the PostgreSQL statement timeout to
ensure transactions don't take more than 10 seconds, plus 10 seconds
DDL time == 20 seconds pause in their web requests and no error
messages == quick enough that users will not notice or blame their ISP
instead of us :-D )

If PostgreSQL grew a more aggressive locking mode, you wouldn't need
your application to cooperate at all - it would just block when slony
needs its locks. But that is at least 12 months and one volunteer
away.

> This function takes an exclusive lock on the requested table,
> terminating any competing backends that get in its way.  Perhaps it's
> unfriendly, but if you've declared an outage to your organization, then
> it's not "out there" to say:

Just put in a check to not terminate other slony connections or you
might blow your own foot off :-) And a timeout - I'll like to give my
existing connections a few seconds grace before they are executed.


-- 
Stuart Bishop <stuart at stuartbishop.net>
http://www.stuartbishop.net/