Re: PostgreSQL 8.1 vs. MySQL 5.0?

On Wed, 2005-10-05 at 20:37, CSN wrote:
> Just so I know (and am armed ;) ), are there any new
> comparable features in MySQL 5.0 that aren't in
> PostgreSQL up to the forthcoming 8.1? AFAIK, PG just
> lacks updatable views (which are on the TODO).

Bit type: Postgresql supports binary string already.

Cursors: PostgreSQL does everything up updatable cursors (unless this
got added recently) MySQL's cursors are only available in a procedure
or function, and can't be scolled.

Information Schema: MySQL's support of this looks fairly extensive.

Instance Manager: Uniquely MySQL. It allows things like starting and
stopping the database remotely.

Fixed point arithmetic: PostgreSQL has had good behaviour for
arbitrarily long numeric math for quite some time.

Archive Storage Engine: PostgreSQL does the same thing, on the fly,
with no add on engine, and no limitations like this one has. I.e. you
have fill transactions, and can use more than select and insert on your
text types, which are automagically toasted if over a certain size.

Federated Storage Engine: Allows MySQL to access tables in other
servers like they are here. No real direct equivalent in PostgreSQL,
but dblink provides similar functionality.

Stored Routines: PostgreSQL's user defined functions have done the same
thing as stored routines for quite some time now. And in many brightly
colored languages.

Strict Mode and Error handling: Not an option, but always on in
PostgreSQL. There are still plenty of things that "fall through the
cracks" on MySQL, like my previously mentioned problem with column level
constraints (specificall fk but all column level constraints are
ignored, no error, no warning, no notice.) Jeez, how hard would it be
to just throw a danged notice?

Triggers: PostgreSQL has been there, done that, and has a large
collection of TShirts. Each with a name of a different language it can
use to create triggers / user defined functions.

varchar data type extended to 64k. PostgreSQL has a limit of 1 Meg on
varchar (if you use a limit) and can make a text type of ~ 1 gig.

Views: Similar functionality, but PostgreSQL has updatable views by the
DBA writing simple rules that allow it. This means that for simple
updatable views, MySQL wins for ease of use, and for complex updatable
views, PostgreSQL wins because you can still do them, you just get to do
it yourself.

XA Transactions: MySQL's are pretty primitive, and PostgreSQL's XA may
not be much further ahead there. XA transactions need some form of
management for partial transactions. MySQL's answer here was to just
refuse to commit on any member if any other member failed to be prepared
for commit. This is possibly the least useful implementation of XA
there could be, as the primary reason I've seen for it is to allow an
application to have n db servers, and to "kick one out" if it starts
misbehaving and run on the remaining n-1 servers. Note that right now,
PostgreSQL's XA has, as far as I know, no real conflict management. But
I'm guessing PostgreSQL will have a better fleshed out XA interface
before MySQL.