Re: PostgreSQL or MySQL ?
- From: Nis Jørgensen <nis@xxxxxxxxxxxxx>
- Date: Fri, 25 May 2007 19:38:10 +0200
Bill Karwin skrev:
On May 22, 5:13 pm, Nis Jørgensen <n...@xxxxxxxxxxxxx> wrote:
I have used both. Love Postgres. Hate MySQL.
I'm not going to argue that PostgreSQL isn't superior technology,
implemented some features long before MySQL, etc. I do agree with
that, and your points are well taken. But a few of your statements
below are not true.
Comments below, some agreements, some disagreement
The documentation for MySQL covers all versions at once. This means you
cannot just look at the syntax diagrams and assume that they apply to
the version you are running.
This is the case for MySQL now too. They do still have doc for
3.23/4.0/4.1 lumped together, but now the 5.0 and 5.1 doc are
presented separately.
http://dev.mysql.com/doc/refman/4.1/en/index.html
http://dev.mysql.com/doc/refman/5.0/en/index.html
http://dev.mysql.com/doc/refman/5.1/en/index.html
True. I am really wondering: Did 4.0 not have documentation? Why is that
documentation no longer available separately online?
Postgres supports transactions, subselects, foreign keys, constraints,
updatable views (via RULES or TRIGGERS) and stored procedures/functions
in several languages - all in the standard configuration.
MySQL 5.0 supports these features (except updatable views) in its
standard configuration. That is, the default storage engine in recent
versions is InnoDB, which supports the features you mention.
Subqueries have been supported since MySQL 4.1, the production release
of which was October 2004. MySQL 4.0 and earlier versions don't
support subqueries, that's true. But those earlier versions are now
so old that MySQL has discontinued support for them. How many years
does a feature need to be in the product before it is considered to be
a standard, supported feature?
My experience: Mysql support these _to_some_degree_ in the versions you
quote. I regularly run into problems with these and other "advanced"
features, even when using 5.x. Examples include:
- Not being able to update multiple rows in one UPDATE statement, if the
intermediate state of the table violates a constraint. Example: UPDATE
mytable SET order = 2 - order WHERE order in (1,2);
- Not being able to use a subquery on the same table in an UPDATE
statement. Example: UPDATE mytable set order = 1+ (SELECT Max(order)
FROM mytable) where id = 123;
- Not being able to rename a field with a FOREIGN KEY constraint on it
(no error message until trying to manipulate the data).
- FOREIGN KEY constraints being silently ignored if the table is MyISAM.
If you are lucky, you will get an immediate error when you try doing
these things.
By the same argument I could cite PostgreSQL's lack of support for
Windows. :-)
(To be clear, I'm referring to the fact that at one time in the
distant past, PostgreSQL did not offer a Windows binary, but of course
today they do.)
Actually, I believe this feature (lack of Windows support) has been
crucial in achieveing the current level of quality of postgresql. I
strongly recommend it for any Open Source project aiming for excellence.
Postgres gives you meaningful error messages most of the time. MySQL
mostly just says "You have an error in your SQL syntax;"
Yes, that is part of the MySQL error message... and it's followed by
the context in the SQL statement where the error occurred.
Postgres, AFAIR, will tell me what the parser expected at this point,
and what it got. Sometimes it will even guess what I was trying to do,
and tell me the exact syntax for doing it.
As I remember it, MySQL has a very broad definition of "Syntax error",
which also includes calling builtin functions with the wrong number of
arguments, unknown functions and the like. All making it just a little
bit harder to find out what went wrong.
I don't work for MySQL, but I feel like sometimes people don't give
that product a fair chance. You should judge it based on its current
version, not versions that are 3+ years out of date.
The thing is, even 3+ years ago, people where claiming that it was a
viable alternative, that it had every thing a "normal" user needed etc.
Furthermore, people would at the same time praise its performance (using
myISAM) and functionality (using InnoDB). Claiming to support a bit of
functionality as soon as you had hacked in support for the simplest
cases didn't help either.
Perhaps MySQL today supports these things. However, based on the past, I
don't trust MySQL A/B to actually tell me whether something is working
or not, at least not without reading the fine print. In the few cases
where I have tried using the "advanced" features, I have often run into
trouble, a s mentioned above.
YMMW
Nis
.
- Follow-Ups:
- Re: PostgreSQL or MySQL ?
- From: Bill Karwin
- Re: PostgreSQL or MySQL ?
- References:
- Re: PostgreSQL or MySQL ?
- From: Nis Jørgensen
- Re: PostgreSQL or MySQL ?
- From: Bill Karwin
- Re: PostgreSQL or MySQL ?
- Prev by Date: Re: Midget amongst Eagles
- Next by Date: Re: Whose Fish? RDBMS Solution
- Previous by thread: Re: PostgreSQL or MySQL ?
- Next by thread: Re: PostgreSQL or MySQL ?
- Index(es):
Relevant Pages
|
