What that does is very simple: it rolls back the one that is keeping track
of it's transactions. Think of the overhead if someone doesn't have
transactional statements. The idea is, in PGSQL, all inserts and updates
are essentially logged so that they can be rolled back. Here is the MySQL
concept:
Have a log table that logs all transactions (lets say, failed or not)
1. begin transaction
2. insert into non-transactional table 'user did this, status-
unprocessed'
3. insert into payment table
4. insert into product table
5. update to processed
6. insert into shipping
7. update to 'pending shipping'
Perfectly common transaction that happens. Now! What if you want the
entry inserted and dealt with as a status and what happens, but you don't
want all the evidence of that to disappear when you hit rollback. It means
you can have some things roll back and others don't. In PGSQL, that would
have to be begin/rollback for only transactional entries.
--
Mike
----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Mike Rogers" <temp6453(at)hotmail(dot)com>; <pgsql-hackers(at)postgresql(dot)org>;
"Jean-Michel POURE" <jm(dot)poure(at)freesurf(dot)fr>
Sent: Sunday, October 28, 2001 9:52 PM
Subject: RE: [HACKERS] Ultimate DB Server
> > MySQL and PostgreSQL are starting to move together as far as I can see.
> > MySQL has the _option_ of transactional database formats (you can use
both
> > normal MyISAM tables and transactional tables). MySQL 4.0 has all those
> > various features you speak of.
>
> Not it doesn't.
>
> It supports the UNION statement (thank god!)
>
> And this is for 4.1:
>
> -------
> MySQL 4.1, the following development release
>
> Internally, through a new .frm file format for table definitions, MySQL
4.0
> lays the foundation for the new features of MySQL 4.1, such as nested
> subqueries, stored procedures, and foreign key integrity rules, which form
> the top of the wish list for many of our customers. Along with those, we
> will also include simpler additions, such as multi-table UPDATE
statements.
>
> After those additions, critics of MySQL have to be more imaginative than
> ever in pointing out deficiencies in the MySQL Database Management System.
> For long already known for its stability, speed, and ease of use, MySQL
will
> then match the requirement checklist of very demanding buyers.
> --------
>
> I don't get how you can have different tables being transactional in your
> database??
>
> ie. What on earth does this do? (pseudo)
>
> create table blah not_transactional;
> create table hum not_transactional;
>
> begin;
> insert into blah values (1);
> insert into hum values (2);
> rollback;
>
> ?????
>
>
> On all too many applications, MySQL kicks
> > ass. Admitedly, if you do massive complex database applications,
> > PostgreSQL
> > can smoke it when done right, but MySQL works great for most tasks.
It's
> > not even a matter of which is better or how to compare them. It is a
> > question of 'what is your purpose for the database' and then
> > deciding based
> > on the intended purpose.
> > I did mention that it would be running BOTH MySQL and PostgreSQL,
and
> > not just one. I use them both for various purposes, depending on the
need
> > and am trying to move it to a seperate server to increase the speed of
> > queries on BOTH database systems. It's not a question of which is
better,
> > but a question of what will maximize output for cost.
> > I think you may have misinterpreted the question
> > --
> > Mike
> >
> > ----- Original Message -----
> > From: "Jean-Michel POURE" <jm(dot)poure(at)freesurf(dot)fr>
> > To: <pgsql-hackers(at)postgresql(dot)org>
> > Cc: "Mike Rogers" <temp6453(at)hotmail(dot)com>
> > Sent: Sunday, October 28, 2001 3:18 PM
> > Subject: Re: [HACKERS] Ultimate DB Server
> >
> >
> > > At 13:07 28/10/01 -0400, you wrote:
> > > >I'm questioning whether anyone has done benchmarks on various hardwar
e
> > for
> > > >PGSQL and MySQL. I'm either thinking dual P3-866's, Dual AMD-1200's,
> > etc.
> > > >I'm looking for benchmarks of large queries on striped -vs-
non-striped
> > > >volumes, different processor speeds, etc.
> > >
> > > Hello Mike,
> > >
> > > IMHO, you should consider *simple* software optimization first.
> > >
> > > Hardware can bring a 2x gain whereas software optimization can boost
an
> > > application by 10x. Until now, I never heard or read about a real
> > *software
> > > optimization* benchmark between MySQL and PostgreSQL.
> > >
> > > Software optimization includes the use of views, triggers,
> > rules, PL/pgSQL
> > > server side programming. By definition, it is hard to compare MySQL
with
> > > PostgreSQL because MySQL *does not include* these important
> > features (and
> > > probably will never do).
> > >
> > > I see at least two easy cases where PostgreSQL beats MySQL:
> > > 1) Create a simple relational DB with triggers storing values instead
of
> > > performing LEFT JOINS. Increase the number of simultaneous
> > queries. MySQL
> > > will die at x queries and PostgreSQL will still be working at
> > 5x queries.
> > > 2) Use PL/pgSQL to perform complex jobs normally devoted to an
> > application
> > > server (Java, PHP) on a separate platform. In some case (recursive
loops
> > > for example), network traffic can be divided by 100. As a result,
> > > PostgreSQL can be 10x faster because everything is performed
> > server-side.
> > >
> > > This is to say that, in some circomstances, PostgreSQL running
> > on an i586
> > > with IDE drive beats MySQL on a double Pentium. In real life,
> > applications
> > > are always optimized at software level first before hardware level.
This
> > is
> > > why PostsgreSQL is *by nature* better than MySQL.
> > >
> > > Unless MySQL gets better, there is no real challenge in comparing both
> > systems.
> > >
> > > Cheers,
> > > Jean-Michel POURE
> > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>