Request to review Guidelines for writing MySQL and PostgreSQL compliant SQL
Dear friends, Just a quick note that I added some information in the manual: Developing Drupal using PostgreSQL: http://drupal.org/node/338676 Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": http://drupal.org/node/555514 This page lists major differences between MySQL and PostgreSQL syntax. Some issues can be fixed in D7 (some may already as I did not follow development). I saw compatibility functions around. Some automatic casts may be added also in D7 PostgreSQL schema. If this was done, please inform me and I will update the pages or update them yourself. Some need fixing in PostgreSQL. All issues require attention from developers. Would it be possible to review http://drupal.org/node/555514 to make sure anyone agrees on the standard SQL to use for both MySQL and PostgreSQL. When it is done, I will contact PostgreSQL hackers to ask for modifications in PostgreSQL core. Some modifications seem to be in the scope of possibility. Besides, as stated before, I think we would gain to flag minimal PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions and from a better compatibility with Drupal. PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 without running into a single problem. SO IMHO let us not stick to an old PG version. This is a minor issue, please focus on http://drupal.org/node/555514 Kind regards, Jean-Michel
Hey Jean-Michel, Thanks for your hard work. Unfortunately, some of the information you added to this handbook are either completely wrong or very misleading. Here are a few remarks: - http://drupal.org/node/338676: PostgreSQL 8.4 is not supported by any Drupal version to date. I am not aware of any test results that can confirm that it works correctly. PostgreSQL < 8.3 is recommended for Drupal 6 (because PostgreSQL 8.3 became *very* picky about column types), and PostgreSQL 8.3 is recommended for Drupal 7. That might change before Drupal 7 is released, if test results prove that we support PostgreSQL 8.4 correctly. - http://drupal.org/node/555580: CONCAT() is supported by Drupal on every database engine, but only with two parameters - http://drupal.org/node/555558: WTF? I'm not sure you understand how indexes work. - http://drupal.org/node/555562 and http://drupal.org/node/555648: it's enough to state that Drupal doesn't support multiple table DELETE or UPDATE. MySQL has a long standing bug (http://bugs.mysql.com/bug.php?id=5037) that prevents you from doing proper subqueries in DELETE (apparently the index state is getting mixed up). In that case, you need DELETE FROM table WHERE id = (SELECT id FROM (SELECT id FROM table WHERE ...) AS temptable). Not that elegant, but it works. => Drupal 7 is not affected - http://drupal.org/node/555568: Yes. Drupal 7 is not affected either. - http://drupal.org/node/555518: Those are *not* nested ORDER BY, but a special syntax of Subquery, only supported by MySQL. It's enough to say that you should use a proper syntax. - http://drupal.org/node/555540: we need to make clear that putting a random value in a boolean clause and expecting it will work is a bad idea - http://drupal.org/node/555536: this looks like a programming error in the Event module, and has very little to do with automatic casting. - http://drupal.org/node/555530: here you say that the MySQL parser is "smart". It is not. What this query will return is the value of the u.name column from a random line matching the aggregation. This is a non deterministic query, and should be avoided. - http://drupal.org/node/555548: per coding standard, we only use single quotes in SQL queries. Drupal 7 uses PDO placeholders, so it is not affected. Damien Tournoud 2009/8/21 Jean-Michel Pouré <jm@poure.com>:
Dear friends,
Just a quick note that I added some information in the manual: Developing Drupal using PostgreSQL: http://drupal.org/node/338676
Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": http://drupal.org/node/555514
This page lists major differences between MySQL and PostgreSQL syntax.
Some issues can be fixed in D7 (some may already as I did not follow development). I saw compatibility functions around. Some automatic casts may be added also in D7 PostgreSQL schema. If this was done, please inform me and I will update the pages or update them yourself.
Some need fixing in PostgreSQL. All issues require attention from developers.
Would it be possible to review http://drupal.org/node/555514 to make sure anyone agrees on the standard SQL to use for both MySQL and PostgreSQL.
When it is done, I will contact PostgreSQL hackers to ask for modifications in PostgreSQL core. Some modifications seem to be in the scope of possibility.
Besides, as stated before, I think we would gain to flag minimal PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions and from a better compatibility with Drupal.
PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 without running into a single problem. SO IMHO let us not stick to an old PG version.
This is a minor issue, please focus on http://drupal.org/node/555514
Kind regards, Jean-Michel
Thank you for your efforts. PostgreSQL needs all the help it can get in Drupal, otherwise, judging from history, it becomes barely supported and little used. PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it
as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 without running into a single problem. SO IMHO let us not stick to an old PG version.
This is a show stopper for many people who rely on Debian for production servers and Ubuntu server LTS. They still have 8.3. Regardless of the fact that 8.4 is available and stable, the fact that it is not in the repository makes it a deal breaker, since it means it has to be installed from source, and has to be patched manually. Not something that an admin used to the Debian way would want to do, given that the whole rationale of Debian is to make sure that the system as a whole is stable and only updated from the repositories. -- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting. Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra Simplicity is the ultimate sophistication. -- Leonardo da Vinci
Khalid Baheyeldin writes
This is a show stopper for many people who rely on Debian for production servers and Ubuntu server LTS. They still have 8.3. Regardless of the fact that 8.4 is available and stable, the fact that it is not in the repository makes it a deal breaker, since it means it has to be installed from source, and has to be patched manually.
The popular solution to this problem is http://www.backports.org See http://packages.debian.org/lenny-backports/postgresql for postgresql-8.4 packaged for debian stable. Best regards, -- Daniel Vérité PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
The real issue is using MySQL. PostgreSQL is a way better in many aspects. And it does understand standard SQL, better than MySQL. War launched! On Fri, 2009-08-21 at 13:42 +0200, Jean-Michel Pouré wrote:
Dear friends,
Just a quick note that I added some information in the manual: Developing Drupal using PostgreSQL: http://drupal.org/node/338676
Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": http://drupal.org/node/555514
This page lists major differences between MySQL and PostgreSQL syntax.
Some issues can be fixed in D7 (some may already as I did not follow development). I saw compatibility functions around. Some automatic casts may be added also in D7 PostgreSQL schema. If this was done, please inform me and I will update the pages or update them yourself.
Some need fixing in PostgreSQL. All issues require attention from developers.
Would it be possible to review http://drupal.org/node/555514 to make sure anyone agrees on the standard SQL to use for both MySQL and PostgreSQL.
When it is done, I will contact PostgreSQL hackers to ask for modifications in PostgreSQL core. Some modifications seem to be in the scope of possibility.
Besides, as stated before, I think we would gain to flag minimal PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions and from a better compatibility with Drupal.
PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 without running into a single problem. SO IMHO let us not stick to an old PG version.
This is a minor issue, please focus on http://drupal.org/node/555514
Kind regards, Jean-Michel
:D And we don't want talk about MS SQL Server ? On Fri, Aug 21, 2009 at 5:54 PM, Pierre Rineau < pierre.rineau@makina-corpus.com> wrote:
The real issue is using MySQL. PostgreSQL is a way better in many aspects. And it does understand standard SQL, better than MySQL.
War launched!
On Fri, 2009-08-21 at 13:42 +0200, Jean-Michel Pouré wrote:
Dear friends,
Just a quick note that I added some information in the manual: Developing Drupal using PostgreSQL: http://drupal.org/node/338676
Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": http://drupal.org/node/555514
This page lists major differences between MySQL and PostgreSQL syntax.
Some issues can be fixed in D7 (some may already as I did not follow development). I saw compatibility functions around. Some automatic casts may be added also in D7 PostgreSQL schema. If this was done, please inform me and I will update the pages or update them yourself.
Some need fixing in PostgreSQL. All issues require attention from developers.
Would it be possible to review http://drupal.org/node/555514 to make sure anyone agrees on the standard SQL to use for both MySQL and PostgreSQL.
When it is done, I will contact PostgreSQL hackers to ask for modifications in PostgreSQL core. Some modifications seem to be in the scope of possibility.
Besides, as stated before, I think we would gain to flag minimal PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions and from a better compatibility with Drupal.
PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 without running into a single problem. SO IMHO let us not stick to an old PG version.
This is a minor issue, please focus on http://drupal.org/node/555514
Kind regards, Jean-Michel
-- Paolo Mainardi CTO Twinbit Blog: http://www.paolomainardi.com -- Please consider the environment before printing this email --
MS SQL? Ugh, maybe we should just tear out the DB abstraction layer and use Excel spreadsheets instead. /me would rather do that than try to use MS SQL for -anything- =P ----- Cameron Eagans Owner, Black Storms Studios, LLC http://www.blackstormsstudios.com On Fri, Aug 21, 2009 at 10:53 AM, Paolo Mainardi <paolomainardi@gmail.com>wrote:
:D
And we don't want talk about MS SQL Server ?
On Fri, Aug 21, 2009 at 5:54 PM, Pierre Rineau < pierre.rineau@makina-corpus.com> wrote:
The real issue is using MySQL. PostgreSQL is a way better in many aspects. And it does understand standard SQL, better than MySQL.
War launched!
On Fri, 2009-08-21 at 13:42 +0200, Jean-Michel Pouré wrote:
Dear friends,
Just a quick note that I added some information in the manual: Developing Drupal using PostgreSQL: http://drupal.org/node/338676
Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": http://drupal.org/node/555514
This page lists major differences between MySQL and PostgreSQL syntax.
Some issues can be fixed in D7 (some may already as I did not follow development). I saw compatibility functions around. Some automatic casts may be added also in D7 PostgreSQL schema. If this was done, please inform me and I will update the pages or update them yourself.
Some need fixing in PostgreSQL. All issues require attention from developers.
Would it be possible to review http://drupal.org/node/555514 to make sure anyone agrees on the standard SQL to use for both MySQL and PostgreSQL.
When it is done, I will contact PostgreSQL hackers to ask for modifications in PostgreSQL core. Some modifications seem to be in the scope of possibility.
Besides, as stated before, I think we would gain to flag minimal PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions and from a better compatibility with Drupal.
PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 without running into a single problem. SO IMHO let us not stick to an old PG version.
This is a minor issue, please focus on http://drupal.org/node/555514
Kind regards, Jean-Michel
-- Paolo Mainardi
CTO Twinbit Blog: http://www.paolomainardi.com
-- Please consider the environment before printing this email --
Pierre R We get this every now and then: endless preaching about PostgreSQL and how it is a real DB and MySQL isn't, how it is free and not controlled by a company, yadda yadda ... What we don't get is people passionate enough about it to be involved with core enough to make sure everything works under PostgreSQL just as well as MySQL. Yes, I am aware of a few who are envolved, but they are not the ones who preach the virtues of PostgreSQL on the lists, and they are mostly dual users for MySQL, not pure PostgreSQL users. So, to this group, please go to the issue queue and help with PostgreSQL. We are sold on the virtues, but short of hands ... On Fri, Aug 21, 2009 at 11:54 AM, Pierre Rineau < pierre.rineau@makina-corpus.com> wrote:
The real issue is using MySQL. PostgreSQL is a way better in many aspects. And it does understand standard SQL, better than MySQL.
War launched!
On Fri, 2009-08-21 at 13:42 +0200, Jean-Michel Pouré wrote:
Dear friends,
Just a quick note that I added some information in the manual: Developing Drupal using PostgreSQL: http://drupal.org/node/338676
Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": http://drupal.org/node/555514
This page lists major differences between MySQL and PostgreSQL syntax.
Some issues can be fixed in D7 (some may already as I did not follow development). I saw compatibility functions around. Some automatic casts may be added also in D7 PostgreSQL schema. If this was done, please inform me and I will update the pages or update them yourself.
Some need fixing in PostgreSQL. All issues require attention from developers.
Would it be possible to review http://drupal.org/node/555514 to make sure anyone agrees on the standard SQL to use for both MySQL and PostgreSQL.
When it is done, I will contact PostgreSQL hackers to ask for modifications in PostgreSQL core. Some modifications seem to be in the scope of possibility.
Besides, as stated before, I think we would gain to flag minimal PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions and from a better compatibility with Drupal.
PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 without running into a single problem. SO IMHO let us not stick to an old PG version.
This is a minor issue, please focus on http://drupal.org/node/555514
Kind regards, Jean-Michel
-- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting. Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra Simplicity is the ultimate sophistication. -- Leonardo da Vinci
This was a joke :) I really don't like MySQL but the fact is it exists so I won't burn in useless flame wars :) I will go to issue queue as soon as I have some time to do it, I currently just have submited patches to make the DataSync module work with PostgreSQL, I really do help the community, but just bit by bit. I did not mean to harm :) On Fri, 2009-08-21 at 14:47 -0400, Khalid Baheyeldin wrote:
Pierre R
We get this every now and then: endless preaching about PostgreSQL and how it is a real DB and MySQL isn't, how it is free and not controlled by a company, yadda yadda ...
What we don't get is people passionate enough about it to be involved with core enough to make sure everything works under PostgreSQL just as well as MySQL.
Yes, I am aware of a few who are envolved, but they are not the ones who preach the virtues of PostgreSQL on the lists, and they are mostly dual users for MySQL, not pure PostgreSQL users.
So, to this group, please go to the issue queue and help with PostgreSQL. We are sold on the virtues, but short of hands ...
On Fri, Aug 21, 2009 at 11:54 AM, Pierre Rineau <pierre.rineau@makina-corpus.com> wrote: The real issue is using MySQL. PostgreSQL is a way better in many aspects. And it does understand standard SQL, better than MySQL.
War launched!
On Fri, 2009-08-21 at 13:42 +0200, Jean-Michel Pouré wrote: > Dear friends, > > Just a quick note that I added some information in the manual: > Developing Drupal using PostgreSQL: > http://drupal.org/node/338676 > > Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": > http://drupal.org/node/555514 > > This page lists major differences between MySQL and PostgreSQL syntax. > > Some issues can be fixed in D7 (some may already as I did not follow > development). I saw compatibility functions around. Some automatic casts > may be added also in D7 PostgreSQL schema. If this was done, please > inform me and I will update the pages or update them yourself. > > Some need fixing in PostgreSQL. All issues require attention from > developers. > > Would it be possible to review http://drupal.org/node/555514 > to make sure anyone agrees on the standard SQL to use for both MySQL and > PostgreSQL. > > When it is done, I will contact PostgreSQL hackers to ask for > modifications in PostgreSQL core. Some modifications seem to be in the > scope of possibility. > > Besides, as stated before, I think we would gain to flag minimal > PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions > and from a better compatibility with Drupal. > > PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it > as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 > without running into a single problem. SO IMHO let us not stick to an > old PG version. > > This is a minor issue, please focus on http://drupal.org/node/555514 > > Kind regards, > Jean-Michel
-- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting. Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra Simplicity is the ultimate sophistication. -- Leonardo da Vinci
Joke taken. But I was serious. The recurring problem with PostgreSQL that we, as a community, have is that we get people that help for a little while and then disappear. No one who uses PostgreSQL on large site help on a regular basis to make sure that the future is safe with them. We then get the preaching, very similar to yours, and then not much, until the following cycle. The new DB TNG API makes things better, but it does not address the scalability issues and speed when Drupal meets PostgreSQL. On Fri, Aug 21, 2009 at 8:43 PM, Pierre Rineau < pierre.rineau@makina-corpus.com> wrote:
This was a joke :) I really don't like MySQL but the fact is it exists so I won't burn in useless flame wars :)
I will go to issue queue as soon as I have some time to do it, I currently just have submited patches to make the DataSync module work with PostgreSQL, I really do help the community, but just bit by bit.
I did not mean to harm :)
On Fri, 2009-08-21 at 14:47 -0400, Khalid Baheyeldin wrote:
Pierre R
We get this every now and then: endless preaching about PostgreSQL and how it is a real DB and MySQL isn't, how it is free and not controlled by a company, yadda yadda ...
What we don't get is people passionate enough about it to be involved with core enough to make sure everything works under PostgreSQL just as well as MySQL.
Yes, I am aware of a few who are envolved, but they are not the ones who preach the virtues of PostgreSQL on the lists, and they are mostly dual users for MySQL, not pure PostgreSQL users.
So, to this group, please go to the issue queue and help with PostgreSQL. We are sold on the virtues, but short of hands ...
On Fri, Aug 21, 2009 at 11:54 AM, Pierre Rineau <pierre.rineau@makina-corpus.com> wrote: The real issue is using MySQL. PostgreSQL is a way better in many aspects. And it does understand standard SQL, better than MySQL.
War launched!
On Fri, 2009-08-21 at 13:42 +0200, Jean-Michel Pouré wrote: > Dear friends, > > Just a quick note that I added some information in the manual: > Developing Drupal using PostgreSQL: > http://drupal.org/node/338676 > > Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL": > http://drupal.org/node/555514 > > This page lists major differences between MySQL and PostgreSQL syntax. > > Some issues can be fixed in D7 (some may already as I did not follow > development). I saw compatibility functions around. Some automatic casts > may be added also in D7 PostgreSQL schema. If this was done, please > inform me and I will update the pages or update them yourself. > > Some need fixing in PostgreSQL. All issues require attention from > developers. > > Would it be possible to review http://drupal.org/node/555514 > to make sure anyone agrees on the standard SQL to use for both MySQL and > PostgreSQL. > > When it is done, I will contact PostgreSQL hackers to ask for > modifications in PostgreSQL core. Some modifications seem to be in the > scope of possibility. > > Besides, as stated before, I think we would gain to flag minimal > PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions > and from a better compatibility with Drupal. > > PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it > as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4 > without running into a single problem. SO IMHO let us not stick to an > old PG version. > > This is a minor issue, please focus on http://drupal.org/node/555514 > > Kind regards, > Jean-Michel
-- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting. Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra Simplicity is the ultimate sophistication. -- Leonardo da Vinci
-- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting. Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra Simplicity is the ultimate sophistication. -- Leonardo da Vinci
participants (7)
-
Cameron Eagans -
Damien Tournoud -
Daniel Vérité -
Jean-Michel Pouré -
Khalid Baheyeldin -
Paolo Mainardi -
Pierre Rineau