Thursday, April 1. 2010
PostgreSQL 9.0: Includes the new MySQL Emulation Layer
Andreas 'ads' Scherbaum
PostgreSQL 9.0.0, released today, contains the MySQL Emulation Layer.
To enable this feature, set the mysql_compatible option GUC to "on".
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 takes positive integers in addition to "true" and "false".
- The default TCP port is 3306 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 since 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 be start with a space after the dashes.
- Division by zero now raises a warning instead of an error.
- The || no longer concats strings but is Exclusive 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.
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 non-transactional.
- Table names depend on the underlaying filesystem: on Windows the table name is not case sentitive, on Unix "data", "Data" and "DATA" are different tables.
- Several release-critical bugs were introduced, to make debugging the application more fun.
- For convenience, PostgreSQL has provided 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 things, 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 pure BSD-style, allowing users to use and integrate "PostgreSQL with MySQL Emulation Layer" into your application. Dual-licensing is no longer source of trouble.
Awesome! Thank Stallman! Now my applications can work properly.
I demand you remove this. Those behaviours are our sole property and we do not authorise their use. Cease and desist.
Oh! I was close to believe last night :) Translated it to Russian in my blog
There are so many legitimate concerns about MySQL, why include phony ones as well?
"Non-aggregate columns in the SELECT list need no longer appear in the GROUP BY clause."
That is a great MySQL feature (if you know what you're doing). That blog post you linked it kind of disingenious - of course MySQL has to "guess", but only if you don't provide ordering. PostgreSQL has DISTINCT ON, which works similarly, but doesn't solve all use cases as well as MySQL - and does the exact same thing as MySQL if you don't provide explicit ordering, namely return a largely arbitrary result! Pot, meet kettle.
In fact, PostgreSQL DISTINCT ON is at its core IDENTICAL with MySQLs GROUP BY syntax extension.
Stephan, just check the date ...
And to pick up your point: of course, if would be a great feature, if it would allow predictable results. It does not.
When I first read the item, I thought it did something like implicitly adding the columns to the group by, so "select a, b, sum(c) from foo" would be interpreted as "select a, b, sum(c) from foo group by a, b". This is something i often have wished (but I aren't sure it wouldn't have unintentioned side effects). But reading the link, it was of course clear that what they do (i.e. treating it as "select first(a), first(b), sum(c) from foo") is not the right way.
> That is a great MySQL feature
No, it's not. It's a sign that either your design or your thought process is severely broken. Just yesterday I had to clean up the mess from a program bug where another developer decided this sort of query (using a MySQL database) was a good idea.
Of course, MySQL has been inbreeding this sort of broken thinking since its inception.
When will it be possible to have :
CTRL + C leave the current query and also leave the command line client (in fact, I think we can even go further and reboot the computer, that would be more fun) ?
Have the CLI to remember the \e but not the queries you edited ?
Have only one error message for all foreign key types of errors ?
support different encodings between tables so we cannot foreign key with varchars ?
Do not allow multiple autoincrement values on one table ?
Forget about inheritance ?
Forget about generate_series ?
I am sure without all the unecessary features, my computer will , maybe not, be faster. Thank in advance !
Oh ... I forgot ... the support of «FLUSH PRIVILEGES» would definitely make Pg to look like a genuine MySQL database.
Events and stored procedures will not be backuped by default anymore? Nobody needs that stuff anyway, way too difficult.
;)
Is there an option for transparently truncating data that doesn't fit into a field? I hate all those lame errors I keep getting in Postgres.
Quicksearch
Blog Administration
PostgreSQL Buch
Calendar
|
September '10 | |||||
| Mon | Tue | Wed | Thu | Fri | Sat | Sun |
| 1 | 2 | 3 | 4 | 5 | ||
| 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 27 | 28 | 29 | 30 | |||
UBERWACH!
Bookmarks
Upcoming Events
Categories
Last Search
Statistics
Top Referers
planete.postgresql.fr (3033)
www.postgresql.org (2877)
www.google.de (2847)
google.com (1582)
planet.postgresql.org (1509)
ak-zensur.de (1358)
ads.wars-nicht.de (1168)
www.planetpostgresql.org (964)
image.youdao.com (377)
Recent Entries
- PostgreSQL @ FrOSCamp 2010 in Zurich
- Tuesday, August 31 2010
- Wöchentlicher PostgreSQL Newsletter - 29. August 2010
- Monday, August 30 2010
- Bericht: PostgreSQL auf der FrOSCon 2010
- Friday, August 27 2010
Archives
Syndicate This Blog
Powered by
PostgreSQL 9.1 sürümü ile birlikte NoSQL'e geçiyor: http://pgsnake.blogspot.com/2010/04/postgres-91-release-theme.html 9.0 sürümünde MySQL Emulation Layer geliyor: http://andreas.scherbaum.la/blog/archives/657-PostgreSQL-9.0-Includes-the-ne Comment (1)
Tracked: Apr 01, 10:26
This blog post of mine about the new MySQL Emulation Layer in PostgreSQL 9.0 was (more of less obvious) an april joke. Most readers have guessed right. On the other hand the idea, that this joke is based, is very real: during the last conferences and exhi Comment (1)
Tracked: Apr 02, 18:04