On Mon, Aug 23, 2010 at 8:55 PM, Steve Singer <ssinger at ca.afilias.info> wrote:

> It is more of an issue with needing a lock that has already been granted to
> another connection.  Say t1 (your application) needs locks on tables a and
> b, and t2 (your DDL script) needs locks on tables b and a.
>
> t1: locks a
> t2: locks b
> t1: tries to lock b, it has to wait for t2
> t2: tries to lock a but it has to wait for t1.
>
> Sometimes postgres can detect these deadlocks but it can't always, in theory
> the lock dependency cycles can be rather complex.
>
> The discussion elsewhere in the thread on solving this with query timeouts
> for application transactions sounds like it would work when combined with
> the 'lock file' option to execute script discussed.

I see. So even if PG grew more aggressive locking options, the
applications using the database would still have to cooperate. I'm
fine with that. For our rollouts, I'm expecting:

 - Background jobs stop running themselves before the rollout time.
 - Interactive systems promise short running transactions, and block
for slony lock requests at the start of a transaction.
 - A process is run after kicking off the slonik script to kill all
non-slony connections that we don't know are cooperating (a white list
of database user names in my case).

The suggestion to automatically kill processes with locks slony is
after would make that last stop unnecessary and would be friendlier.


> The DDL echoing sounds do-able open a bug in the slony bugzilla for it.
>
> I would also find SQL inline to the slonik script useful.  Open a bug for
> that as well but I'm not sure (without looking into it) how easy it will be
> to get the slonik parser to properly accept inline SQL.

Bug 151, Bug 152 and you get Bug 153 as a freebie.


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