Re: Re: MySQL has transactions

On Wed, 24 Jan 2001 01:09:06 -0500
Steve Leibel <stevel(at)bluetuna(dot)com> wrote:

Hi all
I have had the unpleasant experience of developing for MySQL at work, while at home I can enjoy using PostGres for my part-time work.

> At 8:30 PM -0800 1/23/01, David Wall wrote:
> >Now that MySQL has transaction support through Berkeley DB lib, and it's
> >always had way more data types, what are the main advantages postgresql has
> >over it? I don't think mysql has subselects and such, but they did add a
> >master-slave replication feature as well as online reorganization (perhaps
> >locks tables like vacuum?).
> >
> >Anybody used both of the current releases who can comment?

I must admit, I *haven't* used the version of MySQL with transaction support enabled, but they have numerous other issues too....

>
> I haven't seen the new mysql. My feeling is that all things being
> equal, gluing transactions on top of a database implementation can
> not possibly be as stable and correct as building them in from the
> beginning. The design heuristic that applies is "Make it run first,
> THEN make it run fast." Mysql was built to run fast from the
> beginning, and now they're jamming in functionality. So if I needed
> transactions I'd go with postgres until mysql has a track record.
>
> I happen to be on a project at this very moment in which we're
> converting a mysql database to postgres specifically to get
> transactions, and I prefer making the conversion rather than taking a
> chance on mysql transactions.
>
> I'd be interested to hear any arguments or real-life experiences pro or con.

Firstly, I agree whole-heartedly with this. Transactions are unlikely to work well if they haven't been designed in from the outset. They're also sure to put quite substantial overhead on the processing of writes, so we'll see how well it performs now. But since I've not used the transaction-enabled MySQL at all, I think that's all I'm fit to say at this point...

Other irritations I've found with MySQL are (briefly):
- no subselects (makes for ugly hacks in code)
- no views
- no foreign keys
- no constraint support
- completely lacking date integrity checking (eg will accept '2001-15-45' as a valid date).
- no rules
- no triggers
- no intersects or unions
- table-level locking only
- inability to go beyond FS limits of filesize for databases
All in all, about the only thing MySQL has going for it is the replication.

The only issues I've had with PostGres are:
- this doesn't work: select a from b where a.fld in (select c from d where e = f intersect select c from d where e=g)
but I believe that will be working in 7.1
- 8k row limit
pretty severe, but can be fixed at copmpile-time to 32k. Completely removed for 7.1

Thanks guys!

Ciao