Re: PostgreSQL 8.1 vs. MySQL 5.0?

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

Thanks,
CSN

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

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(at)commandprompt(dot)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(at)commandprompt(dot)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

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:
> 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
>
> Thanks,
> CSN
>
>
>
> __________________________________
> Yahoo! Mail - PC Magazine Editors' Choice 2005
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

cool_screen_name90001(at)yahoo(dot)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(at)~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(at)commandprompt(dot)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(at)pervasive(dot)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(at)pervasive(dot)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(dot)dharshi(at)telus(dot)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.

On 10/6/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 2005-10-06 at 21:40 +0300, 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 ?

Over MySQL, yes. However since not even Microsoft supports Windows 98
anymore, it is better to update them.

Sincerely,

Joshua D. Drake

>
> Andrus.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
--
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/

--- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:

> On Wed, 2005-10-05 at 23:41, Tom Lane wrote:
> > "Joshua D. Drake" <jd(at)commandprompt(dot)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.
>

What's the difference between a fk at the table level
vs. column level? The only fk's I've used are one
column referencing another.

CSN

______________________________________________________
Yahoo! for Good
Donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/

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

http://sql-info.de/mysql/gotchas.html

Of course, one should probably also look at the PostgreSQL Gotchas page
(same guy) just to be fair :-)

http://sql-info.de/postgresql/postgres-gotchas.html

Now whether or not those are still valid or not I have no idea...

-philip

--- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
>
> 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.

Would that be possible with table partitions? Or
Slony?

CSN

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

Support for windows 98 was infact extended to June 2006:
http://support.microsoft.com/gp/lifean1

Alex

On 10/6/05, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> On Thu, 2005-10-06 at 21:40 +0300, 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 ?
>
> Over MySQL, yes. However since not even Microsoft supports Windows 98
> anymore, it is better to update them.
>
> Sincerely,
>
> Joshua D. Drake
>
>
> >
> > Andrus.
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> --
> 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/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

On Thu, 2005-10-06 at 14:35, CSN wrote:
> --- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> 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.
> >
>
> What's the difference between a fk at the table level
> vs. column level? The only fk's I've used are one
> column referencing another.

It's just where they're defined. See this bug for an explanation:

http://bugs.mysql.com/bug.php?id=13301

Dawid Kuroczko wrote:
> "Updatable Views Using Triggers"
>
> This would make it much easier for beginners to find the subject,

Updatable views do not make use of triggers.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Thu, Oct 06, 2005 at 10:30:26AM -0500, Scott Marlowe wrote:
>
> Information Schema: MySQL's support of this looks fairly extensive.

But PostgreSQL's is pretty good, too, last I looked.

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

What does "Instance Manager" buy you that ssh doesn't? (For bonus
points, what does ssh get you that Instance Manager doesn't? Hint: I
have a Symbian UIQ phone. Google for "PuTTY".)

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

Well, to be fair, one of the Open Group's XA targets is actual
distributed data sets, and not just reliability through redundancy.
So MySQL's implementation appears to be enough to support the former
in some ways. What seems more troublesome to me is that if a machine
fails after the PREPARE step succeds, and then the client
disconnects, the transaction is automatically rolled back and can't
be recovered. I haven't figured out yet whether this is merely
dodgy, or an outright violation of the spec.

A
--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
It is above all style through which power defers to reason.
--J. Robert Oppenheimer

On Thu, Oct 06, 2005 at 12:35:38PM -0700, CSN wrote:
> Scott Marlowe <smarlowe(at)g2switchworks(dot)com> 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.
>
> What's the difference between a fk at the table level
> vs. column level? The only fk's I've used are one
> column referencing another.

He means the way the foreign key constraint is defined. In MySQL,
defining the constraint as part of column definition has no effect:

CREATE TABLE bar (
fooid integer NOT NULL REFERENCES foo (id)
) TYPE innodb;

The database accepts the above without warning but won't enforce
the foreign key constraint. One must write this instead:

CREATE TABLE bar (
fooid integer NOT NULL,
FOREIGN KEY (fooid) REFERENCES foo (id)
) TYPE innodb;

Also, notice the "TYPE innodb" clause of the CREATE TABLE statement.
The default table type in MySQL is MyISAM, which doesn't support
foreign key contraints at all, but which will silently allow you
to declare them. If you haven't changed the default table type,
then you must remember to specify that you want an InnoDB table,
or else your REFERENCES clauses are nothing but documentation.

--
Michael Fuhr

On Thu, 2005-10-06 at 12:40 -0700, CSN wrote:
> --- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> >
> > 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.
>
> Would that be possible with table partitions? Or
> Slony?

No. This is a actual cross database kind of thing. That is why
you need dblink. Table partitioning is same type of data multiple
tables.

Slony like Mammoth Replicator doesn't give you this either as we
just mirror (replicate) the data.

Sincerely,

Joshua D. Drake

>
> CSN
>
>
>
> __________________________________
> Yahoo! Mail - PC Magazine Editors' Choice 2005
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
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/

On Thu, Oct 06, 2005 at 09:10:53PM +0200, Dawid Kuroczko wrote:
> On 10/6/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)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. ;)

s/triggers/rules/, but I agree. It would be great to have a prominent,
succinct example of creating an updateable view. I think there's
something close in the docs currently, but you have to dig through a
pretty big chapter to find it.

I don't know that it makes sense to promote it to 34 (putting it in the
TOC), but I do think it should be linked from the CREATE VIEW syntax
page.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)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 01:46:29PM -0500, Scott Marlowe wrote:
> 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.

Please submit any missing items to the author. If he refuses them send
them to me and I'll start an addendum.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)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 12:40:49PM -0700, CSN wrote:
>
> --- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> >
> > 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.
>
> Would that be possible with table partitions? Or
> Slony?

Slony would give you a loose approximation. Table partitioning is
unrelated. Better yet, I don't know of any reason why you can't define a
view using dblink that would duplicate the features of a federated
system. Of course it would be easier if it was in the back-end...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On 10/7/05, Jim C. Nasby <jnasby(at)pervasive(dot)com> wrote:
> On Thu, Oct 06, 2005 at 01:46:29PM -0500, Scott Marlowe wrote:
> > On Thu, 2005-10-06 at 12:23, Jim C. Nasby wrote:
(...)
> > > 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.
>
> Please submit any missing items to the author. If he refuses them send
> them to me and I'll start an addendum.

The author writes: all additions, corrections etc. most welcome. I
haven't had a chance to update the site much recently, but I'm slowly
going through the list to update it for MySQL 5.

Ian Barwick

Scott Marlowe wrote:
>
> It's just where they're defined. See this bug for an explanation:

And a table-level foreign-key can involve more than one column of course.

--
Richard Huxton
Archonet Ltd

On Thursday 06 October 2005 17:31, Michael Fuhr wrote:
> On Thu, Oct 06, 2005 at 12:35:38PM -0700, CSN wrote:
> > Scott Marlowe <smarlowe(at)g2switchworks(dot)com> 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.
> >
> > What's the difference between a fk at the table level
> > vs. column level? The only fk's I've used are one
> > column referencing another.
>
> He means the way the foreign key constraint is defined. In MySQL,
> defining the constraint as part of column definition has no effect:
>
> CREATE TABLE bar (
> fooid integer NOT NULL REFERENCES foo (id)
> ) TYPE innodb;
>
> The database accepts the above without warning but won't enforce
> the foreign key constraint. One must write this instead:
>
> CREATE TABLE bar (
> fooid integer NOT NULL,
> FOREIGN KEY (fooid) REFERENCES foo (id)
> ) TYPE innodb;
>
> Also, notice the "TYPE innodb" clause of the CREATE TABLE statement.
> The default table type in MySQL is MyISAM, which doesn't support
> foreign key contraints at all, but which will silently allow you
> to declare them. If you haven't changed the default table type,
> then you must remember to specify that you want an InnoDB table,
> or else your REFERENCES clauses are nothing but documentation.

I'm working on porting mediawiki to postgresql and was really puzzled by the
following:

CREATE TABLE trackbacks (
tb_id INTEGER AUTO_INCREMENT PRIMARY KEY,
tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
tb_title VARCHAR(255) NOT NULL,
tb_url VARCHAR(255) NOT NULL,
tb_ex TEXT,
tb_name VARCHAR(255),

INDEX (tb_page)
);

I couldn't figure out why they weren't specifying type = innodb for the table,
but then figured they must have declared it some place else or something...
but now I see that even that wouldn't work. Makes you wonder if my$ql users
realize this behavior or not....I would have to guess not because otherwise
why would you use this type of syntax at all?

(And people claim my$ql is eaiser to use? I still don't get that one)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Thursday 06 October 2005 18:18, Jim C. Nasby wrote:
> On Thu, Oct 06, 2005 at 12:40:49PM -0700, CSN wrote:
> > --- Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> > > 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.
> >
> > Would that be possible with table partitions? Or
> > Slony?
>
> Slony would give you a loose approximation. Table partitioning is
> unrelated. Better yet, I don't know of any reason why you can't define a
> view using dblink that would duplicate the features of a federated
> system. Of course it would be easier if it was in the back-end...

You can, but if the view runs against a large table you're screwed since pg
can't optimize complex queries into the inital query to set up the view.

Not sure if my$ql is any smarter about this, but that's the first thing to
look for if you were to investigate how well it worked.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Fri, Oct 07, 2005 at 10:45:06AM -0400, Robert Treat wrote:
> On Thursday 06 October 2005 17:31, Michael Fuhr wrote:
> >
> > Also, notice the "TYPE innodb" clause of the CREATE TABLE
> > statement. The default table type in MySQL is MyISAM, which
> > doesn't support foreign key contraints at all, but which will
> > silently allow you to declare them. If you haven't changed the
> > default table type, then you must remember to specify that you
> > want an InnoDB table, or else your REFERENCES clauses are nothing
> > but documentation.
>
> I'm working on porting mediawiki to postgresql and was really
> puzzled by the following:
>
> CREATE TABLE trackbacks (
> tb_id INTEGER AUTO_INCREMENT PRIMARY KEY,
> tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
> tb_title VARCHAR(255) NOT NULL,
> tb_url VARCHAR(255) NOT NULL,
> tb_ex TEXT,
> tb_name VARCHAR(255),
> INDEX (tb_page)
> );
>
> I couldn't figure out why they weren't specifying type = innodb for
> the table, but then figured they must have declared it some place
> else or something... but now I see that even that wouldn't work.
> Makes you wonder if my$ql users realize this behavior or not....

As a rule, they don't expect the database to handle any data
integrity. This is a quite reasonable (lack of) expectation from that
product. The trouble starts to happen when they run across DBMSs that
*can* do this.

> I would have to guess not because otherwise why would you use this
> type of syntax at all?

Right in one.

> (And people claim my$ql is eaiser to use? I still don't get that
> one)

I was confused, too. Then it dawned on me. When people say, "MySQL
is easier to use," what they really mean is, "I started from nothing.
I worked hard getting used to these quirks, and I'll be *damned* if
I'll consider all that painful effort wasted and start over." It
reminds me a lot of the kind of mental gymnastics that cause people
not to call the law when they realize they've been taken in by a con
artist. In both cases, the emotional investment in not being wrong on
an important matter is too big.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

On 10/6/2005 4:37 AM, Tzvetan Tzankov wrote:
> 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 ...)

AFAIK MySQL's fulltext indexing is only supported on MyIsam tables, so
if you want to use it, you lose ACID, hot backup and a couple other nice
things entirely for that part of your data. Many MySQL users still
believe that the pluggable storage engine design is an advantage ... I
think one storage engine that supports the full feature set is better.

Jan

>
> 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
>>
>> Thanks,
>> CSN
>>
>>
>>
>> __________________________________
>> Yahoo! Mail - PC Magazine Editors' Choice 2005
>> http://mail.yahoo.com
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

> On 10/6/2005 4:37 AM, Tzvetan Tzankov wrote:
>
> > 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 ...)
>
>
> AFAIK MySQL's fulltext indexing is only supported on
MyIsam tables, so if you want to use it, you lose
ACID, hot backup and a couple other nice things
entirely for that part of your data. Many MySQL users
still believe that the pluggable storage engine design
is an advantage ... I think one storage engine that
supports the full feature set is better.
>
> Jan

I agree - MySQL really has a confusing array of
different database engines:

# MyISAM
# MERGE
# ISAM
# HEAP
# InnoDB
# BDB or BerkeleyDB Tables
# Example
# Archive
# Federated
# CSV
# Blackhole
# NDB Cluster

http://dev.mysql.com/doc/mysql/en/storage-engines.html

CSN

__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

On Oct 8, 2005, at 2:04 PM, CSN wrote:

>> AFAIK MySQL's fulltext indexing is only supported on
> MyIsam tables, so if you want to use it, you lose
> ACID,

For me, the fact that to use a feature means one needs to give up
ACIDity ends any debate on which DB to choose, and I'm not even a power
user.

Scott Marlowe wrote:

>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).
>>
>>
><snip>
>
>Instance Manager: Uniquely MySQL. It allows things like starting and
>stopping the database remotely.
>
>
I cannot think of a reason ever to need this when we have OpenSSH....
<snip>

>
>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.
>
>
DBI-Link also has a wider range of functionality and can access tables
on MySQL, Oracle, DB2, etc. servers.
<snip>

>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?
>
>
Or worse, do you want an RDBMS where an application can turn of data
verification and then insert dates like Feb 31, 2005? (Strict mode can
be enabled/disabled per connection/session)
<snip>

Best Wishes,
Chris Travers
Metatron Technology Consulting

Alex Turner wrote:

> Support for windows 98 was infact extended to June 2006:
> http://support.microsoft.com/gp/lifean1
>
Right....

And it was extended again last year as it was supposed to extend this
last June, and Last June, etc. We will see if it is not extended again....

But if you are running an production database on Windows 98 you have
bigger problems than support from Microsoft....

Best Wishes,
Chris Travers
Metatron Technology Consulting

On Thu, 2005-10-13 at 00:32, Chris Travers wrote:
> Scott Marlowe wrote:

> >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?
> >
> >
> Or worse, do you want an RDBMS where an application can turn of data
> verification and then insert dates like Feb 31, 2005? (Strict mode can
> be enabled/disabled per connection/session)
> <snip>

Oh my dear god, you have got be kidding! No way to lock out bad data
then? ugh.

<snip>
>
>
> >
> >Instance Manager: Uniquely MySQL. It allows things like starting and
> >stopping the database remotely.
> >
> >
> I cannot think of a reason ever to need this when we have OpenSSH....
> <snip>

I'm just curious, but how does this work for a windows box?

>
> >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.
> >
> >
> DBI-Link also has a wider range of functionality and can access tables
> on MySQL, Oracle, DB2, etc. servers.
> <snip>
>
>
If I had just one wish for postgresql it would be to support cross-database
queries like Oracle. This is a HUGE pain in the ass, and DBI-Link syntax is
clunky as hell.

I would switch to Oracle tomorrow if I had the budget just because of this
feature. I have data across four and five databases that are related, and I
need to build cross database views, and do data munging _easily_, DBI link
is far from easy, and I suspect that it's performance is far from stellar,
but I've not actualy benched it. For me this needs to be a core database
feature. I have certain legal problems that are also an issue where I have
to keep data that is related in seperate databases, and my clients _want_ me
to cross join it for select purposes, but I'm legaly required to keep it in
a seperate database.

Maybe it's just difference shock - Postgresql<>Oracle so I'm scared ;), but
I don't like dblink very much ;)

<snip>

Alex Turner
NetEconomist

Actualy to me, it seems like postgres is a perfect partner for MS Access.
Throw out Jet, and use Pgsql. It's infinately better than Jet, so operating
in a Win98 environment seems reasonable in this scenario.

I swear you could build a business just building MS Access apps on a
Postgresql databases so that they can actualy _scale_ when a business grows.

Alex

On 10/13/05, Chris Travers <chris(at)travelamericas(dot)com> wrote:
>
> Alex Turner wrote:
>
> > Support for windows 98 was infact extended to June 2006:
> > http://support.microsoft.com/gp/lifean1
> >
> Right....
>
> And it was extended again last year as it was supposed to extend this
> last June, and Last June, etc. We will see if it is not extended again....
>
> But if you are running an production database on Windows 98 you have
> bigger problems than support from Microsoft....
>
> Best Wishes,
> Chris Travers
> Metatron Technology Consulting
>

On Thu, Oct 13, 2005 at 01:00:03PM -0400, Alex Turner wrote:
> <snip>
> > >Instance Manager: Uniquely MySQL. It allows things like starting
> > >and stopping the database remotely.
> > >
> > I cannot think of a reason ever to need this when we have
> > OpenSSH.... <snip>
>
> I'm just curious, but how does this work for a windows box?

You can get many varieties of SSH for windows. Probably the easiest
to install is the one that comes with Cygwin.

> > >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.
> > >
> > DBI-Link also has a wider range of functionality and can access
> > tables on MySQL, Oracle, DB2, etc. servers. <snip>
> >
> If I had just one wish for postgresql it would be to support
> cross-database queries like Oracle. This is a HUGE pain in the ass,
> and DBI-Link syntax is clunky as hell.

I'm the author of DBI-Link, and I am *always* eager to hear
suggestions for how to improve it. Concrete suggestions that come
with resources like testers, test environments, test plans, and so
forth take priority :)

> I would switch to Oracle tomorrow if I had the budget just because
> of this feature. I have data across four and five databases that
> are related, and I need to build cross database views, and do data
> munging _easily_, DBI link is far from easy, and I suspect that it's
> performance is far from stellar,

I'm working on this. The next performance improvement relies on pg
8.1 features of PL/Perl. Further improvements...well, you're right.
I'll need a generic way to get predicates at run time from one place
and push them to an opaque place, in my case, a set-returning
function, at run time.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

Am Donnerstag, den 13.10.2005, 13:00 -0400 schrieb Alex Turner:
...
>
>
>
> If I had just one wish for postgresql it would be to support
> cross-database queries like Oracle. This is a HUGE pain in the ass,
> and DBI-Link syntax is clunky as hell.
>
> I would switch to Oracle tomorrow if I had the budget just because of
> this feature. I have data across four and five databases that are
> related, and I need to build cross database views, and do data munging
> _easily_, DBI link is far from easy, and I suspect that it's
> performance is far from stellar, but I've not actualy benched it. For
> me this needs to be a core database feature. I have certain legal
> problems that are also an issue where I have to keep data that is
> related in seperate databases, and my clients _want_ me to cross join
> it for select purposes, but I'm legaly required to keep it in a
> seperate database.

Why not put them in separate schemas and tell the customers these
are separate databases? From outside it looks exactly like it.
You can constraint the users to the different schemas and still
join between the tables at will. See schema-searchpath and
stuff for sticking users to a schema.

HTH
Tino

I could, but it would breach the terms of our contract. Our contract with
the data providers clearly specifies seperate databases ;), so I'm kind of
tied down by the legalese.

I have certainly considered just putting them in schemas, but I talked to
legal and they didn't really like that idea ;).

Alex

On 10/13/05, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
>
> Am Donnerstag, den 13.10.2005, 13:00 -0400 schrieb Alex Turner:
> ...
> >
> >
> >
> > If I had just one wish for postgresql it would be to support
> > cross-database queries like Oracle. This is a HUGE pain in the ass,
> > and DBI-Link syntax is clunky as hell.
> >
> > I would switch to Oracle tomorrow if I had the budget just because of
> > this feature. I have data across four and five databases that are
> > related, and I need to build cross database views, and do data munging
> > _easily_, DBI link is far from easy, and I suspect that it's
> > performance is far from stellar, but I've not actualy benched it. For
> > me this needs to be a core database feature. I have certain legal
> > problems that are also an issue where I have to keep data that is
> > related in seperate databases, and my clients _want_ me to cross join
> > it for select purposes, but I'm legaly required to keep it in a
> > seperate database.
>
> Why not put them in separate schemas and tell the customers these
> are separate databases? From outside it looks exactly like it.
> You can constraint the users to the different schemas and still
> join between the tables at will. See schema-searchpath and
> stuff for sticking users to a schema.
>
> HTH
> Tino
>
>

If separate databases are required by contract, and oracle lets you
treat multiple databases like one big one, wouldn't using oracle breach
your contract then? In this case, PostgreSQL's schemas and Oracle's
separate databases are functionally identical, nomenclature aside.

On Thu, 2005-10-13 at 13:58, Alex Turner wrote:
> I could, but it would breach the terms of our contract. Our contract
> with the data providers clearly specifies seperate databases ;), so
> I'm kind of tied down by the legalese.
>
> I have certainly considered just putting them in schemas, but I talked
> to legal and they didn't really like that idea ;).
>
> Alex
>
> On 10/13/05, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> Am Donnerstag, den 13.10.2005, 13:00 -0400 schrieb Alex
> Turner:
> ...
> >
> >
> >
> > If I had just one wish for postgresql it would be to support
> > cross-database queries like Oracle. This is a HUGE pain in
> the ass,
> > and DBI-Link syntax is clunky as hell.
> >
> > I would switch to Oracle tomorrow if I had the budget just
> because of
> > this feature. I have data across four and five databases
> that are
> > related, and I need to build cross database views, and do
> data munging
> > _easily_, DBI link is far from easy, and I suspect that it's
> > performance is far from stellar, but I've not actualy
> benched it. For
> > me this needs to be a core database feature. I have certain
> legal
> > problems that are also an issue where I have to keep data
> that is
> > related in seperate databases, and my clients _want_ me to
> cross join
> > it for select purposes, but I'm legaly required to keep it
> in a
> > seperate database.
>
> Why not put them in separate schemas and tell the customers
> these
> are separate databases? From outside it looks exactly like it.
> You can constraint the users to the different schemas and
> still
> join between the tables at will. See schema-searchpath and
> stuff for sticking users to a schema.
>
> HTH
> Tino
>
>

Of course, but _legaly_ we would be complying with the contract ;)

Alex

On 10/13/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
>
> If separate databases are required by contract, and oracle lets you
> treat multiple databases like one big one, wouldn't using oracle breach
> your contract then? In this case, PostgreSQL's schemas and Oracle's
> separate databases are functionally identical, nomenclature aside.
>
> On Thu, 2005-10-13 at 13:58, Alex Turner wrote:
> > I could, but it would breach the terms of our contract. Our contract
> > with the data providers clearly specifies seperate databases ;), so
> > I'm kind of tied down by the legalese.
> >
> > I have certainly considered just putting them in schemas, but I talked
> > to legal and they didn't really like that idea ;).
> >
> > Alex
> >
> > On 10/13/05, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> > Am Donnerstag, den 13.10.2005, 13:00 -0400 schrieb Alex
> > Turner:
> > ...
> > >
> > >
> > >
> > > If I had just one wish for postgresql it would be to support
> > > cross-database queries like Oracle. This is a HUGE pain in
> > the ass,
> > > and DBI-Link syntax is clunky as hell.
> > >
> > > I would switch to Oracle tomorrow if I had the budget just
> > because of
> > > this feature. I have data across four and five databases
> > that are
> > > related, and I need to build cross database views, and do
> > data munging
> > > _easily_, DBI link is far from easy, and I suspect that it's
> > > performance is far from stellar, but I've not actualy
> > benched it. For
> > > me this needs to be a core database feature. I have certain
> > legal
> > > problems that are also an issue where I have to keep data
> > that is
> > > related in seperate databases, and my clients _want_ me to
> > cross join
> > > it for select purposes, but I'm legaly required to keep it
> > in a
> > > seperate database.
> >
> > Why not put them in separate schemas and tell the customers
> > these
> > are separate databases? From outside it looks exactly like it.
> > You can constraint the users to the different schemas and
> > still
> > join between the tables at will. See schema-searchpath and
> > stuff for sticking users to a schema.
> >
> > HTH
> > Tino
> >
> >
>

I wouldn't be so sure of that. IT might be that in order to be
considered to be complying with the contract you have to setup oracle in
such a way as to disable any database to database access / joining.
Seems to me the second you can run a query that hits both databases you
might well be in breach of contract, depending on the terminology used.

On Thu, 2005-10-13 at 14:44, Alex Turner wrote:
> Of course, but _legaly_ we would be complying with the contract ;)
>
> Alex
>
> On 10/13/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> If separate databases are required by contract, and oracle
> lets you
> treat multiple databases like one big one, wouldn't using
> oracle breach
> your contract then? In this case, PostgreSQL's schemas and
> Oracle's
> separate databases are functionally identical, nomenclature
> aside.
>
> On Thu, 2005-10-13 at 13:58, Alex Turner wrote:
> > I could, but it would breach the terms of our contract. Our
> contract
> > with the data providers clearly specifies seperate databases
> ;), so
> > I'm kind of tied down by the legalese.
> >
> > I have certainly considered just putting them in schemas,
> but I talked
> > to legal and they didn't really like that idea ;).
> >
> > Alex
> >
> > On 10/13/05, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> > Am Donnerstag, den 13.10.2005, 13:00 -0400 schrieb
> Alex
> > Turner:
> > ...
> > >
> > >
> > >
> > > If I had just one wish for postgresql it would be
> to support
> > > cross-database queries like Oracle. This is a
> HUGE pain in
> > the ass,
> > > and DBI-Link syntax is clunky as hell.
> > >
> > > I would switch to Oracle tomorrow if I had the
> budget just
> > because of
> > > this feature. I have data across four and five
> databases
> > that are
> > > related, and I need to build cross database views,
> and do
> > data munging
> > > _easily_, DBI link is far from easy, and I suspect
> that it's
> > > performance is far from stellar, but I've not
> actualy
> > benched it. For
> > > me this needs to be a core database feature. I
> have certain
> > legal
> > > problems that are also an issue where I have to
> keep data
> > that is
> > > related in seperate databases, and my clients
> _want_ me to
> > cross join
> > > it for select purposes, but I'm legaly required to
> keep it
> > in a
> > > seperate database.
> >
> > Why not put them in separate schemas and tell the
> customers
> > these
> > are separate databases? From outside it looks
> exactly like it.
> > You can constraint the users to the different
> schemas and
> > still
> > join between the tables at will. See
> schema-searchpath and
> > stuff for sticking users to a schema.
> >
> > HTH
> > Tino
> >
> >
>

heh... anythings possible ;) I guess we are okay for now then seeing that we
are using postgresql with no dblinkg ;)

Alex

On 10/13/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
>
> I wouldn't be so sure of that. IT might be that in order to be
> considered to be complying with the contract you have to setup oracle in
> such a way as to disable any database to database access / joining.
> Seems to me the second you can run a query that hits both databases you
> might well be in breach of contract, depending on the terminology used.
>
> On Thu, 2005-10-13 at 14:44, Alex Turner wrote:
> > Of course, but _legaly_ we would be complying with the contract ;)
> >
> > Alex
> >
> > On 10/13/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> > If separate databases are required by contract, and oracle
> > lets you
> > treat multiple databases like one big one, wouldn't using
> > oracle breach
> > your contract then? In this case, PostgreSQL's schemas and
> > Oracle's
> > separate databases are functionally identical, nomenclature
> > aside.
> >
> > On Thu, 2005-10-13 at 13:58, Alex Turner wrote:
> > > I could, but it would breach the terms of our contract. Our
> > contract
> > > with the data providers clearly specifies seperate databases
> > ;), so
> > > I'm kind of tied down by the legalese.
> > >
> > > I have certainly considered just putting them in schemas,
> > but I talked
> > > to legal and they didn't really like that idea ;).
> > >
> > > Alex
> > >
> > > On 10/13/05, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> > > Am Donnerstag, den 13.10.2005, 13:00 -0400 schrieb
> > Alex
> > > Turner:
> > > ...
> > > >
> > > >
> > > >
> > > > If I had just one wish for postgresql it would be
> > to support
> > > > cross-database queries like Oracle. This is a
> > HUGE pain in
> > > the ass,
> > > > and DBI-Link syntax is clunky as hell.
> > > >
> > > > I would switch to Oracle tomorrow if I had the
> > budget just
> > > because of
> > > > this feature. I have data across four and five
> > databases
> > > that are
> > > > related, and I need to build cross database views,
> > and do
> > > data munging
> > > > _easily_, DBI link is far from easy, and I suspect
> > that it's
> > > > performance is far from stellar, but I've not
> > actualy
> > > benched it. For
> > > > me this needs to be a core database feature. I
> > have certain
> > > legal
> > > > problems that are also an issue where I have to
> > keep data
> > > that is
> > > > related in seperate databases, and my clients
> > _want_ me to
> > > cross join
> > > > it for select purposes, but I'm legaly required to
> > keep it
> > > in a
> > > > seperate database.
> > >
> > > Why not put them in separate schemas and tell the
> > customers
> > > these
> > > are separate databases? From outside it looks
> > exactly like it.
> > > You can constraint the users to the different
> > schemas and
> > > still
> > > join between the tables at will. See
> > schema-searchpath and
> > > stuff for sticking users to a schema.
> > >
> > > HTH
> > > Tino
> > >
> > >
> >
>

Am Donnerstag, den 13.10.2005, 15:44 -0400 schrieb Alex Turner:
> Of course, but _legaly_ we would be complying with the contract ;)
>
> Alex
>
> On 10/13/05, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> If separate databases are required by contract, and oracle
> lets you
...
> >
> > On 10/13/05, Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> > Am Donnerstag, den 13.10.2005, 13:00 -0400 schrieb
> Alex
> > Turner:
> > ...
...

Btw, would it be possible to stick with usenet/mailinglist
usual quoting? Like not posting in HTML and write comments
below / between sensibly choosen excerpts?

See http://www.netmeister.org/news/learn2quote.html

For the poor fellows out there forced to use an incapable
client, there is a little bit relief:

http://home.in.tum.de/~jain/software/outlook-quotefix/

Greets
Tino

Very much a description of the Business I am in.

For all the criticism leveled at it, I still think that as a rich
Database Client that permits really rapid development of Database driven
applications Access is unbeatable. Pair it with a good Database server
and it is the perfect combination.

That said I would love if there really were an OSS alternative to Access
(I would settle even for a closed source competitor whose product
philosophy is standards compliance and offers a range of interfaces
(JDBC, ODBC, Native Drivers)).

I had high hopes for the new Base Application in Open Office but have
been disappointed - hopefully continued development will see it improve
but at this stage it is a long way from matching Access 97 (or even 95)
as a Database client. Which is to say that it is a decade behind MS Access.

Although there is a lot to like about Access as a DB client and RAD tool
for Database centric applications I worry that Microsoft's desire to use
Access to push SQL Server sales, as well as it's fears about OSS, and
it's antipathy towards Java, means that the future of a Technology stack
built on Postgresql and MS Access looks uncertain.

If someone knows of a RAD/DB client that offers what Access does -
especially the rapidity with which rich forms can be created, the
capabilities and ease of use of the Visual query builder, the breadth of
data analysis and manipulation tools that can be run from and on the
client, the integration of a capable Report editor which is usable with
a small amount of training by the end user, and the ability to easily
talk to a spreadsheet or word processor, I would love to hear of it.

As it is I find myself combating the FUD that Access really only plays
nice with SQL Server all the time, and the fact is that Microsoft's
commitment to ODBC is lukewarm, and it will probably be a cold day in
hell before Access ships with a JDBC driver, or for that matter sports a
well documented API for developing native drivers for Particular
Database systems.

The absolute necessity of Outlook for many people used to be one of the
key strategic locks that Microsoft had, but the development of Evolution
on the client end has made exchange competitors like OpenGroupware that
much more viable, and the converse is true.

There has been massive growth and improvement in the OSS database server
segment over the past 5 to 7 years, but it has not been matched on the
client end - admittedly the spread of web based applications has
disguised the need for such development, but the huge number of SME's
with 5-50 employees still need a decent rich Database client if they are
to replace their often expensive and primitive off the shelf solutions
with tailored solutions. And here the cost is overwhelmingly represented
by developer time. With Access I can spend time make sure my
Postgresql back end is all that it can be, knowing that when the time
comes I can create most of the forms that I will need with a single
click of the Autoform button , and ten minutes later, after cleaning up
a few captions and adding some drop down lists, and then a bit of extra
functionality with VBA, such as pumping a Query into Form letters or
charts or a report, I am done. And because these are not the sort of
companies who can afford a full time DBA, it is handy that Access
permits a mildly technically savvy user to extend it, which means the
client can add customizations when they need to provided you have
documented the schema for them.

Doing the same thing with either a web scripting language or a Java IDE
is still a process that takes 4 to 6 times as long, (when equivalent
functionality is even available) and this pretty much prices most SME's
out of the market except for either trivial applications (which is what
the majority of web based applications in this market are) or for
projects that become quite risky for developer and client alike.

Johan Wehtje

Alex Turner wrote:
> Actualy to me, it seems like postgres is a perfect partner for MS
> Access. Throw out Jet, and use Pgsql. It's infinately better than Jet,
> so operating in a Win98 environment seems reasonable in this scenario.
>
> I swear you could build a business just building MS Access apps on a
> Postgresql databases so that they can actualy _scale_ when a business grows.
>
> Alex
>
> On 10/13/05, *Chris Travers* <chris(at)travelamericas(dot)com
> <mailto:chris(at)travelamericas(dot)com>> wrote:
>
> Alex Turner wrote:
>
> > Support for windows 98 was infact extended to June 2006:
> > http://support.microsoft.com/gp/lifean1
> >
> Right....
>
> And it was extended again last year as it was supposed to extend this
> last June, and Last June, etc. We will see if it is not extended
> again....
>
> But if you are running an production database on Windows 98 you have
> bigger problems than support from Microsoft....
>
> Best Wishes,
> Chris Travers
> Metatron Technology Consulting
>
>

Someone trying to stick microsoft yet another place
they don't belong.

--- Johan Wehtje <joweht(at)tpgi(dot)com(dot)au> wrote:

> Very much a description of the Business I am in.
>
> For all the criticism leveled at it, I still think
> that as a rich
> Database Client that permits really rapid
> development of Database driven
> applications Access is unbeatable. Pair it with a
> good Database server
> and it is the perfect combination.

__________________________________
Yahoo! Music Unlimited
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

I doubt you read the rest of the post otherwise I don't think you would
make that comment.

I think there really is a need for a rich DB client that allows Rapid
development and is easy to link to an office Suite. To be useful to a
business a database needs the applications built on top of it, and like
I said if someone can show me a quicker way of doing this than Access
and still lets me use Postgresql for my backend without any great drama,
I would love to hear of it. My experience with Access 2000 and the
latest ODBC drivers has been very positive, but I am wary of Microsoft's
intentions for Access going forward, which I why I asked if anyone knew
of a really viable alternative that I have not tried.

The next best thing that I have tried is Delphi with some of the
libraries available from Sqlmanager or Active Query builder.

But really, for most of the situations I encounter this is a bit like
re-inventing the wheel every time someone wants a cart built. You end up
trying to build an Access like application from the ground up every time
someone wants a Db app. And that seems to me to be taking antipathy
towards Microsoft to a point where it benefits no-one.

Cheers
Johan Wehtje

Matthew Peter wrote:
> Someone trying to stick microsoft yet another place
> they don't belong.
>
>
> --- Johan Wehtje <joweht(at)tpgi(dot)com(dot)au> wrote:
>
>> Very much a description of the Business I am in.
>>
>> For all the criticism leveled at it, I still think
>> that as a rich
>> Database Client that permits really rapid
>> development of Database driven
>> applications Access is unbeatable. Pair it with a
>> good Database server
>> and it is the perfect combination.
>
>
>
> __________________________________
> Yahoo! Music Unlimited
> Access over 1 million songs. Try it free.
> http://music.yahoo.com/unlimited/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
> .
>

Johan Wehtje wrote:

> I doubt you read the rest of the post otherwise I don't think you
> would make that comment.

Personlly I think you were right. Access is a good front end, at least
in the sense that it is a hell of a lot better
than anything the OSS community has bothered to come up with. I actually
Paradox is a better choice :).

It is also a front-end that customers are comfortable with.

>
> I think there really is a need for a rich DB client that allows Rapid
> development and is easy to link to an office Suite. To be useful to a
> business a database needs the applications built on top of it, and
> like I said if someone can show me a quicker way of doing this than
> Access and still lets me use Postgresql for my backend without any
> great drama, I would love to hear of it. My experience with Access
> 2000 and the latest ODBC drivers has been very positive, but I am wary
> of Microsoft's intentions for Access going forward, which I why I
> asked if anyone knew of a really viable alternative that I have not
> tried.

FYI Open Office 2.0 database, forms, reports etc... is MUCH better than
it was. I believe you can script it as well in Python and maybe Java.

>
> The next best thing that I have tried is Delphi with some of the
> libraries available from Sqlmanager or Active Query builder.

You can use Kylix for that but I don't know its status.

>
> But really, for most of the situations I encounter this is a bit like
> re-inventing the wheel every time someone wants a cart built. You end
> up trying to build an Access like application from the ground up every
> time someone wants a Db app. And that seems to me to be taking
> antipathy towards Microsoft to a point where it benefits no-one.
>
> Cheers
> Johan Wehtje
>
> Matthew Peter wrote:
>
>> Someone trying to stick microsoft yet another place
>> they don't belong.
>>
>> --- Johan Wehtje <joweht(at)tpgi(dot)com(dot)au> wrote:
>>
>>> Very much a description of the Business I am in.
>>>
>>> For all the criticism leveled at it, I still think
>>> that as a rich Database Client that permits really rapid
>>> development of Database driven applications Access is unbeatable.
>>> Pair it with a
>>> good Database server and it is the perfect combination.
>>
>>
>>
>>
>> __________________________________ Yahoo! Music Unlimited Access over
>> 1 million songs. Try it free.
>> http://music.yahoo.com/unlimited/
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>> .
>>
>
> ---------------------------(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/

On Oct 13, 2005, at 12:00 PM, Alex Turner wrote:

>
> <snip>
>
> >
> >Instance Manager: Uniquely MySQL. It allows things like starting
> and
> >stopping the database remotely.
> >
> >
> I cannot think of a reason ever to need this when we have OpenSSH....
> <snip>
>
> I'm just curious, but how does this work for a windows box?

There are plenty of Remote Management options for Windows. One of
the common ones ships with XP Pro and allows you to start and stop
services remotely, etc.

Jeff

This thread should continue under the proper title
since it's been hi-jacked .

I didn't read your entire post. If you know how to
join a pk and fk it's not difficult to build an
effective diagram on paper and reuse the same schema
for other applications.

> > I think there really is a need for a rich DB
> client that allows Rapid
> > development and is easy to link to an office
> Suite. To be useful to a
> > business a database needs the applications built
> on top of it

Ya I watched the videos on microsofts new Mail &
sparkle applications. Mail suprisingly uses a database
backend to manage their files which may helped open
eyes of the ways they could use them in other ares of
the desktop.

For instance, w/ sparkle you could write a simple
program to do what you need as defining tables and
relationships is easy. They also have a 3d engine so
you can emerse yourself in the database and fly around
the tables!

Is it possible to bind vector interfaces to C++ apps
w/ libs like (<a
href="http://www.linuxartist.org/2d.html">ZODIUS</a>)?
I'm not sure how ENLIGHTENMENT runs their engine on
xorg but it's not vector based.

If possible I'd like to know. I don't have the time
now but in the near future I plan to find out. Maybe
someone here already knows?

It would be pretty neat to build desktop packages that
scale and stretch any resolution or device.

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