On 10-08-20 11:44 AM, Christopher Browne 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)
>   declare
>      c_oid oid;
>      c_gotit boolean;
>   begin
>      loop
>          begin
>              execute 'lock table ' || tab_schema || '.' || 'tab_name;';
>              c_gotit := 't';
>              return 't';
>          exception
>              when 'COULD_NOT_OBTAIN_LOCK';
>              c_gotit := 'f';
>              select c.oid into c_oid from pg_class c, pg_namespace n
>                where c.relname = tab_name and n.nspname = tab_schema
>                      and n.oid = c.relnamespace;
>              perform pg_terminate_backend(pid) from
>                 (select pid from pg_locks where relation = c_oid and
>                  granted) as arnold;
>          end;
>          if c_gotit = 't' then
>             exit;
>          end if;
>      end loop;
>   end;
>
> 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:
>

Maybe this is getting a bit parameter happy, but adding a FORCE option 
would give control to the operator allowing both.

It also touches on another case I was going to bring up - autovacuum.  
Even in a maintenance window, autovacuum may kick off an process tables, 
which can get in the way of DDL.  A way to automatically terminate the 
vacuums would be nice

>    - I declared my intentions and my needs
>    - Y'all aren't following directions
>    - If this takes a *long* time, then there will be greater
>      inconveniences
>    - You're terminated...
>

This would have to be 8.4 and greater though, and you need 
pg_terminate_backend to reliably get rid of connections.  
pg_cancel_backend is too soft of a touch, and lots of times it can't 
interrupt backends in a timely fashion.


-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.