Re: SQL compatibility reminder: MySQL vs PostgreSQL

Yes, I've seen quite a few of François's posts around on Drupal. Drupal 
7 has an OO query building abstraction layer which _should_ address most 
of the issues and differences between MySQL, PostgreSQL and SQLite 
(newly supported in Drupal 7) because each driver can form the query 
string specific for the database it supports. That leaves Drupal core 
fairly well supported. On issue that does still remain though is 
casting. There is no abstraction around most functions such as CAST. 
CAST(1234 as TEXT) in PostgreSQL is CAST(1234 as CHAR) in MySQL - its an 
edge case, but Drupal being a PHP app - its likely to come up often.

Aside from Drupal core, its too soon to know if there will be problems 
with D7 contrib but there are a few major bugs about other D6 contrib 
modules. The biggest one, which I think need movement to get fixed is in 
the Views module. The Views module has a bit of a hard time trying to 
please both databases and its surrounding the use of DISTINCT and 
restricting duplicate results. They've opted for a solution that really 
hits hard on PostgreSQL's performance. Bascially, when a DISTINCT clause 
is used, all other fields being selected get a custom functional called 
FIRST rapped around them: SELECT DISTINCT(nid), FIRST(title), 
FIRST(body), ..... The function merely returns the first value when two 
values are present for that row. This is the alternate instead of 
grouping by each field. Its stupid and needs to be fixed. The issue is 
here: http://drupal.org/node/460838

Josh Waihi - Drupal PostgreSQL Maintainer

Mark Kirkwood wrote:
> François Pérou wrote:
>>
>> I will continue using PostgreSQL and MySQL user base will continue to
>> grow and one day it will be 1 PostgreSQL user for 1.000 MySQL users.
>>
>> This is life. People have a deep psychological addiction to their
>> believes and ideas. IMHO, PostgreSQL has to be more flexible (in
>> psychological terms) to understand MySQL user needs and answer them,
>> just to give them a choice to migrate to PostgreSQL.
>>
>> All your discussions are about technical things and you think I make fun
>> of Drupal developers. I only tried to point out psychological believes,
>> which we have to understand to answer their needs and grow PostgreSQL
>> user base.
>>
>>
>>
>>   
> I think the Drupal developers are addressing the main thrust of your 
> concerns - one of the gentlemen I work with here at Catalyst (Josh 
> Waihi) has spent considerable time working on Postgresql issues for 
> Drupal 7. Last time I checked, Drupal 7 + Postgresql passes most of 
> the regression tests.
>
> Maybe you could consider helping out making Drupal 7 + Postgresql pass 
> the remaining ones?
>
> regards
>
> Mark


-- 
Joshua Waihi // Drupal Architect

Catalyst.Net Limited,
Level 6, Catalyst House,
150 Willis Street, Wellington.
P.O.Box 11053, Manners Street,
Wellington 6142

DDI: +64 4 803 2228
Mob: +64 21 979 794
Tel: +64 4 499 2267
Web: http://catalyst.net.nz