|
Hi,
My mailer crashed, but before I had to delete all
the messages I saw a thread regarding making it easier for mysql users to port
over to postgresql. Well guys, you've gone and made a hole for yourself,
ESPECIALLY by adding the limitless row lengths in 7.1. With the
performance gains, reliability, history and now the ability to not have to deal
with the 8k row limit length, you're going to have a scarey number of "how do I
do this?" from converted mysql users (myself being new as of last week).
I'll hold off telling my 10,000 friends about how great postgresql is until you
decide how you're going to deal with your success (kidding). :-0
I've been able to convert my interface applications
from mysql sql to postgresql specifics pretty easily. There are some
things that required some tricky workarounds, but come to think of it, the
majority of sql statements had to be carefully examined and most NOT
reworked. But then again, I tried to stick to SQL standard stuff rather
than mysql specifics. I am willing to bet many mysql users aren't in the
same boat I am -- and possibly their SQL savvy isn't at a "been there done that
a few times" level.
Some things that created problems:
"drop table if exists tablename"
[oh boy, is this a great one. imagine not
even having to worry if the table is there or not]
"alter table tablename drop column
columnname"
"replace into tablename (col1,col2,col3) values
(val1, val2, val3)"
[ replace is the combo of a delete and insert,
sorta like a cover yur butt update
statement ]
"show tables" -- a great way of just getting a
listing of the tables in ur database.
Some things that were problems, but should not be
changed:
postgresql requires stricter syntax with GROUP BY,
and combining grouping
functions and non-grouping columns. That's
just mysql letting crappy SQL slide, which it should not. Mysql has some
JOINS and other add-ons that are used because of its lack of subselects. I
AM WILLING TO BET everyone who is using the more exotic joins and functions
started out beating their heads because mysql had no subselects, which would
have made things oh-so-much easier.
You end up creating a crazy amount of temporary
tables to get around the lack of subselects, doing crazy optimizations to deal
with having so many temporary tables, etc.
Some things that were problems, but should
change:
Mysql has a richer set of date and time functions
and easy-to-use conversions. PostgreSQL should have that at some
point.
I don't know if postgresql has set and enumeration
data types ... or if Array covers that. But as far as storing data,
nothing beats an automatic conversion of 2 million City names into a set of 150
city names and only the needed bits to store the city name per row, with a cross
reference to the enumeration or set value. I'm sure there are better
examples of that.
Other than these things, the only big time
differences are some different function definitions (which can be easily I'd
assume implemented) and just who's gotten farther along in implementing SQL92 or
whichever one of those standards everybody strives toward.
The big problem is going to come from users of
MySQL who are not experienced SQL DBA's and who relied heavily on the snappy
features of MySQL (a la web administration stuff).
I hope this helps a little bit on your decision
about whether to expend energy toward making MySQL Friends &
Family.
George Johnson
|
