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:

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

> I do like the idea (later in this thread) of me explicitly listing
> which resources to lock. Pulled from the top of the DDL or specified
> separately is fine by me.
>
> If you want to make my life easier let me specify the DDL inline in
> the slonik script rather than needing a separate file. Oh - and an
> option to only echo DDL to stdout if it failed (my daily staging
> rebuild logs are very noisy). And a pony.

Upthread, Brad Nicholson commended making the DDL separate, and I pretty
much agree.  It's logically quite separate from the main DDL.

As for echoing to STDOUT only on error, that may be what already
happens, unless I mussed with it (which may have happened ;-)).  I'm not
sure just now.  I'm not sure I like the idea of adding an option for
this; I prefer slonik to remain comparatively simple.  But it's not
purely about my preferences :-).

Feel free to raise bugs on the Bugzilla instance at slony.info.  That
doesn't necessarily mean they'll be accepted and fixed as requested, but
you'll certainly have better luck having things not be missed from some
corner of an email message if they're tracked somewhere that has status
:-).
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"