'Lite' Databases (Re: sqlite3 and dates)

[rurpy at yahoo.com]

On 02/19/2015 01:47 PM, Chris Angelico wrote:
> On Fri, Feb 20, 2015 at 7:26 AM,  <rurpy at yahoo.com.dmarc.invalid> wrote:
>> I'll point out that five people in this thread (by my
>> count) have said that Postgresql requires a significant
>> amount of work to setup and use.  Only you and Steven claim
>> the opposite.  (And it sounds to me like Steven does not
>> have a lot of experience installing, configuring and
>> maintaining Postgresql -- please correct me if I am wrong
>> Steven.)
> 
> I don't know about Steven, but I do have a certain amount of
> experience installing, configuring, and maintaining;

Right.  I am aware of that as you might have guessed by the 
fact that I directed my question to Steven and not to you. :-)

> [...] the most common changes I've needed to make to Postgres
configs are:
>[...]
> 2) Increasing logging, eg turning on the logging of all queries. Not
> particularly common, but can be handy; and it's a trivial edit anyway.

It may be trivial to you but it is not going to be trivial to
many of end users the kind of apps we're talking about.  One
of the people interested in my little app was at the limits of her
ability when it came to finding a PowerPoint file she'd just been
editing so she could attach it to an email.  So if the logging 
level needs changing, the app better be prepared to do it itself.

>[...]
> So while it makes sense to talk about *experience* "installing,
> configuring, and maintaining", it's really just the installing part
> that actually demands effort. 

No, see below.

> And that can be mitigated in a few ways,
> but ultimately, there's no single simple solution for everything.

That's right but misses the obvious: some solutions are simpler 
than others.  Installing a Postgresql client-server database and 
a Python application is inherently more complex than installing 
a Python application (Sqlite being included in Python).  (Doesn't
that seem rather obvious, even to you?)

And, if using Postgresql is as easy as you claim, surely you 
don't expect to reserve its use only to your applications?  
So we could expect to see many applications, potentially all 
those currently using Sqlite, to start using Postgresql.

Do they all install and run their own independent server?  Each
with its own set of more than a half dozen processes running all
the time?  Each that has to be started up at system boot?  Each
with their own directory of Postgresql software and backend data
directory?  Where do these files all go and how do app developers 
agree on some standard to avoid chaos?  How does each app find a 
network port or socket that doesn't conflict with the others or 
with things the machine owner may run?  For a Sqlite app you are
installing files for which the rules are all pretty well established.

And if the Postresql apps share a server who arbitrates when 
app X decides it needs to set some parameter that breaks app Y?
Or change anything else on the server; things shared in common
by all the apps like users or (as you yourself mentioned) log 
file settings?

The complexities you deny exist do exist and have to be dealt 
with somewhere.  Either you, the developer have to handle them
in the installation and anticipate problems like "Install error:
/var/lib/pgsql/data: already exists" or "data directory contain 
wrong version of Postresql" or you shove the task off onto your 
users in which case they will have to have sufficient expertise 
to deal with them.

Install problems are not of course limited to server software,
they occur with simple Python apps too.  But installing a single
Python app that depends on the version of Sqlite in the minimally
supported version of Python from a self-contained installer will 
have far lower probability of problems than an automated install 
of server software in a very uncontrolled environment.  And whatever
that app install failure probability is, requiring server software 
in addition can only increase it.

> Even
> SQLite3 has issues with that - as soon as you have two programs that
> try to read the same file (eg your application, using Python's module,
> and the sqlite3 command line tool), you have version differences, and
> quite a number of my students have come to me asking why on earth they
> can't upgrade their SQLite3 properly. (Though usually both versions
> are "new enough", so it doesn't matter. It's just a point of
> confusion.)

Irrelevant.  As the application designer you are responsible for 
what you provide (the application and its backend database).  If 
I want to obtain a command line tool somewhere, it's my problem 
to worry about versions -- it has nothing to do with you.

If you mean that you as a developer have to worry about not shipping
two incompatible versions of app code, well... yeah.  I think that's
pretty much a standard part of putting together a working application.

Or is there some other point I missed?

As for no maintenance, I mentioned two common maintenance activities 
in my reply to Steven: backups and server upgrades.