Re: [HACKERS] Enticing interns to PostgreSQL - Mailing list pgsql-advocacy

On Tue, Jul 26, 2005 at 01:53:54PM -0700, Chris Travers wrote:
> Jim C. Nasby wrote:
>
> >
> >And what do we use as a platform for this?
> >
> ><dons flame suit> Should we have a "Why you probably don't want to use
> >MySQL" document somewhere on the site?
> >
> >
> >
> It would be better to have a "Benefits over MySQL" page.  Something like:

Well, I guess that's a more polite way to put it, but it misses the real
point: MySQL does a lot of things that are at best not a very good way
to do them and at worst put your data at serious risk. Many people
simply have no idea about these issues. So while it'd be nice if these
people chose PostgreSQL over MySQL, I personally think it's more
important that they save themselves (and others) the pain that's likely
to follow from deciding to use MySQL.

BTW, your list is missing some critical items, such as silent data
truncation, count(*) returning an estimate, how there's numerous ways to
configure MySQL so it's not ACID without even knowing it, etc. In other
words, problems that make it easy to trash your data (without even
knowing it), as opposed to standard database features that are just
missing.

> In fact, having a maintained feature sheet listing PostgreSQL, MySQL,
> and FirebirdSQL probably wouldn't be a bad idea.

That's probably not a bad idea either, just to give people an idea of
how the different projects stack up. SQLlite would probably be a good
addition to that list as well.

> >The case I'm thinking of is when you have a small table that you want to
> >have an enum-like field in; in such a case using an ID to reference
> >another table every time you want to find a value probably doesn't make
> >sense. But if you've got a moderately large number of allowable values
> >(say more than a couple dozen), maintaining a check constraint to handle
> >the ENUM might be a bear.
> >
> >
> Probably the best way of doing this is to have a VARCHAR primary key in
> the enum table (sole column), and a VARCHAR  foreign key referencing it.

Yup. Or text for that matter...

> >But as you mentioned, if we're careful with syntax the type can always
> >be expanded. Another interesting use case would be an enum that
> >automatically adds new values to the lookup table if they don't already
> >exist. I know it's no longer an enum in the traditional sense, but this
> >is a common enough use case that it would be very convenient to have.
> >
> >
> This could be done with triggers and deferred RI constraints.  However,
> how does it differ from a VARCHAR?

I know it can be done now; I'm just saying it would make the developers
job a bit easier.

As for varchar, they're orthogonal issues. If you have a large table
with a limited number of text values that could change over time you'd
want to store an integer ID in the large table, but make it easy to deal
with new values being added.

> >Agreed and agreed. I'm absolutely opposed to continuing dumb mistakes
> >
> >made by MySQL. This should be an example of how they should have done
> >things in the first place.
> >
> >I'm starting a new job next week that might allow doing this kind of
> >work with some official corporate sponsorship. Because of that I'm going
> >to hold off a bit on creating a pg-foundry project (though I suspect
> >that's where this will be hosted anyway). In any case, expect to see
> >something mid-next week.
> >
> >
> I will be bringing this up to another possibly interested party as well.
>
> Best Wishes,
> Chris Travers
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"