Re: MySQL has transactions - Mailing list pgsql-general

Hi David,

Thanks for the pointer on the type 4 driver for MySQL.

I too found the tuple size limitations in 7.0.3 constraining - I'm helping
develop an app that stores genetic sequences which are routinely much
larger than the 8 to 32k limit in 7.0.3.

Since my project timeline has a June release date I've been developing for
Pg 7.1 and have been quite pleased with the results and stability so far.
I believe it's pretty close to release now, so if your timeline allows for
it you may wish to give it a try.

Like anything it's not perfect but I think Pg is by and large a better
long-term solution for my project than MySQL. Our first alpha version runs
with a MySQL backend (we needed blobs), and the lack of the "standard" SQL
features (triggers, foreign keys, stored procedures) led to many
uncomfortable workarounds. Much of the core database logic needed to go
into Java, which lead to the need for extensive collaboration with other
programmers on the team.

I was afraid to use the then-alpha transactions in MySQL because "CHECK
TABLE" and isamchk did not work for BDB tables. Hopefully this is resolved
now.

By using Postgres I have been able to provide the other programmers a
clean API to access the database: complex queries are reduced to "SELECT *
FROM <view> WHERE ..." and error checking can occur inside constraints,
stored procedures and triggers. This has made all of us more a great deal
more productive.

I have heard there is some interest among the MySQL developers to get
stored procedures in MySQL using the same Zend scripting engine used by
PHP. If they were to do that, implement foreign keys, implement row-level
locking, and get the performance of BDB tables up to par with MyISAM (or
get transactions in MyISAM tables), then I think it will be quite usable
for complex schemas.

Norm

--------------------------------------
Norman Clarke
Combimatrix Corp Software Development
Harbour Pointe Tech Center
6500 Harbour Heights Pkwy, Suite 301
Mukilteo, WA 98275

tel: 425.493.2240
fax: 425.493.2010
--------------------------------------

On Wed, 24 Jan 2001, David Wall wrote:

> Great comparison.  I've just compiled MySQL 3.23.32 with the Berkeley DB
> support for transactions (the binary distribution, sad to say, does not
> include it!).  I know that MySQL also has a type 4 JDBC driver by Mark
> Matthews and it's worked well for me in the past using the pre-BDB
> transaction files.
>
> I do love the features of Postgresql 7.0.3, but the large object support has
> been really bad, causing an 800 byte binary item to require 24K of disk
> space across two files, neither of which are part of the backup of the
> database, and neither of which are deleted when the row pointing to them is
> deleted.  (There's a vacuumlo that solves that one in the background.)  And
> the JDBC library doesn't seem to want me to use the BYTEA type for small
> byte arrays.  What I really want is a good-old BLOBs with minimal overhead
> that are truly part of the database and its transaction/backup world.
>
> David