Moving udd away from sqlite

[Robert Collins]

On Tue, Jun 19, 2012 at 3:09 AM, James Westby
<james.westby at canonical.com> wrote:
>> Since https://bugs.launchpad.net/udd/+bug/724893 has been filed, we
>> haven't understood *when* the importer fails but we know it can fail and
>> it failed more often recently (and a lot during your first attempt if I
>> remember correctly). If you have a fix for that, great ! Show us how
>> it's fixed with a test.
>
> I'm still not sure what test would be satisfactory for doing this. The
> above setup isn't amendable to a fast unit test.
>
>> And the more tests we add the easier it becomes to add a new one.
>
> Agreed, but I'm not sure that we are working in an area where unit tests
> are a good fix. A unit test can't prove the absence of deadlocks in a
> multi-process system.

Further to that, no amount of testing can show whether an architecture
is good or bad: the architecture talks to things like *tendency to
fail* and expected or predictable emergent behaviour. Testing, of
various sorts, can help you measure particular aspects like - 'in
situation X, does a failure occur', but it cannot tell you which
situation X's to test, nor which situation Y's a test isn't needed
because the structure supports the desired use.

So in this case its very simple: SQLite is designed for a
single-process embedded DB use case. The import is not such a use
case, and *all* the failure modes you are experiencing are predicted
by the statement 'using SQLite with multiple processes'.

Postgresql is designed for multiple processes working on the system at
once, as long as no schema change are being made: schema changes will
cause lock contention, as will reading the same rows at maximum
isolation - which is why LP, for instance, explicitly loosens the
isolation we have.

AIUI we want, and we know we want, to move to postgresql, in
production, as soon as possible. The storm migration is an attempt to
do that.

Some routes are:
 - move to postgresql using the lower level APIs and add storm later
 - work on the storm patchset to make it reliable with sqlite
 - do both postgresql and sqlite at the same time.

My recommendation, given the existing investment, is to bite the
bullet: bring up a postgresql instance in staging and prod, dump and
import the data to it, and switch over to postgresql. Debug any issues
in real-time, and move on. The depth of potential issues is sharply
limited, because postgresql handles many more combinations of client
interaction than SQLite (by design and necessity).

-Rob