Re: General Advice for avoiding concurrency during schema migrations

Hi Ken,

With that level of dinamism of application servers where there is no way to
keep consistency among them , as you say concurrency must be turned into a
single thread to make sure schema migration will not be locked up by
application threads .

Have you though about constraining connections with a connection pooler to
handle incoming concurrency ?

Cheers,
A.A.

On Mon, Mar 24, 2014 at 10:59 AM, Ken Barber <ken(at)bob(dot)sh> wrote:

> > Do you really need to allow web server connections to the database
> during a
> > schema migration ? Why not locking them up either with pg_hba.cong or a
> > firewal rule or symply shut it off temporarily ?
>
> So this would be ideal if we could control the situation 100%, to be
> clear our software is a shipped product
> (http://github.com/puppetlabs/puppetdb) not something in-house so we
> have to deal with cases that involve users not reading docs and doing
> crazy stuff and still trying to preserve integrity :-).
>
> The more I think on the issue, the more it is around dealing with two
> potential scenarios:
>
> * Someone has upgraded our software in two (or more) places and they
> race to migrate.
> * Someone has upgraded our software in only one place and other
> application servers are not aware of that and continue to attempt to
> work as before.
>
> My current line of thought is around:
>
> * Bumping our migration transaction isolation level to serializable.
> For two migrations that are racing, I'd prefer that the loser backs
> off, then realizes the migration has already happened and skips that
> step.
> * Perhaps locking tables across the board (or an a case-by-case,
> depending on how hard-core we need it). While this might seem
> unnecessary with serialization, it might fill some other gaps I have
> fears about.
> * Having the other (older & still running) application servers handle
> a bump in the schema version properly by either stopping processing
> and returning errors, or something else more clever. Without a
> constant check on each transaction though, this could still allow some
> failures through.
>
> In most cases I'd prefer a fatal error rather than old application
> servers to still continue processing and failing. There is also a lot
> to be said about taking an app out of commission from a load balancer
> perspective to avoid continued failed reception of messages an old app
> server can't possibly process.
>
> ken.
>