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