Thread: mysql issues
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
Since I'm just startingto understand the wisdom of my switch from
MySQL to PostgreSQL I offer these notes for the archives in the hopes
that they'll show up in later searches:
George Johnson writes:
> Mysql has a richer set of date and time functions and easy-to-use
> conversions. PostgreSQL should have that at some point.
I just wanna get this into the archives so that the next person who
goes searching finds it. To get my code up and running fast (within
the hour) my first pass actually used calls to NOW() in the values of
the update and inserts, making the conversion from MySQL to PostgreSQL
actually have less smarts in the database:
Some triggers for created/last updated times:
CREATE FUNCTION entered_stamp () RETURNS OPAQUE AS
' BEGIN
IF NEW.entered ISNULL THEN
NEW.entered := ''now'';
END IF;
IF NEW.updated ISNULL THEN
NEW.updated := ''now'';
END IF;
RETURN NEW;
END;
'LANGUAGE 'plpgsql';
CREATE FUNCTION updated_stamp () RETURNS OPAQUE AS
' BEGIN
IF NEW.updated ISNULL THEN
NEW.updated := ''now'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER abc_entered BEFORE INSERT ON abc
FOR EACH ROW EXECUTE PROCEDURE entered_stamp();
CREATE TRIGGER abc_updated BEFORE UPDATE ON abc
FOR EACH ROW EXECUTE PROCEDURE updated_stamp();
> I don't know if postgresql has set and enumeration data types ... or
> if Array covers that.
Enum should generally be done with an extra table and a join.
Dan