MySQL has transactions

Postgresql's SQL implementation is way ahead of MySQL's relatively
stunted vocabulary. But on the other hand, MySQL implements most
of the popular functionality. The other thing is that MySQL is
blindingly fast and has a very uncomplicated API.

If you need real SQL and can't afford Oracle/Sybase/DB2 then the
obvious choice is Postgresql. If you need speed and simplicity
and maximum ease of administration and maintenance, that would
be MySQL.

-joseph

David Wall wrote:

speed with tranactions on
row level locking
I though that postgresql had more data type
extensable interface
choice of index types
better performance under load
triggers
to name a few. Has anyone actually benchmarked mySQL with transations
enabled?

-----Original Message-----
From: David Wall [mailto:d.wall@computer.org]
Sent: Wednesday, 24 January 2001 3:30 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] MySQL has transactions

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?

Thanks,
David

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 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.

Steve Leibel

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.

You may be right, though they did this with berkeley db, which I guess is
pretty stable with transaction support.

The problems I'm having with postgresql are mainly in the area of blobs. I
need to store several binary objects, generally in the 800-2400 byte range,
and I also need to store text messages sent by people, and I have to deal
with truncation and such to stay within the 8k row-size limit. I've heard I
can update the blocksize to 32k, but then I've read this has other negative
impacts and that 7.1 solves it anyway -- but when will that be stable and
ready?

Anyway, I'm giving them both a quick test, primarily with regard to
transactions and blobs. I can report back what I learn, but it will only be
at a testing level, and I'd prefer to hear from production users.

David

On Wed, 24 Jan 2001 01:09:06 -0500
Steve Leibel <stevel@bluetuna.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

On Tue, 23 Jan 2001, 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?).

I've been using PostgreSQL since about 1997, and only used MySQL for the
first time last year, and MySQL just seems to sparse for a lot of things.
The lack of foreign key contraints and views is a problem for me.
PostgreSQL still has more features, like triggers, rules, referential
integrity, views, sub-selects, row-level locking, to name a few things.

I think MySQL is a very good way to introduce beginners to SQL and
database concepts, but you can only go so far with it. It's very good for
doing archiving of static data and fast retrieval for websites, but I
wouldn't build an e-commerce site with it.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Dear Lord:
I just want *___one* one-armed manager so I never have to hear "On
the other hand", again.

I completely agree! I've been "shopping" for affordable databases over the
last months and have come to the conclusion that postgresql has the most
powerful features. I ruled out mySql immediately because of the same things
you pointed out. I found Interbase to be the biggest contender of postgresql.

On my wishlist for postgresql the top three would be:

* 24x7 support (load-balancing, failover, online-backup, multiple parallel
servers, ...)
* Fast case insensitive text search via indexes (function based indexes)
* Java in the server (for triggers and functions)

I know I'm quite modest :-)

Alexander Jerusalem
ajeru@gmx.net
vknn

At 12:06 24.01.01, Zak McGregor wrote:

* Fast case insensitive text search via indexes (function based indexes)

Try to:

create table test (s text);
create index ix_test_s on test(lower(s));

And try select * from test where lower(s) = 'test';

If you made vacuum, and have enough data in the table you will get index
scan. Also you will get index scan for this:
select * from test where lower(s) like 'test%';

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

On Wed, 24 Jan 2001, Willis, Ian (Ento, Canberra) wrote:

speed with tranactions on
row level locking
I though that postgresql had more data type

no, MySQL has 'type of the week' contests to see how many non-compliant
types they can think of, and add them ...

El Mi� 24 Ene 2001 08:06, Zak McGregor escribi�:

Other irritations I've found with MySQL are (briefly):
- no subselects (makes for ugly hacks in code)
- no views
- no foreign keys

Didn't know they didn't have foreign keys. :-(

- no constraint support
- completely lacking date integrity checking (eg will accept '2001-15-45'
as a valid date).

That is pretty ugly.

- 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

Both (AFAIK) are added in 7.1

--
System Administration: It's a dirty job,
but someone told I had to do it.
-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Wow! postgresql is a miracle! :-)

I'm starting to wonder why anybody would want to use Oracle...

Alexander Jerusalem
ajeru@gmx.net
vknn

At 14:02 24.01.01, Denis Perchine wrote:

On Wed, 24 Jan 2001 10:18:56 -0300
"Martin A. Marques" <martin@math.unl.edu.ar> wrote:

El Mi� 24 Ene 2001 08:06, Zak McGregor escribi�:

Other irritations I've found with MySQL are (briefly):
- no subselects (makes for ugly hacks in code)
- no views
- no foreign keys

Didn't know they didn't have foreign keys. :-(

Not only that - this is what the MySQL site used to say about foreign
keys:

The FOREIGN KEY syntax in MySQL exists only for compatibility with other
SQL vendors CREATE TABLE commands: It doesn't do anything.
...
Foreign keys is something that makes life very complicated, because the
foreign key definition must be stored in some database and
then the hole [sic] 'nice approach' by using only files that can be moved,
copied and removed will be destroyed. In the near future we will extend
FOREIGN KEYS so that the at least the information will be saved and may be
retrieved by mysqldump and ODBC.

Ciao

I though that postgresql had more data type

no, MySQL has 'type of the week' contests to see how many non-compliant
types they can think of, and add them ...

Something I'm curious about: does MySQL support user-defined datatypes?
If so, how hard is it to make a new datatype? I think datatype
extensibility is one of the cooler features of Postgres ...

regards, tom lane

David Wall writes:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types,

I count 25 documented and distinct data types for MySQL, and 30 for
PostgreSQL.

http://www.postgresql.org/devel-corner/docs/postgres/datatype.htm
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Column_types

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

There have been several recent benchmarks by non-mysql and postgres people
and the speed argument does not seem to be valid.

Even though MySQL still beats postgres in speed if they are compared with
one user on the DB, postgres seems to destroy MySQL in speed as you tend to
add users.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Joseph N. Hall @5sigma.com>" <" <heard_it_on_the_internet>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, January 24, 2001 12:19 AM
Subject: [GENERAL] Re: MySQL has transactions

Postgresql's SQL implementation is way ahead of MySQL's relatively
stunted vocabulary. But on the other hand, MySQL implements most
of the popular functionality. The other thing is that MySQL is
blindingly fast and has a very uncomplicated API.

If you need real SQL and can't afford Oracle/Sybase/DB2 then the
obvious choice is Postgresql. If you need speed and simplicity
and maximum ease of administration and maintenance, that would
be MySQL.

-joseph

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

David Wall schrieb:

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?

Thanks,
David

Another question: now the sap-db is open and the source will be
released
this years, what is the advantage of MySQL over SPA-DB ?

Marten

I count 25 documented and distinct data types for MySQL, and 30 for
PostgreSQL.

In my case, I'd just settle for a workable BLOB/LONGBLOB. I think counting
types is less interesting than meeting ones needs. They "redefine" types
like BLOB as LONGVARBINARY and TEXT as LONGVARCHAR, but does that add two
types? Anyway, blobs are prettys standard for sql, and that's what I'm
looking to have work for me.

David

On Wed, 24 Jan 2001, Peter Eisentraut wrote:

David Wall writes:

Now that MySQL has transaction support through Berkeley DB lib, and it's
always had way more data types,

I count 25 documented and distinct data types for MySQL, and 30 for
PostgreSQL.

Not to mention that Postgres has an extensible type system whereas MySQL
does not.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
When a man knows he is to be hanged in a fortnight, it concentrates his
mind wonderfully.
-- Samuel Johnson

On Wed, 24 Jan 2001, Tom Lane wrote:

Something I'm curious about: does MySQL support user-defined datatypes?

No, it does not.

If so, how hard is it to make a new datatype? I think datatype
extensibility is one of the cooler features of Postgres ...

Check this out:
http://www.mysql.com/documentation/mysql/bychapter/manual_Comparisons.html#Compare_PostgreSQL

http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
If you give a man enough rope, he'll claim he's tied up at the office.