[mythtv] More ideas on PostgreSQL support, comments welcome

[David Härdeman]

On Wed, Sep 01, 2004 at 03:20:25PM +1000, Hamish Moffatt wrote:
>Maybe you (or anyone) could post some reasons why support for Postgres
>would be useful, and why MySQL isn't sufficient? So far there seems to
>have been plenty of people wanting it and/or offering to help, without
>really explaining why.

On Wed, Sep 01, 2004 at 10:59:13AM -0400, J. Donavan Stanley wrote:
>Funny, I've never had a table "randomly" corrupted.   I myself prefer 
>Postgres over MySQL, but MySQL works just fine for Myth in my experience.

Ok some reasons why I would like to see PostgreSQL support in MythTV 
(before anyone reads this: first off, these are to a large extent my 
*opinions*, and secondly, they are intended as an explaination to people  
that feel the need for one).

1) I don't particularly fancy MySQL's tradition of unexpected 
side-effects to queries. A quick look at the MySQL Gotchas page
(http://sql-info.de/mysql/gotchas.html) is enough to scare me...
and I have been bitten by these on more than one occasion.

Hint: check mythtv/libs/libmyth/settings.cpp, line 739:
void AutoIncrementStorage::save(QSqlDatabase* db) {
    if (intValue() == 0) {
        // Generate a new, unique ID
        QString query = QString("INSERT INTO %1 (%2) VALUES (0);").arg(table).arg(column);

When this code is called the first time MythTV is run (when the default 
settings are being inserted in the DB), the following queries are 
generated:

INSERT INTO settings (data) VALUES ('0');

Does anyone see a problem here? Not yet maybe, but look at the settings 
table in MySQL:

settings (
    value varchar(128) NOT NULL,
    data text NULL,
    hostname varchar(255) NULL
)

Does the query above fail? No. Should it? Yes. Will it in the future? 
Who knows?

2) I take a quite dim view of MySQL's stability (yes, subjective)...from 
the top of my head here are three situtations which has caused MySQL to 
crap out during the last year:

A) Another application went haywire, logs filled the partition where 
MySQL data was stored, MySQL responded by suddenly malloc:ing obscene 
amounts of memory and got killed by the OOM killer. Disk space was 
corrected, MySQL restarted and the repair tools run...result? Tables 
were minced.

B) Out-of-memory situation, MySQL thrashes around for a while then craps 
in the tables and dies.

C) SELECT queries from a table which is larger than the total amount of 
RAM...PostgreSQL runs circles around MySQL (this one could be 
interesting in a low-end MythTV box with loads of storage).

Now if all this depends on my skills, my hardware, MySQL vs PostgreSQL 
or just that I am born under a bad sign doesn't really matter...the 
point is that I would prefer PostgreSQL, I am sure that the same applies 
for others, and if MythTV supported both of them, we'd be free to 
choose.

3) The license...I've discussed licensing with people from MySQL AB in a 
previous (commercial) project and let's just say I wasn't too impressed 
with their interpretations (no, I'm not working for making a proprietary 
MythTV easier, but the whims of one company is something which I can 
live without).

4) This is the most important point IMHO, PostgreSQL support gives 
end-users the freedom to choose which DB suits them the best. Sure, it 
adds extra code, but in the process the DB functions might even see some 
cleanups (see the first patch I sent which reduced the total number of 
lines of code) and the PostgreSQL code should be quite 
compartementalized.

Think about it, there is support for OSS and ALSA sound systems in Myth, 
there is patches floating on the list right now for OS X support, there 
is specialized code for some (PVR-350) hardware decoders in Myth.

Does a change in the core sound libraries mean that ALSA and OSS drivers 
must be changed? Possibly. Does that mean that there can be only one audio 
output driver? No. Can it be guaranteed that all sound subsystems work at 
release? No, but a best effort can be made.

So, let's not degenerate into a flamefest on which DB is best, let each 
user choose for him/herself.

(And as a side note, Hamish - thanks for the www-sql packages in Debian, 
they've saved a lot of time for me in the past on some occasions...but 
isn't it weird that they support both MySQL and PostgreSQL?)

//David