Steve Singer ssinger at ca.afilias.info
Mon Aug 23 06:55:07 PDT 2010
Stuart Bishop wrote:
>> Robert Collins wrote
>>
> So the problem with needing locks on busy tables is they might never
> be granted. A busy table will always have several connections with
> open transactions that have read from it, and the exclusive lock will
> never be granted because there are always conflicting locks in the
> way. I suspect this needs extensions to PostgreSQL, so we can request
> an exclusive lock in such a way that requests for other locks are
> blocked. This isn't a slony issue even - the same issue applies when I
> ALTER TABLE in an busy non-replicated environment.
> 

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.




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

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.





-- 
Steve Singer
Afilias Canada
Data Services Developer
416-673-1142


More information about the Slony1-general mailing list