fairly current mysql v postgresql comparison need for advocacy

I'm getting beaten up by the mysql users around me because they claim
that MySQL 4.x with InnoDB solves all of the ACID problems, so Pg no
longer pulls clearly ahead.

Has anyone examined this closely so that I have some nice things to
say about Pg instead?

Also, has anyone done a MySQL 4.x v Pg 7.{2,3}.x comparison recently?
All of the comparisons I saw in google were older.

Thanks.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

On Monday 24 Mar 2003 8:22 pm, Randal L. Schwartz wrote:
> I'm getting beaten up by the mysql users around me because they claim
> that MySQL 4.x with InnoDB solves all of the ACID problems, so Pg no
> longer pulls clearly ahead.
>
> Has anyone examined this closely so that I have some nice things to
> say about Pg instead?

Yes.. Plenty of them..

* subselects in updates/deletes
* stored procedures
* stored procedures in different language
* ability to create different languages
* inheritance
* speed with transactions( Contrary to populer myth, mysql with transactions
is not at all faster than postgresql
* ability to survive large number of connections
* wealth of data types( Mysql has type to store network addresses?)
* ability to create user defined data types..

So on and so forth..

> Also, has anyone done a MySQL 4.x v Pg 7.{2,3}.x comparison recently?
> All of the comparisons I saw in google were older.

I suggest you do it if you have the setup. The community needs the results
anyway..

Shridhar

Randal L. Schwartz wrote:
> I'm getting beaten up by the mysql users around me because they claim
> that MySQL 4.x with InnoDB solves all of the ACID problems, so Pg no
> longer pulls clearly ahead.
>
> Has anyone examined this closely so that I have some nice things to
> say about Pg instead?

VIEWS
Triggers
Rules
Subselects in target lists*
NUMERIC type of arbitraty precision
PL/pgSQL, PL/Tcl, PL/Perl, PL/Pythin
Multiversioning
User-definited data types, operators, and functions
Write-ahead logging
Partial indexes
Functional indexes
Rollbackable DDL (CREATE/DROP TABLE, etc.)
Absolutely 100% free

How's that, for a start?

*They apparently added subselects as of Jan of this year in FROM
and WHERE clauses. Docs don't mention target lists, they might
have implemented subselects in target lists though.

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> *They apparently added subselects as of Jan of this year in FROM
> and WHERE clauses. Docs don't mention target lists, they might
> have implemented subselects in target lists though.

It would be worth asking some hard questions about how good their
subselect implementation is, too. "We've got subselects!" doesn't
mean that they can optimize 'em with any efficiency. Our subselect
implementation has gotten a lot better over the years than it started
out as; I'd imagine MySQL is just starting to climb that same learning
curve ...

regards, tom lane

On 24 Mar 2003, Randal L. Schwartz wrote:

>
> I'm getting beaten up by the mysql users around me because they claim
> that MySQL 4.x with InnoDB solves all of the ACID problems, so Pg no
> longer pulls clearly ahead.
>
> Has anyone examined this closely so that I have some nice things to
> say about Pg instead?
>
> Also, has anyone done a MySQL 4.x v Pg 7.{2,3}.x comparison recently?
> All of the comparisons I saw in google were older.

Try this one:

Setup a postgresql database. Make sure fsync is on (the default.)
Make sure you're using a journaling file system, like ext3 or xfs for your
data.
Initiate 100 simultaneous transactions (pgbench works well for this.)

Pull the plug while they are running. Turn your machine back on. Check
the consistency of your database. It should be ok after recovery.

Do the same to MySQL.

Repeat the above tests over and over. Contrast and compare.

On 24 Mar 2003, Randal L. Schwartz wrote:

>
> I'm getting beaten up by the mysql users around me because they claim
> that MySQL 4.x with InnoDB solves all of the ACID problems, so Pg no
> longer pulls clearly ahead.
>
> Has anyone examined this closely so that I have some nice things to
> say about Pg instead?
>
> Also, has anyone done a MySQL 4.x v Pg 7.{2,3}.x comparison recently?
> All of the comparisons I saw in google were older.

Oh, and another thing. How do you do hot backups of a MySQL database
running Innodb tables? Better get out the checkbook for some closed
source software.

> [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens scott.marlowe
> Verzonden: maandag 24 maart 2003 19:26

> Oh, and another thing. How do you do hot backups of a MySQL database
> running Innodb tables? Better get out the checkbook for some closed
> source software.
Please elaborate what you see as a hot backup tool?

Is that, for postgresql, the pg_dump tool? In that case, mysql has the
same. Mysqldump works fine with innodb-tables as well as for myisam
tables.
A really nice thing you'd test is load a very large table into your
innodb (something like 4GB+) and issue a 'delete from testtable', last
time I tried that I spent the rest of the day trying to recover our
innodb-setup... It completely crashed mysql and failed to start
afterwards for a while... Lately it has been fixed 'a bit', your system
probably is able to restart, even though it still crashes or renders
itself unuseable.

Or very cute aswell, set it up with 10GB of innodb-files and load a 6GB
database in it. Then you notice performance is worse than expacted and
you find out a certain index needs to be added... No chance you can do
that on your running system, since mysql needs to create a temporary
duplicate of your table and add the index to that, which of course is
impossible due to missing diskspace. The temporary table is loaded into
the innodb-space aswell and it failes due to lack of space, if you _do_
have enough space it still takes ages to accomplish, copying a 6GB table
isn't a thing database handles very fast...

The same silly behaviour is done when renaming columns or doing any
other changes to the tables. As far as I know, all 'alter table'
statements (and create index appearantly gets translated to that)
require a copy of the entire table and while copying changing the
structure. This is mysql 3.23 knowledge by the way, not 4.0, so maybe
they solved this.

Make a table structure like:
users = uid, uname
questions = qid, qtext
answers = aid, qid, atext
answered_questions = aid, qid, uid

Fill it with a decent amount of answers (please note that it is legal
not to answer a question in this design).
And issue a query like:
SELECT answers.atext,
answers.aid,
count(answered_question.aid) AS answercount
FROM
answers
LEFT JOIN answered_question ON answers.aid = answered_question.aid
WHERE
answers.qid = 28
GROUP BY answers.aid, answers.atext

In my test (3.23.56 vs 7.3) it results in runtimes like resp: 34.96 vs
23.43 ms.
Postgresql 7.4 dev is even faster with these queries, again I haven't
tested it with 4.0.
These tables are pretty small by the way, 202 questions, 801 answers on
that and 81215 answered_question records, over 40000 userid's, but just
some 3000 different ones had answered these questions.

Most queries where a count/agregate is involved are quite slow in mysql,
I heard.

Another nice test is something where you do something like:
select * from table order by some_indexed_column asc limit 5;
select * from table order by some_indexed_column desc limit 5;

Although mysql probably handles these faster than postgresql it is
unable to sort results backwards as well as it does forward... That can
result in queries over 5 to 10 times slower than the forward version.
Somehow it even uses a file-sort when it has to sort 2 results backward
|:(

I hope this is a bit usefull, it is a short summary of my bad
experiences with mysql ;)

Best regards,

Arjen van der Meijden

Arjen van der Meijden wrote:
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens scott.marlowe
> > Verzonden: maandag 24 maart 2003 19:26
>
> > Oh, and another thing. How do you do hot backups of a MySQL database
> > running Innodb tables? Better get out the checkbook for some closed
> > source software.
> Please elaborate what you see as a hot backup tool?
>
> Is that, for postgresql, the pg_dump tool? In that case, mysql has the
> same. Mysqldump works fine with innodb-tables as well as for myisam
> tables.

Can the database be active during the backup, read/writes? pg_dump can
do that, and make a consistent dump.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Arjen van der Meijden wrote:
>
>>Is that, for postgresql, the pg_dump tool? In that case, mysql has the
>>same. Mysqldump works fine with innodb-tables as well as for myisam
>>tables.
>
> Can the database be active during the backup, read/writes? pg_dump can
> do that, and make a consistent dump.

I don't see how it could be done without locking out writers, or
writers locking out the dump utility, since InnoDB uses row
locks and BDB uses page locks instead of MVCC. Remember when
Sleepycat wanted to convince hackers that the storage manager
should be trashed for Berkley DB? MVCC was going to be pitched
overboard. Clearly the native tables wouldn't support it:

"Table locking is, however, not very good under the following
senario:

A client issues a SELECT that takes a long time to run.
Another client then issues an UPDATE on a used table. This
client will wait until the SELECT is finished.
Another client issues another SELECT statement on the same
table. As UPDATE has higher priority than SELECT, this SELECT
will wait for the UPDATE to finish. It will also wait for the
first SELECT to finish!"

Their spin on this is awful:

"MySQL uses table locking (instead of row locking or column
locking) on all table types, except InnoDB and BDB tables, to
achieve a very high lock speed. For large tables, table locking
is much better than row locking for most applications, but there
are, of course, some pitfalls."

Does the truth matter at all? Ugh.

Mike Mascari
mascarm(at)mascari(dot)com

Our MVCC allows it because there are multiple copies of the row --- pre-dump
and post-dump.

---------------------------------------------------------------------------

Mike Mascari wrote:
> Bruce Momjian wrote:
> > Arjen van der Meijden wrote:
> >
> >>Is that, for postgresql, the pg_dump tool? In that case, mysql has the
> >>same. Mysqldump works fine with innodb-tables as well as for myisam
> >>tables.
> >
> > Can the database be active during the backup, read/writes? pg_dump can
> > do that, and make a consistent dump.
>
> I don't see how it could be done without locking out writers, or
> writers locking out the dump utility, since InnoDB uses row
> locks and BDB uses page locks instead of MVCC. Remember when
> Sleepycat wanted to convince hackers that the storage manager
> should be trashed for Berkley DB? MVCC was going to be pitched
> overboard. Clearly the native tables wouldn't support it:
>
> "Table locking is, however, not very good under the following
> senario:
>
> A client issues a SELECT that takes a long time to run.
> Another client then issues an UPDATE on a used table. This
> client will wait until the SELECT is finished.
> Another client issues another SELECT statement on the same
> table. As UPDATE has higher priority than SELECT, this SELECT
> will wait for the UPDATE to finish. It will also wait for the
> first SELECT to finish!"
>
> Their spin on this is awful:
>
> "MySQL uses table locking (instead of row locking or column
> locking) on all table types, except InnoDB and BDB tables, to
> achieve a very high lock speed. For large tables, table locking
> is much better than row locking for most applications, but there
> are, of course, some pitfalls."
>
> Does the truth matter at all? Ugh.
>
> Mike Mascari
> mascarm(at)mascari(dot)com
>
>
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, 24 Mar 2003, Arjen van der Meijden wrote:

> > [mailto:pgsql-general-owner(at)postgresql(dot)org] Namens scott.marlowe
> > Verzonden: maandag 24 maart 2003 19:26
>
> > Oh, and another thing. How do you do hot backups of a MySQL database
> > running Innodb tables? Better get out the checkbook for some closed
> > source software.
> Please elaborate what you see as a hot backup tool?

For MySQL you need a special tool to backup innodb tables "hot". i.e.
while transactions are running or the database is in use. For Postgresql,
hot backups as well as cold are handled by pg_dump. The elaboration on
the tool needed is here:

http://www.mysql.com/doc/en/Backing_up.html

> Is that, for postgresql, the pg_dump tool? In that case, mysql has the
> same. Mysqldump works fine with innodb-tables as well as for myisam
> tables.

As long as there aren't any transactions pending while you run it. It
does have issues then. The hotbackuptool that MySQL comes with is
explicitly documented by the mysql folks as NOT working for innodb tables.

Bruce Momjian wrote:
> Our MVCC allows it because there are multiple copies of the row --- pre-dump
> and post-dump.

Exactly. However, according to the mySQL docs I see the
mysqldump option:

"--single-transaction

This option issues a BEGIN SQL command before dumping data from
server. It is mostly useful with InnoDB tables and
READ_COMMITTED transaction isolation level, as in this mode it
will dump the consistent state of the database at the time then
BEGIN was issued without blocking any applications. When using
this option you should keep in mind that only transactional
tables will be dumped in a consistent state, e.g., any MyISAM or
HEAP tables dumped while using this option may still change
state. The --single-transaction option was added in version 4.0.2."

They also have mysqlhotcopy which:

"mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH
TABLES and cp or scp to quickly make a backup of a database.
It's the fastest way to make a backup of the database or single
tables, but it can only be run on the same machine where the
database directories are."

Again, without MVCC, I fail to see how InnoDB or BDB can perform
a hot-backup without blocking UPDATES/DELETES until the dump is
complete. Of course mysqlhotcopy just locks the whole database
until the backup is complete.

scott(dot)marlowe(at)ihs(dot)com also wrote:

> As long as there aren't any transactions pending while
> you run it. It does have issues then. The
> hotbackuptool that MySQL comes with is explicitly
> documented by the mysql folks as NOT working for
> innodb tables.

Apparently --single-transaction was added in 4.0.2. It still
doesn't resolve the underlying concurrency issues associated
with a non-MVCC database, however.

medi(dot)montaseri(at)intransa(dot)com also wrote:

> Not to diverge from the main issue, but was this a
> referene to a conversation between Bill Gate and
> Steve Jobs in "Silicon Valley Pirates" movie?

Not intentionally ;-)

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari wrote:

>
> Does the truth matter at all? Ugh.
>
> Mike Mascari
> mascarm(at)mascari(dot)com
>
Not to diverge from the main issue, but was this a referene to a
conversation between
Bill Gate and Steve Jobs in "Silicon Valley Pirates" movie?

It is so true....unfortunately many products live by that....

Now back to PG vs MySQL.....if we put some replication or distributed
features into PG,
then MySQL will definitely look "My", as in "its mine", as in "personal
DB" like personal computers....

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

If I had the big bucks, I'd pony up to put this into it.

I don't have them.

But for curiosity's sake,

1/ How much money,
2/ How much time
3/ How many people

to get the replication and distribution features in Postgres?

Medi Montaseri wrote:
> Mike Mascari wrote:
>
>>
>> Does the truth matter at all? Ugh.
>>
>> Mike Mascari
>> mascarm(at)mascari(dot)com
>>
> Not to diverge from the main issue, but was this a referene to a
> conversation between
> Bill Gate and Steve Jobs in "Silicon Valley Pirates" movie?
>
> It is so true....unfortunately many products live by that....
>
> Now back to PG vs MySQL.....if we put some replication or distributed
> features into PG,
> then MySQL will definitely look "My", as in "its mine", as in "personal
> DB" like personal computers....
>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

> Van: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]

> > Is that, for postgresql, the pg_dump tool? In that case,
> mysql has the
> > same. Mysqldump works fine with innodb-tables as well as for myisam
> > tables.
>
> Can the database be active during the backup, read/writes?
> pg_dump can do that, and make a consistent dump.
No it can't, or actually it can... But your backup probably isn't as
consistent as you'd like. So indeed, you probably can't hotbackup a
innodb table for free, you can't even isolate one on disk if you tried.
And indeed, you'll have to pay a certain amount of money,
http://www.innodb.com/hotbackup.html, for a real hotbackup solution.

I managed to forget that it happens like that, in our production-setup
it just isn't very important that the backups are 100% consistent. ;)

The thing that really counts in our setup is raw page-serve-speed and
with our website that is better provided by mysql than postgresql.
Although recent devevelopments (pg 7.3 and 7.4dev) have made sure
postgresql has come much closer to mysql on the
simpel-query-performance. Too bad the query-plans aren't stored in some
overall memory, instead of just for the current connection itself.
Connection pooling isn't something php is very good at.

Best regards,

Arjen van der Meijden

Supposedly MySQL did add MVCC in some fashion, so I assume that's how
they would do it.

---------------------------------------------------------------------------

Mike Mascari wrote:
> Bruce Momjian wrote:
> > Our MVCC allows it because there are multiple copies of the row --- pre-dump
> > and post-dump.
>
> Exactly. However, according to the mySQL docs I see the
> mysqldump option:
>
> "--single-transaction
>
> This option issues a BEGIN SQL command before dumping data from
> server. It is mostly useful with InnoDB tables and
> READ_COMMITTED transaction isolation level, as in this mode it
> will dump the consistent state of the database at the time then
> BEGIN was issued without blocking any applications. When using
> this option you should keep in mind that only transactional
> tables will be dumped in a consistent state, e.g., any MyISAM or
> HEAP tables dumped while using this option may still change
> state. The --single-transaction option was added in version 4.0.2."
>
> They also have mysqlhotcopy which:
>
> "mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH
> TABLES and cp or scp to quickly make a backup of a database.
> It's the fastest way to make a backup of the database or single
> tables, but it can only be run on the same machine where the
> database directories are."
>
> Again, without MVCC, I fail to see how InnoDB or BDB can perform
> a hot-backup without blocking UPDATES/DELETES until the dump is
> complete. Of course mysqlhotcopy just locks the whole database
> until the backup is complete.
>
> scott(dot)marlowe(at)ihs(dot)com also wrote:
>
> > As long as there aren't any transactions pending while
> > you run it. It does have issues then. The
> > hotbackuptool that MySQL comes with is explicitly
> > documented by the mysql folks as NOT working for
> > innodb tables.
>
> Apparently --single-transaction was added in 4.0.2. It still
> doesn't resolve the underlying concurrency issues associated
> with a non-MVCC database, however.
>
> medi(dot)montaseri(at)intransa(dot)com also wrote:
>
> > Not to diverge from the main issue, but was this a
> > referene to a conversation between Bill Gate and
> > Steve Jobs in "Silicon Valley Pirates" movie?
>
> Not intentionally ;-)
>
> Mike Mascari
> mascarm(at)mascari(dot)com
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Supposedly MySQL did add MVCC in some fashion, so I assume that's how
> they would do it.

Yep. The row-level locking remark threw me. According to 7.5.8
of their docs:

"In the InnoDB transaction model the goal has been to combine
the best properties of a multi-versioning database to
traditional two-phase locking. InnoDB does locking on row level
and runs queries by default as non-locking consistent reads, in
the style of Oracle. The lock table in InnoDB is stored so
space-efficiently that lock escalation is not needed: typically
several users are allowed to lock every row in the database, or
any random subset of the rows, without InnoDB running out of
memory."

Now I'm not sure why someone would need InnoDB Hot Backup:

http://www.innodb.com/hotbackup.html

when the --single-transaction option to mysqldump would provide
a consistent snapshot.

Mike Mascari
mascarm(at)mascari(dot)com

OK, I've thought about it a bit, and have a more considered opinion. I've
used MySQL a bit, and Postgresql a lot. I've found that Postgresql and
MySQL seem to have divergent philosophies in many areas.

One of them is value checking of input data.
This next paragraph clearly spells out the philosophy of data checking
being something in the realm of what your program should be doing, so
MySQL doesn't have to.

From their documentation at: http://www.mysql.com/doc/en/Open_bugs.html

** QUOTE **

# Because MySQL Server allows you to work with table types that don't
support transactions, and thus can't rollback data, some things behave a
little differently in MySQL Server than in other SQL servers. This is just
to ensure that MySQL Server never needs to do a rollback for a SQL
command. This may be a little awkward at times as column values must be
checked in the application, but this will actually give you a nice speed
increase as it allows MySQL Server to do some optimisations that otherwise
would be very hard to do. If you set a column to an incorrect value, MySQL
Server will, instead of doing a rollback, store the best possible value in
the column:

** END QUOTE **

NULLS:

Postgresql is pedantic about getting the data right, all right, and
completely right, or throwing up its hands and telling you to get your
data straight and try again. For instance, if you try to put a null in a
not null column Postgresql will generate an error and not take the data
in.

** QUOTE **

# If you try to store NULL into a column that doesn't take NULL values,
MySQL Server will store 0 or '' (empty string) in it instead. (This
behaviour can, however, be changed with the -DDONT_USE_DEFAULT_FIELDS
compile option.)

** END QUOTE **

DATES:

If you set a wrong or impossible date in postgresql it complains. If you
don't supply one, and there is no default, it inserts NULL. If the column
is NOT NULL, it refuses to insert the data.

** QUOTE **

# MySQL allows you to store some wrong date values into DATE and DATETIME
columns (like 2000-02-31 or 2000-02-00). The idea is that it's not the SQL
server job to validate date. If MySQL can store a date and retrieve
exactly the same date, then MySQL will store the date. If the date is
totally wrong (outside the server's ability to store it), then the special
date value 0000-00-00 will be stored in the column.

** END QUOTE **

NUMERIC:

If you try to insert a number outside the range allowed by a numeric,
postgresql will error out.

** QUOTE **

* If you try to store a value outside the range in a numerical column,
MySQL Server will instead store the smallest or biggest possible value in
the column.

** END QUOTE**

Just this one area alone shows the great difference in philosophy between
the two databases. It's not that one is better than the other, it's that
they're aimed at difference audiences.

If you've got to implement a financial system in a database, and you're
familiar with the commercial databases, then Postgresql seems like
familiar ground.

If you've got to implement a content management system for 28 people who
will be checking out and in documents all day, with locking being the
course grained "checked in / checked out" settings for a file, along with
a timeout and a name of the checker outer, then MySQL is a fine fit.

I've found that this difference makes it a little harder to get started in
Postgresql than MySQL, since Postgresql will spit up on the programmer in
a heart beat if they don't give it the right data. Data that would slide
right into a MySQL database will get tossed by Postgresql.

I know someone who lost all the dates in a MySQL table because it
never complained when inputting 0000-00-00 when his field format was
wrong and he converted the field from one type to another. Table
converted, original data gone. Postgresql doesn't even try to change the
type of a field, since that's an inherently unsafe and uncertain thing to
do, you get to figure it out for yourself in Postgresql. :-)

MySQL is often run as root. Not a safe thing for a database program.
Postgresql refuses to run as root. Every year or so someone comes on the
list opining for the postmaster to be able to run as root, how we don't
trust the users and all. It's not about that, it's just good safety
practice.

The cornerstone of MySQL is performance above all, the cornerstone of
Postgresql is correctness above all.

Thank you, that is exatly why I love the postgres database so much!!

I write all my programs to the tune of they either work or die and complain
loudly :)

Josh.

On March 24, 2003 04:46 pm, scott.marlowe wrote:
> OK, I've thought about it a bit, and have a more considered opinion. I've
> used MySQL a bit, and Postgresql a lot. I've found that Postgresql and
> MySQL seem to have divergent philosophies in many areas.
>
> One of them is value checking of input data.
> This next paragraph clearly spells out the philosophy of data checking
> being something in the realm of what your program should be doing, so
> MySQL doesn't have to.
>
> From their documentation at: http://www.mysql.com/doc/en/Open_bugs.html
>
> ** QUOTE **
>
> # Because MySQL Server allows you to work with table types that don't
> support transactions, and thus can't rollback data, some things behave a
> little differently in MySQL Server than in other SQL servers. This is just
> to ensure that MySQL Server never needs to do a rollback for a SQL
> command. This may be a little awkward at times as column values must be
> checked in the application, but this will actually give you a nice speed
> increase as it allows MySQL Server to do some optimisations that otherwise
> would be very hard to do. If you set a column to an incorrect value, MySQL
> Server will, instead of doing a rollback, store the best possible value in
> the column:
>
> ** END QUOTE **
>
> NULLS:
>
> Postgresql is pedantic about getting the data right, all right, and
> completely right, or throwing up its hands and telling you to get your
> data straight and try again. For instance, if you try to put a null in a
> not null column Postgresql will generate an error and not take the data
> in.
>
> ** QUOTE **
>
> # If you try to store NULL into a column that doesn't take NULL values,
> MySQL Server will store 0 or '' (empty string) in it instead. (This
> behaviour can, however, be changed with the -DDONT_USE_DEFAULT_FIELDS
> compile option.)
>
> ** END QUOTE **
>
> DATES:
>
> If you set a wrong or impossible date in postgresql it complains. If you
> don't supply one, and there is no default, it inserts NULL. If the column
> is NOT NULL, it refuses to insert the data.
>
> ** QUOTE **
>
> # MySQL allows you to store some wrong date values into DATE and DATETIME
> columns (like 2000-02-31 or 2000-02-00). The idea is that it's not the SQL
> server job to validate date. If MySQL can store a date and retrieve
> exactly the same date, then MySQL will store the date. If the date is
> totally wrong (outside the server's ability to store it), then the special
> date value 0000-00-00 will be stored in the column.
>
> ** END QUOTE **
>
> NUMERIC:
>
> If you try to insert a number outside the range allowed by a numeric,
> postgresql will error out.
>
> ** QUOTE **
>
> * If you try to store a value outside the range in a numerical column,
> MySQL Server will instead store the smallest or biggest possible value in
> the column.
>
> ** END QUOTE**
>
> Just this one area alone shows the great difference in philosophy between
> the two databases. It's not that one is better than the other, it's that
> they're aimed at difference audiences.
>
> If you've got to implement a financial system in a database, and you're
> familiar with the commercial databases, then Postgresql seems like
> familiar ground.
>
> If you've got to implement a content management system for 28 people who
> will be checking out and in documents all day, with locking being the
> course grained "checked in / checked out" settings for a file, along with
> a timeout and a name of the checker outer, then MySQL is a fine fit.
>
> I've found that this difference makes it a little harder to get started in
> Postgresql than MySQL, since Postgresql will spit up on the programmer in
> a heart beat if they don't give it the right data. Data that would slide
> right into a MySQL database will get tossed by Postgresql.
>
> I know someone who lost all the dates in a MySQL table because it
> never complained when inputting 0000-00-00 when his field format was
> wrong and he converted the field from one type to another. Table
> converted, original data gone. Postgresql doesn't even try to change the
> type of a field, since that's an inherently unsafe and uncertain thing to
> do, you get to figure it out for yourself in Postgresql. :-)
>
> MySQL is often run as root. Not a safe thing for a database program.
> Postgresql refuses to run as root. Every year or so someone comes on the
> list opining for the postmaster to be able to run as root, how we don't
> trust the users and all. It's not about that, it's just good safety
> practice.
>
> The cornerstone of MySQL is performance above all, the cornerstone of
> Postgresql is correctness above all.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

On Mon, 24 Mar 2003 14:46:42 -0700 (MST) "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
> Just this one area alone shows the great difference in philosophy
> between
> the two databases. It's not that one is better than the other, it's
> that
> they're aimed at difference audiences.

i did a lot of C code back in the early to mid 80s, and got used to fending
for myself.

then i spent 10 years as a professional lisp developer, mostly using lisp
machines.

when i went back to normal languages, i realized how important it is to
have systems actually enforce rules about data for you. i prefer postgresql
for the simple reason that if i use it properly, it will protect me from
much of my own stupidity as a developer, just like my lisp machine used to.

people who turn off these features to gain speed are asking for a world of
hurt somewhere down the line.

richard
--
Richard Welty rwelty(at)averillpark(dot)net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security

On Mon, Mar 24, 2003 at 05:07:00PM -0500, Richard Welty wrote:
> people who turn off these features to gain speed are asking for a world of
> hurt somewhere down the line.

No, they're not. They're planning that there will be some poor
schmoe who'll have to clean up the work ;-) (These are, by the way,
the same folks who are always advocating doing the transaction
management "in the application: it's faster." Mostly, as far as my
experience goes, it's faster because it doesn't work.)

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

> On Mon, Mar 24, 2003 at 05:07:00PM -0500, Richard Welty wrote:
> > people who turn off these features to gain speed are asking for a world of
> > hurt somewhere down the line.
>
> No, they're not. They're planning that there will be some poor
> schmoe who'll have to clean up the work ;-)

Either that, or the data is expected to be(come) throw-away before real
clean-up matters.

(From what I've seen, expectations about how valuable the data is going
to be often miss the mark widely to either side.)

> (These are, by the way,
> the same folks who are always advocating doing the transaction
> management "in the application: it's faster." Mostly, as far as my
> experience goes, it's faster because it doesn't work.)

It's important to have both types of products (i. e., do-it-right
products and get-it-done products). The evil, unforgivable sin is when
one "side" tries to take over the market, because if that happens either
everyone becomes too busy doing it right to get it done, or everyone
becomes too busy getting it done to do it right. Either way, the market
falls over.

That's painting things a little too black and white, I suppose, ...

--
Joel Rees <joel(at)alpsgiken(dot)gr(dot)jp>