Re: atomicity

Good Day All,

On Wed, 2002-02-27 at 01:28, Jean-Michel POURE wrote:
> > Is it true that the current version of PostgreSQL does always respect
> > atomicity of transactions and MySQL does not?

The latest version of MySQL provides the option of supporting
ACID-compliant transactions and row-level locking

> MySQL only supports transactions on INNODB tables. Therefore if your
> transactions includes other tables, we doubt atomicity works. Note this might
> change with MySQL 4.1.

MySQL supports transactions with InnoDB tables and DBD tables. Users
are provided with the choice of two different transactional table
types and two non-transactional table types - one file-based (MyISAM),
one memory-based (HEAP). The MySQL manual details this information.

Users can choose the table type that best suits their needs.

> PostgreSQL offers the ability to build "intelligent" database with
> server-side programming (PLperl, PLpgSQL, ..), triggers, rules, views,
> operators... It is a real development tool.

I agree that PostgreSQL is a good development tool that has features
that MySQL does not yet possess (or that are more convenient to use in
PostgreSQL than MySQL).

I also believe that other databases, including MySQL are 'real'
development tools.

> In a complex environment, PostgreSQL can be optimized at ***software level***
> (i.e. the way you build the database and program is server-side). MySQL can
> only be optimized at ***OS level*** or ***Harware level***.
>
> OS and Hardware level optimization can speed up an application by a factor of
> 2. Software optimization has no limit other than your imagination.

This is an incorrect characterization of MySQL. Users have great
control over the behavior of queries and the behavior of databases.
These include options like delayed inserts, memory-stored indexes,
packed indexes for faster index reading, hash-based tables stored in
memory, read-efficient read-only databases, database replication, User
Defined Functions, etc.

> On the converse, MySQL is just an "dumb" database only running simple SQL

MySQL has a large, well-tested feature set that includes fewer
traditional RDBMS features that PostgreSQL. PostgreSQL and MySQL come
from very different traditions. PostgreSQL is the heir to quite
phenonmenal amounts of academic research work that focused on
developing a complete set of RDBMS features. MySQL evolved from a set
of working development tools - it was developed with attention to
speed, stability and performance at the cost of certain features.

As PostgreSQL works to become a more practical development tool that
requires less maintenance and is more robust, MySQL works towards
having a larger set of features that are as robust as the current set.

> queries. Furthermore, it has very poor ODBC support and will only work in an

To be specific, MySQL supports ODBC levels 0-3.51 - which is more than
PostgreSQL supported last time that I checked.

> Apache environment.

This is incorrect. In addition to the source, we distribute binaries
for the following platforms:

- Linux
- Windows 95/98/NT/2000/XP
- Solaris
- FreeBSD
- MacOS X
- HP-UX
- AIX
- SGI Irix
- Dec OSF

> If you are new to PostgreSQL, maybe the best thing to do is play with it
> using pgAdmin2 GUI, which can be found at http://pgadmin.postgresql.org.
I would strongly consider reading Bruce Momjian's excellent book
(See http://www.ca.postgresql.org/docs/awbook.html)

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Zak Greant <zak(at)mysql(dot)com>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate
/_/ /_/\_, /___/\___\_\___/ Calgary, Canada
<___/ www.mysql.com 403.244.7213