Skip site navigation (1) Skip section navigation (2)

Re: Updating old code for new engine

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bob McConnell <rmcconne(at)lightlink(dot)com>
Cc: pgsql-novice mailing list <pgsql-novice(at)postgresql(dot)org>, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
Subject: Re: Updating old code for new engine
Date: 2007-04-01 06:45:30
Message-ID: 6308.1175409930@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Bob McConnell <rmcconne(at)lightlink(dot)com> writes:
> Sean Davis wrote:
>> I'm not sure, but I don't think the statement was ever correct SQL.  In 
>> any case, you would need to add a GROUP BY clause.

> Thanks for the help, looks like I have my work cut out for me. 
> PHPRecipeBook was written for MySQL, but added Postgres as an apparent 
> afterthought. They have not updated the PG side for some time, but they 
> claim it did work with 7.2.3. It would appear they have been seduced by 
> someone with a bias in the other direction.

Hm, these examples appear to shed no credit on either PHPRecipeBook or
MySQL.  Some comments:

* The first query (SELECT count(*) ... ORDER BY with no GROUP BY) is
surely pretty silly, because an aggregate function without GROUP BY
is going to yield only one row, so what's the point of ORDER BY?

It's not legal by my reading of the SQL spec, yet according to my test
just now mysql 5.0.22 takes it (1 demerit for mysql, maybe 2 because
this is a recent release and they still haven't fixed it) ... even in
sql_mode=ansi (make that 3 demerits).  Also, pgsql 7.2 certainly didn't
take it (I checked 7.0 too, the oldest version still alive hereabouts),
so 1 demerit for PHPRecipeBook who obviously didn't test this query on
anything but mysql.

* 'LIMIT OFFSET 0' bombs in both mysql and Postgres, and always has
AFAIK --- there's supposed to be a numeric argument to the LIMIT.
1 demerit to PHPRecipeBook for being just plain broken.

> As far as other options, I haven't found any. There are packages with 
> fat clients written for KDE (KRecipes) or GNOME (Gourmet) users, but I 
> have not found anything useful with a web front end. I'm not sure this 
> is yet, but it's all I could find to try out.

Seems like you need to be prepared to do a bit of work on PHPRecipeBook.
These things don't sound real hard to fix if you know a little SQL
... the real question is can you get upstream to take back the patches?
It's no fun fixing bugs if you just have to fix 'em again in the next
release.

> I just want something 
> useful to organize a few thousand family recipes before they are lost. 
> My mother just turned 82 and we have most of hers stored on paper. But 
> we have already lost a couple of her and dad's sisters with no records 
> of theirs. I haven't even begun to talk with my wife's family, and one 
> daughter in law whose grandmother and aunts used to operate a catering 
> kitchen.

Sounds a bit familiar ... my wife spent a lot of time a few years ago
trying to organize recipes from her father's and grandfather's bakery
business.  Strangely, she had no interest in putting 'em into a
database.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: David MonarchiDate: 2007-04-01 13:56:34
Subject: Re: Behavior of hash index on a text field
Previous:From: Bob McConnellDate: 2007-03-31 23:07:38
Subject: Re: Updating old code for new engine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group