Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
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. 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. ALL the web apps that I've done CPR ressuscitation on follow the same schema : - devs are database noobs - generous use of MyISAM - numerous queries, most of them unoptimized and/or useless - use of Apache/mod_php instead of fastcgi - sometimes, use of a huge slow bloated CMS/framework which issues even more unoptimized and/or useless SQL queries - site gains popularity - huge traffic takes an unprepared team by surprise (never heard of stuff like concurrency or scaling) - site fails horribly That said, I've got a 150.000+ members forum running on MySQL with sub 5 ms page times on a low-end server, it works if you do it right. Most opensource PHP apps developers have to expend lots of efforts to work on MyISAM that doesn't support foreign keys or constraints. If those resources could be directed to useful work instead of wasted like this, the result would be a lot better. The irony is that even with all that effort, you can't make a web app work without transactions, sooner or later your database integrity will fail. My theory on this is simple : - PHP is a very weak language, not suited to implementation of really useful frameworks (unlike Python / Ruby) example : Find an ORM for PHP that is as good as sqlalchemy. It does not exist, because it is impossible to do. - really smart programmers dislike PHP because it is a pretty weak language, so they all flee to Python, Ruby, etc All big PHP applications turn into a huge usine à gaz, impossible to understand code, because of language weakness. - really smart DBAs dislike MySQL (unless they have a nice paying job at facebook or flickr) So, it is very difficult to find good PHP developers, and especially with database knowledge. 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. Problem is, as you mentioned above, most PHP developers don't know what their needs are because they have little database expertise. About stuff MySQL does that I would like postgres to implement, I'd focus more on features, not syntax : - some form of index-only scans or equivalent (visibility map would probably suffice) - some form of INSERT ON DUPLICATE KEY UPDATE or equivalent (merge...) where the DB, not me, takes care of concurrency - some way to SELECT a,b,c,d GROUP BY a when it can be determined that it is equivalent to GROUP BY a,b,c,d, ie a is UNIQUE NOT NULL - index skip scans (well, MySQL doesn't really do index skip scans, but since it can do index-only scans, it's an approximation) - simpler syntax for DELETEs using JOINs And while I'm at it, I'll add my pet feature : An extremely fast form of temporary storage. Table main is referenced by tables child1, child2, ... childN - SELECT ... FROM main WHERE (very complex condition involving gist coordinates search etc) ORDER BY Then I want the rows from child tables which reference those results. If I add a lot of JOINs to my query, it's entirely possible that the (very complex condition involving gist coordinates search etc) is mis-estimated . This is generally not a problem since it usually uses bitmap index scans which can survive lots of abuse. However it causes mis-planning of the JOINs which is a problem. Besides, some of the child tables have few rows, but lots of columns, so it complicates the query and returns many times the same data, which the ORM doesn't care about since it would rather instanciate 1 object per referenced table row instead of 1 object per main table row. I would like to do : CREATE TEMP TABLE foo AS SELECT ... FROM main WHERE (very complex condition involving gist coordinates search etc); ANALYZE foo; SELECT * FROM foo ORDER BY ... SELECT c.* FROM foo JOIN child1 ON (...) SELECT c.* FROM foo JOIN child2 ON (...) etc This splits the query into much easier to manage fragments, and the results are easier to use, too. I can store in the application only 1 object per child table row. But I can't do this because it causes an update of system catalogs (slow, iowait, and bloat). Basically it would be nice to have something (temp table, cursor, CTE, tuplestore, whatever) that can hold a short-lived result set, can be used like a table, can have accurate statistics, and can be used in several queries, without disk writes. Note this would completely solve the
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On 2010-03-08 11:17 +0200, Pierre C wrote: - index skip scans (well, MySQL doesn't really do index skip scans, but since it can do index-only scans, it's an approximation) As far as I can tell, we already do index skip scans: = create index foo_a_b_idx on foo(a,b); CREATE INDEX = explain analyze select * from foo where b = 2; QUERY PLAN --- Index Scan using foo_a_b_idx on foo (cost=0.00..20.30 rows=5 width=8) (actual time=0.027..0.057 rows=1 loops=1) Index Cond: (b = 2) Total runtime: 0.075 ms (3 rows) Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
As far as I can tell, we already do index skip scans: This feature is great but I was thinking about something else, like SELECT DISTINCT, which currently does a seq scan, even if x is indexed. Here is an example. In both cases it could use the index to skip all non-interesting rows, pulling only 69 rows from the heap instead of 120K. EXPLAIN ANALYZE SELECT DISTINCT vente, type_id FROM annonces; QUERY PLAN --- HashAggregate (cost=15270.98..15271.82 rows=84 width=3) (actual time=113.277..113.288 rows=69 loops=1) - Seq Scan on annonces (cost=0.00..14682.32 rows=117732 width=3) (actual time=0.005..76.069 rows=119655 loops=1) EXPLAIN ANALYZE SELECT DISTINCT ON( vente, type_id ) * FROM annonces; QUERY PLAN Unique (cost=0.00..34926.90 rows=84 width=1076) (actual time=0.019..107.318 rows=69 loops=1) - Index Scan using annonces_type on annonces (cost=0.00..34338.24 rows=117732 width=1076) (actual time=0.017..52.982 rows=119655 loops=1) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On 2010-03-08 11:47 +0200, Pierre C wrote: As far as I can tell, we already do index skip scans: This feature is great but I was thinking about something else, like SELECT DISTINCT, which currently does a seq scan, even if x is indexed. Here is an example. In both cases it could use the index to skip all non-interesting rows, pulling only 69 rows from the heap instead of 120K. Oh, this is what I believe MySQL calls loose index scans. I'm actually looking into this as we speak, but there seems to be a non-trivial amount of work to be done in order for this to work. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Oh, this is what I believe MySQL calls loose index scans. I'm Exactly : http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html actually looking into this as we speak, Great ! Will it support the famous top-n by category ? but there seems to be a non-trivial amount of work to be done in order for this to work. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 4:58 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 2010-03-08 11:47 +0200, Pierre C wrote: As far as I can tell, we already do index skip scans: This feature is great but I was thinking about something else, like SELECT DISTINCT, which currently does a seq scan, even if x is indexed. Here is an example. In both cases it could use the index to skip all non-interesting rows, pulling only 69 rows from the heap instead of 120K. Oh, this is what I believe MySQL calls loose index scans. I'm actually looking into this as we speak, but there seems to be a non-trivial amount of work to be done in order for this to work. We should probably have a TODO for this, if we don't already. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
+1 Isn´t that a good time to think to put that question into the list of things PostgreSQL doesn´t want to do? Cheers Von: Andrew Dunstan and...@dunslane.net An: francois.pe...@free.fr CC: Josh Berkus j...@agliodbs.com; Craig Ringer cr...@postnewspapers.com.au; pgsql-hackers@postgresql.org; dp...@pgadmin.org Gesendet: Samstag, den 6. März 2010, 22:01:06 Uhr Betreff: Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL François Pérou 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. This is just fantasy. Doing this will destabilize Postgres, cost us hugely in maintenance effort and LOSE us users. If we do this why the heck should we stop there? Why shouldn't we replicate the broken behaviour of every major database out there? It's really time for you to stop making this suggestion, once and for all. It is just not going to happen. Moreover MySQL appears to be fracturing into a bunch of different forks, so why now, of all times, would we want to adopt its broken syntax? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers __ Do You Yahoo!? Sie sind Spam leid? Yahoo! Mail verfügt über einen herausragenden Schutz gegen Massenmails. http://mail.yahoo.com
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 9:48 AM, Wolfgang Wilhelm wolfgang20121...@yahoo.de wrote: +1 Isn´t that a good time to think to put that question into the list of things PostgreSQL doesn´t want to do? Yes. ...Robert Von: Andrew Dunstan and...@dunslane.net François Pérou 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. This is just fantasy. Doing this will destabilize Postgres, cost us hugely in maintenance effort and LOSE us users. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Robert Haas robertmh...@gmail.com wrote: Wolfgang Wilhelm wolfgang20121...@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? Yes. Done. http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mar 8, 2010, at 9:16 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: Wolfgang Wilhelm wolfgang20121...@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? Yes. 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. Regards, David -- David Christensen End Point Corporation da...@endpoint.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
David Christensen da...@endpoint.com writes: On Mar 8, 2010, at 9:16 AM, Kevin Grittner wrote: Wolfgang Wilhelm wolfgang20121...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Tom Lane t...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/9 Jaime Casanova jcasa...@systemguards.com.ec: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@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... Could parser rewriter hook be another solution here? I'm completely against the wrong GROUP BY syntax from MySQL, but it is also true that SQL is only an interface. -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 12:10 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@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? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 08, 2010 at 12:18:31PM -0500, Robert Haas wrote: On Mon, Mar 8, 2010 at 12:10 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@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? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. MySQL's SQL isn't Turing complete. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Mon, Mar 8, 2010 at 12:43 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 12:18:31PM -0500, Robert Haas wrote: On Mon, Mar 8, 2010 at 12:10 PM, David Fetter da...@fetter.org wrote: On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote: On Mon, Mar 8, 2010 at 4:17 AM, Pierre C li...@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? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. MySQL's SQL isn't Turing complete. It still doesn't matter. Turing-completeness does not preclude syntax transformation. Non-Turing completeness, even less so. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Robert Haas wrote: You do know that SQL is Turing-complete, right? That seems largely irrelevant to the problem at hand. It's not impossible to do syntactic transformations from one Turing-complete langauge to another; if it were, there could be no such thing as a compiler. If we were engaged in an academic exercise this might be interesting. But we aren't. The fact that one can do something is not an argument for actually doing it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Kevin Grittner kevin.gritt...@wicourts.gov writes: 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 think the pre-existing TODO item is evidence that there's at least willingness to consider such things. (OTOH I believe that item has been there for quite a long time, without any action being taken.) 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? Yes, I think we should narrowly list things we don't want to do. The current wording reads like we aren't interested in adopting any MySQL ideas, which I don't think is actually the project consensus, not to mention that it doesn't look good from a PR standpoint. I believe we do have consensus that we aren't interested in adopting MySQL's nonstandard GROUP BY semantics. I don't recall what else there might be a definite no for. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
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 The best ever reply I got from phpBB guys on I don't remember which question was : WE DO IT THIS WAY BECAUSE WE WANT TO SUPPORT MYSQL 3.x You can frame this and put it on your wall. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Tom Lane t...@sss.pgh.pa.us wrote: I believe we do have consensus that we aren't interested in adopting MySQL's nonstandard GROUP BY semantics. I don't recall what else there might be a definite no for. TODO not wanted entry rewritten to address just this one issue. The other issues raise in the original post are valid possible enhancements, or is there something else to list?: http://archives.postgresql.org/pgsql-hackers/2010-03/msg00257.php -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Tom Lane wrote: Yes, I think we should narrowly list things we don't want to do. The current wording reads like we aren't interested in adopting any MySQL ideas, which I don't think is actually the project consensus, not to mention that it doesn't look good from a PR standpoint. Indeed. We are always open to good ideas, I hope. The really obvious candidate of missing functionality from MySQL hasn't even been mentioned in this thread, AFAIK: some form of insert_or_update. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Kevin Grittner kevin.gritt...@wicourts.gov writes: TODO not wanted entry rewritten to address just this one issue. The other issues raise in the original post are valid possible enhancements, or is there something else to list?: http://archives.postgresql.org/pgsql-hackers/2010-03/msg00257.php I'm not too sure either way about the other items mentioned there. But anyway the GROUP BY business is the only one that seems to come up often enough to justify an explicit no listing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Andrew Dunstan wrote: But AIUI that won't be the same as the MySQL behaviour, as documented at http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html: When using this feature, all rows in each group should have the same values for the columns that are ommitted from the |GROUP BY| part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same. That sounds a lot like the behavior of `DISTINCT ON (...)' and it'd actually be really rather useful to have under some circumstances. Whether it should be written as 'GROUP BY', though, isn't so clear. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On 3/6/10 1:05 AM, Craig Ringer wrote: Andrew Dunstan wrote: But AIUI that won't be the same as the MySQL behaviour, as documented at http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html: When using this feature, all rows in each group should have the same values for the columns that are ommitted from the |GROUP BY| part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same. That sounds a lot like the behavior of `DISTINCT ON (...)' and it'd actually be really rather useful to have under some circumstances. Whether it should be written as 'GROUP BY', though, isn't so clear. I believe that functional dependencies for GROUP BY (that is, if you group on the PK, it shouldn't be necessary to group on the other columns) are in our TODO list already. However, The server is free to return any value from the group doesn't sound like the way we do things, ever. MySQL users might be OK with queries which return an indeterminate answer; our users are not. You'll notice that SELECT DISTINCT ON requires you to have an ORDER BY; that's by design. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Le samedi 06 mars 2010 à 11:01 -0800, Josh Berkus a écrit : However, The server is free to return any value from the group doesn't sound like the way we do things, ever. MySQL users might be OK with queries which return an indeterminate answer; our users are not. You'll notice that SELECT DISTINCT ON requires you to have an ORDER BY; that's by design. --Josh Berkus Dear Josh, I unregistered the list, to avoid a flame war. 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. During my work on Drupal, I noticed that PostgreSQL was very near to running any MySQL code. Most users with no interest in SQL try PostgreSQL during a few minutes and then stop at the first SQL error. If there was no errors but only warnings they would have a choice. Presently, they don't. PHP developers don't have time to invest in learning deep SQL. This leads to situations where 99% of the Internet base uses MySQL, not PostgreSQL. The situation is the same with Windows sucking 90% of OSes in the world with bad software design. I had a discussion with OVH, the first French provider with 500.000 clients. We discussed about the possibility for shared hosting in PostgreSQL. He asked me whether I would buy PostgreSQL shared hosting. I answered no, because I have dedicated servers. Then he told me I was the only person interested in PostgreSQL. You may discuss and discuss and say Yeah, we are the best at PostgreSQL. Being the best does not suffice without sufficient user base. Okay, you can always pretend to fight at the level of DB2 or Oracle. At this point, I will not discuss further. The user base of MySQL is huge and it would be so nice that many people would migrate to PostgreSQL. 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. Kind regards, Jean-Michel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
François Pérou 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. This is just fantasy. Doing this will destabilize Postgres, cost us hugely in maintenance effort and LOSE us users. If we do this why the heck should we stop there? Why shouldn't we replicate the broken behaviour of every major database out there? It's really time for you to stop making this suggestion, once and for all. It is just not going to happen. Moreover MySQL appears to be fracturing into a bunch of different forks, so why now, of all times, would we want to adopt its broken syntax? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. What did you work on François? I can't find your name in my email archives or on archives.postgresql.org. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
François Pérou wrote:
An important pending issue, which goes on and on for years:
= All non-aggregate fields must be present in the GROUP BY clause
http://drupal.org/node/30
The trouble is that the bottom of this page looks like nonsense to me.
The reason that
|SELECT COUNT(nid) FROM node
WHERE nid 0 AND type IN ('page')
ORDER BY nid
|
fails really has nothing to do with GROUP BY. It has to do with a
meaningless and silly ORDER BY clause:
andrew=# SELECT COUNT(nid) FROM node
andrew-# WHERE nid 0 AND type IN ('page')
andrew-# ORDER BY nid;
ERROR: column node.nid must appear in the GROUP BY clause or be
used in an aggregate function
And it could be cured by using an alias:
SELECT COUNT(nid) as nid FROM node
WHERE nid 0 AND type IN ('page')
ORDER BY nid;
or by omitting the ORDER BY altogether, or by using ORDER BY 1.
I think this query is NOT, as the page states, equivalant to:
|SELECT COUNT(nid) FROM node
WHERE nid 0 AND type IN ('page')
GROUP BY nid
ORDER BY nid
|
If it is equivalent in MySQL then MySQL is broken, IMNSHO, and there
would be no reason for us to mimic that brokenness. The first query
(with the order by removed) should produce a single row. The second
should produce one row per nid.
Now, there is an issue with GROUP BY that has the following TODO item,
which has not been done (and thus will not be in 9.0):
Add support for functional dependencies
This would allow omitting GROUP BY columns when grouping by the
primary key.
But AIUI that won't be the same as the MySQL behaviour, as documented at
http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html:
When using this feature, all rows in each group should have the same
values for the columns that are ommitted from the |GROUP BY| part.
The server is free to return any value from the group, so the
results are indeterminate unless all values are the same.
It will only be usable when PostgreSQL can know that the omitted columns
have a single value for the group, i.e. you won't ever get a different
result by omitting a redundant GROUP BY column.
In general, our aim is not to mimic MySQL. Asking us to do so simply for
the sake of compatibility is just about a sure way to get people's backs
up around here. Try going to the MySQL folks and asking them to be more
compatible with Postgres, and see how far you get. It is quite possible
to write code that runs on multiple databases. Bugzilla (to mention one
I have had a personal hand in enabling) has been doing it for years.
cheers
andrew
||
||
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: = All non-aggregate fields must be present in the GROUP BY clause http://drupal.org/node/30 My take is that this is never going to happen unless we are strictly talking about cases where the non-aggregate fields can be unambiguously determined. If we aren't, mysql is wrong to allow this, and developers that depend on it are wrong, and that is pretty much all you are ever going to get from this list. :-) The other stuff is mainly tangential fluff issues (takes 1% extra effort to write portable sql for) except for the flexible multi table delete, which would be nice although I wouldn't expect a strict copy of mysql syntax. I am personally looking at writeable CTE (which didn't make 9.0) to do most of the things I would need to do with a multi table delete feature, plus a quite a few other things. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. I would like to point out Drupal community efforts (including myself) to write down the most frequent problems when porting MySQL from/to PostgreSQL: The main MySQL/PostgreSQL issues can be found here: http://drupal.org/node/14 An important pending issue, which goes on and on for years: = All non-aggregate fields must be present in the GROUP BY clause http://drupal.org/node/30 These ones are less urgent, but still needed to ease migration: = Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM (SELECT DISTINCT ... ) ... for multiple http://drupal.org/node/706264 = DELETE SYNTAX on several tables requires the USING syntax: http://drupal.org/node/62 Interestingly, all there of these are cases where a portable syntax is available, but at least some Drupal developers have chosen not to use it. All three web pages include a description of the portable syntax, and a suggestion that it be used. So the case that we should modify PostgreSQL to support MySQL-specific syntax seems pretty weak. Nor is it the case that every other database in the world handles these like MySQL and only PostgreSQL does the opposite. In fact it's closer to the other way around. For example, Microsoft SQL Server generates this error on your first query: Column 'u.uid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. PostgreSQL says: ERROR: column u.uid must appear in the GROUP BY clause or be used in an aggregate function And it sounds like Oracle may do something similar: http://searchoracle.techtarget.com/answer/Invalid-GROUP-BY-SQL-query Your complaint about SELECT COUNT(DISTINCT...) is similar. There is a perfectly portable way to write this that works in all database engines, but some Drupal developers have chosen to write it in a way that only works in some database engines. Why not use the portable method? In fact, the docs already recommend using the portable method; this seems like a non-issue. The docs also say that PostgreSQL will support the other syntax beginning in version 9.0, but I'm not certain that's correct. IMHO, it is no use working on complex issues like replication if the SQL code of major softwares cannot run on PostgreSQL. I think it would be great if Drupal ran on PostgreSQL, but I don't believe that the solution is for PostgreSQL to support whatever syntax Drupal happens to use. I think the solution is for Drupal to use syntax that works on more than one database, as is already suggested by the web pages listed above. Sounds like for about the same amount of work they could pick up Oracle and Microsoft SQL server support as well. IMHO, 99% Drupal developers do not have a deep knowledge in SQL: * For example, part of Drupal developers are trying to find an automated way to insert DISTINCT on queries automatically using PHP preg. Of course, this creates bugs, which go on and on for years. The attempt can be seen here: http://drupal.org/node/284392 (400 replies). It could well be 10 years more bugs in this thread. Interestingly the very first reply here includes this phrase: Wow, and here I thought Drupal 6 would finally have fixed various db_rewrite_sql bugs. And reply #145 includes: This is always what happens when using MySQL. Franckly, you should always use PostgreSQL and read detailed logs to understand how the parser works. * Another very funny thing from Drupal community is that MySQL trims data without complaining, which is not the case for PostgreSQL: http://drupal.org/node/279219 That's a feature, and the MySQL behavior is a bug. From reply #7 on that thread: When inserting TEXT into a VARCHAR(255), MySQL trims the value to the first 255 characters. PostgreSQL complains and returns an error, which the correct behavior. I hope that Drupal can get fixed on this issue ... As MySQL does not complain, this bug is unseen. It is maybe Drupal most annoying bug, as it trims and destroys data, and noone complains. But there is no way to change people. It looks like PostgreSQL SQL syntax and parser should evolve to become more tolerant. If PostgreSQL syntax was more tolerant, Drupal developers would be interested in leaving MySQL for PostgreSQL. SO PLEASE take a deep look at my request. So what are your plans for PostgreSQL 9? Do you finally plan to beat MySQL? I finally abandoned MySQL completely seven years ago because the query planner was so poor that no matter what I did I couldn't get even moderately complex queries to perform decently. So for my use case PostgreSQL had MySQL beat even back then. The main reason I stuck with MySQL as long as I did is that the first versions of PostgreSQL that I used didn't support things like dropping columns (that feature was added in 2002) which was
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Thanks for your answers. To speak frankly: * I wrote the Drupal guide for porting from MySQL to PostgreSQL. * I am also the author of remarks about people should use PostgreSQL to write portable SQL. * I am very surprised by the SQL level of Php developers. The example Drupal developers trying to rewrite SQL queries dynamically adding DISTINCT clause is just an example. So don't expect them to understand the difference between MySQL and PostgreSQL. It is out of reach. They focuse on Php code. * I got banned from Drupal website during 2 days because I opened a bug complaining about a long running SQL query that moved the whole content of a 20.000 rows forum into PHP variables just to display 'Previous' and 'Next' links. I had to write Dries Buytaert to get unbanned. Then Prev and Next features got removed from Drupal. They did not even try to use SELECT FROM ... LIMIT ... OFFSET to find prev and next records. * Php developers analyze database performance using PHP cache. They never read MySQL logging information. I guess they don't have such information, as on some providers, MySQL is configured without logging (for ... speed as MySQL configuration states). So they use Php code to display performance information. All this is true. Nevertheless, I feel my explanations are useless. This is like fighting against the wind. I believe that PostgreSQL should support more MySQLisms in order to BEAT MySQL. Feel free to use my guide on Drupal website. We have to adapt tools to people, not the converse. Kind regards, Jean-Michel Pouré -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: * I am very surprised by the SQL level of Php developers. The example Drupal developers trying to rewrite SQL queries dynamically adding DISTINCT clause is just an example. So don't expect them to understand the difference between MySQL and PostgreSQL. It is out of reach. They focuse on Php code. I think you're still missing the point. I am sorry that the Drupal developers (at least in your opinion) do not understand the difference between MySQL and PostgreSQL, but I don't feel like it's our problem to fix that. As far as I can tell, no promiment member of this community agrees with any of the changes you are proposing. I have a certain feeling of deja vu about this whole conversation: http://archives.postgresql.org/pgsql-hackers/2009-08/msg01491.php http://archives.postgresql.org/pgsql-hackers/2009-08/msg01500.php http://archives.postgresql.org/pgsql-hackers/2009-08/msg01494.php http://archives.postgresql.org/pgsql-hackers/2009-08/msg01495.php http://archives.postgresql.org/pgsql-hackers/2009-08/msg01492.php ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
François Pérou wrote: I believe that PostgreSQL should support more MySQLisms in order to BEAT MySQL. Our aim is not to beat MySQL. At least mine is not, and I don't think I'm alone. Many of the MySQLisms you want supported are just broken behaviour, in the view of many people. So you are not going to win arguments using this line of reasoning, IMNSHO. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: Thanks for your answers. To speak frankly: * I wrote the Drupal guide for porting from MySQL to PostgreSQL. * I am also the author of remarks about people should use PostgreSQL to write portable SQL. * I am very surprised by the SQL level of Php developers. The example Drupal developers trying to rewrite SQL queries dynamically adding DISTINCT clause is just an example. So don't expect them to understand the difference between MySQL and PostgreSQL. It is out of reach. They focuse on Php code. * I got banned from Drupal website during 2 days because I opened a bug complaining about a long running SQL query that moved the whole content of a 20.000 rows forum into PHP variables just to display 'Previous' and 'Next' links. I had to write Dries Buytaert to get unbanned. Then Prev and Next features got removed from Drupal. They did not even try to use SELECT FROM ... LIMIT ... OFFSET to find prev and next records. * Php developers analyze database performance using PHP cache. They never read MySQL logging information. I guess they don't have such information, as on some providers, MySQL is configured without logging (for ... speed as MySQL configuration states). So they use Php code to display performance information. All this is true. Nevertheless, I feel my explanations are useless. This is like fighting against the wind. I believe that PostgreSQL should support more MySQLisms in order to BEAT MySQL. Feel free to use my guide on Drupal website. We have to adapt tools to people, not the converse. Kind regards, Jean-Michel Pouré This is confusing advocacy and technical considerations. Yes PHP coders are often deeply ignorant of database issues, many of them deliberately and militantly so. I have had my altercations with the Drupal community over dumb SQL and uncritical praise of document databases as a pancea. MySQL has a set of characteristics (maybe even deliberately) which work well for uptake in that market. One of the reasons I like Postgres is the feeling I get that the product and the community around it would like to make better databases. They would like to use databases better and make it possible for others to use databases better. On that front MySQL is already beaten. Just as the abuse of spreadsheets for data management will probably never be properly vanquished, the permissiveness of MySQL will always present a lower hurdle to some coders. The perception of MySQL as enemy number one does also concern me a bit. Postgres is competing for installed base on a far wider front than that. If installed base is really that meaningful a competition in the first place. Bell. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: I believe that PostgreSQL should support more MySQLisms in order to BEAT MySQL. we BEAT mysql long ago... to make postgres as broken as mysql is not an improve... Feel free to use my guide on Drupal website. We have to adapt tools to people, not the converse. with that reasoning, then Galileo and Copernico should had faken their tests to adjust the results to most people expections about the earth being the center of the universe and our sun orbiting us -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, 2010-03-05 at 12:42 -0500, Robert Haas wrote: 2010/3/5 François Pérou francois.pe...@free.fr: * I am very surprised by the SQL level of Php developers. The example Drupal developers trying to rewrite SQL queries dynamically adding DISTINCT clause is just an example. So don't expect them to understand the difference between MySQL and PostgreSQL. It is out of reach. They focuse on Php code. I think you're still missing the point. I am sorry that the Drupal developers (at least in your opinion) do not understand the difference between MySQL and PostgreSQL, but I don't feel like it's our problem to fix that. As far as I can tell, no promiment member of this community agrees with any of the changes you are proposing. Correct. Not to mention as far as I understand it, 99% of this is resolved in Drupal 7. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, Mar 5, 2010 at 9:55 AM, Dave Page dp...@pgadmin.org wrote: 2010/3/5 François Pérou francois.pe...@free.fr: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. What did you work on François? I can't find your name in my email archives or on archives.postgresql.org. I believe the OP is the same person as Jean-Michel Poure j...@poure.com. And I believe we discussed many of these same things back in August. And now he is posting them over again just to see if he gets an answer he likes better the second time. Perhaps Greg Stark summed it up best: http://archives.postgresql.org/pgsql-hackers/2009-08/msg01818.php ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
All, Given that Francois seems to return to this list every 3 months with the exact same set of requests, I think we need to make a habit of ignoring him the way we used to ignore Al Dev (although I'll comment that Al Dev was *much* more entertaining). Several members of our community are working with Drupal project leaders to help them make Drupal more database-independant, and version 7 is making great strides in this direction. If there's an next step, it's *our* community providing a test instance of PostgreSQL which Drupal developers can test their modules against, and a pgsql-drupal list or similar for them to work out the problems quickly and easily. Neither of the Drupal project leaders I've talked with wanted PostgreSQL to support more whacky MySQL syntax. Francois is out on his own here. And, Francois, it's not our goal to beat MySQL. Nobody can be a better MySQL than MySQL, ever. Our goal is to make the best possible SQL-Relational database. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, Mar 5, 2010 at 1:48 PM, Josh Berkus j...@agliodbs.com wrote: Given that Francois seems to return to this list every 3 months with the exact same set of requests, I think we need to make a habit of ignoring him the way we used to ignore Al Dev (although I'll comment that Al Dev was *much* more entertaining). Perhaps we should refer Francois/Jean-Michele to Al Dev's comments on MySQL. :-) http://www.yolinux.com/HOWTO/PostgreSQL-HOWTO.html#ss4.2 ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. You're doing a poor job on that latter. You asked before for the PostgreSQL project to address the concerns of some, but by no means all, developers of some little project by introducing massive bugs. That is never going to happen, and you need to stop asking. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
David Fetter wrote: On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. You're doing a poor job on that latter. You asked before for the PostgreSQL project to address the concerns of some, but by no means all, developers of some little project by introducing massive bugs. Drupal is not a little project. Let's keep our own facts straight. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, 2010-03-05 at 15:10 -0500, Andrew Dunstan wrote: David Fetter wrote: On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. You're doing a poor job on that latter. You asked before for the PostgreSQL project to address the concerns of some, but by no means all, developers of some little project by introducing massive bugs. Drupal is not a little project. Let's keep our own facts straight. Not it isn't, it dwarfs us I am sure. It is within our interest to work with them but in a proper way. As I have said, Drupal 7 addresses most of our concerns, so anything we want to complain about should be in the interest of making sure Drupal 7 works properly, not previous versions. Sincerely, Joshua D. Drake cheers andrew -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, Mar 05, 2010 at 03:10:59PM -0500, Andrew Dunstan wrote: David Fetter wrote: On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. You're doing a poor job on that latter. You asked before for the PostgreSQL project to address the concerns of some, but by no means all, developers of some little project by introducing massive bugs. Drupal is not a little project. Let's keep our own facts straight. Drupal 6 support is, given its finite future. He's basically asking us to do something that Drupal 7 and later won't need or want, so I stick by little. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 5, 2010 at 9:55 AM, Dave Page dp...@pgadmin.org wrote: 2010/3/5 François Pérou francois.pe...@free.fr: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. What did you work on François? I can't find your name in my email archives or on archives.postgresql.org. I believe the OP is the same person as Jean-Michel Poure Yeah, I had email from him offlist. FYI, whilst I disagree that we should break Postgres for the sake of some lazy developers on another project, Jean-Michel was a long-term major contributor to pgAdmin I and II and I have a lot of respect for him and am extremely grateful for his past work on the project. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
On Fri, Mar 5, 2010 at 3:30 PM, Dave Page dp...@pgadmin.org wrote: On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 5, 2010 at 9:55 AM, Dave Page dp...@pgadmin.org wrote: 2010/3/5 François Pérou francois.pe...@free.fr: Dear friends, As a reminder, I took part in the development of pgAdmin and I am not looking for a flame war. What did you work on François? I can't find your name in my email archives or on archives.postgresql.org. I believe the OP is the same person as Jean-Michel Poure Yeah, I had email from him offlist. FYI, whilst I disagree that we should break Postgres for the sake of some lazy developers on another project, Jean-Michel was a long-term major contributor to pgAdmin I and II and I have a lot of respect for him and am extremely grateful for his past work on the project. That is good to hear. I do not think (nor do I think anyone else here thinks) that making Drupal work with PostgreSQL is a bad idea, and I hope that he and others will continue to pursue that goal - having said that, asking us to make changes that are not based on solid technical arguments, don't conform to the SQL standard, and most important that we already clearly said we were not going to make is not the way to get there. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
francois.pe...@free.fr (François Pérou) writes: * I am very surprised by the SQL level of Php developers. The example Drupal developers trying to rewrite SQL queries dynamically adding DISTINCT clause is just an example. So don't expect them to understand the difference between MySQL and PostgreSQL. It is out of reach. They focuse on Php code. If they refuse to contemplate suggestions as to how to write more portable SQL queries that would work with databases other than MySQL, then I don't know what constructive discussion there can be about this. I believe that PostgreSQL should support more MySQLisms in order to BEAT MySQL. Why, when the MySQLisms in questions are divergences from the SQL standards, and the issue seems to bite all the other databases in more or less the same way? It doesn't seem to me that the answer to Drupal developers refuse to consider writing portable SQL is to try to impose MySQL's divergences from SQL onto all the *other* DBMSes. -- Have you noticed that, when we were young, we were told that `everybody else is doing it' was a really stupid reason to do something, but now it's the standard reason for picking a particular software package? -- Barry Gehm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
Le vendredi 05 mars 2010 à 15:40 -0500, Robert Haas a écrit : having said that, asking us to make changes that are not based on solid technical arguments, don't conform to the SQL standard, and most important that we already clearly said we were not going to make is not the way to get there. Okay, thank you all for these explanations. I was talking about flexibility in the SQL syntax, which is linked to the need to be flexible in front of developers communities which are not interested in SQL and therefore use broken tools like MySQL. Drupal 6 is just an example. D7 may be better, okay. A lot of applications are built with a bad SQL syntax. You cannot change the world. I was talking about flexibility to allow people to migrate and test other databases than MySQL and you stick to the idea of a pure SQL syntax, like ayatollahs preaching in the desert. If an SQL syntax is not pure, why not rewrite it and issue a warning. This would allow to run MySQL code nearly unchanged. All you say is NO, NOT PURE code. I will not ever post again on PostgreSQL mailing list about these issues. Everyone expressed their ideas. I admit my ideas are not supported by anyone. This is bad enough because I debugged hundreds of Drupal modules and wrote a short technical guide on D website. Too bad you are not listening for more flexibility. Bye. Jean-Michel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
