Thread

[OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-18T00:08:08Z

Sorry to interrupt, but I have had the "opportinuty" to have to work with
MySQL. This nice little gem is packed away in the reference for
mysql_use_result().

"On the other hand, you shouldn't use mysql_use_result() if you are doing
a lot of processing for each row on the client side, or if the output is
sent to a screen on which the user may type a ^S (stop scroll). This ties
up the server and prevent other threads from updating any tables from
which the data is being fetched."

How do busy web sites work like this?

What is the best way to go about creating a "plug and play," PostgreSQL
replacement for MySQL? I think the biggest problem getting PostgreSQL
accepted is that so much code is available for MySQL.

Re: [OT] MySQL is bad, but THIS bad?

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-18T00:47:40Z

Mark Woodward wrote:
> Sorry to interrupt, but I have had the "opportinuty" to have to work with
> MySQL. This nice little gem is packed away in the reference for
> mysql_use_result().
> 
> "On the other hand, you shouldn't use mysql_use_result() if you are doing
> a lot of processing for each row on the client side, or if the output is
> sent to a screen on which the user may type a ^S (stop scroll). This ties
> up the server and prevent other threads from updating any tables from
> which the data is being fetched."
> 
> How do busy web sites work like this?
> 
> What is the best way to go about creating a "plug and play," PostgreSQL
> replacement for MySQL? I think the biggest problem getting PostgreSQL
> accepted is that so much code is available for MySQL.

That reminds me of the Twilight Zone episode where the guy had a
stopwatch that stopped time:

	http://en.wikipedia.org/wiki/A_Kind_of_a_Stopwatch_(The_Twilight_Zone)

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: [OT] MySQL is bad, but THIS bad?

John DeSoi <desoi@pgedit.com> — 2006-05-18T01:35:34Z

On May 17, 2006, at 8:08 PM, Mark Woodward wrote:

> What is the best way to go about creating a "plug and play,"  
> PostgreSQL
> replacement for MySQL? I think the biggest problem getting PostgreSQL
> accepted is that so much code is available for MySQL.

http://pgfoundry.org/projects/mysqlcompat/

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Re: [OT] MySQL is bad, but THIS bad?

Florian Weimer <fw@deneb.enyo.de> — 2006-05-18T05:18:53Z

* Mark Woodward:

> "On the other hand, you shouldn't use mysql_use_result() if you are doing
> a lot of processing for each row on the client side, or if the output is
> sent to a screen on which the user may type a ^S (stop scroll). This ties
> up the server and prevent other threads from updating any tables from
> which the data is being fetched."
>
> How do busy web sites work like this?

Any system based on locking exhibits this problem.  Even with MVCC,
you can run into it if you've got multiple writers.  As a rule of
thumb, I never perform network I/O within transactions which update
the database (or "read the database", for systems without MVCC).

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-18T16:24:21Z

On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
> 
> On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
> 
> >What is the best way to go about creating a "plug and play,"  
> >PostgreSQL
> >replacement for MySQL? I think the biggest problem getting PostgreSQL
> >accepted is that so much code is available for MySQL.
> 
> 
> http://pgfoundry.org/projects/mysqlcompat/

Even better would be coming up with a compatability mode, a la what
EnterpriseDB has done for Oracle.
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Josh Berkus <josh@agliodbs.com> — 2006-05-18T16:38:51Z

All,

> > >What is the best way to go about creating a "plug and play,"
> > >PostgreSQL
> > >replacement for MySQL? I think the biggest problem getting PostgreSQL
> > >accepted is that so much code is available for MySQL.
> >
> > http://pgfoundry.org/projects/mysqlcompat/
>
> Even better would be coming up with a compatability mode, a la what
> EnterpriseDB has done for Oracle.

Um, no offense, but why?   Do we really *want* to seek out thousands of users 
who won't pay anyone for support, don't care about data integrity, and regard 
the database as an enhanced flat file?    Who adore non-standard syntax like 
`db_object_name` and REPLACE INTO?   Who want to just get free downloads and 
not contribute to a project?

Personally, I'd go after MSSQL before I bothered with MySQL.   Sure, let's 
make *migration* easier for those who wake up and smell the BS, but migration 
can (and probably should) be one-way.

If we're talking about other OSS projects, then I think it makes more sense 
for us to help those projects add PostgreSQL support.  And do promote the 
projects that already *do* support us.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Re: [OT] MySQL is bad, but THIS bad?

John DeSoi <desoi@pgedit.com> — 2006-05-18T16:48:47Z

On May 18, 2006, at 12:24 PM, Jim C. Nasby wrote:

> Even better would be coming up with a compatability mode, a la what
> EnterpriseDB has done for Oracle.

Right, you'll definitely need to hack the C source code to force  
PostgreSQL to accept invalid dates ;)

http://sql-info.de/mysql/gotchas.html#1_14



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-18T17:10:37Z

Jim C. Nasby wrote:
> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
>> On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
>>
>>> What is the best way to go about creating a "plug and play,"  
>>> PostgreSQL
>>> replacement for MySQL? I think the biggest problem getting PostgreSQL
>>> accepted is that so much code is available for MySQL.
>>
>> http://pgfoundry.org/projects/mysqlcompat/
> 
> Even better would be coming up with a compatability mode, a la what
> EnterpriseDB has done for Oracle.

Good Lord NO. I don't want a bunch of hacked up code *because* MySQL 
does it this way, running on top of PostgreSQL.

I want to run PostgreSQL. If you want to run MySQL, run MySQL. If you 
want to run Oracle, run Oracle.

Sincerely,

Joshua D. Drake




-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Alvaro Herrera <alvherre@commandprompt.com> — 2006-05-18T17:16:06Z

Josh Berkus wrote:

> Personally, I'd go after MSSQL before I bothered with MySQL.   Sure, let's 
> make *migration* easier for those who wake up and smell the BS, but migration 
> can (and probably should) be one-way.

Yeah.  Let's write a Transact-SQL PL handler.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [OT] MySQL is bad, but THIS bad?

Marc G. Fournier <scrappy@postgresql.org> — 2006-05-18T17:25:06Z

On Thu, 18 May 2006, Joshua D. Drake wrote:

> Jim C. Nasby wrote:
>> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
>>> On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
>>> 
>>>> What is the best way to go about creating a "plug and play,"  PostgreSQL
>>>> replacement for MySQL? I think the biggest problem getting PostgreSQL
>>>> accepted is that so much code is available for MySQL.
>>> 
>>> http://pgfoundry.org/projects/mysqlcompat/
>> 
>> Even better would be coming up with a compatability mode, a la what
>> EnterpriseDB has done for Oracle.
>
> Good Lord NO. I don't want a bunch of hacked up code *because* MySQL does it 
> this way, running on top of PostgreSQL.

'k, so you want "pure PostgreSQL" ... but, shouldn't it be possible, with 
all of our CREATE FUNCTION / RULES / etc features to create a 'translation 
layer' that could be loaded, like anything else in contrib?

Hell, even if it gave an initial "in" for MySQL software developers to get 
their code running on PostgreSQL, and then when they come out that "do 
this is slower under PostgreSQL", they could optimize their code 
appropriately?

  ----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-18T17:35:48Z

> 
> 'k, so you want "pure PostgreSQL" ... but, shouldn't it be possible, 
> with all of our CREATE FUNCTION / RULES / etc features to create a 
> 'translation layer' that could be loaded, like anything else in contrib?

Sure but that isn't what was suggested :)

> 
> Hell, even if it gave an initial "in" for MySQL software developers to 
> get their code running on PostgreSQL, and then when they come out that 
> "do this is slower under PostgreSQL", they could optimize their code 
> appropriately?

I understand the idea but I personally don't like it. I am not really 
interested in cross-database compatible code. 9 times out of 10 it is 
hacky, slow and lacks a solid supportable model because you are always 
taking into account *the other* databases the application supports.

And to be frank, I don't think we should waste our time on MySQL. It 
isn't a competitor, it just thinks it is. We should focus on our real 
competition which is DB2, MSSQL, and Oracle.

If we want to create contrib modules that have types etc... that help 
port from Oracle to PostgreSQL or DB2 to PostgreSQL I am all for it.

The first thing that comes to mind is a set of domains that implement 
Oracle types (names) as PostgreSQL types.

Here's a start ;)

postgres=# create domain varchar2 AS text;
CREATE DOMAIN
postgres=# create domain clob as text;
CREATE DOMAIN
postgres=# create domain blob as bytea;
CREATE DOMAIN
postgres=# create domain number as integer;
CREATE DOMAIN
postgres=#



Sincerely,

Joshua D. Drake






-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Stephen Frost <sfrost@snowman.net> — 2006-05-18T17:51:05Z

* Joshua D. Drake (jd@commandprompt.com) wrote:
> And to be frank, I don't think we should waste our time on MySQL. It 
> isn't a competitor, it just thinks it is. We should focus on our real 
> competition which is DB2, MSSQL, and Oracle.

One thing which would be kind of nice would be to have a mapping for the
common commands.  A friend of mine did this with a bit of perl so he
could have a single SQL script that could run against both Postgres and
Oracle.  His thought was actually just an aliasing ability in psql so
that you could, for example, alias 'desc' to '\d' and '@' to '\i'.  This
would have also helped me out with some projects for school- the main
issues I ran into were that Oracle required a cascade-drop to include
the keyword 'constraints' (which Postgres correctly forbids) and that 
Oracle uses 'MINUS' instead of 'EXCEPT' (where 'EXCEPT' is the SQL
standard, which Oracle doesn't accept for some reason. :/).

Just some thoughts,

	Stephen

Re: [OT] MySQL is bad, but THIS bad?

Marc G. Fournier <scrappy@postgresql.org> — 2006-05-18T17:56:12Z

On Thu, 18 May 2006, Joshua D. Drake wrote:

> I understand the idea but I personally don't like it. I am not really 
> interested in cross-database compatible code. 9 times out of 10 it is 
> hacky, slow and lacks a solid supportable model because you are always 
> taking into account *the other* databases the application supports.

I don't disagree about the 'hacky, slow and lacks ...' ... but, there are 
alot of MySQL based apps out there that are just *way too big* to convert 
wholesale to PostgreSQL that, in all honesty, are better suited to 
PostgreSQL ...

To give someone a running chance at migrating it to PostgreSQL, a 'MySQL 
compatibility module' would allow them to just plug the existing DB in, 
and then work at improving sections of the code over time ...

Hell, if done well, the module should be able to dump appropriately 
'clean' PgSQL schemas ... as in your example elow about the domains ...

something like:

postgres=# create domain varchar2 AS text;
CREATE DOMAIN
postgres=# create table test ( mytext varchar2 );

should dump out to:

create tabel test ( mytext text );

So, developer would be able to load the MySQL schema and run the 
application, but would also be able to improve the code in the application 
and dump out a PgSQL clean schema so that the next person, in theory, 
wouldn't even need the MySQL module ...

the point isn't whether or not MySQL is a competitor ... the point is that 
there are *alot* of MySQL based applications out there that are a major 
PITA to convert (or get converted) all at once ...

  ----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Re: [OT] MySQL is bad, but THIS bad?

Josh Berkus <josh@agliodbs.com> — 2006-05-18T18:01:11Z

Marc,

> To give someone a running chance at migrating it to PostgreSQL, a 'MySQL
> compatibility module' would allow them to just plug the existing DB in,
> and then work at improving sections of the code over time ...

Have you even looked at KL's mysqlcompat?

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Re: [OT] MySQL is bad, but THIS bad?

David Fetter <david@fetter.org> — 2006-05-18T18:09:28Z

On Thu, May 18, 2006 at 10:35:48AM -0700, Joshua D. Drake wrote:

> I understand the idea but I personally don't like it. I am not
> really interested in cross-database compatible code. 9 times out of
> 10 it is hacky, slow and lacks a solid supportable model because you
> are always taking into account *the other* databases the application
> supports.
> 
> And to be frank, I don't think we should waste our time on MySQL. It
> isn't a competitor, it just thinks it is. We should focus on our
> real competition which is DB2, MSSQL, and Oracle.
> 
> If we want to create contrib modules that have types etc... that
> help port from Oracle to PostgreSQL or DB2 to PostgreSQL I am all
> for it.
> 
> The first thing that comes to mind is a set of domains that
> implement Oracle types (names) as PostgreSQL types.
> 
> Here's a start ;)
> 
> postgres=# create domain varchar2 AS text;
> CREATE DOMAIN
> postgres=# create domain clob as text;
> CREATE DOMAIN
> postgres=# create domain blob as bytea;
> CREATE DOMAIN
> postgres=# create domain number as integer;
> CREATE DOMAIN
> postgres=#

postgres=# CREATE TABLE dual();
CREATE TABLE

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: [OT] MySQL is bad, but THIS bad?

David Fetter <david@fetter.org> — 2006-05-18T18:14:29Z

On Thu, May 18, 2006 at 02:56:12PM -0300, Marc G. Fournier wrote:
> On Thu, 18 May 2006, Joshua D. Drake wrote:
> 
> >I understand the idea but I personally don't like it. I am not really 
> >interested in cross-database compatible code. 9 times out of 10 it is 
> >hacky, slow and lacks a solid supportable model because you are always 
> >taking into account *the other* databases the application supports.
> 
> I don't disagree about the 'hacky, slow and lacks ...' ... but,
> there are alot of MySQL based apps out there that are just *way too
> big* to convert wholesale to PostgreSQL that, in all honesty, are
> better suited to PostgreSQL ...

They're not too big when critical data is in there.  Then it's a
matter of what cost to make this wrong decision and have to fix it.

> To give someone a running chance at migrating it to PostgreSQL, a
> 'MySQL compatibility module' would allow them to just plug the
> existing DB in, and then work at improving sections of the code over
> time ...

You cannot cross a chasm in two steps.  MySQL apps don't generally
lend themselves to incremental improvements because they are so
tightly tied to MySQL's misbehaviors.  It would be silly and dangerous
to give people the misapprehension that this transition can be made
painlessly or at low cost.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-18T18:22:55Z

On Thu, May 18, 2006 at 11:09:28AM -0700, David Fetter wrote:
> postgres=# CREATE TABLE dual();
> CREATE TABLE

You forgot to populate it.

In reality I think you'd want dual to be a view on SELECT 1; or whatever
the appropriate value is.
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-18T18:29:56Z

On Thu, May 18, 2006 at 02:56:12PM -0300, Marc G. Fournier wrote:
> the point isn't whether or not MySQL is a competitor ... the point is that 
> there are *alot* of MySQL based applications out there that are a major 
> PITA to convert (or get converted) all at once ...

More importantly, there's a lot of MySQL *users*, and they get to
influence which database is chosen in many companies. For many years,
FreeBSD was far superior technologically to Linux, but Linux had the
popularity to make it into the enterprise.

And MySQL is much closer to being a competitor now than they were in
4.1. And feature-wise they'll probably equal PostgreSQL in the next
release. Will the features be anywhere near as robust or well thought
out? No. But in a heck of a lot of companies that doesn't matter.

Maybe a compatability layer isn't worth doing, but I certainly think
it's very much worthwhile for the community to do everything possible to
encourage migration from MySQL. We should be able to lay claim to most
advanced and most popular OSS database.
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-18T18:32:30Z

> And MySQL is much closer to being a competitor now than they were in
> 4.1. And feature-wise they'll probably equal PostgreSQL in the next
> release. Will the features be anywhere near as robust or well thought
> out? No. But in a heck of a lot of companies that doesn't matter.

Your kidding right? Have you seen their "features"? Look at what their 
stored procedures are actually capable of.

The only thing that MySQL *might* pull off is a really good storage 
backend finally.

> Maybe a compatability layer isn't worth doing, but I certainly think
> it's very much worthwhile for the community to do everything possible to
> encourage migration from MySQL. We should be able to lay claim to most
> advanced and most popular OSS database.

Oh absolutely, I agree with you here but in order to do so in the most 
productive manner possible the community would have to be willing to be 
much more aggressive and much more antagnositic that I believe the 
community has the stomach for.

Sincerely,

Joshua D. Drake


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

David Fetter <david@fetter.org> — 2006-05-18T18:40:52Z

On Thu, May 18, 2006 at 01:22:55PM -0500, Jim C. Nasby wrote:
> On Thu, May 18, 2006 at 11:09:28AM -0700, David Fetter wrote:
> > postgres=# CREATE TABLE dual();
> > CREATE TABLE
> 
> You forgot to populate it.

Oh, right. :)

postgres=# CREATE TABLE dual AS SELECT 1;
SELECT

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: [OT] MySQL is bad, but THIS bad?

Andrew Dunstan <andrew@dunslane.net> — 2006-05-18T18:41:47Z

Jim C. Nasby wrote:
> Maybe a compatability layer isn't worth doing, but I certainly think
> it's very much worthwhile for the community to do everything possible to
> encourage migration from MySQL. We should be able to lay claim to most
> advanced and most popular OSS database.
>   

We'll do that by concentrating on spiffy features, not compatibility 
layers. I want people to use PostgreSQL because it's the best, not 
because it's just like something else.

cheers

andrew

Re: [OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-18T18:58:11Z

> Jim C. Nasby wrote:
>> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
>>> On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
>>>
>>>> What is the best way to go about creating a "plug and play,"
>>>> PostgreSQL
>>>> replacement for MySQL? I think the biggest problem getting PostgreSQL
>>>> accepted is that so much code is available for MySQL.
>>>
>>> http://pgfoundry.org/projects/mysqlcompat/
>>
>> Even better would be coming up with a compatability mode, a la what
>> EnterpriseDB has done for Oracle.
>
> Good Lord NO. I don't want a bunch of hacked up code *because* MySQL
> does it this way, running on top of PostgreSQL.
>
> I want to run PostgreSQL. If you want to run MySQL, run MySQL. If you
> want to run Oracle, run Oracle.

It isn't always about what we want to do, unfortunately, sometimes it has
to do with external factors outside of our control.

The reality is that MySQL is widely supported by some very, shall we say,
"interesting" open source projects and using these products with
PostgreSQL would be a plus.

I ask you, try to find some PHP/Open Source projects that support
PostgreSQL just as well as MySQL? Most don't even support PostgreSQL.

Re: [OT] MySQL is bad, but THIS bad?

Thomas Hallgren <thomas@tada.se> — 2006-05-18T19:04:12Z

John DeSoi wrote:
> 
> Right, you'll definitely need to hack the C source code to force 
> PostgreSQL to accept invalid dates ;)
> 
> http://sql-info.de/mysql/gotchas.html#1_14
> 
Couldn't we just install something that replaced invalid dates with a randomly generated but 
otherwise correct dates? That way they would become completely invisible. No one could even 
tell that the date was invalid to start with.

Regards,
Thomas Hallgren

Re: [OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-18T19:11:09Z

> Jim C. Nasby wrote:
>> Maybe a compatability layer isn't worth doing, but I certainly think
>> it's very much worthwhile for the community to do everything possible to
>> encourage migration from MySQL. We should be able to lay claim to most
>> advanced and most popular OSS database.
>>
>
> We'll do that by concentrating on spiffy features, not compatibility
> layers. I want people to use PostgreSQL because it's the best, not
> because it's just like something else.
>

While I do agree with the ideal, the reality may not be good enough. Even
I, a PostgreSQL user for a decade, have to use MySQL right now because
that is what the client uses.

Again, there is so much code for MySQL, a MySQL emulation layer, MEL for
short, could allow plug and play compatibility for open source, and closed
source, applications that otherwise would force a PostgreSQL user to hold
his or her nose and use MySQL.

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-18T19:17:24Z

> 
> While I do agree with the ideal, the reality may not be good enough. Even
> I, a PostgreSQL user for a decade, have to use MySQL right now because
> that is what the client uses.

Then you aren't choosing your clients wisely. :)

I am not trying to be rude, but if you don't want to use MySQL, don't. 
We don't and we are testament to the fact that it is indeed possible to 
make a living off of JUST PostgreSQL.

The only time we touch a different database is to MIGRATE from that 
database.

Joshua D. Drake



-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Andrew Dunstan <andrew@dunslane.net> — 2006-05-18T19:22:42Z

Mark Woodward wrote:
>> Jim C. Nasby wrote:
>>     
>>> Maybe a compatability layer isn't worth doing, but I certainly think
>>> it's very much worthwhile for the community to do everything possible to
>>> encourage migration from MySQL. We should be able to lay claim to most
>>> advanced and most popular OSS database.
>>>
>>>       
>> We'll do that by concentrating on spiffy features, not compatibility
>> layers. I want people to use PostgreSQL because it's the best, not
>> because it's just like something else.
>>
>>     
>
> While I do agree with the ideal, the reality may not be good enough. Even
> I, a PostgreSQL user for a decade, have to use MySQL right now because
> that is what the client uses.
>
> Again, there is so much code for MySQL, a MySQL emulation layer, MEL for
> short, could allow plug and play compatibility for open source, and closed
> source, applications that otherwise would force a PostgreSQL user to hold
> his or her nose and use MySQL.
>
>
>   
If we had infinite resources this might make sense. We don't, so it 
doesn't. There is a real cost to producing a compatibility layer, and 
the cost will be those spiffy new features.

Let's get recursive queries, MERGE, and a couple more things and they 
will still be chasing our heels.

cheers

andrew

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-18T19:41:25Z

Moving to -advocacy, since this isn't really about hacking anymore...

On Thu, May 18, 2006 at 11:32:30AM -0700, Joshua D. Drake wrote:
> 
> >And MySQL is much closer to being a competitor now than they were in
> >4.1. And feature-wise they'll probably equal PostgreSQL in the next
> >release. Will the features be anywhere near as robust or well thought
> >out? No. But in a heck of a lot of companies that doesn't matter.
> 
> Your kidding right? Have you seen their "features"? Look at what their 
> stored procedures are actually capable of.

But it doesn't really matter from a marketing standpoint how badly the
feature sucks, so long as it's there. And if you want to believe the
marketing, they're actually more advanced than us, with "features" such
as replication and clustering.

> The only thing that MySQL *might* pull off is a really good storage 
> backend finally.
> 
> >Maybe a compatability layer isn't worth doing, but I certainly think
> >it's very much worthwhile for the community to do everything possible to
> >encourage migration from MySQL. We should be able to lay claim to most
> >advanced and most popular OSS database.
> 
> Oh absolutely, I agree with you here but in order to do so in the most 
> productive manner possible the community would have to be willing to be 
> much more aggressive and much more antagnositic that I believe the 
> community has the stomach for.

I don't think it has to necessarily be antagonistic. Simply looking at
how these features compare would probably shed a lot of light.

BTW, should anyone want to undertake some writing along these lines,
Pervasive will probably pay for it, depending on what exactly the topic
is:
http://www.pervasivepostgres.com/postgresql/partners_in_publishing.asp
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-18T19:43:23Z

On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote:
> > Jim C. Nasby wrote:
> >> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
> >>> On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
> >>>
> >>>> What is the best way to go about creating a "plug and play,"
> >>>> PostgreSQL
> >>>> replacement for MySQL? I think the biggest problem getting PostgreSQL
> >>>> accepted is that so much code is available for MySQL.
> >>>
> >>> http://pgfoundry.org/projects/mysqlcompat/
> >>
> >> Even better would be coming up with a compatability mode, a la what
> >> EnterpriseDB has done for Oracle.
> >
> > Good Lord NO. I don't want a bunch of hacked up code *because* MySQL
> > does it this way, running on top of PostgreSQL.
> >
> > I want to run PostgreSQL. If you want to run MySQL, run MySQL. If you
> > want to run Oracle, run Oracle.
> 
> It isn't always about what we want to do, unfortunately, sometimes it has
> to do with external factors outside of our control.
> 
> The reality is that MySQL is widely supported by some very, shall we say,
> "interesting" open source projects and using these products with
> PostgreSQL would be a plus.
> 
> I ask you, try to find some PHP/Open Source projects that support
> PostgreSQL just as well as MySQL? Most don't even support PostgreSQL.

Which means most ISPs don't support PostgreSQL. And most OSS users don't
get exposed to PostgreSQL. Which leads to more OSS that doesn't support
PostgreSQL...
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-18T19:49:39Z

Thomas Hallgren wrote:
> John DeSoi wrote:
>>
>> Right, you'll definitely need to hack the C source code to force 
>> PostgreSQL to accept invalid dates ;)
>>
>> http://sql-info.de/mysql/gotchas.html#1_14
>>
> Couldn't we just install something that replaced invalid dates with a 
> randomly generated but otherwise correct dates? That way they would 
> become completely invisible. No one could even tell that the date was 
> invalid to start with.
> 

No we can't, because then we are taking an invalid date, which is 
potentially valid data (to the user) and modifying it to a valid date 
that is indeed invalid data.

One of the reasons that mysql is just stupid.

Joshua D. Drake

> Regards,
> Thomas Hallgren
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Michael Paesold <mpaesold@gmx.at> — 2006-05-18T19:55:52Z

Joshua D. Drake wrote:
> Thomas Hallgren wrote:
 >
>> Couldn't we just install something that replaced invalid dates with a 
>> randomly generated but otherwise correct dates? That way they would 
>> become completely invisible. No one could even tell that the date was 
>> invalid to start with.
> 
> No we can't, because then we are taking an invalid date, which is 
> potentially valid data (to the user) and modifying it to a valid date 
> that is indeed invalid data.

I think you should have read a `;-)' after Thomas' suggestion.

;-)

Best Regards,
Michael

Re: [OT] MySQL is bad, but THIS bad?

Lukas Smith <smith@pooteeweet.org> — 2006-05-18T19:58:21Z

Joshua D. Drake wrote:
> Thomas Hallgren wrote:
>> John DeSoi wrote:
>>>
>>> Right, you'll definitely need to hack the C source code to force 
>>> PostgreSQL to accept invalid dates ;)
>>>
>>> http://sql-info.de/mysql/gotchas.html#1_14
>>>
>> Couldn't we just install something that replaced invalid dates with a 
>> randomly generated but otherwise correct dates? That way they would 
>> become completely invisible. No one could even tell that the date was 
>> invalid to start with.
>>
> 
> No we can't, because then we are taking an invalid date, which is 
> potentially valid data (to the user) and modifying it to a valid date 
> that is indeed invalid data.
> 
> One of the reasons that mysql is just stupid.

I do agree that its probably not worth allocating core resources to 
this, but spouting outdated FUD is really making you two look foolish.

You have to understand that MySQL evolves just like PostgreSQL does. So 
you better focus on advertising where PostgreSQL shines instead of 
poking fun at something you apparently do not follow.

regards,
Lukas

Re: [OT] MySQL is bad, but THIS bad?

Philippe Schmid <phschmid@cadinfo.ch> — 2006-05-18T20:17:32Z

>>
> If we had infinite resources this might make sense. We don't, so it  
> doesn't. There is a real cost to producing a compatibility layer,  
> and the cost will be those spiffy new features.
>
> Let's get recursive queries, MERGE, and a couple more things and  
> they will still be chasing our heels.

As a users of both Postgres and MySQL, I would also say, better add  
missing features to Postgres than chasing some specialties that are  
going to vanish anyway in MySQL.

I miss :
- a core full-text indexing engine. Tsearch2 is nice, but not  
included. This is a feature often used by PHP/MySQL packages.
- per table or column choice of char set/encoding
- better configurations or a tool to give the user some clue how to  
optimize the postgres settings (I know, the pro will know every time,  
but not every MySQL user - potential Postgres convert is a pro, by large

More high end features like these are also very welcomed...
- recursive queries
- better partitioning
- multi-master sync
- the compression thread is also interesting (cf IBM DB2 Viper about  
this)

Till now, the postgres project managed to produce amazing stuff.  
Keeping the project focused will very probably be more difficult as  
more users are converted...

Best regards,
Philippe Schmid

Re: [OT] MySQL is bad, but THIS bad?

David Fetter <david@fetter.org> — 2006-05-18T20:18:17Z

On Thu, May 18, 2006 at 09:58:21PM +0200, Lukas Smith wrote:
> Joshua D. Drake wrote:
> >
> >No we can't, because then we are taking an invalid date, which is
> >potentially valid data (to the user) and modifying it to a valid
> >date that is indeed invalid data.
> >
> >One of the reasons that mysql is just stupid.
> 
> I do agree that its probably not worth allocating core resources to
> this, but spouting outdated FUD is really making you two look
> foolish.

In what way is this outdated?  Please provide a specific example.

> You have to understand that MySQL evolves just like PostgreSQL does.

If it were true, I would have to understand it, but the way MySQL
evolves is not even remotely like the way PostgreSQL does.  Here are a
few concrete differences:

* Community input.  PostgreSQL's code all comes from the community.
  MySQL's all comes from employees of MySQL AB because MySQL AB
  requires that all developers sign over copyrights to MySQL AB.
  Independent developers are not naÃ¯f or foolish enough to contribute
  on those terms.

* Standards compliance.  PostgreSQL has bailed on quite a few
  features, and will in the future, in order to comply to SQL
  standards.

* Marketing departments.  PGDG doesn't have one, and if it did, it
  wouldn't have the power to drive features.

> So you better focus on advertising where PostgreSQL shines instead
> of poking fun at something you apparently do not follow.

You've made some sweeping allegations here and no specifics.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: [OT] MySQL is bad, but THIS bad?

Lukas Smith <smith@pooteeweet.org> — 2006-05-18T20:24:38Z

David Fetter wrote:
> On Thu, May 18, 2006 at 09:58:21PM +0200, Lukas Smith wrote:

> In what way is this outdated?  Please provide a specific example.

see below ..

>> You have to understand that MySQL evolves just like PostgreSQL does.
> 
> If it were true, I would have to understand it, but the way MySQL
> evolves is not even remotely like the way PostgreSQL does.  Here are a
> few concrete differences:

Ok, I was not trying to imply that MySQL evolves in the same way .. only 
that it also evolves.

> You've made some sweeping allegations here and no specifics.

sweeping allegations?

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
SET [GLOBAL|SESSION] sql_mode='ALLOW_INVALID_DATES'

That being said the solution is still lacking. I even filed a bug report 
  over this:
http://bugs.mysql.com/bug.php?id=17998

So I guess you can continue your commentary. Anyways, it was not my 
intention to educate PostgreSQL developers about MySQL, only that it 
would be wise not to make general comments about MySQL if you do not 
follow its development.

regards,
Lukas

Re: [OT] MySQL is bad, but THIS bad?

Chris Browne <cbbrowne@acm.org> — 2006-05-18T20:25:17Z

scrappy@postgresql.org ("Marc G. Fournier") writes:
> To give someone a running chance at migrating it to PostgreSQL, a
> 'MySQL compatibility module' would allow them to just plug the
> existing DB in, and then work at improving sections of the code over
> time ...
>
> Hell, if done well, the module should be able to dump appropriately
> 'clean' PgSQL schemas ... as in your example elow about the domains ...

You can't have that because you essentially need to throw out four
aspects of fairly vital "data validation" functionality:

1.  Dates cease to be validatable.

2.  NULL and 0 and '' are all roughly equivalent, even though they
aren't.

3.  Foreign key constraints have to be ignored.

4.  You have to fabricate a locale offering a case-insensitive sort
order.

I suppose #4 isn't "vital data validation"...

But after you "gut" the PostgreSQL-based system of those four aspects
of data integrity, I'm not sure there's any remaining benefit to
having PostgreSQL in play...
-- 
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable.  Gov. George W. Bush declared
Tarrant County a disaster area.  Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-18T20:25:34Z

> I do agree that its probably not worth allocating core resources to 
> this, but spouting outdated FUD is really making you two look foolish.

And which FUD would this be?

> 
> You have to understand that MySQL evolves just like PostgreSQL does. So 
> you better focus on advertising where PostgreSQL shines instead of 
> poking fun at something you apparently do not follow.

Uhmmm, I am not even going to bother responding to this part. It is 
obvious that *you* don't follow MySQL versus PostgreSQL.

No offense but the development models and thus evolution thereof are 
completely different.

Sincerely,

Joshua D. Drake


> 
> regards,
> Lukas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-18T20:26:39Z

>> You've made some sweeping allegations here and no specifics.
> 
> sweeping allegations?
> 
> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
> SET [GLOBAL|SESSION] sql_mode='ALLOW_INVALID_DATES'

Which can be turned off or on by any mysql user. Not to mention the fact 
that it is even an option.

Joshua D. Drake

> follow its development.
> 
> regards,
> Lukas
> 


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-18T20:31:46Z

On Thu, May 18, 2006 at 01:25:34PM -0700, Joshua D. Drake wrote:
> 
> >I do agree that its probably not worth allocating core resources to 
> >this, but spouting outdated FUD is really making you two look foolish.
> 
> And which FUD would this be?

That Feb. 31st is a valid date in MySQL. You can now configure it to
reject that (don't know if that's the default or not).

> >
> >You have to understand that MySQL evolves just like PostgreSQL does. So 
> >you better focus on advertising where PostgreSQL shines instead of 
> >poking fun at something you apparently do not follow.
> 
> Uhmmm, I am not even going to bother responding to this part. It is 
> obvious that *you* don't follow MySQL versus PostgreSQL.
> 
> No offense but the development models and thus evolution thereof are 
> completely different.

You just proved one of my points. It's pretty easy for executives to
understand that trying to store Feb. 31st in their database is probably
a bad idea, but arguments about development models and their impact on
software quality are likely to fall on deaf/befuddled ears.
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Tom Lane <tgl@sss.pgh.pa.us> — 2006-05-18T20:36:31Z

Andrew Dunstan <andrew@dunslane.net> writes:
> Mark Woodward wrote:
>> Again, there is so much code for MySQL, a MySQL emulation layer, MEL for
>> short, could allow plug and play compatibility for open source, and closed
>> source, applications that otherwise would force a PostgreSQL user to hold
>> his or her nose and use MySQL.
>> 
> If we had infinite resources this might make sense. We don't, so it 
> doesn't. There is a real cost to producing a compatibility layer, and 
> the cost will be those spiffy new features.

The real problem is that there's a whole lot of stuff, such as mysql's
weak error checking, that I don't think a "compatibility layer" could
sanely provide.

			regards, tom lane

Re: [OT] MySQL is bad, but THIS bad?

Thomas Hallgren <thomas@tada.se> — 2006-05-18T21:14:24Z

Lukas Smith wrote:
> .. but spouting outdated FUD is really making you two look foolish.
> 
Wow. On a scale from 1 to 10 measuring seriousness, I'd put my posting on -4. I'd advice you 
to take a step back and get some distance if you consider it "outdated FUD".

Regards,
Thomas Hallgren

Re: [OT] MySQL is bad, but THIS bad?

Thomas Hallgren <thomas@tada.se> — 2006-05-18T21:35:01Z

Chris Browne wrote:
> scrappy@postgresql.org ("Marc G. Fournier") writes:
>> To give someone a running chance at migrating it to PostgreSQL, a
>> 'MySQL compatibility module' would allow them to just plug the
>> existing DB in, and then work at improving sections of the code over
>> time ...
>>
>> Hell, if done well, the module should be able to dump appropriately
>> 'clean' PgSQL schemas ... as in your example elow about the domains ...
> 
> You can't have that because you essentially need to throw out four
> aspects of fairly vital "data validation" functionality:
> 
> 1.  Dates cease to be validatable.
> 
> 2.  NULL and 0 and '' are all roughly equivalent, even though they
> aren't.
> 
> 3.  Foreign key constraints have to be ignored.
> 
> 4.  You have to fabricate a locale offering a case-insensitive sort
> order.
> 
> I suppose #4 isn't "vital data validation"...
> 
> But after you "gut" the PostgreSQL-based system of those four aspects
> of data integrity, I'm not sure there's any remaining benefit to
> having PostgreSQL in play...

Assuming the objective with a transition would be to improve on things, an alternative 
approach could be to offer a three step migration path:

1. A dump/restore utility that dumps a MySQL database and restores it into a PostgreSQL 
database. This utility must have plugin capabilities where logic can be added that deals 
with cases #1, #2, and #3 above. It might be as simple as just logging incorrect records to 
a file and skip them. A pre-defined set of generic plugins could be supplied that did just 
that. A user would have the chance to replace them with customized plugins to cover for 
special cases in his own app. Perl or PHP would probably be good candidates for plugin language.

2. Provide an add-on to the PostgreSQL parser that would make it accept MySQL syntax. The 
database would still run untainted underneath so from this point on, no more invalid dates 
or foreign keys can be entered. Some other add-ons are needed as well to cater for some sane 
but non-standard MySQL behavior that PostgreSQL is lacking.

3. A good user guide that helps the user to, over time, move away from the non standard 
MySQL specific expressions. The objective being to at some point skip the MySQL syntax layer 
altogether.

Regards,
Thomas Hallgren

(dead serious this time)

Re: [OT] MySQL is bad, but THIS bad?

Dawid Kuroczko <qnex42@gmail.com> — 2006-05-18T23:26:34Z

On 5/18/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> On Wed, May 17, 2006 at 09:35:34PM -0400, John DeSoi wrote:
> >
> > On May 17, 2006, at 8:08 PM, Mark Woodward wrote:
> >
> > >What is the best way to go about creating a "plug and play,"
> > >PostgreSQL
> > >replacement for MySQL? I think the biggest problem getting PostgreSQL
> > >accepted is that so much code is available for MySQL.
> >
> >
> > http://pgfoundry.org/projects/mysqlcompat/
>
> Even better would be coming up with a compatability mode, a la what
> EnterpriseDB has done for Oracle.

I don't think we actually want such a mode.  In theory it would be
great, but in practise... Especially from performance standpoint.

I mean, application for MySQL would usually be written as: quick
connect, do a query, disconnect.  No application-level caching, no
connection keep-alive.  First run on a similar hardware would be
a performance disaster. PostgreSQL connection initialisation
will be slower (yes, pgpool, but how much do you want to bet,
the reaction would be: "you said compatibility mode! no mention
of pgpool!"), and no caching would be noticable, to say the least.

I don't want to suggest adding caching to postgresql (I know it
isn't that great).  I want to suggest that MySQL users are used
to it and will be unhappy to part from it.

So, if you want to help people migrate to Postgres, better tell
them how to fix mysqlisms. I mean: "no replace into"? tell them
about create trigger on insert to handle duplicates.  No caching,
tell them about memcached and its advantages.  Tell how to
use pgpool.

Personally, I wish I had better knowledge of PostgreSQL years
back, when one project was started... I didn't know PostgreSQL
then, and well, MySQL outperformed it.  Of course when real
load was given, MyISAM were a performance disaster, but then
there was InnoDB...  And sometimes some developer will select
count(*) on InnoDB, and InnoDB will make a temporary table with
all the data, holding the lock on the table...  And sometimes the
replication will just break, and sometimes you need repair table.
All the usual fun. ;)

While I can talk to the DBAs and tell them about table partitioning,
PITR, MVCC, online backups, etc, etc. then add spice with PL/pgSQL
and other PL/*s the real problem is people are used to MySQL --
-- they know how to handle it, how to support it, what to expect
from it. The bigger the company, the harder it is to convince people
that they should start using something completely new. An important
application with 1 or 2 people who know what to do, when after a major
failure, restart doesn't help. This makes it very hard...

Personally my opinion is that there is no point in pushing PostgreSQL
everywhere -- if there is no siginifcant performance gain, most managers
will refuse it, on the grounds that "if it ain't (too) broke, don't fix it".
The real places to "attack at" are the BIG dbs, the dataware housing
applications.  Places where MySQL is not used, because someones
select count(*) should not kill the database.  Because the queries
take few hours to complete "by design". This should be doable. :)

   Regards,
      Dawid

Re: [OT] MySQL is bad, but THIS bad?

Robert Treat <xzilla@users.sourceforge.net> — 2006-05-19T00:14:59Z

On Thursday 18 May 2006 16:17, Philippe Schmid wrote:
> As a users of both Postgres and MySQL, I would also say, better add
> missing features to Postgres than chasing some specialties that are
> going to vanish anyway in MySQL.
> I miss :
> - a core full-text indexing engine. Tsearch2 is nice, but not
> included. This is a feature often used by PHP/MySQL packages.

We also need better support for non C locales in tsearch.  As I was porting 
mysql's sakila sample database I was reminded just how painful it is when you 
initdb in a non-supported locale (which is probably the default on the 
majority of distros out there)

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

Re: [OT] MySQL is bad, but THIS bad?

Robert Treat <xzilla@users.sourceforge.net> — 2006-05-19T00:35:02Z

On Thursday 18 May 2006 12:38, Josh Berkus wrote:
> Personally, I'd go after MSSQL before I bothered with MySQL.   Sure, let's
> make *migration* easier for those who wake up and smell the BS, but
> migration can (and probably should) be one-way.
>

If you want to get users to swtich to your software from your competitors, you 
have to eliminate barriers, and a big one for any database is getting locked 
into a specific one.  People aren't going to take the time to try switching 
to postgresql if they can't easily make it back to thier former database. 
It's one of the reasons why PostgreSQL's standards compliance is so 
important; if you want to swtich to a new database, your best bet is to give 
PostgreSQL a shot, because even if you don't like it, we're not going to try 
and trap you into our software with bunches of non-standard knobs. Low 
barrier to exit == low barrier to entry. 

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

Re: [OT] MySQL is bad, but THIS bad?

Christopher Kings-Lynne <chris.kings-lynne@calorieking.com> — 2006-05-19T01:33:16Z

> And MySQL is much closer to being a competitor now than they were in
> 4.1. And feature-wise they'll probably equal PostgreSQL in the next
> release. Will the features be anywhere near as robust or well thought
> out? No. But in a heck of a lot of companies that doesn't matter.

Don't forget that they got nested transactions and PITR both before us. 
  They will also shortly have really nice partitioning before us...

...don't underestimate their development speed.

Re: [OT] MySQL is bad, but THIS bad?

Christopher Kings-Lynne <chris.kings-lynne@calorieking.com> — 2006-05-19T01:40:01Z

> We also need better support for non C locales in tsearch.  As I was porting 
> mysql's sakila sample database I was reminded just how painful it is when you 
> initdb in a non-supported locale (which is probably the default on the 
> majority of distros out there)


In 8.2 tsearch2 supports utf8...

Re: [OT] MySQL is bad, but THIS bad?

Christopher Kings-Lynne <chris.kings-lynne@calorieking.com> — 2006-05-19T01:40:40Z

> If you want to get users to swtich to your software from your competitors, you 
> have to eliminate barriers, and a big one for any database is getting locked 
> into a specific one.  People aren't going to take the time to try switching 
> to postgresql if they can't easily make it back to thier former database. 
> It's one of the reasons why PostgreSQL's standards compliance is so 
> important; if you want to swtich to a new database, your best bet is to give 
> PostgreSQL a shot, because even if you don't like it, we're not going to try 
> and trap you into our software with bunches of non-standard knobs. Low 
> barrier to exit == low barrier to entry. 

Another reason why a tool to export from pgsql to mysql is just as 
important as the vice versa...

Chris

Re: [OT] MySQL is bad, but THIS bad?

Mark Kirkwood <markir@paradise.net.nz> — 2006-05-19T01:41:31Z

Christopher Kings-Lynne wrote:
>> And MySQL is much closer to being a competitor now than they were in
>> 4.1. And feature-wise they'll probably equal PostgreSQL in the next
>> release. Will the features be anywhere near as robust or well thought
>> out? No. But in a heck of a lot of companies that doesn't matter.
> 
> Don't forget that they got nested transactions and PITR both before us. 
>  They will also shortly have really nice partitioning before us...
> 
> ...don't underestimate their development speed.
> 

Second that. In addition they have (early) in-memory multi-node 
clustering and Jim Starkey is writing them a new transactional storage 
engine to replace the probably-soon-to-be-license-hampered Innodb...

Cheers

Mark

Re: [OT] MySQL is bad, but THIS bad?

Thomas Hallgren <thomas@tada.se> — 2006-05-19T06:25:47Z

Christopher Kings-Lynne wrote:
>> If you want to get users to swtich to your software from your 
>> competitors, you have to eliminate barriers, and a big one for any 
>> database is getting locked into a specific one.  People aren't going 
>> to take the time to try switching to postgresql if they can't easily 
>> make it back to thier former database. It's one of the reasons why 
>> PostgreSQL's standards compliance is so important; if you want to 
>> swtich to a new database, your best bet is to give PostgreSQL a shot, 
>> because even if you don't like it, we're not going to try and trap you 
>> into our software with bunches of non-standard knobs. Low barrier to 
>> exit == low barrier to entry. 
> 
> Another reason why a tool to export from pgsql to mysql is just as 
> important as the vice versa...
> 
If that's really true, then let's create a bidirectional compatibility layer as a joint 
venture with people from the MySQL camp. Should be a win-win situation. I somehow doubt that 
is the case. Important yes. But "just as important"? No way.

We would loose big time on the export side since the vendor lock-in aspect is seriously out 
balanced by current levels of standards compliance. On the other hand, we'd win by order of 
magnitude on the import side. I bet the MySQL people would be utterly uninterested in such a 
venture. I think that if anything should be done, we should concentrate on import and let 
the MySQL people worry about going the other way. Once it becomes "just as imporant", they will.

Regards,
Thomas Hallgren

Re: [OT] MySQL is bad, but THIS bad?

Jeroen Vermeulen <jtv@xs4all.nl> — 2006-05-19T07:55:53Z

On Fri, May 19, 2006 13:25, Thomas Hallgren wrote:

> If that's really true, then let's create a bidirectional compatibility
> layer as a joint
> venture with people from the MySQL camp. Should be a win-win situation. I
> somehow doubt that
> is the case. Important yes. But "just as important"? No way.

I'm not too hopeful, for two reasons.  First: MySQL is very, very
different.  I heard they just introduced a "create user" command like
everybody else, but that's a drop in an ocean.  I'm sure it's
unintentional, but publishing a "quaint" SQL dialect amounts to a
vendor-lock-in scheme--this time with the barn being locked before the
cash cows have walked in.

Second: management changes at MySQL seem to have favoured conventional
business thinking over following the techs where no man has gone before. 
A year or two back we discussed porting libpqxx to MySQL so we'd have at
least a strong, common C++ layer.  Some of the technical people loved it,
a provisional team was sketched out, and the idea was pitched to
management.  The argument: the more stable interfaces we share, the more
confident corporate customers will feel adopting free databases.

It didn't go anywhere.  Reports I heard later amounted to "they don't see
why they should spend the money."

Jeroen

Re: [OT] MySQL is bad, but THIS bad?

Tommi Maekitalo <t.maekitalo@epgmbh.de> — 2006-05-19T08:37:26Z

Am Freitag, 19. Mai 2006 02:35 schrieb Robert Treat:
> On Thursday 18 May 2006 12:38, Josh Berkus wrote:
> > Personally, I'd go after MSSQL before I bothered with MySQL.   Sure,
> > let's make *migration* easier for those who wake up and smell the BS, but
> > migration can (and probably should) be one-way.
>
> If you want to get users to swtich to your software from your competitors,
> you have to eliminate barriers, and a big one for any database is getting
> locked into a specific one.  People aren't going to take the time to try
> switching to postgresql if they can't easily make it back to thier former
> database. It's one of the reasons why PostgreSQL's standards compliance is
> so important; if you want to swtich to a new database, your best bet is to
> give PostgreSQL a shot, because even if you don't like it, we're not going
> to try and trap you into our software with bunches of non-standard knobs.
> Low barrier to exit == low barrier to entry.

The way to go are standards. If postgresql supports standard-sql (like we all 
know it know), mysql-users has to justify their apps to use standard-sql. 
What they gain is not only compatibility with PostgreSQL but compatiblity 
with all database-servers, which supports this standard. They wont have much 
trouble to switch back to mysql or downgrade their postgresql to oracle ;-), 
if they follow standards.

Also if PostgreSQL would have a compatibility-layer, it has to follow every 
quirk of mysql and will be measured by that. Much better is to promote users 
of mysql to use standards.

Tommi

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-19T14:33:35Z

On Fri, May 19, 2006 at 01:26:34AM +0200, Dawid Kuroczko wrote:
> Personally my opinion is that there is no point in pushing PostgreSQL
> everywhere -- if there is no siginifcant performance gain, most managers
> will refuse it, on the grounds that "if it ain't (too) broke, don't fix it".
> The real places to "attack at" are the BIG dbs, the dataware housing
> applications.  Places where MySQL is not used, because someones
> select count(*) should not kill the database.  Because the queries
> take few hours to complete "by design". This should be doable. :)

The problem with limiting ourselves to going after only the 'high end'
of databases is that MySQL is also pushing in that direction, but they
have the advantage of a much larger user base than us. So in the
not-to-distant future, a lot of people who are looking to come off of
Oracle will look at both MySQL and PostgreSQL (in fact I'm sure there's
already some people moving from Oracle to MySQL). When MySQL is at that
point, which database do you think executives will be choosing? The one
with a very large userbase and lots of marketing and PR that they've
heard plenty about, or the one that might theoretically be technically
superior but has a small userbase and they've never heard of? And if the
technical people in the company are MySQL users, because that's the
database they cut their teeth on...
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-19T15:11:42Z

  When MySQL is at that
> point, which database do you think executives will be choosing? The one
> with a very large userbase and lots of marketing and PR that they've
> heard plenty about,

All due respect, Jim -- but don't you work for a publicly traded 
database company that happens to have its own version of PostgreSQL?

This is really a discussion for your marketing (and mine frankly) then 
the PostgreSQL mailing lists :)

Sincerely,

Joshua D. Drake




-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-19T15:29:23Z

> Andrew Dunstan <andrew@dunslane.net> writes:
>> Mark Woodward wrote:
>>> Again, there is so much code for MySQL, a MySQL emulation layer, MEL
>>> for
>>> short, could allow plug and play compatibility for open source, and
>>> closed
>>> source, applications that otherwise would force a PostgreSQL user to
>>> hold
>>> his or her nose and use MySQL.
>>>
>> If we had infinite resources this might make sense. We don't, so it
>> doesn't. There is a real cost to producing a compatibility layer, and
>> the cost will be those spiffy new features.
>
> The real problem is that there's a whole lot of stuff, such as mysql's
> weak error checking, that I don't think a "compatibility layer" could
> sanely provide.
>
I kind of agree with this statement, but while I was playing devils's
advocate and just grousing a bit about having to use MySQL, there is a
sort of reality of "openomics" where mind-share is everything.

The more mind-share you have, the more opportunities you have and the more
resources become available. Not always, of course, look at OpenSSH, but
for the most part.

As MySQL adds features, not matter how poorly implemented, and maintain a
migration path, we will never reach their users.

PostgreSQL is better, true, but it is not ideal in many ways. It can be
best said that the difference between PostgreSQL and MySQL is similar to
the difference between Linux/BSD and Windows.

Re: [OT] MySQL is bad, but THIS bad?

Martijn van Oosterhout <kleptog@svana.org> — 2006-05-19T15:38:21Z

On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote:
> The reality is that MySQL is widely supported by some very, shall we say,
> "interesting" open source projects and using these products with
> PostgreSQL would be a plus.

The biggest headache I find with using postgres is that various GPL
licenced programs have trouble directly shipping postgresql support
because of our use of OpenSSL. Each and every one of those program
needs to add an exception to their licence for distributors to
distribute postgresql support.

I'm thinking particularly of FreeRadius but there are others. More than
once I thought while waiting for stuff to compile: if I'd chosen mysql
I'd be done by now...

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: [OT] MySQL is bad, but THIS bad?

Andreas Pflug <pgadmin@pse-consulting.de> — 2006-05-19T15:52:02Z

Martijn van Oosterhout wrote:

> The biggest headache I find with using postgres is that various GPL
> licenced programs have trouble directly shipping postgresql support
> because of our use of OpenSSL. Each and every one of those program
> needs to add an exception to their licence for distributors to
> distribute postgresql support.

They could distribute a non-ssl-enabled version, *if* they really need 
to include libpq in the package, or advise to to replace it with the 
common version if ssl is required. I bet >99 % of pgsql connections are 
not encrypted anyway.

Regards,
Andreas

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-19T16:11:57Z

Martijn van Oosterhout wrote:
> On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote:
>> The reality is that MySQL is widely supported by some very, shall we say,
>> "interesting" open source projects and using these products with
>> PostgreSQL would be a plus.
> 
> The biggest headache I find with using postgres is that various GPL
> licenced programs have trouble directly shipping postgresql support
> because of our use of OpenSSL. Each and every one of those program
> needs to add an exception to their licence for distributors to
> distribute postgresql support.

Why would that be the case... OpenSSL and PostgreSQL both are BSD 
licensed... Am I missing something?

Joshua D. Drake

> 
> I'm thinking particularly of FreeRadius but there are others. More than
> once I thought while waiting for stuff to compile: if I'd chosen mysql
> I'd be done by now...
> 
> Have a nice day,


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Alvaro Herrera <alvherre@commandprompt.com> — 2006-05-19T16:15:38Z

Joshua D. Drake wrote:
> Martijn van Oosterhout wrote:
> >On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote:
> >>The reality is that MySQL is widely supported by some very, shall we say,
> >>"interesting" open source projects and using these products with
> >>PostgreSQL would be a plus.
> >
> >The biggest headache I find with using postgres is that various GPL
> >licenced programs have trouble directly shipping postgresql support
> >because of our use of OpenSSL. Each and every one of those program
> >needs to add an exception to their licence for distributors to
> >distribute postgresql support.
> 
> Why would that be the case... OpenSSL and PostgreSQL both are BSD 
> licensed... Am I missing something?

Advertising clause.  PostgreSQL doesn't have it, OpenSSL does.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [OT] MySQL is bad, but THIS bad?

Andrew Dunstan <andrew@dunslane.net> — 2006-05-19T16:22:25Z

Joshua D. Drake wrote:
> Martijn van Oosterhout wrote:
>> On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote:
>>> The reality is that MySQL is widely supported by some very, shall we 
>>> say,
>>> "interesting" open source projects and using these products with
>>> PostgreSQL would be a plus.
>>
>> The biggest headache I find with using postgres is that various GPL
>> licenced programs have trouble directly shipping postgresql support
>> because of our use of OpenSSL. Each and every one of those program
>> needs to add an exception to their licence for distributors to
>> distribute postgresql support.
>
> Why would that be the case... OpenSSL and PostgreSQL both are BSD 
> licensed... Am I missing something?


http://www.openssl.org/support/faq.html#LEGAL2

Of course, on that reasoning, they would need to provide a similar 
exception for libpq with or without openssl. More and more I love the 
fact that we don't play these games.

cheers

andrew

Re: [OT] MySQL is bad, but THIS bad?

Rod Taylor <pg@rbt.ca> — 2006-05-19T16:25:44Z

On Fri, 2006-05-19 at 09:11 -0700, Joshua D. Drake wrote:
> Martijn van Oosterhout wrote:
> > On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote:
> >> The reality is that MySQL is widely supported by some very, shall we say,
> >> "interesting" open source projects and using these products with
> >> PostgreSQL would be a plus.
> > 
> > The biggest headache I find with using postgres is that various GPL
> > licenced programs have trouble directly shipping postgresql support
> > because of our use of OpenSSL. Each and every one of those program
> > needs to add an exception to their licence for distributors to
> > distribute postgresql support.
> 
> Why would that be the case... OpenSSL and PostgreSQL both are BSD 
> licensed... Am I missing something?

OpenSSL is not the 3 clause BSD license, it also includes a number of
advertising clauses that the GPL has never liked -- GPL must not be
modified for derivatives but the advertising clauses are in addition to
the GPL, so it must be modified for the combination.

Exceptions exist in the GPL for libraries and tools included in the
operating system and this is enough in most cases. GPL applications on
Windows may have problems.

http://www.openssl.org/support/faq.html#LEGAL2
2. Can I use OpenSSL with GPL software?

On many systems including the major Linux and BSD distributions, yes
(the GPL does not place restrictions on using libraries that are part of
the normal operating system distribution). 

On other systems, the situation is less clear. Some GPL software
copyright holders claim that you infringe on their rights if you use
OpenSSL with their software on operating systems that don't normally
include OpenSSL.

If you develop open source software that uses OpenSSL, you may find it
useful to choose an other license than the GPL, or state explicitly that
"This program is released under the GPL with the additional exemption
that compiling, linking, and/or using OpenSSL is allowed." If you are
using GPL software developed by others, you may want to ask the
copyright holder for permission to use their software with OpenSSL.

OpenSSL License
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer. 
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. All advertising materials mentioning features or use of this
 *    software must display the following acknowledgment:
 *    "This product includes software developed by the OpenSSL Project
 *    for use in the OpenSSL Toolkit. (http://www.openssl.org/)"
 *
 * 4. The names "OpenSSL Toolkit" and "OpenSSL Project" must not be used
to
 *    endorse or promote products derived from this software without
 *    prior written permission. For written permission, please contact
 *    openssl-core@openssl.org.
 *
 * 5. Products derived from this software may not be called "OpenSSL"
 *    nor may "OpenSSL" appear in their names without prior written
 *    permission of the OpenSSL Project.
 *
 * 6. Redistributions of any form whatsoever must retain the following
 *    acknowledgment:
 *    "This product includes software developed by the OpenSSL Project
 *    for use in the OpenSSL Toolkit (http://www.openssl.org/)"

> > I'm thinking particularly of FreeRadius but there are others. More than
> > once I thought while waiting for stuff to compile: if I'd chosen mysql
> > I'd be done by now...
> > 
> > Have a nice day,
--

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-19T16:32:17Z

Alvaro Herrera wrote:
> Joshua D. Drake wrote:
>> Martijn van Oosterhout wrote:
>>> On Thu, May 18, 2006 at 02:58:11PM -0400, Mark Woodward wrote:
>>>> The reality is that MySQL is widely supported by some very, shall we say,
>>>> "interesting" open source projects and using these products with
>>>> PostgreSQL would be a plus.
>>> The biggest headache I find with using postgres is that various GPL
>>> licenced programs have trouble directly shipping postgresql support
>>> because of our use of OpenSSL. Each and every one of those program
>>> needs to add an exception to their licence for distributors to
>>> distribute postgresql support.
>> Why would that be the case... OpenSSL and PostgreSQL both are BSD 
>> licensed... Am I missing something?
> 
> Advertising clause.  PostgreSQL doesn't have it, OpenSSL does.
> 
Is that the same clause that caused the XFree86/X.Org fork?

J


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-19T18:44:11Z

On Fri, May 19, 2006 at 11:29:23AM -0400, Mark Woodward wrote:
> I kind of agree with this statement, but while I was playing devils's
> advocate and just grousing a bit about having to use MySQL, there is a
> sort of reality of "openomics" where mind-share is everything.
> 
> The more mind-share you have, the more opportunities you have and the more
> resources become available. Not always, of course, look at OpenSSH, but
> for the most part.
> 
> As MySQL adds features, not matter how poorly implemented, and maintain a
> migration path, we will never reach their users.
> 
> PostgreSQL is better, true, but it is not ideal in many ways. It can be
> best said that the difference between PostgreSQL and MySQL is similar to
> the difference between Linux/BSD and Windows.

Actually, I think it's a lot more accurate to compare PostgreSQL and
MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was
clearly superior from a technology standpoint, and clearly playing
second-fiddle when it came to users. And now, Linux is actually
technically superior in most ways thanks to all the mindshare that's
been poured into it.
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-19T18:48:50Z

> Actually, I think it's a lot more accurate to compare PostgreSQL and
> MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was
> clearly superior from a technology standpoint, and clearly playing
> second-fiddle when it came to users. And now, Linux is actually
> technically superior in most ways thanks to all the mindshare that's
> been poured into it.

And with that, I am going to sit in a lawn chair and watch the bonfire.

Joshua D. Drake

-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-19T18:58:02Z

Moving to -advocacy, bcc to -hackers.

On Fri, May 19, 2006 at 08:11:42AM -0700, Joshua D. Drake wrote:
>  When MySQL is at that
> >point, which database do you think executives will be choosing? The one
> >with a very large userbase and lots of marketing and PR that they've
> >heard plenty about,
> 
> All due respect, Jim -- but don't you work for a publicly traded 
> database company that happens to have its own version of PostgreSQL?

Actually, we haven't had a distribution of PostgreSQL since 8.0.3, and
even then it was only a distribution; the bits were all community.

> This is really a discussion for your marketing (and mine frankly) then 
> the PostgreSQL mailing lists :)

Yes and no... should MySQL eventually become popular enough that there's
little use of PostgreSQL that hurts the community just as much as it
hurts our companies. In fact, I'd say it's already hurting the community
more than our companies; look at how many people lament about running
software XYZ because it only supports MySQL. Or about trying to find
PostgreSQL hosting providers.

But yes, the group of PostgreSQL companies should also be working to
raise awareness of PostgreSQL as a very viable OSS database.
Unfortunately, a lot of the commercial interest is in the higher-end
market. And to a large extent, this really needs to be a grass-roots
effort. After all, you don't win OSS mindshare by taking out ads or
anything like that. So I think this really needs to be a joint venture
between companies and the community.
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Chris Browne <cbbrowne@acm.org> — 2006-05-19T19:10:42Z

pgsql@mohawksoft.com ("Mark Woodward") writes:
>> Jim C. Nasby wrote:
>>> Maybe a compatability layer isn't worth doing, but I certainly
>>> think it's very much worthwhile for the community to do everything
>>> possible to encourage migration from MySQL. We should be able to
>>> lay claim to most advanced and most popular OSS database.
>>>
>>
>> We'll do that by concentrating on spiffy features, not
>> compatibility layers. I want people to use PostgreSQL because it's
>> the best, not because it's just like something else.
>>
>
> While I do agree with the ideal, the reality may not be good
> enough. Even I, a PostgreSQL user for a decade, have to use MySQL
> right now because that is what the client uses.
>
> Again, there is so much code for MySQL, a MySQL emulation layer, MEL
> for short, could allow plug and play compatibility for open source,
> and closed source, applications that otherwise would force a
> PostgreSQL user to hold his or her nose and use MySQL.

But this is essentially what killed off OS/2 in the marketplace.

IBM created a "good enough" emulation layer that it ran [early]
Windows(tm) applications sufficiently well that nobody bothered
porting applications to *properly* work with OS/2.

Microsoft then played off that with exceeding success; they made sure
that future versions of Windows(tm) were sufficiently different that
OS/2 was left orphaned.

We *are* in a sufficiently comparable state here; MySQL AB is *NOT*
our friend; they want to successfully 'take over the world,' at least
as far as they can do so with their product line...
-- 
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable.  Gov. George W. Bush declared
Tarrant County a disaster area.  Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."

Re: [OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-19T19:39:23Z

>
>> Actually, I think it's a lot more accurate to compare PostgreSQL and
>> MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was
>> clearly superior from a technology standpoint, and clearly playing
>> second-fiddle when it came to users. And now, Linux is actually
>> technically superior in most ways thanks to all the mindshare that's
>> been poured into it.
>
> And with that, I am going to sit in a lawn chair and watch the bonfire.
>

Even I know that is NOT a discussion we want to start.

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-19T20:00:19Z

On Fri, May 19, 2006 at 03:39:23PM -0400, Mark Woodward wrote:
> >
> >> Actually, I think it's a lot more accurate to compare PostgreSQL and
> >> MySQL as FreeBSD vs Linux from about 5 years ago. Back then FreeBSD was
> >> clearly superior from a technology standpoint, and clearly playing
> >> second-fiddle when it came to users. And now, Linux is actually
> >> technically superior in most ways thanks to all the mindshare that's
> >> been poured into it.
> >
> > And with that, I am going to sit in a lawn chair and watch the bonfire.
> 
> Even I know that is NOT a discussion we want to start.

Yeah, wasn't trying to start an OS flamewar; my point is that it's now
pretty hard to find anything FreeBSD related/specific, and that the
sheer popularity of Linux has given it a huge boost in terms of
development.
-- 
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

Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Mischa Sandberg <mischa@ca.sophos.com> — 2006-05-19T20:03:08Z

> On Thursday 18 May 2006 12:38, Josh Berkus wrote:
>> Personally, I'd go after MSSQL before I bothered with MySQL.   Sure, let's
>> make *migration* easier for those who wake up and smell the BS, but
>> migration can (and probably should) be one-way.

Somebody earlier was mentioning, why no automatic transformer from Transact-SQL
to PLPGSQL (maybe with a bunch of glue routines). The grammar is not a problem,
though you have to wonder at all the wired-in keywords (T-SQL always felt like 
COBOL).

The stumbling blocks are not in language, but function. Many of those functions 
are rarely used, but some big ones are quite common ...

T-SQL has statement-level triggers, and they get used a lot (some big apps ONLY 
put code in triggers). Statement-level triggers are very efficient for 
maintaining aggregates; the closest PG has are rewrite rules.

Other issues: stored procs returning multiple result sets; "print" statements; 
SELECT TOP n PERCENT; COMPUTE-expressions (subtotals); and some of the @@global 
variables that are hard to emulate @@IDENTITY being the main problem in older 
T-SQL code.

OpenXML is cool, but such a pig, that its difficulty in emulation is probably 
not an issue.

There are plenty of things that happily go away, or can be implemented with a 
client wrapper; for example, BULK INSERT and BACKUP. Other things just have no 
equivalent, and amount to no-ops in a PG world (partition functions)

A few things require some custom metadata tables (MSSQL "RULE" != PG "RULE").

If you want to convince MSSQL users to move over to PG, statement-level triggers
(with "OLD" and "NEW" rowsets) are a bottom-line requirement.
.......

For high-end MSSQL shops, a high value is being able to trace and profile 
(EXPLAIN) every client SQL command from the server side ... with plenty of 
options for selective trace.

Re: [OT] MySQL is bad, but THIS bad?

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-19T20:31:09Z

Rod Taylor wrote:
> Exceptions exist in the GPL for libraries and tools included in the
> operating system and this is enough in most cases. GPL applications on
> Windows may have problems.

What exception, exactly?  Does an exception apply to libreadline,
because list I looked, it didn't.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-19T20:34:40Z

Jim C. Nasby wrote:
> Yes and no... should MySQL eventually become popular enough that there's
> little use of PostgreSQL that hurts the community just as much as it
> hurts our companies. In fact, I'd say it's already hurting the community
> more than our companies; look at how many people lament about running
> software XYZ because it only supports MySQL. Or about trying to find
> PostgreSQL hosting providers.

With Linux, it was open for developers to join in, and that fueled its
growth.  In fact, some say Linux gained only because it was more open
the *BSD.  Fortunately for us, MySQL foster open development, which
means they will never catch us in the long run.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: [OT] MySQL is bad, but THIS bad?

Andrew Dunstan <andrew@dunslane.net> — 2006-05-19T20:38:29Z

Bruce Momjian wrote:
> Rod Taylor wrote:
>   
>> Exceptions exist in the GPL for libraries and tools included in the
>> operating system and this is enough in most cases. GPL applications on
>> Windows may have problems.
>>     
>
> What exception, exactly?  Does an exception apply to libreadline,
> because list I looked, it didn't.
>
>   

Yes, the exeption applies to libreadline, which is why we can deliver 
psql with libreadline linked on Linux, for example. But we can't on 
Windows or Solaris.

cheers

andrew

Re: [OT] MySQL is bad, but THIS bad?

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-19T20:41:20Z

Andrew Dunstan wrote:
> Bruce Momjian wrote:
> > Rod Taylor wrote:
> >   
> >> Exceptions exist in the GPL for libraries and tools included in the
> >> operating system and this is enough in most cases. GPL applications on
> >> Windows may have problems.
> >>     
> >
> > What exception, exactly?  Does an exception apply to libreadline,
> > because list I looked, it didn't.
> >
> >   
> 
> Yes, the exeption applies to libreadline, which is why we can deliver 
> psql with libreadline linked on Linux, for example. But we can't on 
> Windows or Solaris.

OK, where do you see this exception?  I have not.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: [OT] MySQL is bad, but THIS bad?

Hannu Krosing <hannu@tm.ee> — 2006-05-19T20:47:11Z

Ühel kenal päeval, R, 2006-05-19 kell 11:29, kirjutas Mark Woodward:
> > Andrew Dunstan <andrew@dunslane.net> writes:
> >> Mark Woodward wrote:
> >>> Again, there is so much code for MySQL, a MySQL emulation layer, MEL
> >>> for
> >>> short, could allow plug and play compatibility for open source, and
> >>> closed
> >>> source, applications that otherwise would force a PostgreSQL user to
> >>> hold
> >>> his or her nose and use MySQL.
> >>>
> >> If we had infinite resources this might make sense. We don't, so it
> >> doesn't. There is a real cost to producing a compatibility layer, and
> >> the cost will be those spiffy new features.
> >
> > The real problem is that there's a whole lot of stuff, such as mysql's
> > weak error checking, that I don't think a "compatibility layer" could
> > sanely provide.
> >
> I kind of agree with this statement, but while I was playing devils's
> advocate and just grousing a bit about having to use MySQL, there is a
> sort of reality of "openomics" where mind-share is everything.

Maybe we could have a Google SoC project to strip Postgres of all its
useful features (mainly ACID, but also introduce NULL==""==0 and weak
foreign keys) to make it MySQL compatible. 

And if we start locking a table on each update, we can get rid of MVCC
altogether and do fast selects, in-place updates and select from indexes
only.

Then that branch (PostgreSQLite) could be pimped to MySQL users as a way
to "support" postgres.

Nah, not really useful, but perhaps we could publicize the list of
things that would have to go to be compatible :P

----------------
Hannu

Re: [OT] MySQL is bad, but THIS bad?

Martijn van Oosterhout <kleptog@svana.org> — 2006-05-19T20:53:12Z

On Fri, May 19, 2006 at 04:41:20PM -0400, Bruce Momjian wrote:
> > Yes, the exeption applies to libreadline, which is why we can deliver 
> > psql with libreadline linked on Linux, for example. But we can't on 
> > Windows or Solaris.
> 
> OK, where do you see this exception?  I have not.

The exception is not relevent in this case. The exception is to allow
GPL applications to work on non-free operating systems. Obviously a
GPL'd application on Windows can never supply the source to the Win32
libraries.

libreadline is not a problem because you can distribute postgresql
compiled with readline and comply with all licences involved
simultaneously. It doesn't work with openssl because the licence
requires things that are incompatable with the GPL.

The openssl faq suggest that you can take advantage of the exception,
which reads:

  However, as a special exception, the source code distributed need not
  include anything that is normally distributed (in either source or
  binary form) with the major components (compiler, kernel, and so on)
  of the operating system on which the executable runs, unless that
  component itself accompanies the executable.

I don't buy that argument, and I'm not the only one. OpenSSL is an
optional part of most Linuxes, so there's no way you can use that
exception.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-19T21:16:00Z

Bruce Momjian wrote:
> Jim C. Nasby wrote:
>> Yes and no... should MySQL eventually become popular enough that there's
>> little use of PostgreSQL that hurts the community just as much as it
>> hurts our companies. In fact, I'd say it's already hurting the community
>> more than our companies; look at how many people lament about running
>> software XYZ because it only supports MySQL. Or about trying to find
>> PostgreSQL hosting providers.
> 
> With Linux, it was open for developers to join in, and that fueled its
> growth.  In fact, some say Linux gained only because it was more open
> the *BSD.

I would agree with this. BSD at one time made Linux look like a toy. If 
the *core* of the BSD teams would have been more open in there 
development, linux would probably be relegated to also ran status.

   Fortunately for us, MySQL foster open development, which
> means they will never catch us in the long run.
> 


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Hannu Krosing <hannu@skype.net> — 2006-05-19T21:24:20Z

Ühel kenal päeval, R, 2006-05-19 kell 09:40, kirjutas Christopher
Kings-Lynne:
> > We also need better support for non C locales in tsearch.  As I was porting 
> > mysql's sakila sample database I was reminded just how painful it is when you 
> > initdb in a non-supported locale (which is probably the default on the 
> > majority of distros out there)
> 
> 
> In 8.2 tsearch2 supports utf8...

Utf8 is encoding, but I guess that tsearch2 does not care much about
locales ?

tsearch2 does not do sorts, but it may care about upper()/lower() for
languages that support it, so there our locale support should be good
for utf8 encoding if we care about language-specific case insensitivity.


-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com

Re: [OT] MySQL is bad, but THIS bad?

Jonah H. Harris <jonah.harris@gmail.com> — 2006-05-19T22:01:19Z

On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote:
> And with that, I am going to sit in a lawn chair and watch the bonfire.

This is one of the finest examples of unfocused discussions I've ever
seen on -hackers... while surely entertaining, what a huge waste of
time.


-- 
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 2nd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

Re: [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-19T22:11:56Z

Jonah H. Harris wrote:
> On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote:
>> And with that, I am going to sit in a lawn chair and watch the bonfire.
> 
> This is one of the finest examples of unfocused discussions I've ever
> seen on -hackers... while surely entertaining, what a huge waste of
> time.

All discussions on mailing lists are unfocused and a waste of time to 
some degree, even the best of them because by its very nature, email is 
a time wasting tool.

Have a great weekend!

Joshua D. Drake


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Re: [OT] MySQL is bad, but THIS bad?

Hannu Krosing <hannu@skype.net> — 2006-05-19T22:39:18Z

Ühel kenal päeval, R, 2006-05-19 kell 22:53, kirjutas Martijn van
Oosterhout:

> libreadline is not a problem because you can distribute postgresql
> compiled with readline and comply with all licences involved
> simultaneously. 

oh? my impression was that we are clear, because libreadline is just one
of "readline" implementations we support.

> It doesn't work with openssl because the licence
> requires things that are incompatable with the GPL.

Still clients can compile/use libpq without OpenSSL and be on safe
ground.

> The openssl faq suggest that you can take advantage of the exception,
> which reads:
> 
>   However, as a special exception, the source code distributed need not
>   include anything that is normally distributed (in either source or
>   binary form) with the major components (compiler, kernel, and so on)
>   of the operating system on which the executable runs, unless that
>   component itself accompanies the executable.
> 
> I don't buy that argument, and I'm not the only one. OpenSSL is an
> optional part of most Linuxes, so there's no way you can use that
> exception.

But on most linuxes optional parts ar also "normally distributed" :P

Even network drivers may be counted optional for pure linux (kernel)
experience, but are still normally distributed.

-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com

NOTICE: This communication contains privileged or other confidential
information. If you have received it in error, please advise the sender
by reply email and immediately delete the message and any attachments
without copying or disclosing the contents.

Re: [OT] MySQL is bad, but THIS bad?

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-19T23:04:47Z

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Fri, May 19, 2006 at 04:41:20PM -0400, Bruce Momjian wrote:
> > > Yes, the exeption applies to libreadline, which is why we can deliver 
> > > psql with libreadline linked on Linux, for example. But we can't on 
> > > Windows or Solaris.
> > 
> > OK, where do you see this exception?  I have not.
> 
> The exception is not relevent in this case. The exception is to allow
> GPL applications to work on non-free operating systems. Obviously a
> GPL'd application on Windows can never supply the source to the Win32
> libraries.
> 
> libreadline is not a problem because you can distribute postgresql
> compiled with readline and comply with all licences involved
> simultaneously. It doesn't work with openssl because the licence
> requires things that are incompatable with the GPL.

My question is whether psql using libreadline.so has to be GPL, meaning
the psql source has to be included in a binary distribution.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: [OT] MySQL is bad, but THIS bad?

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-19T23:05:53Z

Joshua D. Drake wrote:
> Jonah H. Harris wrote:
> > On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote:
> >> And with that, I am going to sit in a lawn chair and watch the bonfire.
> > 
> > This is one of the finest examples of unfocused discussions I've ever
> > seen on -hackers... while surely entertaining, what a huge waste of
> > time.
> 
> All discussions on mailing lists are unfocused and a waste of time to 
> some degree, even the best of them because by its very nature, email is 
> a time wasting tool.

Let's not forget my bad jokes.  I am still chuckling at JavaZero.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-20T00:19:48Z

> > Fortunately for us, MySQL foster open development, which
> > means they will never catch us in the long run.

Sorry, I meant to say:

  Fortunately for us, MySQL _do_ _not_ foster open development, which
  means they will never catch us in the long run.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: [OT] MySQL is bad, but THIS bad?

Martijn van Oosterhout <kleptog@svana.org> — 2006-05-20T06:23:21Z

On Fri, May 19, 2006 at 07:04:47PM -0400, Bruce Momjian wrote:
> > libreadline is not a problem because you can distribute postgresql
> > compiled with readline and comply with all licences involved
> > simultaneously. It doesn't work with openssl because the licence
> > requires things that are incompatable with the GPL.
> 
> My question is whether psql using libreadline.so has to be GPL, meaning
> the psql source has to be included in a binary distribution.

IANAL, but yes. Or any other of the methods allowed, like providing a
written voucher valid for at least three years. People who feel they
need to keep the source to psql secret should link against libeditline
instead.

The way I understand it, the GPL affects programs in two main ways:

1. A program which is GPL'd must, when distributed, be able to provide
all source used to build it under terms compatable with the GPL.

2. A program which includes a GPL'd header file while building, must,
when distributed, provide its own source and the library under GPL
compatable terms, but not necessariliy the source of anything else
needed to build it. This is why it's OK that psql links against openssl
and readline.

These are obviously only relevent when distributing precompiled
binaries. If you are only distributing source, none of the above
applies to you.

There's a third method that some people claim, but I don't buy. This
where a program using an interface of a GPL'd library somehow become a
derived work of said library. That's just way whacked out.

You may ofcourse disagree with any of the above, and hey, if you have a
lawyer to back you up, who am I to argue?

As for why you don't solve the problem by distributing a libpq not
compiled against OpenSSL, well, that's a different question. Back when
SSL was considered an arms exports by the US, having both SSL and
non-SSL versions was common (and a big PITA). When that disappeared,
the main reason for the split went away and people started compiling
SSL by default. This solved the problem for 99% of programs.

However, one tiny subset remains problematic:
- A library implements SSL, but only using OpenSSL
- The library doesn't use the GPL, or doesn't have an OpenSSL exception
clause.
- A GPL'd program uses this library, without an OpenSSL exception
clause.

In this subset of a subset of a subset of programs, it's a problem.
Many libraries that implement SSL provide an alternative to OpenSSL,
many programs using such libraries have exception clauses so that
there's just a handful of programs and libraries that are problematic.

As long as there's a possibility that the situation can change (either
every GPL program using postgresql gains an exception clause, or
postgresql might someday support some other library) it will probably
stay this way.

If the the postgresql core decides that OpenSSL will be the only SSL
ever supported, no matter what, well, the split distribution may yet
happen. In the meantime, we have status quo.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: [OT] MySQL is bad, but THIS bad?

Lukas Smith <smith@pooteeweet.org> — 2006-05-20T08:36:25Z

Hi,

I really think that PostgreSQL could benefit from a packaged solution 
that incorporates a lot of the contrib stuff (tsearch2, maybe even some 
replication setups ..). I really like the approach that PostgreSQL is a 
clean yet highly extensible base from which other people can build their 
specific tools.

However the fact of the matter is that MySQL provides a good enough, yet 
very easy to setup and do semi advanced things (like full text, 
replication etc). My key point here is _good enough_. This means there 
is obviously still an opportunity to give them something _better_, as 
long as it does not get in their way of being easy to setup.

The improvements to the installer are great, but there simply needs to 
be a packaged solution that adds more of the things people are very 
likely to use. From my understanding Bizgres goes in that direction? I 
just think that whatever highly packaged solution PostgreSQL picks, this 
should be the download that is pushed at conferences, in articles and 
books. People with a clue will still know where they can get the clean base.

regards,
Lukas

Re: [OT] MySQL is bad, but THIS bad?

Martijn van Oosterhout <kleptog@svana.org> — 2006-05-20T08:42:32Z

On Sat, May 20, 2006 at 10:36:25AM +0200, Lukas Smith wrote:
> The improvements to the installer are great, but there simply needs to 
> be a packaged solution that adds more of the things people are very 
> likely to use. From my understanding Bizgres goes in that direction? I 
> just think that whatever highly packaged solution PostgreSQL picks, this 
> should be the download that is pushed at conferences, in articles and 
> books. People with a clue will still know where they can get the clean base.

There is a fantastic packaged solution already:

apt-get install postgresql-8.1 postgresql-contrib-8.1

Voila! Tsearch installed at your fingertips. What else were you
expecting?

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: [OT] MySQL is bad, but THIS bad?

Lukas Smith <smith@pooteeweet.org> — 2006-05-20T08:48:41Z

Martijn van Oosterhout wrote:
> On Sat, May 20, 2006 at 10:36:25AM +0200, Lukas Smith wrote:
>> The improvements to the installer are great, but there simply needs to 
>> be a packaged solution that adds more of the things people are very 
>> likely to use. From my understanding Bizgres goes in that direction? I 
>> just think that whatever highly packaged solution PostgreSQL picks, this 
>> should be the download that is pushed at conferences, in articles and 
>> books. People with a clue will still know where they can get the clean base.
> 
> There is a fantastic packaged solution already:
> 
> apt-get install postgresql-8.1 postgresql-contrib-8.1
> 
> Voila! Tsearch installed at your fingertips. What else were you
> expecting?

I expect this to be one package and I expect this to be what is pushed 
as the default package on all platforms. If someone just sat in an pgsql 
talk (or even a talk that mentions pgsql), has read an article, picked 
up a book .. this is what he should be downloading and installing.

I do think that the name PostgreSQL has a fair amount of mindshare, but 
I do not think that this package needs to be called PostgreSQL 
necessarily. The problem with calling it PostgreSQL is that this would 
mean moving things into the core distribution which do not belong there. 
But expecting the unwashed masses to understand that they need to 
install contrib ontop of PostgreSQL is not a good idea.

If PostgreSQL pushes FooSQL as its "packaged solution" at all 
opportunities I am sure it would quickly get into the heads of people 
and if done in a concerted effort along with the corporate sponsors it 
could provide for a huge marketing opportunity and a slew of articles 
from the press. But that is a topic for another list.

regards,
Lukas

Re: [OT] MySQL is bad, but THIS bad?

Tino Wildenhain <tino@wildenhain.de> — 2006-05-20T08:57:14Z

Lukas Kahwe Smith wrote:
...
>> apt-get install postgresql-8.1 postgresql-contrib-8.1
>>
>> Voila! Tsearch installed at your fingertips. What else were you
>> expecting?
> 
> I expect this to be one package and I expect this to be what is pushed
> as the default package on all platforms. If someone just sat in an pgsql
> talk (or even a talk that mentions pgsql), has read an article, picked
> up a book .. this is what he should be downloading and installing.

...
> If PostgreSQL pushes FooSQL as its "packaged solution" at all
> opportunities I am sure it would quickly get into the heads of people
> and if done in a concerted effort along with the corporate sponsors it
> could provide for a huge marketing opportunity and a slew of articles
> from the press. But that is a topic for another list.
> 

maybe the package should read: postgresql-heavy
postgresql-complete or even as you seem to suggest: postgresql
where the other parts are postgresql-clients, postgresql-server
postgresql-contrib and so on.

Beware, however, if "complete" means with gui clients, not
all people would be happy if you pull X and friends to their
unix servers :-) So whatever is in "complete" should depend
on the target platform.

I think the naming schema of the debian packages go in the
right direction - maybe this can be harmonized along the
distributions?

Regards
Tino Wildenhain

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Dawid Kuroczko <qnex42@gmail.com> — 2006-05-20T12:29:01Z

On 5/20/06, Lukas Smith <smith@pooteeweet.org> wrote:
> The improvements to the installer are great, but there simply needs to
> be a packaged solution that adds more of the things people are very
> likely to use. From my understanding Bizgres goes in that direction? I
> just think that whatever highly packaged solution PostgreSQL picks, this
> should be the download that is pushed at conferences, in articles and
> books. People with a clue will still know where they can get the clean base.

Hmm, a Comprehensive PostgreSQL Archive Network? ;)

I mean, something like CPAN, CTAN or CRAN? :)

I mean, the -contrib is great, but pushing other things there is a bit
tricky (to say the least) from the maintenance point of view.  (Every
bugfix, a new release of -contrib, etc, etc...).

Then again PGfoundry is great to keep development centered, but
finding and building a new package is not really a one-liner, and
if you're unlucky you might get alpha-quality code installed. :)

I think a CPgAN-like solution would be the best.  A uniform method
of getting approved Pg extensions.  It would simplify installing the
extensions, and would encourage distributions to package such
extensions.  Somebody suggested apt-get install postgresql-contrib.
Imagine:
apt-get install postgresql-datatype-fqdn
apt-get install postgresql-gist-ltree
...and so on.

Regards,
     Dawid

Re: [OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-20T14:41:21Z

>
> My question is whether psql using libreadline.so has to be GPL, meaning
> the psql source has to be included in a binary distribution.

If I understand what I have been told by lawyers, here's what using a GPL,
and NOT LGPL, library means:

According to RMS, the definition of a derivitive work is one which shares
the same address space when running. The in-memory process separation also
separates works. One may argue this definition, but it is in supporting
documents to the GPL and likely to be considered as the intention of the
GPL in a court of law.

There is no requirement of shipping source with a binary. One must "make
available" the source. This can be done by a web site or alternate CD
distribution. It need not be free, as in beer, but must be free of any
restrictions beyond those of the GPL.

There is no requirement that one would need to make the source of the 3rd
party GPL library available, as it is available from the original source
from whence it was obtained in the first place. Any changes, however,
made, by you, to that library must be made available. (If you do not make
modifications to libreadline, you don't even need to worry about it.)

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-20T15:31:09Z

On Fri, May 19, 2006 at 06:01:19PM -0400, Jonah H. Harris wrote:
> On 5/19/06, Joshua D. Drake <jd@commandprompt.com> wrote:
> >And with that, I am going to sit in a lawn chair and watch the bonfire.
> 
> This is one of the finest examples of unfocused discussions I've ever
> seen on -hackers... while surely entertaining, what a huge waste of
> time.

I tried a few times to move it to -advococy...
-- 
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

Re: [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-20T15:34:26Z

On Sat, May 20, 2006 at 10:36:25AM +0200, Lukas Smith wrote:
> Hi,
> 
> I really think that PostgreSQL could benefit from a packaged solution 
> that incorporates a lot of the contrib stuff (tsearch2, maybe even some 
> replication setups ..). I really like the approach that PostgreSQL is a 
> clean yet highly extensible base from which other people can build their 
> specific tools.

I think we're starting to see some of that, with things like the live
CD.

What I'd rather see time spent on is a framework that makes it easier to
grab things from pgFoundry. To use a bad example, think CPAN for
PostgreSQL.
-- 
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

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Joshua D. Drake <jd@commandprompt.com> — 2006-05-20T16:55:23Z

>
> Then again PGfoundry is great to keep development centered, but
> finding and building a new package is not really a one-liner, and
> if you're unlucky you might get alpha-quality code installed. :)
>
Mammoth PostgreSQL was designed to fill this role. It is an FOSS project
(www.mammothpostgresql.org) that is designed to be a COMPLETE postgresql
distribution.

Joshua D. Drake


>

Re: [OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-20T17:58:56Z

> On Fri, May 19, 2006 at 07:04:47PM -0400, Bruce Momjian wrote:
>> > libreadline is not a problem because you can distribute postgresql
>> > compiled with readline and comply with all licences involved
>> > simultaneously. It doesn't work with openssl because the licence
>> > requires things that are incompatable with the GPL.
>>
>> My question is whether psql using libreadline.so has to be GPL, meaning
>> the psql source has to be included in a binary distribution.
>
> IANAL, but yes. Or any other of the methods allowed, like providing a
> written voucher valid for at least three years. People who feel they
> need to keep the source to psql secret should link against libeditline
> instead.
>
> The way I understand it, the GPL affects programs in two main ways:
>
> 1. A program which is GPL'd must, when distributed, be able to provide
> all source used to build it under terms compatable with the GPL.

This is not technically true. If you incorporate GPL code that is
publically available and unchanged, you needn't provide the 3rd party
packages.

>
> 2. A program which includes a GPL'd header file while building, must,
> when distributed, provide its own source and the library under GPL
> compatable terms, but not necessariliy the source of anything else
> needed to build it. This is why it's OK that psql links against openssl
> and readline.

This is sort of a disputable position, and RMS himself isn't clear. If the
header files are simply definitions and declarations, then no GPL material
is actually included in a binary. However, inline functions and macros may
constitute code.

>
> These are obviously only relevent when distributing precompiled
> binaries. If you are only distributing source, none of the above
> applies to you.
>
Of course.

> There's a third method that some people claim, but I don't buy. This
> where a program using an interface of a GPL'd library somehow become a
> derived work of said library. That's just way whacked out.

There is no supporting argument for that, however, RMS supporting writings
indicate that he defines "derived" as being in the same process space.

>
> You may ofcourse disagree with any of the above, and hey, if you have a
> lawyer to back you up, who am I to argue?

I have talked to too many lawyers, sigh, aout this stuff.

>
> As for why you don't solve the problem by distributing a libpq not
> compiled against OpenSSL, well, that's a different question. Back when
> SSL was considered an arms exports by the US, having both SSL and
> non-SSL versions was common (and a big PITA). When that disappeared,
> the main reason for the split went away and people started compiling
> SSL by default. This solved the problem for 99% of programs.
>
> However, one tiny subset remains problematic:
> - A library implements SSL, but only using OpenSSL
> - The library doesn't use the GPL, or doesn't have an OpenSSL exception
> clause.
> - A GPL'd program uses this library, without an OpenSSL exception
> clause.
>
> In this subset of a subset of a subset of programs, it's a problem.
> Many libraries that implement SSL provide an alternative to OpenSSL,
> many programs using such libraries have exception clauses so that
> there's just a handful of programs and libraries that are problematic.
>
> As long as there's a possibility that the situation can change (either
> every GPL program using postgresql gains an exception clause, or
> postgresql might someday support some other library) it will probably
> stay this way.
>
> If the the postgresql core decides that OpenSSL will be the only SSL
> ever supported, no matter what, well, the split distribution may yet
> happen. In the meantime, we have status quo.
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
>> From each according to his ability. To each according to his ability to
>> litigate.
>

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Josh Berkus <josh@agliodbs.com> — 2006-05-21T21:58:17Z

Mischa,

> Somebody earlier was mentioning, why no automatic transformer from
> Transact-SQL to PLPGSQL (maybe with a bunch of glue routines). The grammar
> is not a problem, though you have to wonder at all the wired-in keywords
> (T-SQL always felt like COBOL).

Actually, porting TSQL to PL/pgSQL would be very hard.   I speak as an expert 
TSQL developer.  For example, most data manipulation in TSQL is done through 
updatable cursors, something we don't currently support.  Also, T-SQL uses 
un-ordered, callable parameters for SPs, something which we *also* don't 
support.

> Other issues: stored procs returning multiple result sets; "print"
> statements; SELECT TOP n PERCENT; COMPUTE-expressions (subtotals); and some
> of the @@global variables that are hard to emulate @@IDENTITY being the
> main problem in older T-SQL code.

Yeah, but @@IDENTITY sucks.  Most MSSQL developers are glad to leave it 
behind.  ;-)

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-22T15:00:22Z

On Fri, May 19, 2006 at 01:03:08PM -0700, Mischa Sandberg wrote:
> >On Thursday 18 May 2006 12:38, Josh Berkus wrote:
> >>Personally, I'd go after MSSQL before I bothered with MySQL.   Sure, let's
> >>make *migration* easier for those who wake up and smell the BS, but
> >>migration can (and probably should) be one-way.
> 
> Somebody earlier was mentioning, why no automatic transformer from 
> Transact-SQL
> to PLPGSQL (maybe with a bunch of glue routines). The grammar is not a 
> problem,
> though you have to wonder at all the wired-in keywords (T-SQL always felt 
> like COBOL).
> 
> The stumbling blocks are not in language, but function. Many of those 
> functions are rarely used, but some big ones are quite common ...
> 
> T-SQL has statement-level triggers, and they get used a lot (some big apps 
> ONLY put code in triggers). Statement-level triggers are very efficient for 
> maintaining aggregates; the closest PG has are rewrite rules.
 
Yeah, I wish PostgreSQL had them. I've got clients that could certainly
make use of them.

> For high-end MSSQL shops, a high value is being able to trace and profile 
> (EXPLAIN) every client SQL command from the server side ... with plenty of 
> options for selective trace.

This would also be highly valuable to have in PostgreSQL.
-- 
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

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-22T15:02:38Z

On Sun, May 21, 2006 at 02:58:17PM -0700, Josh Berkus wrote:
> Mischa,
> 
> > Somebody earlier was mentioning, why no automatic transformer from
> > Transact-SQL to PLPGSQL (maybe with a bunch of glue routines). The grammar
> > is not a problem, though you have to wonder at all the wired-in keywords
> > (T-SQL always felt like COBOL).
> 
> Actually, porting TSQL to PL/pgSQL would be very hard.   I speak as an expert 
> TSQL developer.  For example, most data manipulation in TSQL is done through 
> updatable cursors, something we don't currently support.  Also, T-SQL uses 
> un-ordered, callable parameters for SPs, something which we *also* don't 
> support.

And TSQL doesn't fail a transaction on an error, resulting in code like

UPDATE ...

if @@error = 0 then
    UPDATE ...
end
if @@error = 0 then
...

Makes for a lot of needless bloat when going to PostgreSQL. Supposedly
this is changed in MSSQL05 though.
-- 
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

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Martijn van Oosterhout <kleptog@svana.org> — 2006-05-22T15:06:47Z

On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > T-SQL has statement-level triggers, and they get used a lot (some big apps 
> > ONLY put code in triggers). Statement-level triggers are very efficient for 
> > maintaining aggregates; the closest PG has are rewrite rules.
>  
> Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> make use of them.

What are you referring to that is not supported currently?

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
    ON table FOR EACH STATEMENT
    EXECUTE PROCEDURE funcname ( arguments )

> > For high-end MSSQL shops, a high value is being able to trace and profile 
> > (EXPLAIN) every client SQL command from the server side ... with plenty of 
> > options for selective trace.
> 
> This would also be highly valuable to have in PostgreSQL.

Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is
less so)?

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Harald Fuchs <hf0406x@protecting.net> — 2006-05-22T15:24:17Z

In article <20060522150647.GE24404@svana.org>,
Martijn van Oosterhout <kleptog@svana.org> writes:

> On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
>> > T-SQL has statement-level triggers, and they get used a lot (some big apps 
>> > ONLY put code in triggers). Statement-level triggers are very efficient for 
>> > maintaining aggregates; the closest PG has are rewrite rules.
>> 
>> Yeah, I wish PostgreSQL had them. I've got clients that could certainly
>> make use of them.

> What are you referring to that is not supported currently?

Probably he means that a complete implementation of statement-level
triggers should include a way to access the rows affected by the
trigger call.

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Dawid Kuroczko <qnex42@gmail.com> — 2006-05-22T15:30:12Z

On 5/22/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > > T-SQL has statement-level triggers, and they get used a lot (some big apps
> > > ONLY put code in triggers). Statement-level triggers are very efficient for
> > > maintaining aggregates; the closest PG has are rewrite rules.
> >
> > Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> > make use of them.
>
> What are you referring to that is not supported currently?
>
> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
>     ON table FOR EACH STATEMENT
>     EXECUTE PROCEDURE funcname ( arguments )

Each programming language that supports triggers has its own method for making
the trigger input data available to the trigger function. This input
data includes the
type of trigger event (e.g., INSERT or UPDATE) as well as any
arguments that were
listed in CREATE TRIGGER. For a row-level trigger, the input data also
includes the
NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and
DELETE triggers. Statement-level triggers do not currently have any way to
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
examine the individual row(s) modified by the statement.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

So, if user types:
DELETE FROM foo WHERE doh ='bar' and baf > 5;
(resulting, say with 5000 deleted rows)

...you can either create on delete trigger row level, which will:
UPDATE foo_stat SET count = count -1 WHERE doh='bar';
...which will be fired 5000 times.

The idea is that you could write a statement level trigger
which will count deleted rows and issue
UPDATE foo_stat SET count=count-5000 WHERE doh='bar';

   Regards,
      Dawid

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-22T15:41:59Z

On Mon, May 22, 2006 at 05:06:47PM +0200, Martijn van Oosterhout wrote:
> On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > > T-SQL has statement-level triggers, and they get used a lot (some big apps 
> > > ONLY put code in triggers). Statement-level triggers are very efficient for 
> > > maintaining aggregates; the closest PG has are rewrite rules.
> >  
> > Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> > make use of them.
> 
> What are you referring to that is not supported currently?
> 
> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
>     ON table FOR EACH STATEMENT
>     EXECUTE PROCEDURE funcname ( arguments )
 
And that doesn't give you any information on the rows that were
modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
you can select from inside the trigger as if they were real tables.

> > > For high-end MSSQL shops, a high value is being able to trace and profile 
> > > (EXPLAIN) every client SQL command from the server side ... with plenty of 
> > > options for selective trace.
> > 
> > This would also be highly valuable to have in PostgreSQL.
> 
> Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is
> less so)?

It's not so much about which case, it's about being able to control the
them from another connection. IE:

Show EXPLAIN for every query run on PID blah
Show EXPLAIN ANALYZE for every query run on PID blah where the command
string matches this regex
etc. Having the ability to do random sampling in there could be very
handy as well. As would firing on queries that hit certain tables
(though the regex functionality could handle that).
-- 
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

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-22T17:10:48Z

On Sat, May 20, 2006 at 02:29:01PM +0200, Dawid Kuroczko wrote:
> On 5/20/06, Lukas Smith <smith@pooteeweet.org> wrote:
> >The improvements to the installer are great, but there simply needs to
> >be a packaged solution that adds more of the things people are very
> >likely to use. From my understanding Bizgres goes in that direction? I
> >just think that whatever highly packaged solution PostgreSQL picks, this
> >should be the download that is pushed at conferences, in articles and
> >books. People with a clue will still know where they can get the clean 
> >base.
> 
> Hmm, a Comprehensive PostgreSQL Archive Network? ;)
> 
> I mean, something like CPAN, CTAN or CRAN? :)
> 
> I mean, the -contrib is great, but pushing other things there is a bit
> tricky (to say the least) from the maintenance point of view.  (Every
> bugfix, a new release of -contrib, etc, etc...).
> 
> Then again PGfoundry is great to keep development centered, but
> finding and building a new package is not really a one-liner, and
> if you're unlucky you might get alpha-quality code installed. :)
 
I don't see any reason why CPgAN would need to change pgFoundry at all.
In fact, my thought was that any such system should use pgFoundry as
it's backend/repository.

> I think a CPgAN-like solution would be the best.  A uniform method
> of getting approved Pg extensions.  It would simplify installing the
> extensions, and would encourage distributions to package such
> extensions.  Somebody suggested apt-get install postgresql-contrib.
> Imagine:
> apt-get install postgresql-datatype-fqdn
> apt-get install postgresql-gist-ltree
> ...and so on.

Except that apt doesn't work on all platforms. Though it would certainly
make sense to look at lifting the framework for CPgAN from somewhere,
rather than coding it ourselves.
-- 
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

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Martijn van Oosterhout <kleptog@svana.org> — 2006-05-22T18:45:07Z

On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> >     ON table FOR EACH STATEMENT
> >     EXECUTE PROCEDURE funcname ( arguments )
>  
> And that doesn't give you any information on the rows that were
> modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> you can select from inside the trigger as if they were real tables.

Is this on the TODO list? It doesn't seem too difficult to create a
tuplestore and store the NEW and OLD tuples there and pass the whole
set to the trigger.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Mark Woodward <pgsql@mohawksoft.com> — 2006-05-22T18:52:29Z

> On Sat, May 20, 2006 at 02:29:01PM +0200, Dawid Kuroczko wrote:
>> On 5/20/06, Lukas Smith <smith@pooteeweet.org> wrote:
>> >The improvements to the installer are great, but there simply needs to
>> >be a packaged solution that adds more of the things people are very
>> >likely to use. From my understanding Bizgres goes in that direction? I
>> >just think that whatever highly packaged solution PostgreSQL picks,
>> this
>> >should be the download that is pushed at conferences, in articles and
>> >books. People with a clue will still know where they can get the clean
>> >base.
>>
>> Hmm, a Comprehensive PostgreSQL Archive Network? ;)
>>
>> I mean, something like CPAN, CTAN or CRAN? :)
>>
>> I mean, the -contrib is great, but pushing other things there is a bit
>> tricky (to say the least) from the maintenance point of view.  (Every
>> bugfix, a new release of -contrib, etc, etc...).
>>
>> Then again PGfoundry is great to keep development centered, but
>> finding and building a new package is not really a one-liner, and
>> if you're unlucky you might get alpha-quality code installed. :)
>
> I don't see any reason why CPgAN would need to change pgFoundry at all.
> In fact, my thought was that any such system should use pgFoundry as
> it's backend/repository.
>
>> I think a CPgAN-like solution would be the best.  A uniform method
>> of getting approved Pg extensions.  It would simplify installing the
>> extensions, and would encourage distributions to package such
>> extensions.  Somebody suggested apt-get install postgresql-contrib.
>> Imagine:
>> apt-get install postgresql-datatype-fqdn
>> apt-get install postgresql-gist-ltree
>> ...and so on.
>
> Except that apt doesn't work on all platforms. Though it would certainly
> make sense to look at lifting the framework for CPgAN from somewhere,
> rather than coding it ourselves.


A CPgAN would be a great idea in theory, but I have reservations.

As a software developer, I'm fine with pgfoundery, but as a DB admin, and
one who deploys data centers from time to time, I'd like to see something
closer to the contrib.

If I could have any influence at all, I'd like to see "contrib"
essentially go away in the main distribution and replaced or renamed
"extensions." Then, some advisory group "blesses" extensions, and those
extensions get packaged into a PostgreSQL extensions pack. I, again as a
DB admin, would have NO problem with PostgreSQL playing favorites and
picking best of breed for these extensions.

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Dawid Kuroczko <qnex42@gmail.com> — 2006-05-22T18:56:14Z

On 5/22/06, Mark Woodward <pgsql@mohawksoft.com> wrote:
> > Except that apt doesn't work on all platforms. Though it would certainly
> > make sense to look at lifting the framework for CPgAN from somewhere,
> > rather than coding it ourselves.
>
> A CPgAN would be a great idea in theory, but I have reservations.
>
> As a software developer, I'm fine with pgfoundery, but as a DB admin, and
> one who deploys data centers from time to time, I'd like to see something
> closer to the contrib.
>
> If I could have any influence at all, I'd like to see "contrib"
> essentially go away in the main distribution and replaced or renamed
> "extensions." Then, some advisory group "blesses" extensions, and those
> extensions get packaged into a PostgreSQL extensions pack. I, again as a
> DB admin, would have NO problem with PostgreSQL playing favorites and
> picking best of breed for these extensions.

The "problem" with contrib is that no actively developed projects should
be there.  It is a feature, not a bug.  If it is actively developed, it may be
buggy. If it is proven over time, it can be safely used.  Also, for a contrib
it is inefficient to release a whole -contrib whenever a subproject releases
new release.  This "forces" -contrib to use stable-and-unchanging packages.
This also makes it extremaly hard to put new or niche projects.  New are
risky, because they may need immediate bugfixes.  Niche projects used
by a minority of users bloat -contrib and force more frequent releases,
both of which are well, not preferred.

Of course -contrib is great, we all know it. I think a "CPgAN" would be
a good testbed/incubator for new packages, some of which should
eventually get into -contrib.

Also, assuming there is a "pginstall dbanme packagename" interface,
a -contrib package should register all its subpackages within that
system.  So, you install postgresql-contrib, and then you can type:

pg_package install mydb index/ltree

and later, provided you change your mind:

pg_package remove mydb index/ltree
(with -f option to "insert CASCADE whenever possible ;)).

This would be somewhat similar to current createlang(1) and friends. :)

   Regards,
      Dawid

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-22T19:11:14Z

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > >     ON table FOR EACH STATEMENT
> > >     EXECUTE PROCEDURE funcname ( arguments )
> >  
> > And that doesn't give you any information on the rows that were
> > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > you can select from inside the trigger as if they were real tables.
> 
> Is this on the TODO list? It doesn't seem too difficult to create a
> tuplestore and store the NEW and OLD tuples there and pass the whole
> set to the trigger.

TODO has:

	* Allow statement-level triggers to access modified rows

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Alvaro Herrera <alvherre@commandprompt.com> — 2006-05-22T19:14:18Z

Martijn van Oosterhout wrote:
> On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > >     ON table FOR EACH STATEMENT
> > >     EXECUTE PROCEDURE funcname ( arguments )
> >  
> > And that doesn't give you any information on the rows that were
> > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > you can select from inside the trigger as if they were real tables.
> 
> Is this on the TODO list? It doesn't seem too difficult to create a
> tuplestore and store the NEW and OLD tuples there and pass the whole
> set to the trigger.

OTOH it would be nice to be able to save only the TIDs of the tuples,
not the tuples themselves ... maybe create a "TIDstore"?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: [HACKERS] [OT] MySQL is bad, but THIS bad?

Martijn van Oosterhout <kleptog@svana.org> — 2006-05-22T19:14:30Z

On Mon, May 22, 2006 at 08:56:14PM +0200, Dawid Kuroczko wrote:
> Also, assuming there is a "pginstall dbanme packagename" interface,
> a -contrib package should register all its subpackages within that
> system.  So, you install postgresql-contrib, and then you can type:
> 
> pg_package install mydb index/ltree

Incidently, this reminds me of something I proposed late last year
relating to easy installation of modules:

http://archives.postgresql.org/pgsql-hackers/2005-09/msg00476.php

To idea being that for most modules you only need a script to "install"
it (declare functions and types). For these modules you can actually
embed the script into the library itself. Thus, the installer only
needs to be pointed to the shared object and it's done.

If this installer tracked installed modules, you could use pg_depends
to track what installed what and thus uninstall stuff afterwards. It
might help pg_dump upgrades across versions.

Anyway, it was just a thought and it didn't generate any comments at
the time.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-22T19:15:05Z

On Mon, May 22, 2006 at 08:45:07PM +0200, Martijn van Oosterhout wrote:
> On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > >     ON table FOR EACH STATEMENT
> > >     EXECUTE PROCEDURE funcname ( arguments )
> >  
> > And that doesn't give you any information on the rows that were
> > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > you can select from inside the trigger as if they were real tables.
> 
> Is this on the TODO list? It doesn't seem too difficult to create a

No.

> tuplestore and store the NEW and OLD tuples there and pass the whole
> set to the trigger.

I never thought about it being that easy, but yeah, it probably wouldn't
be terribly difficult.

Can we get this on the TODO? Or does someone want to just come up with a
patch if it's easy enough?
-- 
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

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-22T19:33:34Z

On Mon, May 22, 2006 at 03:11:14PM -0400, Bruce Momjian wrote:
> Martijn van Oosterhout wrote:
> -- Start of PGP signed section.
> > On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > >     ON table FOR EACH STATEMENT
> > > >     EXECUTE PROCEDURE funcname ( arguments )
> > >  
> > > And that doesn't give you any information on the rows that were
> > > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > > you can select from inside the trigger as if they were real tables.
> > 
> > Is this on the TODO list? It doesn't seem too difficult to create a
> > tuplestore and store the NEW and OLD tuples there and pass the whole
> > set to the trigger.
> 
> TODO has:
> 
> 	* Allow statement-level triggers to access modified rows

No wonder I missed it, it's under the RI section. :)

Actually, it looks like there's a bunch of things in there that are
generic trigger issues and not really RI issues. And some of those are
exact duplicates of the triggers section... wee!
-- 
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

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Jim C. Nasby <jnasby@pervasive.com> — 2006-05-22T19:38:15Z

On Mon, May 22, 2006 at 03:14:18PM -0400, Alvaro Herrera wrote:
> Martijn van Oosterhout wrote:
> > On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > >     ON table FOR EACH STATEMENT
> > > >     EXECUTE PROCEDURE funcname ( arguments )
> > >  
> > > And that doesn't give you any information on the rows that were
> > > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > > you can select from inside the trigger as if they were real tables.
> > 
> > Is this on the TODO list? It doesn't seem too difficult to create a
> > tuplestore and store the NEW and OLD tuples there and pass the whole
> > set to the trigger.
> 
> OTOH it would be nice to be able to save only the TIDs of the tuples,
> not the tuples themselves ... maybe create a "TIDstore"?

That could become a performance issue, though, since you'd need all the
modified pages to still be in cache for this to perform well. A
tuplestore doesn't have that constraint, and I think it was recently
realized that it also shouldn't be saving any MVCC info either (see the
compressed sort thread).

If there was more information than the tuplestore could keep in memory,
then a TIDstore might be faster, but only if it resulted in reading from
the heap sequentially, or very near it.
-- 
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

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS

Bruce Momjian <pgman@candle.pha.pa.us> — 2006-05-22T20:39:26Z

OK, I created a "trigger" section and moved "referential integrity"
under "SQL commands".

---------------------------------------------------------------------------

Jim C. Nasby wrote:
> On Mon, May 22, 2006 at 03:11:14PM -0400, Bruce Momjian wrote:
> > Martijn van Oosterhout wrote:
> > -- Start of PGP signed section.
> > > On Mon, May 22, 2006 at 10:41:59AM -0500, Jim C. Nasby wrote:
> > > > > CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> > > > >     ON table FOR EACH STATEMENT
> > > > >     EXECUTE PROCEDURE funcname ( arguments )
> > > >  
> > > > And that doesn't give you any information on the rows that were
> > > > modified. Other RDBMSes will provide a NEW rowset and an OLD rowset that
> > > > you can select from inside the trigger as if they were real tables.
> > > 
> > > Is this on the TODO list? It doesn't seem too difficult to create a
> > > tuplestore and store the NEW and OLD tuples there and pass the whole
> > > set to the trigger.
> > 
> > TODO has:
> > 
> > 	* Allow statement-level triggers to access modified rows
> 
> No wonder I missed it, it's under the RI section. :)
> 
> Actually, it looks like there's a bunch of things in there that are
> generic trigger issues and not really RI issues. And some of those are
> exact duplicates of the triggers section... wee!
> -- 
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Porting MSSQL to PGSQL: trace and profile

Mischa Sandberg <mischa@ca.sophos.com> — 2006-05-22T21:51:56Z

Martijn van Oosterhout wrote:

>>> For high-end MSSQL shops, a high value is being able to trace and profile 
>>> (EXPLAIN) every client SQL command from the server side ... with plenty of 
>>> options for selective trace.
>> This would also be highly valuable to have in PostgreSQL.
> 
> Are we talking EXPLAIN (which is cheap) or EXPLAIN ANALYZE (which is
> less so)?

No, that's the thing; server-side trace and filtering are EXPLAIN ANALYZE, and 
require no change to the app, catching (problem) plans in context.

For example, (using PG here) we had an ETL program that occasionally went very 
stupid. It turned out that the problem was the interaction between autovacuum
timing, and when the program wiped out and rebuilt a worktable.
I had modified the app to EXPLAIN ANALYZE the update commands,
but how do you modify the straight SELECTs? Taking the statements out of context
and trying them offline with EXPLAIN ANALYZE sent me up the wrong tree
a few times :-(

With PG, where I end up is setting log_min_duration_statement=10
and grinding the logs for the information I want, hoping to convincingly replay 
the SQL (with EXPLAIN ANALYZE) under similar circumstances.
If there are no applications that PREPARE their statements, I have a chance.
BTW was logging of (execution of) prepared statements with context info ever 
considered? Or have I missed something?
----------
The MSSQL facility make on-the-fly EXPLAIN ANALYZE possible for all statements.
Its selective filter functions make feasible in production systems,
where you can turn it on for a week, to catch hard-to-reproduce issues.
----------
I did go digging into source to make EXPLAIN ANALYZE work for a server-side 
trace, but it looked like a major. Any pointers? Willing to go dive into it again.
-- 
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but

Mischa Sandberg <mischa@ca.sophos.com> — 2006-05-22T22:00:08Z

Jim C. Nasby wrote:
> On Sun, May 21, 2006 at 02:58:17PM -0700, Josh Berkus wrote:

>> Actually, porting TSQL to PL/pgSQL would be very hard.   I speak as an expert 
>> TSQL developer.  For example, most data manipulation in TSQL is done through 
>> updatable cursors, something we don't currently support.  

There are plenty of direct runtime incompatibilities, that have to be 
implemented not as language-to-language, but language-to-procedure.
That also makes them stick out as the first thing to refactor and reimplement,
once the basic working system has been ported :-)

Hadn't thought about updatable cursors, but solved that problem before in 
SimbaExpress and SimbaFusion with (effectively) arrays of OID's.

That's what I love about PG: so many rich consistent facilities
that you can implement things that tie other DB's in knots.

>> Also, T-SQL uses 
>> un-ordered, callable parameters for SPs, something which we *also* don't 
>> support.

Not such a big. Any translation has full access to the catalog
(ported over to the target PG system) and the parser/translator
can fill in the blanks.

> And TSQL doesn't fail a transaction on an error, resulting in code like
> 
> UPDATE ...
> 
> if @@error = 0 then
>     UPDATE ...
> end
> if @@error = 0 then
> ...
Yeah, transaction error behaviour always brings out the nasty incompatibilities
(Jim, was that a major problem on Project Lance?).
-- 
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Tom Lane <tgl@sss.pgh.pa.us> — 2006-05-22T23:12:02Z

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> If there was more information than the tuplestore could keep in memory,
> then a TIDstore might be faster, but only if it resulted in reading from
> the heap sequentially, or very near it.

That's easily arranged, use a bitmap indexing data structure.

I think we could probably even live with the structure becoming lossy
under memory pressure: AFAICS, all rows modified by a single query ought
to have the same XMIN/CMIN (or XMAX/CMAX for deleted rows), so it should
be possible to verify whether a particular row is one of the interesting
ones or not.

I think the hard part of this task is designing the API for access to
the rowsets from triggers.

			regards, tom lane

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but

Andrew Dunstan <andrew@dunslane.net> — 2006-05-22T23:42:11Z


Tom Lane wrote:

>"Jim C. Nasby" <jnasby@pervasive.com> writes:
>  
>
>>If there was more information than the tuplestore could keep in memory,
>>then a TIDstore might be faster, but only if it resulted in reading from
>>the heap sequentially, or very near it.
>>    
>>
>
>That's easily arranged, use a bitmap indexing data structure.
>
>I think we could probably even live with the structure becoming lossy
>under memory pressure: AFAICS, all rows modified by a single query ought
>to have the same XMIN/CMIN (or XMAX/CMAX for deleted rows), so it should
>be possible to verify whether a particular row is one of the interesting
>ones or not.
>
>I think the hard part of this task is designing the API for access to
>the rowsets from triggers.
>
>
>  
>

How expensive is this going to be, especially for huge numbers of rows? 
Would it be done for all queries, or just those with a per statement 
trigger, or only when explicitly requested?

cheers

andrew

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Tom Lane <tgl@sss.pgh.pa.us> — 2006-05-22T23:47:46Z

Andrew Dunstan <andrew@dunslane.net> writes:
> How expensive is this going to be, especially for huge numbers of rows? 

Certainly cheaper than firing a per-row trigger.

> Would it be done for all queries, or just those with a per statement 
> trigger, or only when explicitly requested?

Just when there's a per-statement AFTER trigger, I would think.  One of
the tricky parts is to minimize overhead if the trigger never actually
asks for access to the rows.  However, if all we do during the statement
is build a possibly-lossy bitmap, I don't think the overhead will be
bad.

It might be interesting to think about reimplementing the RI triggers
as per-statement, too ...

			regards, tom lane

Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

Thomas Hallgren <thomas@tada.se> — 2006-05-23T07:23:53Z

Tom Lane wrote:

> I think the hard part of this task is designing the API for access to
> the rowsets from triggers.
> 
My preference would be something similar to two Portal instances (the NEW and OLD). I could 
then map it in the same way that I map the result of a query. If the API actually used two 
real Portal instances, the PL/Java implementation would take minutes.

Regards,
Thomas Hallgren

Re: Porting MSSQL to PGSQL -- triggers

Mischa Sandberg <mischa@ca.sophos.com> — 2006-05-23T23:39:56Z

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> How expensive is this going to be, especially for huge numbers of rows? 
> 
> Certainly cheaper than firing a per-row trigger.

I'm curious: I've never written a MSSQL trigger that did NOT use the 
INSERTED/DELETED pseudotables (aka NEW/OLD). I know STATEMENT-level triggers 
have existed in PG for a while ... but what do people use them for???

-- 
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.