Need concrete "Why Postgres not MySQL" bullet list

In article <200308200839.28230.josh@agliodbs.com>,
Josh Berkus <josh@agliodbs.com> writes:

PROCEDURES: Postgres supports stored procedures (as functions) allowing
programming in the database for the many tasks which are far more efficient,
consistent, and secure done there. Procedures may be written in any of nine
different languages, currently, with two more in development. MySQL does not
support procedures at all.

From the MySQL manual:
* With UDF (user-defined functions) one can extend MySQL Server with
both normal SQL functions and aggregates, but this is not yet as
easy or as flexible as in PostgreSQL.

TRANSACTIONS: blah, blah, blah. MySQL has just begun offering transactions
this year, and their solution is largely untested, slow...

InnoDB transactions in MySQL are pretty robust and fast. However,
this affects only INSERT/UPDATE/DELETE - not CREATE TABLE etc.

and suffers from
complications with the many different "table types".

True. Transactions break unless all tables used are InnoDB.

On Wed, 2003-08-20 at 13:20, Harald Fuchs wrote:

and suffers from
complications with the many different "table types".

True. Transactions break unless all tables used are InnoDB.

Actually the problem is worse than transactions breaking, the problem is
that they don't break and just silently fail you in some circumstances.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Wednesday 20 August 2003 17:39, Josh Berkus wrote:

Folks,

I need someone to prepare a standard response for me to send out to
inquiries on this topic. I get them a lot.

What I'd like is a factual, non-perjorative list of the things which
PostgreSQL and the PostgreSQL project have that MySQL does not,

Oh dear, this excludes my usual one-item "things to think about
when considering what database to use":

* PROPER USAGE OF NULL

mysql> select * from ai_test where id is null;
+----+-------+
| id | txt |
+----+-------+
| 1 | hello |
+----+-------+
1 row in set (0.00 sec)

;-). I digress. Off the top of my head, in no particular order:

* VIEWS
VIEWs are an important element of efficient professional database design,
pushing repetitive, often duplicated queries into the backend,
increasing the flexibility of an application and reducing the risk of errors.
They also enable the encapsulation of table structure, which may change
with application development, behind a consistent interface.

- MySQL has no support for views, but promises them in a future version,
for which no target production date exists:
http://www.mysql.com/doc/en/ANSI_diff_Views.html

* TRIGGERS
Triggers enable predefined actions to be carried out before
or after inserts, deletes and updates are performed on tables.
This enables automation of many tasks in the database, for
example logging specific events such as changes to a table
holding accounting data, or checking and modifying a column
prior to insertion or update.

- MySQL does not support triggers. Implementation is planned:
http://www.mysql.com/doc/en/ANSI_diff_Triggers.html

* SEQUENCES
Often it is useful or necessary to generate a unique number
for a row of data, for example for providing a primary key
ID. Many databases including PostgreSQL provide sequences -
functions for automatically generating unique numbers
in predefined increments.

- MySQL provides only a very primitive sequence generator, AUTO_INCREMENT,
whose behaviour is difficult to modify. It is not possible to explicitly
set the current value of a specific AUTO_INCREMENT sequence or an
incrementation other than 1.
- AUTO_INCREMENT is implemented as SELECT MAX(col) FROM table
(see: http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html )
which makes concurrent transactions prone to deadlocks
- The use of the NULL value to provoke insertion of the next sequence value
means it is impossible to have a sequence on a NULLable column in MySQL.
- there can be only one AUTO_INCREMENT column per table

* RULES, TYPES, DOMAINS

* PROCEDURES
in a variety of languages...

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.
- Some parts of a table definition, although syntactically corrent,
maybe ignored without warning. This applies particularly to syntax
such as
CREATE TABLE my_table (
id INT NOT NULL PRIMARY KEY,
fkey_id INT NOT NULL REFERENCES other_table(id)
) TYPE=InnoDB

See:
http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html

- MySQL may, at its discretion, silently alter column specifications. See:
http://www.mysql.com/doc/en/Silent_column_changes.html
- MySQL only permits constants as default values not functions or expressions.
See: http://www.mysql.com/doc/en/CREATE_TABLE.html
This makes it impossible to provide default values like this:
CREATE TABLE deftest (
id INT,
date_at_creation TEXT DEFAULT 'The date is '|| to_char('dd-mm-yyyy',
now())
);

* GENUINE REFERENTIAL INTEGRITY
An essential part of the relational model is the ability to
create foreign keys, which define relationships between tables.
Naturally only values which exist in the referenced table can
be used in a foreign key column.
PostgreSQL has provided integrated foreign key support
since (find out when).

- In MySQL foreign keys are an "optional extra" and are only
available when the InnoDB table type is specified.
- Not all MySQL server installations are configured to provide
InnoDB support, e.g. ISP-run servers.
- despite the implementation of foreign keys with InnoDB tables,
MySQL does not provide robust referential integrity.
Under certain circumstances MySQL will insert into or permit the
presence of values in a foreign key which are not present in the table
referred to. For example, if a foreign key column can contain null
values, and the column's definition is changed to NOT NULL,
MySQL will insert 0 (zero) into the previously NULL columns,
even if the column referred to in a different table does not contain
0.
- No ON UPDATE ... support

* RIGOROUS FAILURE TESTING
When developing a database, and in day-to-day operation, it
is essential that erroneous statements fail with a specific
warning to prevent violations of data integrity.

- in many cases where a statement would be expected to
fail, MySQL will silently insert default values.
For example, when (mistakenly) inserting an integer
1 into an ENUM field, MySQL will insert a string
containing '0' rather than raise an error.

* DICTIONARY BASED FULL-TEXT INDEXING
Although SQL provides several methods of searching for patterns
in text fields (LIKE, regexes), these do not provide sufficient functionality
for more complex searches, e.g. as used by search engines.

- MySQL provides a builtin index type FULLTEXT, which allows keyword
searches. In contrast to PostgreSQL's tsearch2 functionality, this
does not provide for advanced natural language based searches using
features such as dictionary lookups and stemming.

* LICENSING
- MySQL is available as "open source", but depending on useage
not always with an open source license. MySQL's licencing
conditions are subject to change.
- PostgreSQL is available under a BSD-style licence to all
users whether commercial or private.

I can take the above and any further additions and flesh it out
into a more readable list (if noone else wants to). I have a
project at the moment which will probably be marketed as
a MySQL-compatible application ("it's what the users have"),
though I am prototyping it in PostgreSQL, and I'm collecting
a lot of interesting insights...

Ian Barwick
barwick@gmx.net

Ian,

do you use any specific tools to prototype applications designed for
PostgreSQL?

Anastasios

Oh dear, this excludes my usual one-item "things to think about
when considering what database to use":

* PROPER USAGE OF NULL

mysql> select * from ai_test where id is null;
+----+-------+
| id | txt |
+----+-------+
| 1 | hello |
+----+-------+
1 row in set (0.00 sec)

;-). I digress. Off the top of my head, in no particular order:

You're not trying hard enough:

mysql> create table test3 (a date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 values (-1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test3 values ('1996-02-31');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3 values ('1996-67-31');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+------------+
| a |
+------------+
| 0000-00-00 |
| 1996-02-31 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

I find that even funnier...

Chris

On 21 Aug 2003 at 0:22, Ian Barwick wrote:

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.

Just wondering, what other databases has transactable DDLs? oracle seems to
have autonomous transactions which is arthogonal.

If we are going to compare it, we are going to need it against other databases
as well.

Personally I find transactable DDL's a big plus of postgresql. It allows real
funcky application design at times..:-)

Bye
Shridhar

--
drug, n: A substance that, injected into a rat, produces a scientific paper.

Shridhar Daithankar wrote:

On 21 Aug 2003 at 0:22, Ian Barwick wrote:

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.

Just wondering, what other databases has transactable DDLs? oracle seems to
have autonomous transactions which is arthogonal.

M$ SQL2000 has (and previous versions had too, I believe)

Regards,
Andreas

On Thu, 21 Aug 2003 14:45:03 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:

Just wondering, what other databases has transactable DDLs?

Firebird.

Servus
Manfred

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Shridhar Daithankar wrote:

On 21 Aug 2003 at 0:22, Ian Barwick wrote:

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.

Just wondering, what other databases has transactable DDLs? oracle seems to
have autonomous transactions which is arthogonal.

M$ SQL2000 has (and previous versions had too, I believe)

In Oracle DDL (including truncate!) was special and wasn't in a transaction.
I always just assumed that was just the way it had to be.

--
greg

On 21 Aug 2003 at 9:21, Greg Stark wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Shridhar Daithankar wrote:

On 21 Aug 2003 at 0:22, Ian Barwick wrote:

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.

Just wondering, what other databases has transactable DDLs? oracle seems to
have autonomous transactions which is arthogonal.

M$ SQL2000 has (and previous versions had too, I believe)

Any pointers to documentation?

Bye
Shridhar

--
divorce, n: A change of wife.

On 21 Aug 2003 at 9:21, Greg Stark wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Shridhar Daithankar wrote:

Just wondering, what other databases has transactable DDLs? oracle seems to
have autonomous transactions which is arthogonal.

M$ SQL2000 has (and previous versions had too, I believe)

In Oracle DDL (including truncate!) was special and wasn't in a transaction.
I always just assumed that was just the way it had to be.

That is the autonomous transaction. Right now I am looking at
interbase/firebird documentation. It looks like it has automous DDL transaction
as well..

Bye
Shridhar

--
Collaboration, n.: A literary partnership based on the false assumption that
the other fellow can spell.

Shridhar Daithankar wrote:

On 21 Aug 2003 at 9:21, Greg Stark wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

Shridhar Daithankar wrote:

On 21 Aug 2003 at 0:22, Ian Barwick wrote:

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.

Just wondering, what other databases has transactable DDLs? oracle seems to
have autonomous transactions which is arthogonal.

M$ SQL2000 has (and previous versions had too, I believe)

Any pointers to documentation?

No, just looked at the doc and didn't find anything, it's assumed
implicitely at some locations though.
DDL statement will create a Schema Modification lock (Sch-M), i.e. DDL
statements pending in a transaction will lock a table exclusively. After
commit or rollback, schema modification is committed or undone as
expected, I verified this.

Regards,
Andreas

On 20 Aug 2003, Harald Fuchs wrote:

In article <200308200839.28230.josh@agliodbs.com>,
Josh Berkus <josh@agliodbs.com> writes:

PROCEDURES: Postgres supports stored procedures (as functions) allowing
programming in the database for the many tasks which are far more efficient,
consistent, and secure done there. Procedures may be written in any of nine
different languages, currently, with two more in development. MySQL does not
support procedures at all.

From the MySQL manual:

* With UDF (user-defined functions) one can extend MySQL Server with
both normal SQL functions and aggregates, but this is not yet as
easy or as flexible as in PostgreSQL.

TRANSACTIONS: blah, blah, blah. MySQL has just begun offering transactions
this year, and their solution is largely untested, slow...

InnoDB transactions in MySQL are pretty robust and fast. However,
this affects only INSERT/UPDATE/DELETE - not CREATE TABLE etc.

Well, I wouldn't say that they're that robust. Try this:

create innodb table
begin transaction;
insert 1,000,000 rows;
rollback;

wait for years for the rollback to finish.

From the MySQL manual:

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#Innodb_tuning

QUOTE

8: # Beware of big rollbacks of mass inserts: InnoDB uses the insert
buffer to save disk I/O in inserts, but in a corresponding rollback no
such mechanism is used. A disk-bound rollback can take 30 times the time
of the corresponding insert. Killing the database process will not help
because the rollback will start again at the database startup. The only
way to get rid of a runaway rollback is to increase the buffer pool so
that the rollback becomes CPU-bound and runs fast, or delete the whole
InnoDB database.

ENDQUOTE

It's obvious that innodb transactions aren't meant to handle large data
sets and rollback well, and the compromise here, like in all of MySQL,
tends towards "hoping for the best" and benchmarking that particular
aspect.

and suffers from
complications with the many different "table types".

True. Transactions break unless all tables used are InnoDB.

And, more importantly, they break silently, or you find out too late (i.e.
oh by the way, some of those rows couldn't be rolled back...)

Ian's list is excellent.

I would like to add to the point about pl languages.

Use the right tool for the job:
Procedure may be written in tcl, plpgsql, perl,
python, R (statistics) so you can use the right tool
for the job at hand as well as leverage your
existing knowledge of a given scripting language.

It would help to solidify our specific examples
of failures if the version and platform of mysql
is included. Clarifications with postgresql
functionality working correctly may be appropriate
in some cases (also include version & platform).

At oscon, the mysql guys were fighting "old" rumors.
As they glob on functionality to try to catch up
to 1990 technology, they will assert that "we have
transactions now!", etc.

By including the version and platform *and* sticking
only to the facts, we can forstall a few flames.

elein

On Thu, 21 Aug 2003 15:05:52 +0200, I wrote:

Just wondering, what other databases has transactable DDLs?

Firebird.

Stop! I withdraw that statement. I must have mis-read some feature
list :-(

Tests with InterBase 6 showed that you can change metadata within a
transaction, but when you ROLLBACK, metadata changes persist.

Servus
Manfred

On Thursday 21 August 2003 21:30, Manfred Koizar wrote:

On Thu, 21 Aug 2003 15:05:52 +0200, I wrote:

Just wondering, what other databases has transactable DDLs?

Firebird.

Stop! I withdraw that statement. I must have mis-read some feature
list :-(

Tests with InterBase 6 showed that you can change metadata within a
transaction, but when you ROLLBACK, metadata changes persist.

Aha. I was just about to ask about that, because I was experimenting
with a 1.5 beta version without success. Doesn't seem to work there
(though as I have little experience and virtually no docs I might
be missing something).

Ian Barwick
barwick@gmx.net

On Thursday 21 August 2003 11:15, Shridhar Daithankar wrote:

On 21 Aug 2003 at 0:22, Ian Barwick wrote:

* DDL
- Data definition language (table creation statements etc.) in MySQL
are not transaction based and cannot be rolled back.

Just wondering, what other databases has transactable DDLs? oracle seems to
have autonomous transactions which is arthogonal.

DB2 8.1 seems to support transaction-capable DDL. At least, a rollback
following a CREATE TABLE causes the table to disappear. Haven't gone
into it in any depth.

Ian Barwick
barwick@gmx.net

On Thursday 21 August 2003 00:44, Anastasios Hatzis wrote:

Ian,

do you use any specific tools to prototype applications designed for
PostgreSQL?

'Fraid not. Apart from a pen and a piece of paper ;-).

I find Postgres very "ergonomical" to develop with, particularly
in the earlier stages of an application: transaction-capable
DDL makes design changes easy to test without risk of
messing up the database; strong referential integrity
helps develop robust applications (having the app fail
on the slightest hint of bad data makes bugs in the app
much easier to find) and psql with its tab-completion
and readline support has gone a long way to saving
me from carpal tunnel syndrome.

Ian Barwick
barwick@gmx.net

On Thursday 21 August 2003 20:16, elein wrote:

Ian's list is excellent.

I would like to add to the point about pl languages.

Use the right tool for the job:
Procedure may be written in tcl, plpgsql, perl,
python, R (statistics) so you can use the right tool
for the job at hand as well as leverage your
existing knowledge of a given scripting language.

Good point. I will try and get a working draft
together by the start of next week.

It would help to solidify our specific examples
of failures if the version and platform of mysql
is included.

Yup. I have compiled a long list of behavioural
oddities for my own reference; I've researched
many of these in the MySQL documentation (to make
sure I'm not seeing things), and an astounding number
are actually documented and show no signs of
going away, even in the 4.x series. I will install a 4.1
alpha (?) version just to be on the safe side, as this is
being touted as "the will-MySQL-make-it-into-the
enterprise-league version" (i.e. the version with sub-selects).

Clarifications with postgresql
functionality working correctly may be appropriate
in some cases (also include version & platform).

Here we need to emphasize the progress made in 7.3 / 7.4,
I get the impression a lot of people still see Postgres as
the "database without the DROP COLUMN functionality"...

Ian Barwick
barwick@gmx.net