Hello,

We thought it may be helpful if we were to suggest looking at the
locking problem from a different angle. We see two main problems with
DDL schema changes.

On Fri, 2010-08-20 at 11:44 -0400, Christopher Browne wrote:
> Stuart Bishop <stuart at stuartbishop.net> writes:
> blocked. This isn't a slony issue even - the same issue applies when I
> ALTER TABLE in an busy non-replicated environment.

The first problem is that, as mentioned, an ALTER TABLE statement blocks
indefinitely a table. This is a known limitation of many database
management systems.

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

At the table-lock level, this may be effective in many cases.

If this is looked at from a higher-level, a valuable requirement might
be to offer a solution that guarantees no active database connection is
blocked indefinitely. In other words, not to focus strictly on
addressing table locks.

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

Indeed, the problem is that there is no guarantee as to when the schema
change will complete. This is equivalent to downtime and is an expensive
solution. Terminating competing backends that attempt to acquire locks
does not solve the problem. However, it shouldn't be unreasonable to
declare the intention to update without downtime.


The second problem we see is that this approach requires defining a
switch-over point from which the old version of the application the
database serves will no longer work. In other words, it requires
coordination for updating both the database and client instances of the
application, which may be difficult to achieve. As already pointed out,
one effectively needs to declare an outage to their organization.

A more effective solution might be to allow both the old version and the
new version of the application to both work simultaneously, and manage
to allow the old version of the application to work without errors. This
could be achieved by rewriting live the queries issued by the old
version of the application to meet the semantics of the new schema.


We would like to describe the solution proposed by ChronicDB. We
envision a replication system that offers:
- Declaring an intention to update without downtime
- The guarantee no database connection will ever be unresponsive for
more than a user-configurable time period (e.g. less than 3 seconds).
- The capability of allowing the old version and the new version of the
application can run concurrently, indefinitely.

We would welcome feedback in improving this solution, in adding more
features, and in making it more accessible to PostgreSQL users. More
technical details can be found at:

http://chronicdb.com/technical_strengths