PostgreSQL 9.0: Includes the new MySQL Emulation Layer

PostgreSQL 9.0.0, released today, contains the MySQL Emulation Layer.

To enable this feature, set the mysql_compatible option GUC to on.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

postgres=# SELECT * FROM pg_settings WHERE name = 'mysql_compatible';
-[ RECORD 1 ]----------------------------------------------------------------
name       | mysql_compatible
setting    | on
unit       |
category   | Version and Platform Compatibility / Other Platforms and Clients
short_desc | Enable MySQL Emulation Layer
extra_desc |
context    | backend
vartype    | bool
source     | default
min_val    |
max_val    |

Enabling this option changes the following characteristics (in the entire PostgreSQL cluster):

• The boolean data type now accepts positive integers, in addition to true and false.
• The default TCP port is 3306 in order to make porting applications easier.
• Non-aggregate columns in the SELECT list need no longer appear in the GROUP BY clause.
• CAST may or may not work.
• ENUMs can no longer contain numbers, only characters are allowed
• CHAR, VARCHAR and TEXT are case insensitive now. Case sensitive matching is only done with the new BINARY keyword.
• VARCHAR supports texts up to a length of 255 bytes. Bear in mind that because Unicode/UTF8 characters may need more than one byte for any given character, you must take this into account when calculating what VARCHAR can actually store.
• Double dash comments must start with a space after the dashes.
• Division by zero now raises a warning instead of throwing an error.
• The || operator no longer concats strings but is Logical OR.
• For convenience, you may use dates like 2010-02-31.
• Replication - including bugs - is integrated.
• The parser now understands DESC and SHOW CREATE syntax.
• Porters did not finish the MySQL authentication system in the time allotted. DBAs will need to continue to use pg_hba.conf for the time being.

The following new features are activated by enabling mysql_compatible = on:

• The new black hole engine is an alias for /dev/null, and of course, is not transactional.
• Table names depend on the underlaying filesystem: on Windows the table name is not case sentitive, on most Unix systems data, Data and DATA are different tables.
• Several release-critical bugs were introduced, to make debugging the application more fun.
• For convenience, PostgreSQL is providing a root user.

Some well-known PostgreSQL-features are incompatible with this new extension:

• ACID: Instead, MySQL ACID will be used. Committed data may or may not be written after a crash, depending on - among other details - the current phase of the moon, the color of electrons and the weather conditions in Oz.
• Transactional DDL and DCL.
• User defined data types.
• For convenience, EXPLAIN output is printed on a single line.
• Schemas are no longer supported.

Certainly, support is available for all users of the new emulation layer. The license of this extension is BSD-style, allowing users to use and integrate PostgreSQL with MySQL Emulation Layer into your application. Dual-licensing is no longer a source of trouble.