[OT] MySQL is bad, but THIS bad?
Sorry to interrupt, but I have had the "opportinuty" to have to work with
MySQL. This nice little gem is packed away in the reference for
mysql_use_result().
"On the other hand, you shouldn't use mysql_use_result() if you are doing
a lot of processing for each row on the client side, or if the output is
sent to a screen on which the user may type a ^S (stop scroll). This ties
up the server and prevent other threads from updating any tables from
which the data is being fetched."
How do busy web sites work like this?
What is the best way to go about creating a "plug and play," PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.
Mark Woodward wrote:
Sorry to interrupt, but I have had the "opportinuty" to have to work with
MySQL. This nice little gem is packed away in the reference for
mysql_use_result()."On the other hand, you shouldn't use mysql_use_result() if you are doing
a lot of processing for each row on the client side, or if the output is
sent to a screen on which the user may type a ^S (stop scroll). This ties
up the server and prevent other threads from updating any tables from
which the data is being fetched."How do busy web sites work like this?
What is the best way to go about creating a "plug and play," PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.
That reminds me of the Twilight Zone episode where the guy had a
stopwatch that stopped time:
http://en.wikipedia.org/wiki/A_Kind_of_a_Stopwatch_(The_Twilight_Zone)
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
What is the best way to go about creating a "plug and play,"
PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.
http://pgfoundry.org/projects/mysqlcompat/
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
* Mark Woodward:
"On the other hand, you shouldn't use mysql_use_result() if you are doing
a lot of processing for each row on the client side, or if the output is
sent to a screen on which the user may type a ^S (stop scroll). This ties
up the server and prevent other threads from updating any tables from
which the data is being fetched."How do busy web sites work like this?
Any system based on locking exhibits this problem. Even with MVCC,
you can run into it if you've got multiple writers. As a rule of
thumb, I never perform network I/O within transactions which update
the database (or "read the database", for systems without MVCC).
On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
What is the best way to go about creating a "plug and play,"
PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.
Even better would be coming up with a compatability mode, a la what
EnterpriseDB has done for Oracle.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
All,
What is the best way to go about creating a "plug and play,"
PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.Even better would be coming up with a compatability mode, a la what
EnterpriseDB has done for Oracle.
Um, no offense, but why? Do we really *want* to seek out thousands of users
who won't pay anyone for support, don't care about data integrity, and regard
the database as an enhanced flat file? Who adore non-standard syntax like
`db_object_name` and REPLACE INTO? Who want to just get free downloads and
not contribute to a project?
Personally, I'd go after MSSQL before I bothered with MySQL. Sure, let's
make *migration* easier for those who wake up and smell the BS, but migration
can (and probably should) be one-way.
If we're talking about other OSS projects, then I think it makes more sense
for us to help those projects add PostgreSQL support. And do promote the
projects that already *do* support us.
--
Josh Berkus
Aglio Database Solutions
San Francisco
On May 18, 2006, at 12:24 PM, Jim C. Nasby wrote:
Even better would be coming up with a compatability mode, a la what
EnterpriseDB has done for Oracle.
Right, you'll definitely need to hack the C source code to force
PostgreSQL to accept invalid dates ;)
http://sql-info.de/mysql/gotchas.html#1_14
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
Jim C. Nasby wrote:
On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
What is the best way to go about creating a "plug and play,"
PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.Even better would be coming up with a compatability mode, a la what
EnterpriseDB has done for Oracle.
Good Lord NO. I don't want a bunch of hacked up code *because* MySQL
does it this way, running on top of PostgreSQL.
I want to run PostgreSQL. If you want to run MySQL, run MySQL. If you
want to run Oracle, run Oracle.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Josh Berkus wrote:
Personally, I'd go after MSSQL before I bothered with MySQL. Sure, let's
make *migration* easier for those who wake up and smell the BS, but migration
can (and probably should) be one-way.
Yeah. Let's write a Transact-SQL PL handler.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, 18 May 2006, Joshua D. Drake wrote:
Jim C. Nasby wrote:
On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
What is the best way to go about creating a "plug and play," PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.Even better would be coming up with a compatability mode, a la what
EnterpriseDB has done for Oracle.Good Lord NO. I don't want a bunch of hacked up code *because* MySQL does it
this way, running on top of PostgreSQL.
'k, so you want "pure PostgreSQL" ... but, shouldn't it be possible, with
all of our CREATE FUNCTION / RULES / etc features to create a 'translation
layer' that could be loaded, like anything else in contrib?
Hell, even if it gave an initial "in" for MySQL software developers to get
their code running on PostgreSQL, and then when they come out that "do
this is slower under PostgreSQL", they could optimize their code
appropriately?
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
'k, so you want "pure PostgreSQL" ... but, shouldn't it be possible,
with all of our CREATE FUNCTION / RULES / etc features to create a
'translation layer' that could be loaded, like anything else in contrib?
Sure but that isn't what was suggested :)
Hell, even if it gave an initial "in" for MySQL software developers to
get their code running on PostgreSQL, and then when they come out that
"do this is slower under PostgreSQL", they could optimize their code
appropriately?
I understand the idea but I personally don't like it. I am not really
interested in cross-database compatible code. 9 times out of 10 it is
hacky, slow and lacks a solid supportable model because you are always
taking into account *the other* databases the application supports.
And to be frank, I don't think we should waste our time on MySQL. It
isn't a competitor, it just thinks it is. We should focus on our real
competition which is DB2, MSSQL, and Oracle.
If we want to create contrib modules that have types etc... that help
port from Oracle to PostgreSQL or DB2 to PostgreSQL I am all for it.
The first thing that comes to mind is a set of domains that implement
Oracle types (names) as PostgreSQL types.
Here's a start ;)
postgres=# create domain varchar2 AS text;
CREATE DOMAIN
postgres=# create domain clob as text;
CREATE DOMAIN
postgres=# create domain blob as bytea;
CREATE DOMAIN
postgres=# create domain number as integer;
CREATE DOMAIN
postgres=#
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
* Joshua D. Drake (jd@commandprompt.com) wrote:
And to be frank, I don't think we should waste our time on MySQL. It
isn't a competitor, it just thinks it is. We should focus on our real
competition which is DB2, MSSQL, and Oracle.
One thing which would be kind of nice would be to have a mapping for the
common commands. A friend of mine did this with a bit of perl so he
could have a single SQL script that could run against both Postgres and
Oracle. His thought was actually just an aliasing ability in psql so
that you could, for example, alias 'desc' to '\d' and '@' to '\i'. This
would have also helped me out with some projects for school- the main
issues I ran into were that Oracle required a cascade-drop to include
the keyword 'constraints' (which Postgres correctly forbids) and that
Oracle uses 'MINUS' instead of 'EXCEPT' (where 'EXCEPT' is the SQL
standard, which Oracle doesn't accept for some reason. :/).
Just some thoughts,
Stephen
On Thu, 18 May 2006, Joshua D. Drake wrote:
I understand the idea but I personally don't like it. I am not really
interested in cross-database compatible code. 9 times out of 10 it is
hacky, slow and lacks a solid supportable model because you are always
taking into account *the other* databases the application supports.
I don't disagree about the 'hacky, slow and lacks ...' ... but, there are
alot of MySQL based apps out there that are just *way too big* to convert
wholesale to PostgreSQL that, in all honesty, are better suited to
PostgreSQL ...
To give someone a running chance at migrating it to PostgreSQL, a 'MySQL
compatibility module' would allow them to just plug the existing DB in,
and then work at improving sections of the code over time ...
Hell, if done well, the module should be able to dump appropriately
'clean' PgSQL schemas ... as in your example elow about the domains ...
something like:
postgres=# create domain varchar2 AS text;
CREATE DOMAIN
postgres=# create table test ( mytext varchar2 );
should dump out to:
create tabel test ( mytext text );
So, developer would be able to load the MySQL schema and run the
application, but would also be able to improve the code in the application
and dump out a PgSQL clean schema so that the next person, in theory,
wouldn't even need the MySQL module ...
the point isn't whether or not MySQL is a competitor ... the point is that
there are *alot* of MySQL based applications out there that are a major
PITA to convert (or get converted) all at once ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc,
To give someone a running chance at migrating it to PostgreSQL, a 'MySQL
compatibility module' would allow them to just plug the existing DB in,
and then work at improving sections of the code over time ...
Have you even looked at KL's mysqlcompat?
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
On Thu, May 18, 2006 at 10:35:48AM -0700, Joshua D. Drake wrote:
I understand the idea but I personally don't like it. I am not
really interested in cross-database compatible code. 9 times out of
10 it is hacky, slow and lacks a solid supportable model because you
are always taking into account *the other* databases the application
supports.And to be frank, I don't think we should waste our time on MySQL. It
isn't a competitor, it just thinks it is. We should focus on our
real competition which is DB2, MSSQL, and Oracle.If we want to create contrib modules that have types etc... that
help port from Oracle to PostgreSQL or DB2 to PostgreSQL I am all
for it.The first thing that comes to mind is a set of domains that
implement Oracle types (names) as PostgreSQL types.Here's a start ;)
postgres=# create domain varchar2 AS text;
CREATE DOMAIN
postgres=# create domain clob as text;
CREATE DOMAIN
postgres=# create domain blob as bytea;
CREATE DOMAIN
postgres=# create domain number as integer;
CREATE DOMAIN
postgres=#
postgres=# CREATE TABLE dual();
CREATE TABLE
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On Thu, May 18, 2006 at 02:56:12PM -0300, Marc G. Fournier wrote:
On Thu, 18 May 2006, Joshua D. Drake wrote:
I understand the idea but I personally don't like it. I am not really
interested in cross-database compatible code. 9 times out of 10 it is
hacky, slow and lacks a solid supportable model because you are always
taking into account *the other* databases the application supports.I don't disagree about the 'hacky, slow and lacks ...' ... but,
there are alot of MySQL based apps out there that are just *way too
big* to convert wholesale to PostgreSQL that, in all honesty, are
better suited to PostgreSQL ...
They're not too big when critical data is in there. Then it's a
matter of what cost to make this wrong decision and have to fix it.
To give someone a running chance at migrating it to PostgreSQL, a
'MySQL compatibility module' would allow them to just plug the
existing DB in, and then work at improving sections of the code over
time ...
You cannot cross a chasm in two steps. MySQL apps don't generally
lend themselves to incremental improvements because they are so
tightly tied to MySQL's misbehaviors. It would be silly and dangerous
to give people the misapprehension that this transition can be made
painlessly or at low cost.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
On Thu, May 18, 2006 at 11:09:28AM -0700, David Fetter wrote:
postgres=# CREATE TABLE dual();
CREATE TABLE
You forgot to populate it.
In reality I think you'd want dual to be a view on SELECT 1; or whatever
the appropriate value is.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Thu, May 18, 2006 at 02:56:12PM -0300, Marc G. Fournier wrote:
the point isn't whether or not MySQL is a competitor ... the point is that
there are *alot* of MySQL based applications out there that are a major
PITA to convert (or get converted) all at once ...
More importantly, there's a lot of MySQL *users*, and they get to
influence which database is chosen in many companies. For many years,
FreeBSD was far superior technologically to Linux, but Linux had the
popularity to make it into the enterprise.
And MySQL is much closer to being a competitor now than they were in
4.1. And feature-wise they'll probably equal PostgreSQL in the next
release. Will the features be anywhere near as robust or well thought
out? No. But in a heck of a lot of companies that doesn't matter.
Maybe a compatability layer isn't worth doing, but I certainly think
it's very much worthwhile for the community to do everything possible to
encourage migration from MySQL. We should be able to lay claim to most
advanced and most popular OSS database.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
And MySQL is much closer to being a competitor now than they were in
4.1. And feature-wise they'll probably equal PostgreSQL in the next
release. Will the features be anywhere near as robust or well thought
out? No. But in a heck of a lot of companies that doesn't matter.
Your kidding right? Have you seen their "features"? Look at what their
stored procedures are actually capable of.
The only thing that MySQL *might* pull off is a really good storage
backend finally.
Maybe a compatability layer isn't worth doing, but I certainly think
it's very much worthwhile for the community to do everything possible to
encourage migration from MySQL. We should be able to lay claim to most
advanced and most popular OSS database.
Oh absolutely, I agree with you here but in order to do so in the most
productive manner possible the community would have to be willing to be
much more aggressive and much more antagnositic that I believe the
community has the stomach for.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On Thu, May 18, 2006 at 01:22:55PM -0500, Jim C. Nasby wrote:
On Thu, May 18, 2006 at 11:09:28AM -0700, David Fetter wrote:
postgres=# CREATE TABLE dual();
CREATE TABLEYou forgot to populate it.
Oh, right. :)
postgres=# CREATE TABLE dual AS SELECT 1;
SELECT
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
