Re: SQL compatibility reminder: MySQL vs PostgreSQL

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/555514

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/555530

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/555562

IMHO, it is no use working on complex issues like replication if the SQL
code of major softwares cannot run on PostgreSQL.

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.

* 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

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?

Kind regards,
Jean-Michel Pouré

2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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

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/555530
>
>
>

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

||
||

2010/3/5 François Pérou <francois(dot)perou(at)free(dot)fr>:
> => All non-aggregate fields must be present in the GROUP BY clause
> http://drupal.org/node/555530

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

2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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/555514
>
> 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/555530
>
> 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/555562

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 inconvenient. Needless to say that's
ancient history at this point. But even back then it seemed to me
that it was worth enduring some temporary inconvenience to move from a
database that *would not work for my queries at all no matter what* to
one that *would require some adjustments to my queries*. And
PostgreSQL has made enormous progress on almost every front since
then.

I think it's pretty funny that major features like replication don't
seem as important to you as making PostgreSQL support certain bits of
MySQL-specific syntax. I think I speak for most people here when I
say that you're probably best off sticking with MySQL in that case. I
expect many new PostgreSQL features over the next several years (much
as there have been over the past several years) that will allow me to
do really cool things that I can't do right now, as well as continuing
performance enhancements. These will be real, substantive features,
not just syntax changes. I do expect that there will be some work
toward syntax and feature compatibility with other databases, but I
suspect for the most part we'll be looking at Oracle and the SQL
standard rather than MySQL.

...Robert

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é

2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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

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

2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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.

2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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

On Fri, 2010-03-05 at 12:42 -0500, Robert Haas wrote:
> 2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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.

On Fri, Mar 5, 2010 at 9:55 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> 2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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
<jm(at)poure(dot)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

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

On Fri, Mar 5, 2010 at 1:48 PM, Josh Berkus <josh(at)agliodbs(dot)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

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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

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

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.

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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Mar 5, 2010 at 9:55 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> 2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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

On Fri, Mar 5, 2010 at 3:30 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, Mar 5, 2010 at 9:55 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>> 2010/3/5 François Pérou <francois(dot)perou(at)free(dot)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

francois(dot)perou(at)free(dot)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

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

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

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

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

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

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

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

Joshua,

I've moved this discussion to pgsql-advocacy where it belongs.

> 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

What's the obstacle to fixing it? It seems like just changing to SELECT
DISTINCT ON ... would do the trick, and would allow cutting code.

--Josh Berkus

Josh Berkus wrote:
> Joshua,
>
> I've moved this discussion to pgsql-advocacy where it belongs.
>
>
>> 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
>>
>
> What's the obstacle to fixing it? It seems like just changing to SELECT
> DISTINCT ON ... would do the trick, and would allow cutting code.
>
> --Josh Berkus
>
Views is trying to make MySQL and PostgreSQL compliant code. So the goal
is to make MySQL and PostgreSQL return the same results from the same
views configuration. http://drupal.org/node/607418#comment-2677468 is a
good example of what Views is doing to PostgreSQL to make it work.

Be wary that often the SQL isn't the best written SQL because it is
dynamically generated and designed to be altered by the views framework.

So your saying SELECT DISTINCT(field) is different to SELECT DISTINCT ON
field?

Apparently the problem is that when MySQL uses group by clauses like
pgsql does, it doesn't return the same results (I'm not 100% sure on
this) and thats the reason why Views doesn't want to use group by clauses.

--
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

FWIW
I've heard this complaint from MySQL users switching to PostgreSQL and SQL
Server before. Even heard it from FoxPro users which also seems to have
this non-standard behavior of group by.

A simple DISTINCT ON doesn't quite do it for them. I think you need to wrap
it in a subselect to get the equivalent meaning.

If I am not mistaken in MySQL when you have a construct such as

SELECT u.username, n.subject,n.note, n.post_date
FROM users As u INNER JOIN notes As n ON u.user_id = n.user_id
GROUP BY u.username
ORDER BY n.post_date DESC

It essentially means return the username, subject and note of the last note
for each user and sort by the last post date

The problem they say with DISTINCT ON is that it forces sorting by the key
first, which is not what they want.

So the equivalent I think would be

SELECT *
FROM
(SELECT DISTINCT ON(u.username) u.username, n.subject, n.note, n.post_date
FROM users As u INNER JOIN notes As n ON u.user_id = n.user_id
ORDER BY u.username, n.post_date DESC) As foo
ORDER BY foo.post_date DESC

Hope that helps,
Regina

-----Original Message-----
From: pgsql-advocacy-owner(at)postgresql(dot)org
[mailto:pgsql-advocacy-owner(at)postgresql(dot)org] On Behalf Of Josh Berkus
Sent: Sunday, March 07, 2010 6:05 PM
To: Joshua Waihi
Cc: Mark Kirkwood; francois(dot)perou(at)free(dot)fr; Craig Ringer; Andrew Dunstan;
PostgreSQL Advocacy; dpage(at)pgadmin(dot)org
Subject: Re: [pgsql-advocacy] [HACKERS] SQL compatibility reminder: MySQL vs
PostgreSQL

Joshua,

I've moved this discussion to pgsql-advocacy where it belongs.

> 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

What's the obstacle to fixing it? It seems like just changing to SELECT
DISTINCT ON ... would do the trick, and would allow cutting code.

--Josh Berkus

--
Sent via pgsql-advocacy mailing list (pgsql-advocacy(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-advocacy

> 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 set-returning functions stats problem
since you could store and analyze the function result in an efficient way.

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

> 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)

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

> 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

On Mon, Mar 8, 2010 at 4:58 AM, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)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

+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 <andrew(at)dunslane(dot)net>
An: francois(dot)perou(at)free(dot)fr
CC: Josh Berkus <josh(at)agliodbs(dot)com>; Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>; pgsql-hackers(at)postgresql(dot)org; dpage(at)pgadmin(dot)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(at)postgresql(dot)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

On Mon, Mar 8, 2010 at 9:48 AM, Wolfgang Wilhelm
<wolfgang20121964(at)yahoo(dot)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 <andrew(at)dunslane(dot)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.

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Wolfgang Wilhelm <wolfgang20121964(at)yahoo(dot)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

On Mar 8, 2010, at 9:16 AM, Kevin Grittner wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Wolfgang Wilhelm <wolfgang20121964(at)yahoo(dot)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
david(at)endpoint(dot)com

David Christensen <david(at)endpoint(dot)com> writes:
> On Mar 8, 2010, at 9:16 AM, Kevin Grittner wrote:
> Wolfgang Wilhelm <wolfgang20121964(at)yahoo(dot)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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Mon, Mar 8, 2010 at 4:17 AM, Pierre C <lists(at)peufeu(dot)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

On Mon, Mar 08, 2010 at 11:58:20AM -0500, Jaime Casanova wrote:
> On Mon, Mar 8, 2010 at 4:17 AM, Pierre C <lists(at)peufeu(dot)com> wrote:
> >
> >> My opinion is that PostgreSQL should accept any MySQL syntax and
> >> return warnings. I believe that we should access even innodb
> >> syntax and turn it immediately into PostgreSQL tables. This would
> >> allow people with no interest in SQL to migrate from MySQL to
> >> PostgreSQL without any harm.
> >
> > A solution would be a SQL proxy (a la pgpool) with query
> > rewriting.
>
> This sounds like a better idea...

Aside from that little "halting problem" issue, it sounds wonderful.
You do know that SQL is Turing-complete, right?

> >> PHP developers don't have time to invest in learning deep SQL.
> >
> > This is true, and it is a big problem IMHO. It results in lots of slow,
> > broken, insecure database designs.
>
> So, if php dev doesn't have time to learn to do things right then we
> have to find time to learn to do things wrong? seems like a nosense
> argument to me

Indeed.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

2010/3/9 Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>:
> On Mon, Mar 8, 2010 at 4:17 AM, Pierre C <lists(at)peufeu(dot)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

On Mon, Mar 8, 2010 at 12:10 PM, David Fetter <david(at)fetter(dot)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 <lists(at)peufeu(dot)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

On Mon, Mar 08, 2010 at 12:18:31PM -0500, Robert Haas wrote:
> On Mon, Mar 8, 2010 at 12:10 PM, David Fetter <david(at)fetter(dot)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 <lists(at)peufeu(dot)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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

On Mon, Mar 8, 2010 at 12:43 PM, David Fetter <david(at)fetter(dot)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 <david(at)fetter(dot)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 <lists(at)peufeu(dot)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

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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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

> 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.

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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