SQL compatibility reminder: MySQL vs PostgreSQL
in [PgSql]
|
Prev: Visual Studio 2005, C-language function - avoiding hacks?
Next: [HACKERS] Core dump running PL/Perl installcheck with bleadperl [PATCH]
From: Joshua Waihi on 7 Mar 2010 15:55 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 8 Mar 2010 10:59 David Christensen <david(a)endpoint.com> writes: > On Mar 8, 2010, at 9:16 AM, Kevin Grittner wrote: > Wolfgang Wilhelm <wolfgang20121964(a)yahoo.de> wrote: >>> Isn*t that a good time to think to put that question into the >>> list of things PostgreSQL doesn*t want to do? >> >> Done. >> >> http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want > Does this conflict conceptually with the item from "Exotic Features" > on the same page?: > * Add pre-parsing phase that converts non-ISO syntax to supported syntax > This could allow SQL written for other databases to run without > modification. I think the new item might be phrased a little too broadly. The problem with mysql's GROUP BY behavior is not the syntax but the nonstandard semantics, ie, that it will pick a random result row when the query is underspecified. That means you can't just do a syntax translation, which is what the "exotic" wishlist item seems to be envisioning. I believe what that's actually about is the idea of converting things like Oracle's CONNECT BY into SQL-spec constructs. Doing so wouldn't break any existing PG-compatible applications, whereas messing with the semantics of GROUP BY probably would. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 8 Mar 2010 11:25 Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > I think the new item might be phrased a little too broadly. The > problem with mysql's GROUP BY behavior is not the syntax but the > nonstandard semantics, ie, that it will pick a random result row > when the query is underspecified. I thought that some of the items on the OP's list were requests to add an alternative syntax for an existing feature, without a change in semantics. Did I misunderstand that? If not, is it something we want to consider? I do know that some of the requests were to support behavior we would consider incorrect (like the non-deterministic results from an underspecified GROUP BY); not only do we not want to go to any effort to *add* it, but we'd probably be putting in effort to *eliminate* it if it was present. Should the TODO list "not wanted" section explicitly list each such feature, so that non-listed features aren't painted by the same broad brush? > I believe what that's actually about is the idea of converting > things like Oracle's CONNECT BY into SQL-spec constructs. Doing > so wouldn't break any existing PG-compatible applications, whereas > messing with the semantics of GROUP BY probably would. Yeah, my first draft of that was even broader, not naming MySQL in particular -- but then I remembered that we've made a few concessions to Oracle compatibility. As far as I can recall, though, those tend not to involve new syntax, but functions that aren't required by the standard -- which seems much less invasive than the OP's requests. I'm willing to rework, soften, or narrow the entry as needed, and I certainly would take no offense at anyone else doing so. I was just trying to get it listed, since there seemed to be some community consensus on the point. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jaime Casanova on 8 Mar 2010 11:58 On Mon, Mar 8, 2010 at 4:17 AM, Pierre C <lists(a)peufeu.com> wrote: > >> My opinion is that PostgreSQL should accept any MySQL syntax and return >> warnings. I believe that we should access even innodb syntax and turn it >> immediately into PostgreSQL tables. This would allow people with no >> interest in SQL to migrate from MySQL to PostgreSQL without any harm. > > A solution would be a SQL proxy (a la pgpool) with query rewriting. > This sounds like a better idea... >> PHP developers don't have time to invest in learning deep SQL. > > This is true, and it is a big problem IMHO. It results in lots of slow, > broken, insecure database designs. > So, if php dev doesn't have time to learn to do things right then we have to find time to learn to do things wrong? seems like a nosense argument to me -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: David Fetter on 8 Mar 2010 12:10
On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: > On Mon, Mar 8, 2010 at 4:17 AM, Pierre C <lists(a)peufeu.com> wrote: > > > >> My opinion is that PostgreSQL should accept any MySQL syntax and > >> return warnings. I believe that we should access even innodb > >> syntax and turn it immediately into PostgreSQL tables. This would > >> allow people with no interest in SQL to migrate from MySQL to > >> PostgreSQL without any harm. > > > > A solution would be a SQL proxy (a la pgpool) with query > > rewriting. > > This sounds like a better idea... Aside from that little "halting problem" issue, it sounds wonderful. You do know that SQL is Turing-complete, right? > >> PHP developers don't have time to invest in learning deep SQL. > > > > This is true, and it is a big problem IMHO. It results in lots of slow, > > broken, insecure database designs. > > So, if php dev doesn't have time to learn to do things right then we > have to find time to learn to do things wrong? seems like a nosense > argument to me Indeed. Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |