Getting started with Postgres or MySQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/01/07 12:16, Angelo Bertolli wrote:
> I've been debating whether or not to make a comment on this discussion,
> but it finally got to me. I think you're being way too hard on MySQL
> considering the fact that this question originated from the idea of
> using a database backend for OpenOffice.org. Yeah, I didn't like "MySQL
> is definitely your best choice" kind of answer with no clear indication
> as to why MySQL is a better choice in this case, so I'll give a couple
> of reasons:
>
> (1) MySQL is shown to be faster in a single-user environment than
> Postgres, especially with complicated SELECT statements
>
> (2) MySQL is a shorter learning curve for new users

I think MSFT used to use those same arguments about why people
should use Windows

[snip]
> think anyone who only knows MySQL really needs to start getting into
> Postgres if they ever intend on making a large database.

Bad habits ingrained now are bad habits that you carry with you for
a long time. Start with good habits now and you're better for it
always.

The PostgreSQL Novice list welcomes questions like those from OP.
http://archives.postgresql.org/pgsql-novice/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFwkIaS9HxQb37XmcRAsy5AJ4keWzAP3olwHpbvyLczZxfmaiU+gCg2Pjr
AyElwbG+BVwpYAJJJKDHNOI=
=7u3q
-----END PGP SIGNATURE-----

--

On Thu, Feb 01, 2007 at 01:16:18PM -0500, Angelo Bertolli wrote:
>
> (1) MySQL is shown to be faster in a single-user environment than
> Postgres, especially with complicated SELECT statements
>
IIRC, this does not hold for transactional tables. So, we are back to
the "if you don't care about your data" argument.

> (2) MySQL is a shorter learning curve for new users
>
What? In what way? Learning to develop against MySQL is no harder or
easier than learning to develop against PostgreSQL (besides the fact the
people need to be broken of the stupid misconceptions engendered by the
pervasveness of MySQL). The two are just different.

> If people were just praising and praising MySQL as the best database,
> I'd be playing devil's advocate because honestly MySQL isn't a serious
> database for serious database jobs. It's a good database for rapid web
> development, and for quickies--probably the BEST choice for those

I don't about "BEST" but it is at least one of the available choices.

> things. Yeah, it sucks that MySQL requires an application layer to any
> system (i.e. you, as the programmer, must provide rules, enforce data
> integrity, and up until recently even manage your own foreign keys).

OK. How in the world does this sort of garbage shorten the user's
learning curve. You are contradicting yourself.

> But if OpenOffice.org works with MySQL, then I'm sure the OOo software
> has taken care of it.
>
> Let's not go around telling people "you shouldn't use MySQL" or "you
> shouldn't use Postgres" just because it might not fit what we're doing
> with OUR databases at any given time.
>
Except that there are vanishingly few, if any, reasons to actually
prefer MySQL over anything else.

> With that said, I think anyone who is not the most technically inclined
> and just getting into things should start out by trying MySQL
> because I think overall you can find a lot better information for it
> online, and it you don't get bogged down with different schemas. I
> think anyone who only knows MySQL really needs to start getting into
> Postgres if they ever intend on making a large database.
>
Ever learn to play a musical instrument in a "newbie friendly" way?
Ever take lessons from a master after that? They will spend more time
breaking you of your bad habits than teaching you the instrument. The
same holds true for people who grow up on MySQL. They simply do not
understand how to work with a real database.

I see this at work. People "learn" on MySQL and then totally fall apart
when they try to work with Oracle. This is because they don't
understand that Oracle, being a real database, behaves like a real
database and not like that toy MySQL.

Regards,

-Roberto

--
Roberto C. Sanchez
http://people.connexer.com/~roberto
http://www.connexer.com

On Thu, Feb 01, 2007 at 06:18:03AM +0100, Danesh Daroui wrote:
>
> Maybe MySQL is not the best database engine in the world but it is one
> the best engines. I have worked on MySQL for several years and from four
> years ago I have started to develop my own database engine inspired by
> MySQL. During development of my own database engine (BromeliaSQL) I
> realized the magical techniques which have been used in MySQL which are
> unique. For example MySQL was (and maybe is) the only database engine
> which allows you to read and write to a table which has been indexed
> even by using B-Tree which is the most complex structure, at the same time.

That's nice.

> About allowing corrupted data, it is not the responsibility of database
> engine to verify if the data is valid or not since the database engine
> only "Manages" data and not validate. Although, MySQL have a very very
> powerful library of functions to validate and generate and validate date
> and kind in any possible format.
>
I'm sorry, but this is pure crap. There is no way that anyone can
convince me that inserting 2007-02-31 or even 0000-00-00 into a column
of type DATE is even remotely permissible. What fantasy land do people
live in where such dates are valid?

I simply don't buy that. It is obvious that you do not work with
databases in a multiuser environment. Imagine this. You are running a
huge payment processing system. There a number of client applications
which have been developed to interface to the database backend. Now,
what happens when someone creates a client with a bug that tries to
insert payments dated February 31st? MySQL says, OK, go for it. What
about all the rest of the users who actually depend on the data to be
correct and consistent?

What about management, who expect to be able to generate meaningful
reports? Think about it. How meaningful is it to report on events that
could not have happened according to their dates?

I could go on. Like I said, if you are running a phpBB or some dinky
little application where you don't care about your data and/or you don't
need true multiuser support, then MySQL works fine. I still think it is
a bad idea to use it in those situations, but it will work. What I
don't get is why people are so quick to stand up and defend MySQL even
though it has so many shortcomings.

Regards,

-Roberto

--
Roberto C. Sanchez
http://people.connexer.com/~roberto
http://www.connexer.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/31/07 23:18, Danesh Daroui wrote:
> Roberto C. Sanchez wrote:
>> On Wed, Jan 31, 2007 at 02:54:01PM -0900, Joshua J. Kugler wrote:
>>
[snip]
> Maybe MySQL is not the best database engine in the world but it is one
> the best engines. I have worked on MySQL for several years and from four
> years ago I have started to develop my own database engine inspired by
> MySQL. During development of my own database engine (BromeliaSQL) I
> realized the magical techniques which have been used in MySQL which are
> unique. For example MySQL was (and maybe is) the only database engine
> which allows you to read and write to a table which has been indexed
> even by using B-Tree which is the most complex structure, at the same time.

ROTFLMAO. You have *got* to be kidding.

*Every* RDBMS worth it's salt lets you concurrently access b-tree
indexes.

> About allowing corrupted data, it is not the responsibility of database
> engine to verify if the data is valid or not since the database engine
> only "Manages" data and not validate. Although, MySQL have a very very
> powerful library of functions to validate and generate and validate date
> and kind in any possible format.

What a single-user attitude. Here's a nickel kid, go buy yourself
some VT220s.

It's always the app developer's responsibility to make sure that he
inserts clean data.

OTOH, any DBA worth his/her salt will *not* trust code monkeys to
find their arses with both hands, a map and flashlight. Thus, we
put up our own defenses against incompetent boobs.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFwYFWS9HxQb37XmcRAoA0AKDFmwwSuqO8+VBESeaEfBNx5mw3qQCeLW7b
bS6Ibb3nDO39EzAK7BmXTjY=
=OHw5
-----END PGP SIGNATURE-----

--

On Thursday, 01.02.2007 at 06:18 +0100, Danesh Daroui wrote:

> About allowing corrupted data, it is not the responsibility of database
> engine to verify if the data is valid or not since the database engine
> only "Manages" data and not validate.

It depends what you mean by 'validate'. The database ought, at least,
to return the same value that was INSERTed. Or, if it is not able to do
so, it should raise an error on the INSERT.

Look! This is MySQL 5:

>> mysql> create table foo(a date);
>> Query OK, 0 rows affected (0.05 sec)
>>
>> mysql> describe foo;
>> +-------+------+------+-----+---------+-------+
>> | Field | Type | Null | Key | Default | Extra |
>> +-------+------+------+-----+---------+-------+
>> | a | date | YES | | NULL | |
>> +-------+------+------+-----+---------+-------+
>> 1 row in set (0.00 sec)
>>
>> mysql> insert into foo values('2007-02-31');
>> Query OK, 1 row affected, 1 warning (0.00 sec)
>>
>> mysql> select * from foo;
>> +------------+
>> | a |
>> +------------+
>> | 0000-00-00 |
>> +------------+
>> 1 row in set (0.00 sec)

It generates a warning but still inserts the record. This strikes me as
dangerous, especially as the default behaviour of 'BEGIN' and 'COMMIT'
wrapped around the above *still* inserts the record. I seem to remember
that slightly older MySQL versions returned '2007-03-03' instead in the
above circumstances, which was ludicrous; at least that has been fixed.

The same test under PostgreSQL 8.1:

>> pg=# create table foo(a date);
>> CREATE TABLE
>> pg=# \d foo
>> Table "public.foo"
>> Column | Type | Modifiers
>> --------+------+-----------
>> a | date |
>>
>> pg=# insert into foo values('2007-02-31');
>> ERROR: date/time field value out of range: "2007-02-31"
>> pg=# select * from foo;
>> a
>> ---
>> (0 rows)

Seems like very sensible behaviour to me.

Dave.

--
Please don't CC me on list messages!
...
Dave Ewart - - jabber:
All email from me is now digitally signed, key from http://www.sungate.co.uk/
Fingerprint: AEC5 9360 0A35 7F66 66E9 82E4 9E10 6769 CD28 DA92

Roberto C. Sanchez wrote:

> On Wed, Jan 31, 2007 at 02:54:01PM -0900, Joshua J. Kugler wrote:
>
>> In that same document, they give the reason for doing so:
>>
>> "The reason for using the preceding rules in non-strict
>> mode is that we can't check these conditions until the
>> statement has begun executing. We can't just roll back if
>> we encounter a problem after updating a few rows, because
>> the storage engine may not support rollback. The option
>> of terminating the statement is not that good; in this
>> case, the update would be ???half done,??? which is
>> probably the worst possible scenario. In this case, it's
>> better to ???do the best you can??? and then continue as
>> if nothing happened."
>>
>>
> I'm sorry, but "our database can't always handle
> transactions" is not a valid excuse for allowing bogus
> data.

Does this mean that Mysql isn't really a DB, but is in fact just a
front-end, translating SQL statements into commands to the real DB?

--
Best wishes,

Max Hyre