PostgreSQL 8.1 vs. MySQL 5.0?

On Wed, 2005-10-05 at 18:37 -0700, 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).

MySQL 5.0 new features
http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html

Well "IF" they are being completely honest, we don't have XA
and we don't have an "instance manager" but of course who really needs
one?

Sincerely,

Joshua D. Drake

Thanks,
CSN

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Wed, 2005-10-05 at 18:37 -0700, 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).

MySQL 5.0 new features
http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html

Well "IF" they are being completely honest, we don't have XA
and we don't have an "instance manager" but of course who really needs
one?

We don't have XA built into the backend, but if I've been following the
jdbc list accurately, there's fairly complete XA support for the jdbc
driver, which should be available in the 8.1 release.

More generally, it's worth making the point that a lot of MySQL's "brand
new in 5.0" features have been in Postgres for a *long* time, and are
therefore likely to be both more stable and better-performing than
MySQL's first cut at them.

(BTW, it sure seems like MySQL 5.0 has been a heckuva long time in
getting to release status. Has anyone here been following that
process? Why's it been so painful?)

regards, tom lane

Am Mittwoch, den 05.10.2005, 18:37 -0700 schrieb CSN:

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

MySQL 5.0 new features
http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html

Nice detail:

"Views that use UNION ALL are disallowed even though they might be
theoretically updatable, because the implementation uses temporary
tables to process them."

not sure if silently declare part of the view as updateable
is SQL standard... you can do all you want if you add the
update (and also insert) rule to the view definition
(which is in fact only a select rule in PG)

More generally, it's worth making the point that a lot of MySQL's "brand
new in 5.0" features have been in Postgres for a *long* time, and are
therefore likely to be both more stable and better-performing than
MySQL's first cut at them.

Some specific things could be: Their "initial support" for triggers ;)
Also technically we
do have updateable views via rules.

Sincerely,

Joshua D. Drake

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

I'm not sure what XA (distributed transactions) is -
is that something that can be achieved with Slony?

CSN

--- "Joshua D. Drake" <jd@commandprompt.com> wrote:

On Wed, 2005-10-05 at 18:37 -0700, 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).

MySQL 5.0 new features

http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html

Well "IF" they are being completely honest, we don't
have XA
and we don't have an "instance manager" but of
course who really needs
one?

Sincerely,

Joshua D. Drake

Thanks,
CSN

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

---------------------------(end of

broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster

--
Your PostgreSQL solutions company - Command Prompt,
Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom
Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG -
http://www.commandprompt.com/

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

No. Distributed transactions can cooperate in two phase commit.

I think someone has done some two phase commit work already. IIRC.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of CSN
Sent: Wednesday, October 05, 2005 11:11 PM
To: jd@commandprompt.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?

I'm not sure what XA (distributed transactions) is -
is that something that can be achieved with Slony?

CSN

--- "Joshua D. Drake" <jd@commandprompt.com> wrote:

On Wed, 2005-10-05 at 18:37 -0700, 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).

MySQL 5.0 new features

http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html

Well "IF" they are being completely honest, we don't
have XA
and we don't have an "instance manager" but of
course who really needs
one?

Sincerely,

Joshua D. Drake

Thanks,
CSN

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

---------------------------(end of

broadcast)---------------------------

TIP 2: Don't 'kill -9' the postmaster

--
Your PostgreSQL solutions company - Command Prompt,
Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom
Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG -
http://www.commandprompt.com/

__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

---------------------------(end of

broadcast)---------------------------

They have collation and multiple characterset per table and etc. which
actually is from 4.1 (not new in 5.0), and postgresql have only one
collation per database cluster :-(
Otherwise I think their features are all there, but cannot be used
togather most of them (you can have foreign key, but not using fulltext ...)

CSN wrote:

cool_screen_name90001@yahoo.com (CSN) writes:

I'm not sure what XA (distributed transactions) is -
is that something that can be achieved with Slony?

No.

XA is an interface to allow having updates take place across multiple
databases.

That would mean that you do some updates on one DB, others on another,
and finally issue a "distributed COMMIT" which commits it all at once.

That's not similar to what Slony-I does...
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/oses.html
"Have you noticed that, when we were young, we were told that
`everybody else is doing it' was a really stupid reason to do
something, but now it's the standard reason for picking a particular
software package?" -- Barry Gehm

They have collation and multiple characterset per table and etc. which
actually is from 4.1 (not new in 5.0), and postgresql have only one
collation per database cluster :-(
Otherwise I think their features are all there, but cannot be used
togather most of them (you can have foreign key, but not using fulltext ...)

I heard that MySQL has tons of problems with its multibyte support
(for example SELECT does not return correct data). I don't know if
MySQL AB has fixed the problem or not though.
--
SRA OSS, Inc. Japan
Tatsuo Ishii

On Wed, 2005-10-05 at 23:41, Tom Lane wrote:

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Wed, 2005-10-05 at 18:37 -0700, 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).

MySQL 5.0 new features
http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html

Well "IF" they are being completely honest, we don't have XA
and we don't have an "instance manager" but of course who really needs
one?

We don't have XA built into the backend, but if I've been following the
jdbc list accurately, there's fairly complete XA support for the jdbc
driver, which should be available in the 8.1 release.

More generally, it's worth making the point that a lot of MySQL's "brand
new in 5.0" features have been in Postgres for a *long* time, and are
therefore likely to be both more stable and better-performing than
MySQL's first cut at them.

(BTW, it sure seems like MySQL 5.0 has been a heckuva long time in
getting to release status. Has anyone here been following that
process? Why's it been so painful?)

I've been beta testing 5.0.xx releases and reporting bugs. They're
pretty fast at fixing individual bugs.

Not sure why it's taken so long, really. Maybe they were trying to do
too much at once in one release?

But what really bugs me is that some things that ARE bugs simply aren't
getting fixed and probably won't. Specifically, while mysql understands
fk references made at a table level, it simply ignores, without error,
warning, or notice, fk references made in a column. arg... Very
frustrating. If they just didn't support that syntax it would be much
less bothersome, since I'd try it, get an error, and try the other
syntax. Instead, I spent an afternoon trying to figure out why it
wasn't doing ANYTHING when I declared an FK reference at column level.

Things like that are, sadly, kinda rampant in MySQL.

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.

On Wed, Oct 05, 2005 at 10:50:47PM -0700, Joshua D. Drake wrote:

More generally, it's worth making the point that a lot of MySQL's "brand
new in 5.0" features have been in Postgres for a *long* time, and are
therefore likely to be both more stable and better-performing than
MySQL's first cut at them.

Some specific things could be: Their "initial support" for triggers ;)
Also technically we
do have updateable views via rules.

Actually, is that even a 'technically'? If memory serves, both Oracle
and DB2 have ways to handle updates on views that are not automatically
updateable. What we're missing are *automatically* updateable views.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote:

But what really bugs me is that some things that ARE bugs simply aren't
getting fixed and probably won't. Specifically, while mysql understands
fk references made at a table level, it simply ignores, without error,
warning, or notice, fk references made in a column. arg... Very
frustrating. If they just didn't support that syntax it would be much
less bothersome, since I'd try it, get an error, and try the other
syntax. Instead, I spent an afternoon trying to figure out why it
wasn't doing ANYTHING when I declared an FK reference at column level.

Things like that are, sadly, kinda rampant in MySQL.

Are you aware of the MySQL Gotchas website (just google it)? Any time
you see MySQL being stupid about something you should probably check
there first to see if it's a "feature".
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Now this is rather useful in my opinion. I will be passing it on to some
of my collegues.

Aly.

On Thu, 6 Oct 2005, Jim C. Nasby wrote:

On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote:

But what really bugs me is that some things that ARE bugs simply aren't
getting fixed and probably won't. Specifically, while mysql understands
fk references made at a table level, it simply ignores, without error,
warning, or notice, fk references made in a column. arg... Very
frustrating. If they just didn't support that syntax it would be much
less bothersome, since I'd try it, get an error, and try the other
syntax. Instead, I spent an afternoon trying to figure out why it
wasn't doing ANYTHING when I declared an FK reference at column level.

Things like that are, sadly, kinda rampant in MySQL.

Are you aware of the MySQL Gotchas website (just google it)? Any time
you see MySQL being stupid about something you should probably check
there first to see if it's a "feature".

--
Aly S.P Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

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

PostgreSQL does not run in Windows 98

There is a LOT of customers running Windows 98 .

So I must switch to a Firebird, am I right ?

Andrus.

On Thu, 2005-10-06 at 12:23, Jim C. Nasby wrote:

On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote:

But what really bugs me is that some things that ARE bugs simply aren't
getting fixed and probably won't. Specifically, while mysql understands
fk references made at a table level, it simply ignores, without error,
warning, or notice, fk references made in a column. arg... Very
frustrating. If they just didn't support that syntax it would be much
less bothersome, since I'd try it, get an error, and try the other
syntax. Instead, I spent an afternoon trying to figure out why it
wasn't doing ANYTHING when I declared an FK reference at column level.

Things like that are, sadly, kinda rampant in MySQL.

Are you aware of the MySQL Gotchas website (just google it)? Any time
you see MySQL being stupid about something you should probably check
there first to see if it's a "feature".

Oh yeah, very aware. What's amazed me is how often I find something
that's majorly wrong that isn't in that list. For instance, this
particular problem isn't on the gotcha page, although lots of other
constraint issues are. Sadly, after talking to the author of the innodb
table handler, I get the feeling this one isn't going to change.

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

PostgreSQL does not run in Windows 98

There is a LOT of customers running Windows 98 .

So I must switch to a Firebird, am I right ?

You can run PostgreSQL on Cygwin on Win98, I think.

But ifyou're running your database server on win98, you obviously don't
care much about your data :)

(PostgreSQL *client* tools and drivers run fine on Windows 98, btw)

//Magnus

On 10/6/05, Scott Marlowe <smarlowe@g2switchworks.com> wrote:

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.

Hmm, maybe it would be worth a while to add (or rather move) a section
about programming updatable views between "The Rule System" and
"Procedural Languages". The point could be called:

"Updatable Views Using Triggers"

This would make it much easier for beginners to find the subject, and also
would make easier to support "yes, we do have updatable statements, only
its not automated" statement with just a link to right point in
documentation.

In short: make it more visible. ;)

Regards,
Dawid

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

PostgreSQL does not run in Windows 98

There is a LOT of customers running Windows 98 .

So I must switch to a Firebird, am I right ?

We run on Windoews 98 using Cygwin, I think.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073