Re: ISO 8601 'Time Intervals' of the 'format with time-unit

Several people have asked if we are losing momentum. Specifically, they
are concerned about Red Hat dropping their Red Hat Database and instead
distributing PostgreSQL as part of Red Hat Enterprise Server, and they
are concerned about recent press articles about MySQL.

Let me address these. First, the Red Hat change probably has a lot to
do with Oracle's relationship with Red Hat, and very little to do with
PostgreSQL. Their pullback is similar to Great Bridge's closing, except
that Red Hat's database group is still around, so we aren't losing Tom
Lane or Patrick MacDonald (who is completing our PITR work for 7.4).

As far as MySQL, they have a company to push articles to the press, and
many writers just dress them up and print them --- you can tell them
because the pushed ones mention only MySQL, while the non=pushed ones
mention MySQL and PostgreSQL.

I have been around the globe enough to know that PostgreSQL is well on
track. Our user base is growing, we have Win32 and PITR ready for 7.4
(and each had some commercial funding to make them happen.) Recently, I
have also been fielding questions from several companies that want to
hire PostgreSQL developers to work for the community.

But most importantly, there is mind share. I get _very_ few questions
about MySQL anymore, and when the database topic comes up on Slashdot,
the MySQL guys usually end up looking foolish for using MySQL. And my
recent trip to Toronto (who's details I have shared with core but can
not discuss) left no doubt in my mind that PostgreSQL is moving forward
at a rapid rate.

And, I have 1.5k emails to read after a one week trip. :-)

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

With the feature set that Postgres has, it isn't going to lose momentum.

It is lacking in some areas that are slowly being addressed.

If they weren't being addressed, THEN, postgres would lose momentum.

We're fortunate to have good volunteers and the private donations of
companies as well.

Bruce Momjian wrote:
>
> Several people have asked if we are losing momentum. Specifically, they
> are concerned about Red Hat dropping their Red Hat Database and instead
> distributing PostgreSQL as part of Red Hat Enterprise Server, and they
> are concerned about recent press articles about MySQL.
>
> Let me address these. First, the Red Hat change probably has a lot to
> do with Oracle's relationship with Red Hat, and very little to do with
> PostgreSQL. Their pullback is similar to Great Bridge's closing, except
> that Red Hat's database group is still around, so we aren't losing Tom
> Lane or Patrick MacDonald (who is completing our PITR work for 7.4).
>
> As far as MySQL, they have a company to push articles to the press, and
> many writers just dress them up and print them --- you can tell them
> because the pushed ones mention only MySQL, while the non=pushed ones
> mention MySQL and PostgreSQL.
>
> I have been around the globe enough to know that PostgreSQL is well on
> track. Our user base is growing, we have Win32 and PITR ready for 7.4
> (and each had some commercial funding to make them happen.) Recently, I
> have also been fielding questions from several companies that want to
> hire PostgreSQL developers to work for the community.
>
> But most importantly, there is mind share. I get _very_ few questions
> about MySQL anymore, and when the database topic comes up on Slashdot,
> the MySQL guys usually end up looking foolish for using MySQL. And my
> recent trip to Toronto (who's details I have shared with core but can
> not discuss) left no doubt in my mind that PostgreSQL is moving forward
> at a rapid rate.
>
> And, I have 1.5k emails to read after a one week trip. :-)
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Several people have asked if we are losing momentum.

I don't think we are losing momentum considering the project in
isolation --- things seem to be moving as well as they ever have,
if not better.

But I do sometimes worry that we are losing the mindshare war.
We might be growing fine, but if we're growing slower than MySQL is,
we've got a problem. I was just in the local Barnes & Noble store
yesterday, and could not help but notice how many books had "MySQL" in
the title. I didn't notice a single Postgres title (though I did not
look hard, since I was just passing through the computer area).

Mindshare eventually translates into results, if only because it
means that capable developers will gravitate there instead of here.
So we need to worry about it.

There isn't anyone presently willing to spend real money and effort on
marketing PG (as you say, Red Hat won't, for reasons that have nothing
to do with the merits of the product). That means that MySQL's
marketeers have a free hand to do things like boast about features that
might materialize in a year or so :-(

I don't know what we can do about it, other than maybe push harder to
get some more PG titles into O'Reilly's catalog ... that would help
narrow the bookshelf gap a little, at least. Any wannabee authors
out there? (And Bruce, your book is due for a second edition...)

regards, tom lane

On Mon, 14 Apr 2003, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Several people have asked if we are losing momentum.
>
> I don't think we are losing momentum considering the project in
> isolation --- things seem to be moving as well as they ever have,
> if not better.

I agree. I am surprised at the pace at which new features are added,
considering the relatively small number of people working on the project.

>
> But I do sometimes worry that we are losing the mindshare war.
> We might be growing fine, but if we're growing slower than MySQL is,
> we've got a problem. I was just in the local Barnes & Noble store
> yesterday, and could not help but notice how many books had "MySQL" in
> the title. I didn't notice a single Postgres title (though I did not
> look hard, since I was just passing through the computer area).

I've considered this at length. I put some ideas together in December and
sent it off to the advocacy list. Most/all were not implemented -- not
least because I didn't do anything I said I would :-). But, some of the
most important things, such as a proper media kit, quotes for journos,
press contacts with authority to give fast/correct answers really need to
be implemented.

As for why MySQL has *significantly* more market share: there's not a lot
we can match them on. They have significant financial backing -- important
if you're an IT manager who actually knows very little about the technical
merit of the product. It has close ties to a *very* widely deployed
scripting language (PHP). MySQL AB employs marketing and 'advocacy' staff,
who attend conferences all over the world, speak several languages, and
have a fairly good understanding of the industry, open source, databases,
etc. They have infrastructure: tech support, on site support,
consultancy.

MySQL AB promotes MySQL as a high performance database, easy to use,
uncomplicated, with features implemented in a way which is syntactically
convenient -- not 'complicated' like Oracle, DB2 or Postgres.

Its hard to argue against that. At a *technical* conference I recently
spoke at, I was criticised for delivering a talk which was too advanced
and didn't explain Postgres for MySQL users. During a lecture series at a
university, I was criticised for not discussing Oracle instead of Postgres
-- students told me that Oracle will make them money and Postgres wont.

Regardless, I'm still of the opinion that if you build it, they will come
-- particularly costly features like replication, PITR, etc. But maybe
that is what the BSDs say about Linux?

Gavin

Gavin Sherry wrote:
> During a lecture series at a university, I was criticised for not
> discussing Oracle instead of Postgres -- students told me that
> Oracle will make them money and Postgres wont.

Their impressions are probably based on reality as it was a couple of
years ago before the U.S. economy came crashing down.

But today? Companies are trying to figure out how to do things
cheaper, and there are a lot of situations for which Postgres is a
good fit but for which MySQL is a bad fit -- if it'll fit at all.

I seriously think the native Win32 port of Postgres will make a big
difference, because it'll be a SQL Server killer. Especially if it
comes with a nice administrative GUI. :-)

--
Kevin Brown kevin(at)sysexperts(dot)com

On Mon, 14 Apr 2003, Kevin Brown wrote:

> Gavin Sherry wrote:
> > During a lecture series at a university, I was criticised for not
> > discussing Oracle instead of Postgres -- students told me that
> > Oracle will make them money and Postgres wont.
>
> Their impressions are probably based on reality as it was a couple of
> years ago before the U.S. economy came crashing down.
>
> But today? Companies are trying to figure out how to do things
> cheaper, and there are a lot of situations for which Postgres is a
> good fit but for which MySQL is a bad fit -- if it'll fit at all.
>
>
> I seriously think the native Win32 port of Postgres will make a big
> difference, because it'll be a SQL Server killer. Especially if it
> comes with a nice administrative GUI. :-)

I've been thinking about this too. Addressing Tom's point: any one with
Windows experience, interested in the native port and willing to write a
Windows book would probably do a lot for the project. For one, I would be
willing to help write parts which were not Windows specific -- as I
haven't used that system in some time :-).

Gavin

--On Monday, April 14, 2003 19:54:27 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> Several people have asked if we are losing momentum.
>
> I don't think we are losing momentum considering the project in
> isolation --- things seem to be moving as well as they ever have,
> if not better.
>
> But I do sometimes worry that we are losing the mindshare war.
> We might be growing fine, but if we're growing slower than MySQL is,
> we've got a problem. I was just in the local Barnes & Noble store
> yesterday, and could not help but notice how many books had "MySQL" in
> the title. I didn't notice a single Postgres title (though I did not
> look hard, since I was just passing through the computer area).
I was in the Local MicroCenter, and found 3 PG titles, in addition to
Bruce's.

This is MUCH better than a year ago, when there were NONE.

Agreed, that MySQL, has a bigger shelf space.

I did all 3 authors a favor and bought copies.

LER

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Gretings!

[2003-04-14 19:54] Tom Lane said:
| Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
| > Several people have asked if we are losing momentum.

| I don't know what we can do about it, other than maybe push harder to
| get some more PG titles into O'Reilly's catalog ... that would help
| narrow the bookshelf gap a little, at least. Any wannabee authors
| out there? (And Bruce, your book is due for a second edition...)

I've wanted to pipe up in a few of these "popularity"
discussions in the past. Seeing how I can't make time to
participate in any other meaningful capacity, I'll share
my thoughts on _why_ mysql has the mindshare.

Applications, specifically applications that _use_ mysql.

A quick search over at freshmeat returns 1044 results for
"mysql" and 260 for "postgresql". Before this turns into a
cause/effect discussion, I want to state up front that the
real "effect" of this is that someone is 4 times as likely to
download an application that uses mysql. Sure, many are
"trivial" applications, but I posit that it is _specifically_
these "trivial" applications that inoculate the uninitiated
with the belief that mysql is suitable for use in real, albeit
trivial applications. Additionally, it these rudimentary
applications that will be studied by many as the way to write
a database application.

It is all good and well that postgres /can/ do, but until
the application developers see that those features are
valuable enough to forgo mysql support, they'll write the
application to support whatever database is most likely to
_already_ be installed, which will be mysql. Granted,
many developers will also try to support multiple dbs via
the language's db api, but this leaves the less-supported
dbs in an even worse position; being relegated to an
"might work with XXX database". When anxious user learns
that "might" currently means "doesn't," the second-string
database looks even worse in the eyes of the user.

How to solve this problem? This is the hard part, but
luckily ISTM that there are a few ways to succeed. Neither
of which involves marketing or writing books.

1) become active in the "also supports postgres" projects,
and add features that are made available _because_ of
postgres' superiority. Eventually, market pressure
for the cool feature(s) will lead users to choose
postgres, and mysql could be relegated to the "also
runs on mysql, with limited featureset"
2) take a popular project that uses mysql, fork it, and
add features that can only be implemented using posgres.
3) release that super-cool code that you've been hacking
on for years, especially if it is a "trivial" app.
4) convince your employer that it would be _beneficial_ to
them to release, as open source, the internal app(s) you've
developed, using postgres-specific features. (This is
about all I can claim to be doing at this point in my
indentured servitude, and I can't say I'm doing a good
job... :-/)

I'm sure this idea is not original, but I'm also sure that
it _is_ the answer to gaining market^Wmindshare in this
database market.

(I must apologize in advance, that I might not have time
to even follow this thread, in fact, I hope that instead of
replying to this, the potential respondent might consider
helping to increase the number of apps that require postgres
:-)

wishing-I-could-contribute-more-ly yours,
brent

--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman

> 1) become active in the "also supports postgres" projects,
> and add features that are made available _because_ of
> postgres' superiority. Eventually, market pressure
> for the cool feature(s) will lead users to choose
> postgres, and mysql could be relegated to the "also
> runs on mysql, with limited featureset"

Take, for example, phpPgAdmin. It was originally forked from phpMyAdmin, but we've just done a complete rewrite (because phpMyAdmin was written my mysql/php weenies who couldn't code nicely to save their lives...).

However, it's me doing 99% of the coding, Rob doing advocacy and a heap of people who send in translations. Translations are very nice, but I so rarely get actual code contributions.

phpMyAdmin even implements it's OWN comment and foreign key feature!!

Chris

Kevin, without the "e", wrote...
> I seriously think the native Win32 port of Postgres will make a big
> difference, because it'll be a SQL Server killer. Especially if it
> comes with a nice administrative GUI. :-)

I wouldn't be too sanguine about that, from two perspectives:

a) There's a moving target, here, in that Microsoft seems to be
looking for the next "new thing" to be the elimination of
the use of "files" in favor of the filesystem being treated
as a database.

b) We recently were considering how we'd put a sharable Windows box
in, at the office. Were considering using VNC to allow it to be
accessible. Then someone thought to read the license, only to
discover that the license pretty much expressly forbids running
"foreign, competing applications" on the platform.

It seems pretty plausible that the net result of further development
will be platforms that are actively hostile to foreign software.

If I suggested that the licensing of Win2003 would expressly forbid
installing PostgreSQL, people would rightly accuse me of being a
paranoid conspiracy theorist.

But considering that the thought of VNC being outlawed would have seemed
pretty daft a few years ago, and we see things like DMCA combining with
"Homeland Security." Anti-"hacking" provisions have been going into
telecom laws that appear to classify network hardware that can do NAT as
"illegal hacking" equipment. I'm not sure what we'd have to consider
"daft" come 2005...
--
(reverse (concatenate 'string "gro.mca@" "enworbbc"))
http://cbbrowne.com/info/internet.html
"Heuristics (from the French heure, "hour") limit the amount of time
spent executing something. [When using heuristics] it shouldn't take
longer than an hour to do something."

On Mon, 14 Apr 2003, Brent Verner wrote:

> Applications, specifically applications that _use_ mysql.
>
> A quick search over at freshmeat returns 1044 results for
> "mysql" and 260 for "postgresql".

That's a pretty reasonable thought. I work for a shop that sells
Postgres support, and even we install MySQL for the Q&D ticket tracking
system we recommend because we can't justify the cost to port it to
postgres. If the postgres support were there, we would surely be using it.

How to fix such a situation, I'm not sure. "MySQL Compatability Mode,"
anyone? :-)

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

I agree, things aren't good when you look at the book shelf and app
support, but fortunately these are things that are shaded more by the
state of things 1-3 years ago rather than currently. Certainly, we
would have seen an even worse ratio than 1:4 if we had looked last year
--- we aren't on parity yet, but I think we are getting there.

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

Larry Rosenman wrote:
>
>
> --On Monday, April 14, 2003 19:54:27 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
>
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> Several people have asked if we are losing momentum.
> >
> > I don't think we are losing momentum considering the project in
> > isolation --- things seem to be moving as well as they ever have,
> > if not better.
> >
> > But I do sometimes worry that we are losing the mindshare war.
> > We might be growing fine, but if we're growing slower than MySQL is,
> > we've got a problem. I was just in the local Barnes & Noble store
> > yesterday, and could not help but notice how many books had "MySQL" in
> > the title. I didn't notice a single Postgres title (though I did not
> > look hard, since I was just passing through the computer area).
> I was in the Local MicroCenter, and found 3 PG titles, in addition to
> Bruce's.
>
> This is MUCH better than a year ago, when there were NONE.
>
> Agreed, that MySQL, has a bigger shelf space.
>
> I did all 3 authors a favor and bought copies.
>
> LER
>
>
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
>
>
>
>

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

> That's a pretty reasonable thought. I work for a shop that sells
> Postgres support, and even we install MySQL for the Q&D ticket tracking
> system we recommend because we can't justify the cost to port it to
> postgres. If the postgres support were there, we would surely be using it.
>
> How to fix such a situation, I'm not sure. "MySQL Compatability Mode,"
> anyone? :-)

The real problem is PHP. PHP is just the cruftiest language ever invented (trust me, I use it every day). The PHP people are totally dedicated to MySQL, to the exclusion of all rational thought (eg. When I asked Rasmas at a conference about race conditions in his replicated setup, he replied "it's never going to happen - MySQL's replication is just too fast...).

Chris

cbbrowne(at)cbbrowne(dot)com wrote:

> Kevin, without the "e", wrote...
>
>>I seriously think the native Win32 port of Postgres will make a big
>>difference, because it'll be a SQL Server killer. Especially if it
>>comes with a nice administrative GUI. :-)

I agree. I don't think PostgreSQL will be a SQL Server killer,
but my completely ignorant guess is that 90% of the cause of the
*initial* gap between mySQL and PostgreSQL grew out of the fact
that a Win32 version of mySQL was available. Once the gap became
present, one then had to suffer switching costs. If the
features/performance of PostgreSQL > mySQL switching costs, then
PostgreSQL wins in the long term. Without a Win32 port, the
switching costs also include those switching costs associated
with switching from Win32 to Unix.

>
> I wouldn't be too sanguine about that, from two perspectives:
>
> a) There's a moving target, here, in that Microsoft seems to be
> looking for the next "new thing" to be the elimination of
> the use of "files" in favor of the filesystem being treated
> as a database.

They ought to get their database up to speed first, it seems to
me. I agree Microsoft's view of data management is a moving
target. 6 years ago everything, including network resources were
going to be accessed strickly through an OLE2 Compound Document
interface and OLE structured storage. Then the Internet got hot
and all data suddenly had to be accessible through URLs. Now
it's XML that hot. Perhaps the Microsoft filesystem of the
future will be one big XML document ;-)

>
> b) We recently were considering how we'd put a sharable Windows box
> in, at the office. Were considering using VNC to allow it to be
> accessible. Then someone thought to read the license, only to
> discover that the license pretty much expressly forbids running
> "foreign, competing applications" on the platform.
>
> It seems pretty plausible that the net result of further development
> will be platforms that are actively hostile to foreign software.
>
> If I suggested that the licensing of Win2003 would expressly forbid
> installing PostgreSQL, people would rightly accuse me of being a
> paranoid conspiracy theorist.

I think you are a paranoid conspiracy theorist. :-)

Mike Mascari
mascarm(at)mascari(dot)com

Bruce Momjian wrote:
> I agree, things aren't good when you look at the book shelf and app
> support, but fortunately these are things that are shaded more by the
> state of things 1-3 years ago rather than currently. Certainly, we
> would have seen an even worse ratio than 1:4 if we had looked last year
> --- we aren't on parity yet, but I think we are getting there.

What's missing are the "FOO Applications With PostgreSQL" sorts of
books, where
(member FOO '(|Web| |PHP| |Perl| |Python| |Application Frameworks|))

The one PostgreSQL book that _does_ have some of this is the O'Reilly
one, where I was disappointed to see how much of the book was devoted to
a framework I /wasn't/ planning to use.

Right at the moment is probably /not/ a good time to be pushing books on
potentially-obscure application areas; my ex-publisher (Wrox) just
became an ex-publisher as a result of trying too hard to too quickly
hawk too many books in obscure application areas.

My suspicion is that this, along with very soft book sales throughout
the publishing industry, is likely to make "obscure application area"
books a tough sell in the short term. Like it or not, "PostgreSQL +
FOO" is not going to be the easiest sell, particularly in the absence of
the much denigrated "PostgreSQL Marketing Cabal."
--
output = ("cbbrowne" "@cbbrowne.com")
http://cbbrowne.com/info/wp.html
"There is no psychiatrist in the world like a puppy licking your
face." -- Ben Williams

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> But I do sometimes worry that we are losing the mindshare
Tom> war. We might be growing fine, but if we're growing slower
Tom> than MySQL is, we've got a problem. I was just in the local

This is probably true. Once people get exposed to PostgreSQL then
there is a fair chance of forming an opinion. Today one of the
undergraduates in my class was telling me how after hacking pgsql
internals he has such a different impression of the two systems
(earlier he'd built a site with MySQL going by the "works for
slashdot" philosophy).

--
Peace, at last ?
Sailesh
http://www.cs.berkeley.edu/~sailesh

Mike Mascari wrote:
> cbbrowne(at)cbbrowne(dot)com wrote:
>>I wouldn't be too sanguine about that, from two perspectives:
>>
>> a) There's a moving target, here, in that Microsoft seems to be
>> looking for the next "new thing" to be the elimination of
>> the use of "files" in favor of the filesystem being treated
>> as a database.
>
>
> They ought to get their database up to speed first, it seems to
> me. I agree Microsoft's view of data management is a moving
> target.

Not to mention the fact that there's a significant number of NT 4
servers still out there -- what is that, 7 years old? A lot of places
aren't upgrading because they don't need to & don't want to shell out
the cash. (And it should go without saying that Microsoft is none too
happy with it.) With Windows 2K3 just coming out and who knows how much
longer until the next version (or ther version after that, who knows
when these "features" will actually show up), there's still a
significant window in there for conventional database servers,
especially for the price conscious out there.

----
Jeff Hoffmann
PropertyKey.com

IMVHO it's reference customers/users more than books & windows ports.

If I were a naive middle manager in some company, would I rather
use:

(a) the database used by Yahoo, Cisco, and Sony?
(b) the database used by Shannon Med Center, Mohawk SW, Vanten Inc, and BASF.

Now suppose I told that same middle manager there was an open
source alternative:

(c) used by Lockheed Martin, Nasdaq, AOL, and Unisys.

As far as I can tell (5-minutes searching) (c) is PostgreSQL.

http://jobsearch.monster.com/jobsearch.asp?q=postgresql
http://www.hotjobs.com/cgi-bin/job-search?KEYWORDS=postgres
http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&dockey=xml/8/1/816e9b7e50ae92331bb5c47a791a589f(at)activejobs0&c=1
http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&dockey=xml/c/8/c8dc5841d18329c6c50b55f67a7ff038(at)activejobs0&c=1
http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&dockey=xml/1/6/168f30dc84b8f195d1fc35feb6a2f67a(at)activejobs0&c=1
"The Nasdaq Stock Market ... currently looking to fill the following
positions in Trumbull, CT...Some positions require knowledge of ...Postgre SQL.."

I'm not sure quite what it'd take to get the permission to use
these company's names, but surely we could have a list of links
to the job postings... I'd bet that one of monster, hotjobs,
and/or dice would even provide a datafeed of relevant jobs to
be posted on the postgresql.org site.

If we simply had a list of companies using postgresql highly visible
somewhere -- not necessarily a complex case study, just simple list
of "company X uses postgresql for Y" statements -- I think it would
go a long way. I'll contribute. InterVideo uses postgresql (for
running user surveys and some internal reporting and development tools).

Ron

PS: No offense to Shannon, Mohawk, Vanten, and yes, I know BASF is
an awesome company. But they're all, even BASF, less of
a household name than Sony,Yahoo,Cisco,AOL,Nasdaq,Lockheed.

On Tuesday 15 April 2003 05:48, you wrote:
> Regardless, I'm still of the opinion that if you build it, they will come
> -- particularly costly features like replication, PITR, etc. But maybe
> that is what the BSDs say about Linux?

That is an unfair comparison. The technical differences between BSD and linux
are not as much as postgresql and mysql. Besides what is the parallel of SQL
standard in OS world? POSIX? And both BSD/linux are doing fine sitting next
to each other on that.

After porting my small application in less than half an hour from linux to
freeBSD and vice versa, I really do not agree with that comment. Not even in
the spirit of it.

Shridhar

On Tue, 2003-04-15 at 00:37, Bruce Momjian wrote:
> Several people have asked if we are losing momentum. Specifically, they
> are concerned about Red Hat dropping their Red Hat Database and instead
> distributing PostgreSQL as part of Red Hat Enterprise Server, and they
> are concerned about recent press articles about MySQL.

Just reading GENERAL every day shows quite the contrary! There are more
and more questions which really belong on NOVICE. More and more
questions about porting applications from MySQL and Access.

RedHat renaming "PostgreSQL" to "PostgreSQL" after a short stint a.k.a.
"Red Hat Database" is a very positive step.

Lots of stupid journalists are starting to write "replace Oracle with
MySQL" rubbish. I am very concerned about this because the DBA who is
stupid enough to do this will fail miserably and get the wrong
impression about free RDBMS.

Lets forget the "replace MySQL with PostgreSQL" stuff and go looking for
higher end converts. Our marketing push should be "replace Oracle with
PostgreSQL and replace Access with MySQL". This puts the emphasis on
which database can do what...

Just my 2 EURO cents

Cheers
Tony Grant
(yes I use PostgreSQL where MySQL would suffice...)

--
www.tgds.net Library management software toolkit,
redhat linux on Sony Vaio C1XD,
Dreamweaver MX with Tomcat and PostgreSQL

Curt Sampson <cjs(at)cynic(dot)net> writes:
> That's a pretty reasonable thought. I work for a shop that sells
> Postgres support, and even we install MySQL for the Q&D ticket tracking
> system we recommend because we can't justify the cost to port it to
> postgres. If the postgres support were there, we would surely be using it.

> How to fix such a situation, I'm not sure. "MySQL Compatability Mode,"
> anyone? :-)

What issues are creating a compatibility problem for you?

regards, tom lane

** Reply to message from Tony Grant <tony(at)tgds(dot)net> on 15 Apr 2003 08:12:04
+0200
Hi,
I've got to agree with this.
We have just been through the excercise of porting our quite extensive fleet
maintenance and accounting package from db2 to postgres. While the port was not
without pain :) the results are very good. We run windows clients, (mostly),
and whatever the database of choice performs best on for the backend. We are
seeing performance gains for the 20-100 user bracket of a factor of 10 to 20
for switching from db2 to postgres on the same platform.
We literally could not port this stuff to mysql. It does not have enough
feature to support us :)
Definitely the story is not that postgres competes with mysql/access but
rather with db2/oracle, and the point in time recovery coming in the next
release, just strengthens that story a whole lot more too.

And, client reaction to an opensource (free :) database on linux has been very
enthusiastic.

Regards,
Wayne
http://www.bacchus.com.au

> On Tue, 2003-04-15 at 00:37, Bruce Momjian wrote:
> > Several people have asked if we are losing momentum. Specifically, they
> > are concerned about Red Hat dropping their Red Hat Database and instead
> > distributing PostgreSQL as part of Red Hat Enterprise Server, and they
> > are concerned about recent press articles about MySQL.
>
> Just reading GENERAL every day shows quite the contrary! There are more
> and more questions which really belong on NOVICE. More and more
> questions about porting applications from MySQL and Access.
>
> RedHat renaming "PostgreSQL" to "PostgreSQL" after a short stint a.k.a.
> "Red Hat Database" is a very positive step.
>
> Lots of stupid journalists are starting to write "replace Oracle with
> MySQL" rubbish. I am very concerned about this because the DBA who is
> stupid enough to do this will fail miserably and get the wrong
> impression about free RDBMS.
>
> Lets forget the "replace MySQL with PostgreSQL" stuff and go looking for
> higher end converts. Our marketing push should be "replace Oracle with
> PostgreSQL and replace Access with MySQL". This puts the emphasis on
> which database can do what...
>
> Just my 2 EURO cents
>
> Cheers
> Tony Grant
> (yes I use PostgreSQL where MySQL would suffice...)
>
> --
> www.tgds.net Library management software toolkit,
> redhat linux on Sony Vaio C1XD,
> Dreamweaver MX with Tomcat and PostgreSQL
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

On Tuesday 15 April 2003 14:00, you wrote:
> ** Reply to message from Tony Grant <tony(at)tgds(dot)net> on 15 Apr 2003 08:12:04
> +0200
> Hi,
> I've got to agree with this.
> We have just been through the excercise of porting our quite extensive
> fleet maintenance and accounting package from db2 to postgres. While the
> port was not without pain :) the results are very good. We run windows
> clients, (mostly), and whatever the database of choice performs best on for
> the backend. We are seeing performance gains for the 20-100 user bracket of
> a factor of 10 to 20 for switching from db2 to postgres on the same
> platform.
> We literally could not port this stuff to mysql. It does not have enough
> feature to support us :)
> Definitely the story is not that postgres competes with mysql/access but
> rather with db2/oracle, and the point in time recovery coming in the next
> release, just strengthens that story a whole lot more too.
>
> And, client reaction to an opensource (free :) database on linux has been
> very enthusiastic.

If you don't mind, could you please submit a short write up on your experience
for submission on postgresql advocacy-Case Study section? You can either post
it to posgresql advocacy list or send it to me offlist, if required.

See http://advocacy.postgresql.org

Shridhar

Tom Lane wrote:
> Curt Sampson <cjs(at)cynic(dot)net> writes:
> > That's a pretty reasonable thought. I work for a shop that sells
> > Postgres support, and even we install MySQL for the Q&D ticket tracking
> > system we recommend because we can't justify the cost to port it to
> > postgres. If the postgres support were there, we would surely be using it.
>
> > How to fix such a situation, I'm not sure. "MySQL Compatability Mode,"
> > anyone? :-)
>
> What issues are creating a compatibility problem for you?

Erm...reserved words? "Freeze" is a reserved word, for instance, and
that actually bit me when converting an MS-SQL database...

I have no problem with reserved words in principle, at least when they
refer to the SQL-standard commands and their options, but it's not
clear that turning options (such as FREEZE) for PG-specific commands
(such as VACUUM) into reserved words is a good idea. But it may not
be possible to avoid it, unfortunately. :-(

--
Kevin Brown kevin(at)sysexperts(dot)com

On Tue, 15 Apr 2003, Tom Lane wrote:

> > How to fix such a situation, I'm not sure. "MySQL Compatability Mode,"
> > anyone? :-)
>
> What issues are creating a compatibility problem for you?

We can't unthinkingly point the product at a PostgreSQL server and
have it Just Work. So all we really need is full SQL and wire-protocol
compatability. :-)

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Hi,
Your experience is very interesting.
But could you tell me what is the Database size ?

Regards,
Thierry

Wayne Armstrong wrote:

> ** Reply to message from Tony Grant <tony(at)tgds(dot)net> on 15 Apr 2003 08:12:04
> +0200
> Hi,
> I've got to agree with this.
> We have just been through the excercise of porting our quite extensive fleet
> maintenance and accounting package from db2 to postgres. While the port was not
> without pain :) the results are very good. We run windows clients, (mostly),
> and whatever the database of choice performs best on for the backend. We are
> seeing performance gains for the 20-100 user bracket of a factor of 10 to 20
> for switching from db2 to postgres on the same platform.
> We literally could not port this stuff to mysql. It does not have enough
> feature to support us :)
> Definitely the story is not that postgres competes with mysql/access but
> rather with db2/oracle, and the point in time recovery coming in the next
> release, just strengthens that story a whole lot more too.
>
> And, client reaction to an opensource (free :) database on linux has been very
> enthusiastic.
>
> Regards,
> Wayne
> http://www.bacchus.com.au
>
> > On Tue, 2003-04-15 at 00:37, Bruce Momjian wrote:
> > > Several people have asked if we are losing momentum. Specifically, they
> > > are concerned about Red Hat dropping their Red Hat Database and instead
> > > distributing PostgreSQL as part of Red Hat Enterprise Server, and they
> > > are concerned about recent press articles about MySQL.
> >
> > Just reading GENERAL every day shows quite the contrary! There are more
> > and more questions which really belong on NOVICE. More and more
> > questions about porting applications from MySQL and Access.
> >
> > RedHat renaming "PostgreSQL" to "PostgreSQL" after a short stint a.k.a.
> > "Red Hat Database" is a very positive step.
> >
> > Lots of stupid journalists are starting to write "replace Oracle with
> > MySQL" rubbish. I am very concerned about this because the DBA who is
> > stupid enough to do this will fail miserably and get the wrong
> > impression about free RDBMS.
> >
> > Lets forget the "replace MySQL with PostgreSQL" stuff and go looking for
> > higher end converts. Our marketing push should be "replace Oracle with
> > PostgreSQL and replace Access with MySQL". This puts the emphasis on
> > which database can do what...
> >
> > Just my 2 EURO cents
> >
> > Cheers
> > Tony Grant
> > (yes I use PostgreSQL where MySQL would suffice...)
> >
> > --
> > www.tgds.net Library management software toolkit,
> > redhat linux on Sony Vaio C1XD,
> > Dreamweaver MX with Tomcat and PostgreSQL
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

** Reply to message from Thierry Missimilly <THIERRY(dot)MISSIMILLY(at)BULL(dot)NET> on
Tue, 15 Apr 2003 11:41:47 +0200
Hi,
About 450 tables (+ lots of views:)
Row counts range from 0 to about 3 million.
Database sizes range from about 600 meg (a bus company with about 50 busses
and a years fueling/servicing/rostering history) to around 12 gig currently :).
We are not newbies with db2. We have been using/suppling it since version
2.1(We actually started development on ver 1. something, but didn't ship till
after the version 2 release :). We do do a good job of tuning db2.
Nevertheless, performance of postgresql across the board (and we have not
designed for postgres like we did for db2), leaves db2 for dead most places.

Regards,
Wayne

> Hi,
> Your experience is very interesting.
> But could you tell me what is the Database size ?
>
> Regards,
> Thierry
>
> Wayne Armstrong wrote:
>
> > ** Reply to message from Tony Grant <tony(at)tgds(dot)net> on 15 Apr 2003 08:12:04
> > +0200
> > Hi,
> > I've got to agree with this.
> > We have just been through the excercise of porting our quite extensive fleet
> > maintenance and accounting package from db2 to postgres. While the port was not
> > without pain :) the results are very good. We run windows clients, (mostly),
> > and whatever the database of choice performs best on for the backend. We are
> > seeing performance gains for the 20-100 user bracket of a factor of 10 to 20
> > for switching from db2 to postgres on the same platform.
> > We literally could not port this stuff to mysql. It does not have enough
> > feature to support us :)
> > Definitely the story is not that postgres competes with mysql/access but
> > rather with db2/oracle, and the point in time recovery coming in the next
> > release, just strengthens that story a whole lot more too.
> >
> > And, client reaction to an opensource (free :) database on linux has been very
> > enthusiastic.
> >
> > Regards,
> > Wayne
> > http://www.bacchus.com.au
> >
> > > On Tue, 2003-04-15 at 00:37, Bruce Momjian wrote:
> > > > Several people have asked if we are losing momentum. Specifically, they
> > > > are concerned about Red Hat dropping their Red Hat Database and instead
> > > > distributing PostgreSQL as part of Red Hat Enterprise Server, and they
> > > > are concerned about recent press articles about MySQL.
> > >
> > > Just reading GENERAL every day shows quite the contrary! There are more
> > > and more questions which really belong on NOVICE. More and more
> > > questions about porting applications from MySQL and Access.
> > >
> > > RedHat renaming "PostgreSQL" to "PostgreSQL" after a short stint a.k.a.
> > > "Red Hat Database" is a very positive step.
> > >
> > > Lots of stupid journalists are starting to write "replace Oracle with
> > > MySQL" rubbish. I am very concerned about this because the DBA who is
> > > stupid enough to do this will fail miserably and get the wrong
> > > impression about free RDBMS.
> > >
> > > Lets forget the "replace MySQL with PostgreSQL" stuff and go looking for
> > > higher end converts. Our marketing push should be "replace Oracle with
> > > PostgreSQL and replace Access with MySQL". This puts the emphasis on
> > > which database can do what...
> > >
> > > Just my 2 EURO cents
> > >
> > > Cheers
> > > Tony Grant
> > > (yes I use PostgreSQL where MySQL would suffice...)
> > >
> > > --
> > > www.tgds.net Library management software toolkit,
> > > redhat linux on Sony Vaio C1XD,
> > > Dreamweaver MX with Tomcat and PostgreSQL
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Christopher Kings-Lynne wrote:

>>That's a pretty reasonable thought. I work for a shop that sells
>>Postgres support, and even we install MySQL for the Q&D ticket tracking
>>system we recommend because we can't justify the cost to port it to
>>postgres. If the postgres support were there, we would surely be using it.
>>
>>How to fix such a situation, I'm not sure. "MySQL Compatability Mode,"
>>anyone? :-)
>>
>>
>
>The real problem is PHP. PHP is just the cruftiest language ever invented (trust me, I use it every day). The PHP people are totally dedicated to MySQL, to the exclusion of all rational thought (eg. When I asked Rasmas at a conference about race conditions in his replicated setup, he replied "it's never going to happen - MySQL's replication is just too fast...).
>
>
>
Hey! don't go knocking PHP, it is probably one of the most flexible and
easy to use systems around. I have done several fairly large projects
with PHP and while it is an "ugly" environment, it performs well enough,
has a very usable extension interface, it is quick and easy to even
large projects done.

As for MySQL, there are two things that PostgreSQL does not do, and
probably can not do to support MySQL:

(1) REPLACE INTO (I think that's the name) which does either an insert
or update into a table depending on the existence of a row. I was told
that this was impossible.

(2) MySQL returns a value on insert which is usually usable, for instance,
insert into mytable (x,y,z) values(1,2,3);
select rowid from mytable where x=1 and y=2 and z=3;

I have had many discussions with MySQL people, and one common thread
exists. People who use MySQL do not usually understand databases all
that well. Arguments about *why* it is a horrible database and barely
SQL at all, fall on deaf ears. They don't understand PostgreSQL, they
complain that it is "too big." They complain that it is "too much,"
MySQL is all they need. They complain that it is "too hard" to use.

All of these things are largely imagined. PostgreSQL is not much bigger
than MySQL, in fact, the difference is negligible with regards to
average system capability these days. It isn't any more difficult to
use, its just a little different. They, however, feel safe with MySQL.
MySQL is the Microsoft of databases, everyone uses it because everyone
uses it, not because it is better or even adequate.

We need to take projects like Bugzilla (Did RH ever release the PG
version or am I way out of date?) and port them to PostgreSQL. We need
to write free articles for Linux and IT magazines about how to take a
MySQL project over to PostgreSQL easily, why PostgreSQL is much better
than MySQL, lastly we have to play the MySQL benchmark game .. we need
to create a Benchmark program that clearly shows how PostgreSQL compares
to MySQL.

Mike Mascari wrote:

>cbbrowne(at)cbbrowne(dot)com wrote:
>
>
>> b) We recently were considering how we'd put a sharable Windows box
>> in, at the office. Were considering using VNC to allow it to be
>> accessible. Then someone thought to read the license, only to
>> discover that the license pretty much expressly forbids running
>> "foreign, competing applications" on the platform.
>>
>>It seems pretty plausible that the net result of further development
>>will be platforms that are actively hostile to foreign software.
>>
>>If I suggested that the licensing of Win2003 would expressly forbid
>>installing PostgreSQL, people would rightly accuse me of being a
>>paranoid conspiracy theorist.
>>
>>
>
>I think you are a paranoid conspiracy theorist. :-)
>
>Mike Mascari
>mascarm(at)mascari(dot)com
>
>
"Just because you're paranoid does not mean they're not out to get you."
Henry Kissinger.

> Hey! don't go knocking PHP, it is probably one of the most flexible and
> easy to use systems around. I have done several fairly large projects
> with PHP and while it is an "ugly" environment, it performs well enough,
> has a very usable extension interface, it is quick and easy to even
> large projects done.

Right. PHP is our friend. In Japan Apache+PHP+PostgreSQL combo is the
standard for Web systems. Very few people uses Apache+PHP+MySQL.
--
Tatsuo Ishii

Tatsuo, this has always fascinated me. Any insights you could share about how PostgreSQL achieved the prominence it has in Japan (and how MySQL did not) would be very interesting.

Cheers,
Ned

----- Original Message -----
From: "Tatsuo Ishii" <t-ishii(at)sra(dot)co(dot)jp>
To: <pgsql(at)mohawksoft(dot)com>
Cc: <chriskl(at)familyhealth(dot)com(dot)au>; <cjs(at)cynic(dot)net>; <brent(at)rcfile(dot)org>; <pgsql-hackers(at)postgresql(dot)org>
Sent: Tuesday, April 15, 2003 8:09 AM
Subject: Re: [HACKERS] Are we losing momentum?

> Hey! don't go knocking PHP, it is probably one of the most flexible and
> easy to use systems around. I have done several fairly large projects
> with PHP and while it is an "ugly" environment, it performs well enough,
> has a very usable extension interface, it is quick and easy to even
> large projects done.

Right. PHP is our friend. In Japan Apache+PHP+PostgreSQL combo is the
standard for Web systems. Very few people uses Apache+PHP+MySQL.
--
Tatsuo Ishii

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

As one of the top search engine campaign optimization companies in the
space, we here at Did-it.com have been using Postgresql for over a year
now. We had serious locking problems with MySQL and even switching to
their Innodb handler did not solve all the issues.

As the DB administrator, I recommended we switch when it came time to
re-write our client platform. That we did, and we have not looked back.
We have millions of listings, keywords and we perform live visitor
tracking in our database. We capture on the order of about 1 million
visitors every day, with each hit making updates, selects and possibly
inserts.

We could not have done this in mySQL. Basically when I see silly posts
over on Slashdot about MySQL being as good a sliced bread, you can check
out the debunking posts that I make as "esconsult1".

Yes, perhaps Postgresql needs a central org that manages press and so
on, but we know that we dont get the press that MySQL does, but quietly
in the background Postgresql is handling large important things.

- Ericson Smith
Web Developer
Db Admin
http://www.did-it.com

--
Ericson Smith <eric(at)did-it(dot)com>

Hi,

We should probably put out an FAQ saying, if you have success story, please
make a write-up and send to us at http://advocacy.postgresql.org.

Shridhar

On Tuesday 15 April 2003 18:42, Ericson Smith wrote:
> As one of the top search engine campaign optimization companies in the
> space, we here at Did-it.com have been using Postgresql for over a year
> now. We had serious locking problems with MySQL and even switching to
> their Innodb handler did not solve all the issues.
>
> As the DB administrator, I recommended we switch when it came time to
> re-write our client platform. That we did, and we have not looked back.
> We have millions of listings, keywords and we perform live visitor
> tracking in our database. We capture on the order of about 1 million
> visitors every day, with each hit making updates, selects and possibly
> inserts.
>
> We could not have done this in mySQL. Basically when I see silly posts
> over on Slashdot about MySQL being as good a sliced bread, you can check
> out the debunking posts that I make as "esconsult1".
>
> Yes, perhaps Postgresql needs a central org that manages press and so
> on, but we know that we dont get the press that MySQL does, but quietly
> in the background Postgresql is handling large important things.
>
> - Ericson Smith
> Web Developer
> Db Admin
> http://www.did-it.com

mlw <pgsql(at)mohawksoft(dot)com> writes:
> We need to take projects like Bugzilla (Did RH ever release the PG
> version or am I way out of date?) and port them to PostgreSQL.

See http://bugzilla.redhat.com/bugzilla/ ... note icon at bottom ...
note tarball offered in News ...

regards, tom lane

On Tue, 2003-04-15 at 07:51, mlw wrote:
> Christopher Kings-Lynne wrote:
> >
> >The real problem is PHP. PHP is just the cruftiest language ever invented
> > (trust me, I use it every day). The PHP people are totally dedicated to
> > MySQL, to the exclusion of all rational thought (eg. When I asked
> > Rasmas at a conference about race conditions in his replicated
> > setup, he replied "it's never going to happen - MySQL's replication
> > is just too fast...).
> >
> Hey! don't go knocking PHP, it is probably one of the most flexible and
> easy to use systems around. I have done several fairly large projects
> with PHP and while it is an "ugly" environment, it performs well enough,
> has a very usable extension interface, it is quick and easy to even
> large projects done.
>

The problem is the marriage of PHP and MySql. I've always held the
notion that early on several of the php developers, being windows
hackers, needed an open source database that would run on windows. They
picked mysql (which was probably their best option at the time) and
mysql rode on the shoulders php's success.

> As for MySQL, there are two things that PostgreSQL does not do, and
> probably can not do to support MySQL:
>
> (1) REPLACE INTO (I think that's the name) which does either an insert
> or update into a table depending on the existence of a row. I was told
> that this was impossible.
>
> (2) MySQL returns a value on insert which is usually usable, for instance,
> insert into mytable (x,y,z) values(1,2,3);
> select rowid from mytable where x=1 and y=2 and z=3;
>

I'm pretty sure I've seen people create db functions to duplicate these
features, but admittedly that would be more complicated.

<snip>
>
> We need to take projects like Bugzilla (Did RH ever release the PG
> version or am I way out of date?) and port them to PostgreSQL. We need
> to write free articles for Linux and IT magazines about how to take a
> MySQL project over to PostgreSQL easily, why PostgreSQL is much better
> than MySQL,

Red Hat actually did do this, and does make the source available. One
problem I found with porting of mysql apps is that those apps tend to do
a lot of dump things to make up for mysql's missing features. Unless
you really are willing to fork the code and then maintain it as a new
project, porting applications gets somewhat futile.

Robert Treat

On Tue, 15 Apr 2003, mlw wrote:

>
>
> Christopher Kings-Lynne wrote:
>
> >>That's a pretty reasonable thought. I work for a shop that sells
> >>Postgres support, and even we install MySQL for the Q&D ticket tracking
> >>system we recommend because we can't justify the cost to port it to
> >>postgres. If the postgres support were there, we would surely be using it.
> >>
> >>How to fix such a situation, I'm not sure. "MySQL Compatability Mode,"
> >>anyone? :-)
> >>
> >>
> >
> >The real problem is PHP. PHP is just the cruftiest language ever invented (trust me, I use it every day). The PHP people are totally dedicated to MySQL, to the exclusion of all rational thought (eg. When I asked Rasmas at a conference about race conditions in his replicated setup, he replied "it's never going to happen - MySQL's replication is just too fast...).
> >
> >
> >
> Hey! don't go knocking PHP, it is probably one of the most flexible and
> easy to use systems around. I have done several fairly large projects
> with PHP and while it is an "ugly" environment, it performs well enough,
> has a very usable extension interface, it is quick and easy to even
> large projects done.

I would say that compared to Perl, TCL, and many other scripting languages
that PHP is actually a far better and more logically designed language.
the way it handles arrays and global vars is the way every language
should.

On Mon, Apr 14, 2003 at 07:54:27PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Several people have asked if we are losing momentum.
>
> I was just in the local Barnes & Noble store
> yesterday, and could not help but notice how many books had "MySQL" in
> the title. I didn't notice a single Postgres title (though I did not
> look hard, since I was just passing through the computer area).

2 local stores here:
One has 11 PostgresQL books and 40 MySQL, the other had 5 on
PostgresQL and 23 about MySQL.

Kurt

On Tuesday 15 April 2003 07:51, mlw wrote:
> We need to take projects like Bugzilla (Did RH ever release the PG
> version or am I way out of date?) and port them to PostgreSQL.

http://bugzilla.redhat.com/download/rh-bugzilla-pg-LATEST.tar.gz
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Tom Lane wrote:
> narrow the bookshelf gap a little, at least. Any wannabee authors
> out there? (And Bruce, your book is due for a second edition...)

Agreed. I will contact the publisher and get started, maybe in the
summer.

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

Having good reference sites is important, and I could list as many
impressive ones as MySQL, but who has time to hunt around and get
permission to list them --- I will tell you who --- the MySQL marketing
guys, while the PostgreSQL guys don't. :-(

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

Ron Mayer wrote:
> IMVHO it's reference customers/users more than books & windows ports.
>
> If I were a naive middle manager in some company, would I rather
> use:
>
> (a) the database used by Yahoo, Cisco, and Sony?
> (b) the database used by Shannon Med Center, Mohawk SW, Vanten Inc, and BASF.
>
> Now suppose I told that same middle manager there was an open
> source alternative:
>
> (c) used by Lockheed Martin, Nasdaq, AOL, and Unisys.
>
> As far as I can tell (5-minutes searching) (c) is PostgreSQL.
>
> http://jobsearch.monster.com/jobsearch.asp?q=postgresql
> http://www.hotjobs.com/cgi-bin/job-search?KEYWORDS=postgres
> http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&dockey=xml/8/1/816e9b7e50ae92331bb5c47a791a589f(at)activejobs0&c=1
> http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&dockey=xml/c/8/c8dc5841d18329c6c50b55f67a7ff038(at)activejobs0&c=1
> http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&dockey=xml/1/6/168f30dc84b8f195d1fc35feb6a2f67a(at)activejobs0&c=1
> "The Nasdaq Stock Market ... currently looking to fill the following
> positions in Trumbull, CT...Some positions require knowledge of ...Postgre SQL.."
>
>
> I'm not sure quite what it'd take to get the permission to use
> these company's names, but surely we could have a list of links
> to the job postings... I'd bet that one of monster, hotjobs,
> and/or dice would even provide a datafeed of relevant jobs to
> be posted on the postgresql.org site.
>
>
> If we simply had a list of companies using postgresql highly visible
> somewhere -- not necessarily a complex case study, just simple list
> of "company X uses postgresql for Y" statements -- I think it would
> go a long way. I'll contribute. InterVideo uses postgresql (for
> running user surveys and some internal reporting and development tools).
>
> Ron
>
> PS: No offense to Shannon, Mohawk, Vanten, and yes, I know BASF is
> an awesome company. But they're all, even BASF, less of
> a household name than Sony,Yahoo,Cisco,AOL,Nasdaq,Lockheed.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

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

Shridhar Daithankar wrote:
> On Tuesday 15 April 2003 05:48, you wrote:
> > Regardless, I'm still of the opinion that if you build it, they will come
> > -- particularly costly features like replication, PITR, etc. But maybe
> > that is what the BSDs say about Linux?
>
> That is an unfair comparison. The technical differences between BSD and linux
> are not as much as postgresql and mysql. Besides what is the parallel of SQL
> standard in OS world? POSIX? And both BSD/linux are doing fine sitting next
> to each other on that.

Agreed, Linux and BSD are pretty close --- but Linux used to be behind
BSD --- they caught up because both are open source. The big question
is whether MySQL (which isn't openly developed) will catch up to
PostgreSQL. And if they do catch up, will we have mind share parity by
that time?

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

The impression of MySQL is light weight and fast,
the reputation of PostgreSQL is full featured.
Business chooses PostgreSQL is bacause PostgreSQL is close
to database like Oracle, reliable but without cost.

To compete with MySQL is not a good strategy, IMHO,
PostgreSQL needs to focus adding features such as
table partitioning like Oracle, needs to improve
the performance of subquery, etc. Those lack performance
features are the choke point (it's easy to get better performance
for a big table [~100 million] with partitions in oracle than
postgreSQL; it's a nightmare, a mess for using subquery in postgreSQL,
I can't wait 7.4's smarter on this).

If you really have a super product, don't you
worry user will not switch to it with no cost?

just some thoughts ...

johnl

On Tue, 2003-04-15 at 09:43, Lamar Owen wrote:
> On Tuesday 15 April 2003 07:51, mlw wrote:
> > We need to take projects like Bugzilla (Did RH ever release the PG
> > version or am I way out of date?) and port them to PostgreSQL.
>
> http://bugzilla.redhat.com/download/rh-bugzilla-pg-LATEST.tar.gz

Of course, the installation instructions that come with it tell you
to install perl's interface to MySQL, not PostgreSQL. Sigh.

--
Steve Wampler <swampler(at)noao(dot)edu>
National Solar Observatory

IMHO, mySql 5.0 will put more pressure on PostgreSql, when it's
available.

One of the features that PostgreSql must have, IMHO, is support for
cross-db operations (queries, updates, deletes, inserts). 2PC and
cross-server stuff would be nice but it's not as important as simple
cross -db operations across databases on the same server. All major
comercial RDBMS (and even mySql!) support this but for Postgres. Sad.

__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com

> > Regardless, I'm still of the opinion that if you build it, they
> > will come -- particularly costly features like replication, PITR,
> > etc. But maybe that is what the BSDs say about Linux?
>
> That is an unfair comparison. The technical differences between BSD
> and linux are not as much as postgresql and mysql. Besides what is
> the parallel of SQL standard in OS world? POSIX? And both BSD/linux
> are doing fine sitting next to each other on that.
>
> After porting my small application in less than half an hour from
> linux to freeBSD and vice versa, I really do not agree with that
> comment. Not even in the spirit of it.

Yes, that is the joy of POSIX, ANSI, SUS, SUSv2, XPG*, etc. The
differences in the OS aren't visible at the user level and shouldn't
be (beyond the layout/management). That said, standards are great,
but all select()/poll() calls weren't created equal, just like all
SELECT statements weren't created equal. -sc

--
Sean Chittenden

On Tue, 2003-04-15 at 13:30, ow wrote:
> IMHO, mySql 5.0 will put more pressure on PostgreSql, when it's
> available.
>
> One of the features that PostgreSql must have, IMHO, is support for
> cross-db operations (queries, updates, deletes, inserts). 2PC and
> cross-server stuff would be nice but it's not as important as simple
> cross -db operations across databases on the same server. All major
> comercial RDBMS (and even mySql!) support this but for Postgres. Sad.
>

dblink ?

Robert Treat

>From my experience, almost every time I talk to a MySQL supporter about
PostgreSQL, the whole "vacuum" issue always seems to come up. Some way
to get vacuum automated (and thus out of sight, out of mind) I think
would make great strides in making PG at least "seem" more friendly to
someone on the outside.

Shared hosting enviroments. I work for a web hosting company that offers
MySQL to all of its customers, our MySQL server has several thousand
databases on it, and I must say it works exceptionally well.

Creating users/databases/changing passwords is as simple as sending it a
couple queries from our Customer web interface, trouble shooting poor
queries takes seconds when using "mytop" (mtop), and tracking/billing
for disk usage is as simple as running "du /var/lib/mysql/*". I would
like to say the same things for PG, but I'm affrid I can't.

I think it all comes down to how simple PG is to setup and use on a
daily basis. This is what determines the size of its community. Even
just the simple things make a big difference. ie:

\dt

compared to:

show tables;

Yes, once you get over the "hump" PG is quite efficient, but you need to
understand it, and learn some small quriks first. With MySQL, you can
pretty much guess commands, and they often work! Not as much luck with
PG.

show indexes
show processlist
show columns from <table>

These are all easy/simple commands that make sense to someone who is
just learning the ropes. Short abbreviated, commands are great for the
experts, but can greatly discourage newbies.

On Mon, 2003-04-14 at 18:52, Brent Verner wrote:
> Gretings!
>
> [2003-04-14 19:54] Tom Lane said:
> | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> | > Several people have asked if we are losing momentum.
>
> | I don't know what we can do about it, other than maybe push harder to
> | get some more PG titles into O'Reilly's catalog ... that would help
> | narrow the bookshelf gap a little, at least. Any wannabee authors
> | out there? (And Bruce, your book is due for a second edition...)
>
> I've wanted to pipe up in a few of these "popularity"
> discussions in the past. Seeing how I can't make time to
> participate in any other meaningful capacity, I'll share
> my thoughts on _why_ mysql has the mindshare.
>
>
> Applications, specifically applications that _use_ mysql.
>
>
> A quick search over at freshmeat returns 1044 results for
> "mysql" and 260 for "postgresql". Before this turns into a
> cause/effect discussion, I want to state up front that the
> real "effect" of this is that someone is 4 times as likely to
> download an application that uses mysql. Sure, many are
> "trivial" applications, but I posit that it is _specifically_
> these "trivial" applications that inoculate the uninitiated
> with the belief that mysql is suitable for use in real, albeit
> trivial applications. Additionally, it these rudimentary
> applications that will be studied by many as the way to write
> a database application.
>
> It is all good and well that postgres /can/ do, but until
> the application developers see that those features are
> valuable enough to forgo mysql support, they'll write the
> application to support whatever database is most likely to
> _already_ be installed, which will be mysql. Granted,
> many developers will also try to support multiple dbs via
> the language's db api, but this leaves the less-supported
> dbs in an even worse position; being relegated to an
> "might work with XXX database". When anxious user learns
> that "might" currently means "doesn't," the second-string
> database looks even worse in the eyes of the user.
>
> How to solve this problem? This is the hard part, but
> luckily ISTM that there are a few ways to succeed. Neither
> of which involves marketing or writing books.
>
> 1) become active in the "also supports postgres" projects,
> and add features that are made available _because_ of
> postgres' superiority. Eventually, market pressure
> for the cool feature(s) will lead users to choose
> postgres, and mysql could be relegated to the "also
> runs on mysql, with limited featureset"
> 2) take a popular project that uses mysql, fork it, and
> add features that can only be implemented using posgres.
> 3) release that super-cool code that you've been hacking
> on for years, especially if it is a "trivial" app.
> 4) convince your employer that it would be _beneficial_ to
> them to release, as open source, the internal app(s) you've
> developed, using postgres-specific features. (This is
> about all I can claim to be doing at this point in my
> indentured servitude, and I can't say I'm doing a good
> job... :-/)
>
> I'm sure this idea is not original, but I'm also sure that
> it _is_ the answer to gaining market^Wmindshare in this
> database market.
>
> (I must apologize in advance, that I might not have time
> to even follow this thread, in fact, I hope that instead of
> replying to this, the potential respondent might consider
> helping to increase the number of apps that require postgres
> :-)
>
> wishing-I-could-contribute-more-ly yours,
> brent
--
Best Regards,

Mike Benoit
NetNation Communications Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
---------------------------------------

Disclaimer: Opinions expressed here are my own and not
necessarily those of my employer

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> On Tue, 2003-04-15 at 13:30, ow wrote:
>> One of the features that PostgreSql must have, IMHO, is support for
>> cross-db operations (queries, updates, deletes, inserts). 2PC and
>> cross-server stuff would be nice but it's not as important as simple
>> cross -db operations across databases on the same server. All major
>> comercial RDBMS (and even mySql!) support this but for Postgres. Sad.

> dblink ?

I'm of the opinion that the availability of schemas solves most of the
problems that people say they need cross-database access for. If you
want cross-database access, first say why putting your data into several
schemas in a single database doesn't get the job done for you.

(Obviously, this only addresses cases where you'd have put the multiple
databases under one postmaster, but that's the scenario people seem to be
concerned about.)

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm of the opinion that the availability of schemas solves most of
> the problems that people say they need cross-database access for. If

> you want cross-database access, first say why putting your data into
> several schemas in a single database doesn't get the job done for
you.

Some databases contain lots of data, e.g. dbs that contain historical
data. No one wants to have one HUGE db that runs all company's apps,
takes hours (if not days) to recover and when this huge db goes down
none of the apps is available.

__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com

On Tue, 2003-04-15 at 15:35, ow wrote:
> Some databases contain lots of data, e.g. dbs that contain historical
> data. No one wants to have one HUGE db that runs all company's apps,
> takes hours (if not days) to recover and when this huge db goes down
> none of the apps is available.

Are you talking about queries between databases on the same postmaster
(i.e. running under the same PostgreSQL installation), or queries
between postmasters running on different systems? If the former, I don't
see how putting your data into multiple schemas in a single database is
significantly less reliable than putting it into multiple databases.

Cheers,

Neil

Mike Benoit writes:

> Shared hosting enviroments. I work for a web hosting company that offers
> MySQL to all of its customers, our MySQL server has several thousand
> databases on it, and I must say it works exceptionally well.
>
> Creating users/databases/changing passwords is as simple as sending it a
> couple queries from our Customer web interface, trouble shooting poor
> queries takes seconds when using "mytop" (mtop), and tracking/billing
> for disk usage is as simple as running "du /var/lib/mysql/*". I would
> like to say the same things for PG, but I'm affrid I can't.

At least in the latest versions, things are quite easy.

User/database administration?
CREATE USER someuser ENCRYPTED PASSWORD '...' NOCREATEDB NOCREATEUSER;
CREATE DATABASE someuser OWNER someuser ENCODING 'UNICODE';

Disk usage account? Use contrib/dbsize (README for easy setup)
SELECT database_size('someuser');
Done.

Poor queries -> query stats?

Of course, some things are easier in MySQL. On the other hand, what about
InnoDB, "du /var/lib/mysql/*" won't help much...

I just wanted to show that PostgreSQL administration is not that hard in a
hosting environment.

Regards,
Michael Paesold

--- Neil Conway <neilc(at)samurai(dot)com> wrote:
> Are you talking about queries between databases on the same
> postmaster
> (i.e. running under the same PostgreSQL installation),

Yes

> or queries
> between postmasters running on different systems? If the former, I
> don't
> see how putting your data into multiple schemas in a single database
> is
> significantly less reliable than putting it into multiple databases.

I disagree. For example, suppose you have
app12 that uses db1 and db2,
app23 that uses db2 and db3,
app3 that uses db3.

If db3 goes down then app12 is not affected, app23 could be partially
affected (e.g. user may not be able to run historic queries) and app3
is completely unavailable. This is definitely better than all three
apps are down. Besides, having one huge db makes everything more
difficult and requires (much) more time for backups, restores, etc.

Every major RDBMS vendor (and mySql) finds this feature important and
they support it. Hope Postgresql will too.

__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com

ow <oneway_111(at)yahoo(dot)com> writes:
> --- Neil Conway <neilc(at)samurai(dot)com> wrote:
>> Are you talking about queries between databases on the same
>> postmaster

> Yes

> [snip]

> If db3 goes down then app12 is not affected, app23 could be partially
> affected (e.g. user may not be able to run historic queries) and app3
> is completely unavailable.

This is nonsense. There is no scenario where one DB "goes down" and
other DBs on the same postmaster remain up. There are advantages to
having separate DBs on one postmaster (like separate copies of the
system catalogs), but there's very little reliability differential
compared to a multi-schema approach.

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> This is nonsense. There is no scenario where one DB "goes down" and
> other DBs on the same postmaster remain up. There are advantages to
> having separate DBs on one postmaster (like separate copies of the
> system catalogs), but there's very little reliability differential
> compared to a multi-schema approach.

Perhaps "goes down" is not the best term. You can replace it with "is
not available" (as in being restored, etc) if you like.

__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com

On the "lets make more apps work with Postgres" front people can check out
http://sourceforge.net/projects/bind-dlz

This is a patch for Bind 9.2.1 that allows all DNS data to be stored in an
external database. Makes DNS administration easy, and changes to DNS data
are reflected immediately. The project supports multiple databases now, but
the first one was postgres!

Later
Rob

ow said...
> --- Neil Conway <neilc(at)samurai(dot)com> wrote:
> > Are you talking about queries between databases on the same
> > postmaster
> > (i.e. running under the same PostgreSQL installation),
>
> Yes

Based on your later comments, the answer seems to /actually/ be "No."

> > or queries
> > between postmasters running on different systems? If the former, I
> > don't
> > see how putting your data into multiple schemas in a single database
> > is
> > significantly less reliable than putting it into multiple databases.
>
> I disagree. For example, suppose you have
> app12 that uses db1 and db2,
> app23 that uses db2 and db3,
> app3 that uses db3.
>
> If db3 goes down then app12 is not affected, app23 could be partially
> affected (e.g. user may not be able to run historic queries) and app3
> is completely unavailable. This is definitely better than all three
> apps are down. Besides, having one huge db makes everything more
> difficult and requires (much) more time for backups, restores, etc.
>
> Every major RDBMS vendor (and mySql) finds this feature important and
> they support it. Hope Postgresql will too.

If it's all running as just one PostgreSQL instance, then if db1 goes down,
then, since it's the same postmaster as is supporting db2 and db3, they
necessarily go down as well.

The only way that you get to take down one DB without affecting the others is
for them NOT to be running as part of the same PG installation.

By the way, if you only have one PG instance, then you may very well find it
challenging to suitably parallelize all the loads/dumps of data. If you have
three disks, or three arrays, it may make a lot of sense to have separate PG
instances on each one, as that allows I/O to not need to interfere between
instances. (There are, admittedly, other ways of tuning this sort of thing,
such as moving WAL to a separate disk, or perhaps even specific table files,
identified by OID...)

But the most general ways of separating things out lead to having quite
separate DB instances. And when you've got that, it certainly is attractive
to have 2PC, as is available for the "expensive guys."
--
output = reverse("gro.mca@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/sap.html
You know that little indestructible black box that is used on
planes---why can't they make the whole plane out of the same
substance?

> Tatsuo, this has always fascinated me. Any insights you could share about how PostgreSQL achieved the prominence it has in Japan (and how MySQL did not) would be very interesting.

PostgreSQL started to become popular in 1998(PostgreSQL 6.4 days). In
the year a publisher asked me to write the first PostgreSQL book and
fortunately it has sold very well. From then many PostgreSQL books
have been published and lots of magazine articles have been written
too. As as result, PostgreSQL users could enjoy rich PostgreSQL
information in Japanese. Since most Japanese (including me) is not
very good at English, localized docs for PostgreSQL is the key factor
for the "prominence". On the other hand, almost no good Japanese MySQL
books have ever appeared.

Next point is the community. Japan PostgreSQL Users Group (JPUG) has
been established in 1999 and now has over 1800 registered members
(local ML for PostgreSQL has over 5400 subscribers). I guess MySQL
does not have this kind large community.

These are not proven factors for the popularity of PostgreSQL in
Japan, I believe they definitely could be listed as one of the top 10
reasons.
--
Tatsuo Ishii

On Wednesday 16 April 2003 00:26, Mike Benoit wrote:
> From my experience, almost every time I talk to a MySQL supporter about
> PostgreSQL, the whole "vacuum" issue always seems to come up. Some way
> to get vacuum automated (and thus out of sight, out of mind) I think
> would make great strides in making PG at least "seem" more friendly to
> someone on the outside.

Agreed. But that is not an impossible issue for a DBA, is it? I mean some
learning is required but that can be done.

> Creating users/databases/changing passwords is as simple as sending it a
> couple queries from our Customer web interface, trouble shooting poor
> queries takes seconds when using "mytop" (mtop), and tracking/billing
> for disk usage is as simple as running "du /var/lib/mysql/*". I would
> like to say the same things for PG, but I'm affrid I can't.

Adding users, databases, password changes are as easy in postgresql. Tracking
disk usage is no different in postgresql barring additional step of using
oid2name to find out directory you want to du.

In fact I think postgresql is easier to use. Till date, I could never start
mysql by hand and get it behave sanely. pg_ctl or nohup postmaster has always
worked for me.

Besides postgresql is true to it's resource usage. You allocate 128MB of
shared buffers, and they are consumed. You stop postmaster and all the
buffers are back to system. With mysql, I found that large amount of memory
was never returned to system even after service shutdown. I hate black-boxes
on my system where I can not fathom into. Had to reboot the machine.

> I think it all comes down to how simple PG is to setup and use on a
> daily basis. This is what determines the size of its community. Even
> just the simple things make a big difference. ie:
>
> \dt
>
> compared to:
>
> show tables;

<I assume that show tables is not a standard SQL syntax>

That is very shallow view. \dt is a postgresql terminal client extension where
as show tables is part of mysql SQL offerings. Such brutal twisting of SQL
standards encourages dependence on mysql only features, flushing standard
compliance down the drain.

> Yes, once you get over the "hump" PG is quite efficient, but you need to
> understand it, and learn some small quriks first. With MySQL, you can
> pretty much guess commands, and they often work! Not as much luck with
> PG.
>
> show indexes
> show processlist
> show columns from <table>
>
> These are all easy/simple commands that make sense to someone who is
> just learning the ropes. Short abbreviated, commands are great for the
> experts, but can greatly discourage newbies.

Well, I might get flamed for this but let me clarify. I am not against
newbies. Everybody once was a newbie. But being a newbie, does not justify
reluctance to go thr. manuals. If you are reluctant to go thr. manuals., you
better hire a commercial support.

My advise has always been ,to read postgresql manual start to end before even
touching it. It takes a day to digest but pays off big later. When I started
postgresql back in 1999, I started on postgresql and SQL simalteneously.
Didn't have faintest idea, what any of those stand for. So I read the manual,
start to end in couple of days. In one day I could do things that worked as
expected.

RTFM is not an advice thrown to kick out newbies. It is ground fact that
everybody has to suffer thr. Borg transplants are not yet available here.

Shridhar

On Tuesday 15 April 2003 22:25, Bruce Momjian wrote:
> Shridhar Daithankar wrote:
> > That is an unfair comparison. The technical differences between BSD and
> > linux are not as much as postgresql and mysql. Besides what is the
> > parallel of SQL standard in OS world? POSIX? And both BSD/linux are doing
> > fine sitting next to each other on that.
>
> Agreed, Linux and BSD are pretty close --- but Linux used to be behind
> BSD --- they caught up because both are open source. The big question
> is whether MySQL (which isn't openly developed) will catch up to
> PostgreSQL. And if they do catch up, will we have mind share parity by
> that time?

That is a tough question. But if we focus on enterprise features and reach
threshold in decision making circles, that would be great.

Mind share parity certainly matters. Bigger question is in which circles. I
would better put decision making circle as fist target.

Besides we won't sit still while mysql catches with us.

Shridhar

> BTW, DB2 doesn't have 'em [cross-db queries] either.
> In DB2, you can of course have cross-schema queries but no cross-db
> queries, unless you rig up the federated functionality to connect one
> db to the other.

A while ago I was at a client who wanted to migrate to DB2 and this
questions was raised during discussions with IBM. There was a way to do
this, if I remember correctly the solution involved creating views for
all tables from db2 that you wanted to use in db1 and maybe something
else. Can't tell you for sure, I'm not working with DB2.

Oracle, Sybase, Ms, Informix (? AFAIK) , mySql, they all support
cross-db queries.

Anyway, I thought it was important to bring this up. With large number
of apps and large amount of data having everything in one db is a sure
way for disaster, IMHO.

__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> In fact I think postgresql is easier to use. Till date, I could never start
> mysql by hand and get it behave sanely. pg_ctl or nohup postmaster has always
> worked for me.

This is weird, because despite mysql's technical inferiority, it really is
pretty simple to use. Also seems a little hypocritical of you in light of
the RTFM rant later on in your email. :)

> Besides postgresql is true to it's resource usage. You allocate 128MB of
> shared buffers, and they are consumed. You stop postmaster and all the
> buffers are back to system. With mysql, I found that large amount of memory
> was never returned to system even after service shutdown. I hate black-boxes
> on my system where I can not fathom into. Had to reboot the machine.

"Black-boxes"? It's open-source, just like we are. Did you read their manual
"start to end"? Did you ask on their mailing lists? I'm no MySQL fan, but
I'd rather let them, not us, dish out the FUD. The original poster had some
valid points (auto-vacuum and non-intuitive commands) that still need
addressing, IMO.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200304160945

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+nV/EvJuQZxSWSsgRAsaXAKCAY3vGFxDzk9dniqojpi+RK3ToUwCgpv5L
Sl6e9Or440U5QeLIhvNsaro=
=k5Np
-----END PGP SIGNATURE-----

On Wednesday 16 April 2003 19:21, greg(at)turnstep(dot)com wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> > In fact I think postgresql is easier to use. Till date, I could never
> > start mysql by hand and get it behave sanely. pg_ctl or nohup postmaster
> > has always worked for me.
>
> This is weird, because despite mysql's technical inferiority, it really is
> pretty simple to use. Also seems a little hypocritical of you in light of
> the RTFM rant later on in your email. :)

Yes. That is correct. But going thr. 3.5MB html to find out things which has
got tons of options and figuring out interdependencies by trial and error is
not a good job at that. Whoever thinks that such a style on manual writing is
good, needs an attitude readjustment. Postgresql manual is ten times better.

> > Besides postgresql is true to it's resource usage. You allocate 128MB of
> > shared buffers, and they are consumed. You stop postmaster and all the
> > buffers are back to system. With mysql, I found that large amount of
> > memory was never returned to system even after service shutdown. I hate
> > black-boxes on my system where I can not fathom into. Had to reboot the
> > machine.
>
> "Black-boxes"? It's open-source, just like we are. Did you read their
> manual "start to end"? Did you ask on their mailing lists? I'm no MySQL
> fan, but I'd rather let them, not us, dish out the FUD. The original poster
> had some valid points (auto-vacuum and non-intuitive commands) that still
> need addressing, IMO.

I didn't go to any mailing list. My point is, if I pierce the startup-shutdown
chapter in mysql manual and can not get it working by hand, either I am
stupid or something wrong with mysql. May sound arrogant but I count on
later.

Have you seen postgresql 101 I wrote? It is at
http://wiki.ael.be/index.php/PostgresQL101. It is that simple with
postgresql. Now this is not the forum but can anybody point me to similar
document for mysql. /etc/rc.d/init.d/mysql start always works but it does not
allow me to tweak options for mysqld which is first thing I want.

Anyway I must admit that I was reluctant to use mysql and was turned off
pretty quickly. Mine is probably a irreproducible bug but I did encounter it.

Shridhar

greg(at)turnstep(dot)com kirjutas K, 16.04.2003 kell 16:51:
> The original poster had some
> valid points (auto-vacuum and non-intuitive commands) that still need
> addressing, IMO.

As of 7.3 (or was it 7.2) auto-vacuum is just one line in crontab. In
many scenarios it can be left running continuously with very little
effect on performance. In others it must be run nightly, but having it
kick in at unexpected times may not be what you want at all. So it has
to be configured for good performance weather it is built-in or run in a
separate backend process.

And I can't see how "show tables" is more intuitive than "\dt" - I
expected it to be "list tables" or "tablelist" or "näita tabeleid" .

Once you have found \? it is all there (and you are advised to use \? at
psql startup).

That may also be why PostgreSQL is more popular in Japan - if one has to
remember nonsensical strings, then it is easier to remember short ones
;)

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

> > That's a pretty reasonable thought. I work for a shop that sells
> > Postgres support, and even we install MySQL for the Q&D ticket
> > tracking system we recommend because we can't justify the cost to
> > port it to postgres. If the postgres support were there, we would
> > surely be using it.
>
> > How to fix such a situation, I'm not sure. "MySQL Compatability
> > Mode," anyone? :-)
>
> What issues are creating a compatibility problem for you?

I don't think these should be hacked into the backend/libpq, but I
think it'd be a huge win to hack in "show *" support into psql for
MySQL users so they can type:

SHOW (databases|tables|views|functions|triggers|schemas);

I have yet to meet a MySQL user who understands the concept of system
catalogs even though it's just the 'mysql' database (this irritates me
enough as is)... gah, f- it: mysql users be damned, I have three
developers that think that postgresql is too hard to use because they
can't remember "\d [table name]" and I'm tired of hearing them bitch
when I push using PostgreSQL instead of MySQL. I have better things
to do with my time than convert their output to PostgreSQL. Here goes
nothing...

I've tainted psql and added a MySQL command compatibility layer for
the family of SHOW commands (psql [-m | --mysql]).

The attached patch does a few things:

1) Implements quite a number of SHOW commands (AGGREGATES, CASTS,
CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES,
DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS,
PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS,
TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS)

SHOW thing
SHOW thing LIKE pattern
SHOW thing FROM pattern
SHOW HELP ON (topic || ALL);
etc.

Some of these don't have \ command eqiv's. :( I was tempted to add
them, but opted not to for now, but it'd certainly be a nice to
have.

2) Implements the necessary tab completion for the SHOW commands for
the tab happy newbies/folks out there. psql is more friendly than
mysql's CLI now in terms of tab completion for the show commands.

3) Few trailing whitespace characters were nuked

4) guc.c is now in sync with the list of available variables used for
tab completion

Few things to note:

1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in
this regard and that it should be INDEXES to be plural along with
the rest of the types, but INDEX is preserved for compatibility.

2) There are two bugs that I have yet to address

1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does
2) "SHOW [variable_of_choice];" doesn't work, but "SHOW
[variable_of_choice]\n;" does work... not sure where this
problem is coming from

3) I think psql is more usable as a result of this more verbose
syntax, but it's not the prettiest thing on the planet (wrote a
small parser outside of the backend or libraries: I don't want to
get those dirty with MySQL's filth).

4) In an attempt to wean people over to PostgreSQL's syntax, I
included translation tips on how to use the psql equiv of the SHOW
commands. Going from SHOW foo to \d foo is easy, going from \d foo
to SHOW foo is hard and drives me nuts. This'll help userbase
retention of newbies/converts. :)

5) The MySQL mode is just a bounce layer that provides different
syntax wrapping exec_command() so it should provide little in the
way of maintenance headaches. Some of the SHOW commands, however,
don't have \ couterparts, but once they do and that code is
centralized, this feature should come for zero cost.

6) As an administrator, I'd be interested in having an environment
variable that I could set that'd turn on MySQL mode for some of my
bozo users that way they don't complain if they forget the -m
switch. Thoughts?

I'll try and iron out the last of those two bugs/features, but at this
point, would like to see this patch get wider testing/feedback.
Comments, as always, are welcome.

PostgreSQL_usability++

-sc

--
Sean Chittenden

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> 4) guc.c is now in sync with the list of available variables used for
> tab completion

AFAIK, the GUC variables not listed in tab-complete.c were omitted
deliberately. We could have a discussion about the sensefulness of
those decisions, but please do not consider it a bug to be fixed
out-of-hand.

> 6) As an administrator, I'd be interested in having an environment
> variable that I could set that'd turn on MySQL mode for some of my
> bozo users that way they don't complain if they forget the -m
> switch. Thoughts?

Can't you set it in ~/.psqlrc ?

regards, tom lane

> > 4) guc.c is now in sync with the list of available variables used for
> > tab completion
>
> AFAIK, the GUC variables not listed in tab-complete.c were omitted
> deliberately. We could have a discussion about the sensefulness of
> those decisions, but please do not consider it a bug to be fixed
> out-of-hand.

Alright, there weren't many omitted GUC's, but those that were
omitted did have counterparts that were include already so I figured
there was some bit rot going on.

> > 6) As an administrator, I'd be interested in having an environment
> > variable that I could set that'd turn on MySQL mode for some of my
> > bozo users that way they don't complain if they forget the -m
> > switch. Thoughts?
>
> Can't you set it in ~/.psqlrc ?

Hrm... ah, ok, done. Patch updated.

http://people.freebsd.org/~seanc/patches/patch_postgresql-HEAD::src::bin::psql

-sc

--
Sean Chittenden

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> 4) guc.c is now in sync with the list of available variables used for
> tab completion
>>
>> AFAIK, the GUC variables not listed in tab-complete.c were omitted
>> deliberately. We could have a discussion about the sensefulness of
>> those decisions, but please do not consider it a bug to be fixed
>> out-of-hand.

> Alright, there weren't many omitted GUC's, but those that were
> omitted did have counterparts that were include already so I figured
> there was some bit rot going on.

There could be some of that too. I was just saying that it's not a
foregone conclusion to me that every parameter known to guc.c should
be in the tab completion list.

regards, tom lane

Bruce wrote:

>Having good reference sites is important, and I could list as many
>impressive ones as MySQL, but who has time to hunt around and get
>permission to list them --- I will tell you who --- the MySQL marketing
>guys, while the PostgreSQL guys don't. :-(

Is it a good enough benefit to make the ones we already
have easier to find?

If the content on these pages:

http://techdocs.postgresql.org/techdocs/supportcontracts.php
http://advocacy.postgresql.org/casestudies/
http://archives.postgresql.org/pgsql-announce/2002-11/msg00004.php

could be integrated and put on an easy to find page in the
advocacy area it'd be a lot easier for new people to see.

I know PostgreSQL's got at least as impressive a list as MySQL. It's
just that you need to dig harder to find it.

Ron

On Wed, 16 Apr 2003 greg(at)turnstep(dot)com wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > In fact I think postgresql is easier to use. Till date, I could never start
> > mysql by hand and get it behave sanely. pg_ctl or nohup postmaster has always
> > worked for me.
>
> This is weird, because despite mysql's technical inferiority, it really is
> pretty simple to use. Also seems a little hypocritical of you in light of
> the RTFM rant later on in your email. :)

I hate to join in this thread but...

I don't find it weird. It's probably a different mind set or something but I
find the MySQL documentation discussing something that will be in version 8.34
when they still list 3.23 as the latest production version is so confusing when
it's written with no indication that the thing isn't already in place.

Just my own view. People say MySQL is easy and PostgreSQL is difficult to
learn. I say PostgreSQL is easy and MySQL is difficult to learn.

And as for it being maintenance free while a regular vacuum is something too
difficult a concept for people to grasp. Well, what do these maintenance free
MySQL folk do with the regular tasks that MySQL needs run?

--
Nigel Andrews

Sean Chittenden writes:

> I don't think these should be hacked into the backend/libpq, but I
> think it'd be a huge win to hack in "show *" support into psql for
> MySQL users so they can type:
>
> SHOW (databases|tables|views|functions|triggers|schemas);

Well, we (will) have the information schema, and if you like you can put
it in the path and write

select * from tables;

etc., which seems just as good.

--
Peter Eisentraut peter_e(at)gmx(dot)net

On Thursday 17 April 2003 13:35, Nigel J. Andrews wrote:

> I hate to join in this thread but...

me too, but I am suffering from a bout of MySQL :-(

(...)
> Just my own view. People say MySQL is easy and PostgreSQL is difficult to
> learn. I say PostgreSQL is easy and MySQL is difficult to learn.

Having had to use MySQL seriously for the first time for a long time, I am finding
it makes the easy things (appear) easy and the difficult things impossible.
For example, AUTO_INCREMENT is easy to set up and use, but
is a toy feature compared to real sequences...

> And as for it being maintenance free while a regular vacuum is something
> too difficult a concept for people to grasp. Well, what do these
> maintenance free MySQL folk do with the regular tasks that MySQL needs run?

This is what MySQL recommends:
http://www.mysql.com/doc/en/Maintenance_regimen.html

How about repackaging VACUUM as a "database defragmentation
utility"? After all many many people have come to accept
disk defragmenters as an essential part of their OS ;-)

Ian Barwick
barwick(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Sean Chittenden writes:
>> I don't think these should be hacked into the backend/libpq, but I
>> think it'd be a huge win to hack in "show *" support into psql for
>> MySQL users so they can type:
>>
>> SHOW (databases|tables|views|functions|triggers|schemas);

> Well, we (will) have the information schema, and if you like you can put
> it in the path and write
> select * from tables;
> etc., which seems just as good.

I think Sean's idea is not to make it "just as easy as MySQL", it's to
make it "the *same* as MySQL", for the benefit of those that refuse to
learn differently. Them as won't adjust to "\dt" in place of "show
tables" aren't likely to adjust to "select * from tables" either.
Not even (maybe especially not) if it's arguably a standard.

I think the idea has some merit; although I wonder whether it wouldn't
be smarter to put the code in the backend so that you don't need a
parser in psql. The SHOW code could fall back to looking at these
possibilities after it fails to find a match to a GUC variable name.

regards, tom lane

> >> I don't think these should be hacked into the backend/libpq, but
> >> I think it'd be a huge win to hack in "show *" support into psql
> >> for MySQL users so they can type:
> >>
> >> SHOW (databases|tables|views|functions|triggers|schemas);
>
> > Well, we (will) have the information schema, and if you like you
> > can put it in the path and write select * from tables; etc., which
> > seems just as good.

I thought about changing the SHOW commands to executing a SELECT from
the information_schema schema, actually, but unless the various \d
commands are going to get switched to SELECT'ing from the
information_schema, it decreases the likelihood that someone will
successfully switch over to using the \d commands in psql. I believe
that the \d commands are a good thing and it's good that the backend
doesn't have support for the \d commands. Having \d or SHOW commands
in the backend would dirty up the sources of the backend, IMHO.

> I think Sean's idea is not to make it "just as easy as MySQL", it's
> to make it "the *same* as MySQL", for the benefit of those that
> refuse to learn differently. Them as won't adjust to "\dt" in place
> of "show tables" aren't likely to adjust to "select * from tables"
> either. Not even (maybe especially not) if it's arguably a
> standard.

It's amazing what you can accomplish by taking out your DBAs and
racking up a $20 bar tab (note to would be attempters of this method:
when the bar tab gets to $50, you find out things you didn't really
want to know or weren't intending to hear). So yeah, as Tom said,
they know \d [table_name], I've been able to get that much through,
but syntactically, it doesn't offer the same syntactic goo that their
fingers are used to typing and they _hate_ that there's no SHOW
command for tables. It's a usability irk that kills them every time,
they type "SHOW [tab]" him. There are a few other things I picked up
that evening too.

*) MySQL's CLI tab completion is terrible and the SHOW commands work
even worse than other tab operations in mysql, this _is_ something
that they do like about psql. psql's tab completion is really
snappy by comparison.

*) The _only_ time that they use the SHOW commands is when they're
using a CLI.

So as opposed to supporting MySQL's brokenness, I hacked a small
parser into psql and added a TIP to the top of the result set printed
to stderr that tells the user how to use the equiv \d command.

> I think the idea has some merit; although I wonder whether it
> wouldn't be smarter to put the code in the backend so that you don't
> need a parser in psql. The SHOW code could fall back to looking at
> these possibilities after it fails to find a match to a GUC variable
> name.

Well, I think that the backend should be kept clean of MySQL's
nastiness. It's ugly to have a small parser in psql, but I think it's
better off than letting MySQL dictate non-existent standards to the
rest of the DB community because its developers have struck a chord
with the newbie database masses. PostgreSQL is a better database, we
shouldn't have to cater to their hackery. -sc

--
Sean Chittenden

Sean Chittenden <sean(at)chittenden(dot)org> writes:
>> I think the idea has some merit; although I wonder whether it
>> wouldn't be smarter to put the code in the backend so that you don't
>> need a parser in psql. The SHOW code could fall back to looking at
>> these possibilities after it fails to find a match to a GUC variable
>> name.

> Well, I think that the backend should be kept clean of MySQL's
> nastiness.

Keep in mind though that there was already talk of migrating most of the
\d functionality to the backend (primarily as a way of decoupling psql
from catalog version changes). If we were to do that, it would make
good sense to make it accessible via SHOW as well. IMHO anyway.

regards, tom lane

> >> I think the idea has some merit; although I wonder whether it
> >> wouldn't be smarter to put the code in the backend so that you
> >> don't need a parser in psql. The SHOW code could fall back to
> >> looking at these possibilities after it fails to find a match to
> >> a GUC variable name.
>
> > Well, I think that the backend should be kept clean of MySQL's
> > nastiness.
>
> Keep in mind though that there was already talk of migrating most of
> the \d functionality to the backend (primarily as a way of
> decoupling psql from catalog version changes). If we were to do
> that, it would make good sense to make it accessible via SHOW as
> well. IMHO anyway.

:-/ Yeah, I've been following that from a distance and I'm not so wild
about that. I really like that the information_schema has been
integrated into the base, but translating the SHOW commands into
SELECTs from information_schema on the backend seems like a bad idea
unless its going to be done abstract enough via some kind of rewrite
engine that allows users to program the database to translate their
verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be
kinda fun.

Getting back to SHOW, what do you want to show or not show? Does the
backend show what's most user friendly? If that's the case, do you
only show tables that a user has SELECT access to? Does SHOW return
tuples like a SELECT? What if a SHOW statement doesn't show what the
user is interested in (view definitions)? How about when those view
definitions get really long and hard to visually see on a terminal
screen? There's no select list available in the SHOW syntax to limit
out excessive bits.

While adding the ability to set MYSQL_MODE as something that a user
could set in their .psqlrc, I thought it'd be the ideal progression to
do a few things:

1) change the \d commands to the appropriate SELECT from the
information_schema. Doing this'll go a long way toward keeping the
structure of the database contained in the database and psql
independent.

2) Set a few tunables that specify the select list for the SELECTs
from the information_schema that way a user can specify what they
see/don't see.

3) SHOW is syntactic user goo that makes MySQL users feel happy and
should be in the user interface. Because SHOW is a user interface
nicety, real admins that over see database users could change
users' .psqlrc files to specify the select list that the user/site
wants, which could possibly be even the entire query.

Hrm, how's this for a more concise argument:

Pushing SHOW/\d into the backend is a bad idea. The backend is a
relational database, not a user interface. The information_schema.*
tables/views are the SQL sanctioned interface that the backend
provides. How a user interfaces with the database/information_schema
is something that should be left up to the user interface program
(psql) and not pushed into the backend. If a user wants to type "SHOW
TABLES LIKE p" instead of "\dt p*", so be it, but that's a user
interface concern, not an SQL concern. The SQL way of getting the
same data as "SHOW TABLES" is via SELECTing from the
information_schema schema. Implementing SQL commands in the backend
to make up for MySQL's inability to be forward thinking and
consequently hack in a syntax to wrap around their system catalogs for
newbie DB users is bad juju. By the same token, doesn't mean
PostgreSQL can't provide the same lovey dovey interface that new users
expect, it should, however mean that the backend should be left alone
to do what it specializes in (being an SQL conformant relational DB)
and that the user interface (psql in this case) should be left alone
to implement what SHOW TABLES really means.

Keep in mind, that the only time that the SHOW commands are used, from
what I've been able to ascertain, is when DBAs are in psql and doing
basic admin work and exploring/creating their corner of the universe.
Anyone who's seriously trying to write a tool to inspect the database
knows PostgreSQL reasonably well and uses SELECT + the system
catalogs. The target audience for a SHOW syntax isn't the power DBAs
or people writing interfaces to examine PostgreSQL, it's the newbie
creating a table for a hack project via the CLI (psql). Allowing
users to customize the meaning of the \d/SHOW commands would make psql
much more powerful than it currently is and would address many of
these usability concerns. I'm now thinking that psql should intercept
all non-standard SQL calls (bits not starting with SELECT, UPDATE,
INSERT, ALTER, etc) and translate them into the appropriate SQL.
Having a generic mechanism for doing this would make psql
significantly cleaner.

Anyway, I'll rest on this topic until I hear whether or not folks
would rather have this done in psql or on the backend, but I'd like to
get this in place somewhere so that I can stop reworking bits from
MySQL to PostgreSQL. If it's determined that the bits should be done
in psql, I'll gladly finish things up, clean things up, add the docs,
move things over to use the information_schema, and if folks would
like, add the appropriate functionality that'll allow folks to
configure the \d commands/SHOW via their .psqlrc.

-sc

--
Sean Chittenden

It's out of date and I did not receive any feedback on it (so I assume
it's not very useful), but the patch submitted in late February would
easily allow the described 'translation' to occur.

Summary:

Schema in backend translates a \<command> <arg> command into an sql
query which is then to be executed. Logic is still in psql, but the
available commands and how data is retrieved from the schema was backend
specific.

I'm using it here due to it's ability to add new psql commands (since
it's just data in a table.)

http://archives.postgresql.org/pgsql-patches/2003-02/msg00216.php

Anyway, might be useful for thoughts. Simply moving the commands into
the backend still leaves us with unsupported new commands in old clients
or new commands in old databases (leaving errors) unless the SHOW ...
syntax itself is handled by the backend.

> :-/ Yeah, I've been following that from a distance and I'm not so wild
> about that. I really like that the information_schema has been
> integrated into the base, but translating the SHOW commands into
> SELECTs from information_schema on the backend seems like a bad idea
> unless its going to be done abstract enough via some kind of rewrite
> engine that allows users to program the database to translate their
> verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be
> kinda fun.
--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Sean Chittenden <sean(at)chittenden(dot)org> writes:
>> Keep in mind though that there was already talk of migrating most of
>> the \d functionality to the backend (primarily as a way of
>> decoupling psql from catalog version changes). If we were to do
>> that, it would make good sense to make it accessible via SHOW as
>> well. IMHO anyway.

> :-/ Yeah, I've been following that from a distance and I'm not so wild
> about that. I really like that the information_schema has been
> integrated into the base, but translating the SHOW commands into
> SELECTs from information_schema on the backend seems like a bad idea
> unless its going to be done abstract enough via some kind of rewrite
> engine that allows users to program the database to translate their
> verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be
> kinda fun.

Well, I don't want to convert \d into selects from information_schema,
primarily because that would constrain us to showing only things that
are known to the SQL spec --- goodbye, Postgres-specific features
(such as user-definable operators).

I was, however, wondering whether the backend internal support for
"SHOW tables" couldn't be simply to translate it to "SELECT * FROM
some_view". Then it'd be possible for people to customize the output by
replacing the view definition.

> Getting back to SHOW, what do you want to show or not show? Does the
> backend show what's most user friendly? If that's the case, do you
> only show tables that a user has SELECT access to? Does SHOW return
> tuples like a SELECT? What if a SHOW statement doesn't show what the
> user is interested in (view definitions)?

I thought you only wanted MySQL-equivalent functionality here ;-).
Don't tell me they have customizable SHOW output ...

> The information_schema.* tables/views are the SQL sanctioned interface
> that the backend provides.

This argument sounds great in the abstract, but it falls down as soon as
you consider the reality that we want to support things that aren't
SQL-sanctioned. Now, we could define some views that are *not* exactly
INFORMATION_SCHEMA, but at that point the claim that it's a stable
standard interface is looking a lot weaker :-(

regards, tom lane

> >> Keep in mind though that there was already talk of migrating most
> >> of the \d functionality to the backend (primarily as a way of
> >> decoupling psql from catalog version changes). If we were to do
> >> that, it would make good sense to make it accessible via SHOW as
> >> well. IMHO anyway.
>
> > :-/ Yeah, I've been following that from a distance and I'm not so
> > wild about that. I really like that the information_schema has
> > been integrated into the base, but translating the SHOW commands
> > into SELECTs from information_schema on the backend seems like a
> > bad idea unless its going to be done abstract enough via some kind
> > of rewrite engine that allows users to program the database to
> > translate their verbiage into SQL (ex: KILL -> DROP, GET ->
> > SELECT), which could be kinda fun.
>
> Well, I don't want to convert \d into selects from
> information_schema, primarily because that would constrain us to
> showing only things that are known to the SQL spec --- goodbye,
> Postgres-specific features (such as user-definable operators).

::nods:: Good point. All the more the reason to put this in the
client. :)

> I was, however, wondering whether the backend internal support for
> "SHOW tables" couldn't be simply to translate it to "SELECT * FROM
> some_view". Then it'd be possible for people to customize the
> output by replacing the view definition.

Well, my attitude is to arm psql with a good set of defaults (already
has some good ones, IMHO), and having it catch \[token1] [token2] and
SHOW [token1] and translate it into the appropriate query. If it
works out in psql, then leave it. If people complain about it not
being available in the backend, then we can move it there in 8.0. :)

> > Getting back to SHOW, what do you want to show or not show? Does
> > the backend show what's most user friendly? If that's the case,
> > do you only show tables that a user has SELECT access to? Does
> > SHOW return tuples like a SELECT? What if a SHOW statement
> > doesn't show what the user is interested in (view definitions)?
>
> I thought you only wanted MySQL-equivalent functionality here ;-).
> Don't tell me they have customizable SHOW output ...

Heh, they don't, but letting psql customize what SHOW means would be a
feature that mysql doesn't have and one that'd be reasonably useful,
IMHO.

> > The information_schema.* tables/views are the SQL sanctioned
> > interface that the backend provides.
>
> This argument sounds great in the abstract, but it falls down as
> soon as you consider the reality that we want to support things that
> aren't SQL-sanctioned. Now, we could define some views that are
> *not* exactly INFORMATION_SCHEMA, but at that point the claim that
> it's a stable standard interface is looking a lot weaker :-(

Does the spec preclude us from adding views/tables to the
information_schema that allow information_schema to be a completely
reflective interface into the structure of the backend? I'm worried
that things are out of control because the existing, already in use
backend's system catalogs aren't user friendly (ex: usename ->
username).

-sc

--
Sean Chittenden

Tom Lane writes:

> I think Sean's idea is not to make it "just as easy as MySQL", it's to
> make it "the *same* as MySQL", for the benefit of those that refuse to
> learn differently. Them as won't adjust to "\dt" in place of "show
> tables" aren't likely to adjust to "select * from tables" either.
> Not even (maybe especially not) if it's arguably a standard.

"Same as MySQL" is impossible. We can pick here and there and add tons of
duplicate interfaces, play catch-up when they change them, but there's
always going to be a next feature that "would be *really* nice if
PostgreSQL could support it and it would surely draw *tons* of users to
PostgreSQL". Freely written MySQL code is basically completely
incompatible with PostgreSQL, so someone who needs to switch will have to
relearn anyway.

--
Peter Eisentraut peter_e(at)gmx(dot)net

On 16 Apr 2003, Hannu Krosing wrote:

> greg(at)turnstep(dot)com kirjutas K, 16.04.2003 kell 16:51:
> > The original poster had some
> > valid points (auto-vacuum and non-intuitive commands) that still need
> > addressing, IMO.
>
> As of 7.3 (or was it 7.2) auto-vacuum is just one line in crontab. In
> many scenarios it can be left running continuously with very little
> effect on performance. In others it must be run nightly, but having it
> kick in at unexpected times may not be what you want at all. So it has
> to be configured for good performance weather it is built-in or run in a
> separate backend process.
>
> And I can't see how "show tables" is more intuitive than "\dt" - I
> expected it to be "list tables" or "tablelist" or "näita tabeleid" .

'show tables' is SQL, and can be run from a script, with the output
parsed. For some reason when I run a query of \dt from PHP I get an
error. :-)

> Once you have found \? it is all there (and you are advised to use \? at
> psql startup).

I love \ commands, but remember, those are psql commands, not postgresql
commands. show tables would be a postgreSQL command the backend parser
would understand. Apples and Oranges.

> That may also be why PostgreSQL is more popular in Japan - if one has to
> remember nonsensical strings, then it is easier to remember short ones

But, how do I write an app to ask such questions easily? psql -E is not
the easiest and most intuitive way to learn how to get the data into a
structure for parsing in a client side app.

On Monday 21 April 2003 21:00, scott.marlowe wrote:
> But, how do I write an app to ask such questions easily? psql -E is not
> the easiest and most intuitive way to learn how to get the data into a
> structure for parsing in a client side app.

How about selecting from pg_class? Nothing could have been more structured..

Shridhar

On Mon, 2003-04-21 at 08:30, scott.marlowe wrote:
> On 16 Apr 2003, Hannu Krosing wrote:
>
> > Once you have found \? it is all there (and you are advised to use \? at
> > psql startup).
>
> I love \ commands, but remember, those are psql commands, not postgresql
> commands. show tables would be a postgreSQL command the backend parser
> would understand. Apples and Oranges.
>
> > That may also be why PostgreSQL is more popular in Japan - if one has to
> > remember nonsensical strings, then it is easier to remember short ones
>
> But, how do I write an app to ask such questions easily? psql -E is not
> the easiest and most intuitive way to learn how to get the data into a
> structure for parsing in a client side app.

He speaks my mind.
--
Steve Wampler <swampler(at)noao(dot)edu>
National Solar Observatory

On Mon, 2003-04-21 at 11:30, scott.marlowe wrote:
> 'show tables' is SQL, and can be run from a script, with the output
> parsed.

But "select * from pg_tables" (or the equivalent query on the
information schemas) is SQL, can be run from a script, and can be parsed
by a client application.

> But, how do I write an app to ask such questions easily? psql -E is not
> the easiest and most intuitive way to learn how to get the data into a
> structure for parsing in a client side app.

You're conflating two distinct issues: (1) providing an interface for
CLI use by the DBA (2) providing an API for programmer use in
applications.

If you think the existing system catalogs are not sufficiently
intuitive, then we should fix that problem properly (for example,
through better documentation), not by copying some ad-hoc syntax from
another RDBMS.

If you think the existing CLI interface (\d etc.) is not sufficiently
intuitive (which has been what a couple people in this thread have
argued), I don't see what that has to do with client side applications
or parsing the output.

Cheers,

Neil

On 21 Apr 2003, Neil Conway wrote:

> On Mon, 2003-04-21 at 11:30, scott.marlowe wrote:
> > 'show tables' is SQL, and can be run from a script, with the output
> > parsed.
>
> But "select * from pg_tables" (or the equivalent query on the
> information schemas) is SQL, can be run from a script, and can be parsed
> by a client application.

But it's not an answer. In psql we have the \ commands, which I love. In
a client side app, select * from pg_tables is just the beginning. You've
got to join that to pg_class and jump through quite a few hoops.

For instance, a \d on a simple table in my database produces this much SQL
in the backend:

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='profile'
**************************

********* QUERY **********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'profile'
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = 'profile' AND c.oid = d.adrelid AND d.adnum = 1
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'profile' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid
AND NOT i.indisunique ORDER BY c2.relname
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'profile' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid
AND i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'profile' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid
AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

Yet there is no equivalent materialized view that puts the data together
for the user. I don't know about you, but show table tablename is a bit
easier to grasp for beginners than the above sequence of SQL statements.

> > But, how do I write an app to ask such questions easily? psql -E is not
> > the easiest and most intuitive way to learn how to get the data into a
> > structure for parsing in a client side app.
>
> You're conflating two distinct issues: (1) providing an interface for
> CLI use by the DBA (2) providing an API for programmer use in
> applications.

Why are those two seperate issues? Why can't the same answer be easily
and readily available to both the DBA and the programmer? Why does one
have to first use psql -E to figure out the queries needed then figure out
which ones to use and not use etc...? I'm not saying the \ commands are
bad, I'm saying they're implemented in the wrong place. Having \ in the
psql monitor is fine. But it should really be hitting views in the
background where possible.

> If you think the existing system catalogs are not sufficiently
> intuitive, then we should fix that problem properly (for example,
> through better documentation), not by copying some ad-hoc syntax from
> another RDBMS.

I don't care what MySQL does. Period. But, I do think Postgresql has a
high learning curve because so much of it is hidden from beginners.

Better documentation won't fix this issue. The real issue here is that
psql has a facility (\ commands) that isn't present in the rest of
postgresql, and really should be. psql shouldn't be the only interface
that allows you to easily see how tables are put together etc...

> If you think the existing CLI interface (\d etc.) is not sufficiently
> intuitive (which has been what a couple people in this thread have
> argued), I don't see what that has to do with client side applications
> or parsing the output.

No, I like the psql interface. It's intuitive to me and has been since
day one. It's the lack of intuition on the application side that bothers
me.

> It's out of date and I did not receive any feedback on it (so I assume
> it's not very useful), but the patch submitted in late February would
> easily allow the described 'translation' to occur.
>
> Summary:
>
> Schema in backend translates a \<command> <arg> command into an sql
> query which is then to be executed. Logic is still in psql, but the
> available commands and how data is retrieved from the schema was backend
> specific.
>
>
> I'm using it here due to it's ability to add new psql commands (since
> it's just data in a table.)
>
> http://archives.postgresql.org/pgsql-patches/2003-02/msg00216.php
>
>
> Anyway, might be useful for thoughts. Simply moving the commands into
> the backend still leaves us with unsupported new commands in old clients
> or new commands in old databases (leaving errors) unless the SHOW ...
> syntax itself is handled by the backend.
>
> > :-/ Yeah, I've been following that from a distance and I'm not so wild
> > about that. I really like that the information_schema has been
> > integrated into the base, but translating the SHOW commands into
> > SELECTs from information_schema on the backend seems like a bad idea
> > unless its going to be done abstract enough via some kind of rewrite
> > engine that allows users to program the database to translate their
> > verbiage into SQL (ex: KILL -> DROP, GET -> SELECT), which could be
> > kinda fun.

Anyone other than Rod (and now myself) had a chance to look this over?
This doesn't really address the lack of a SHOW syntax for new MySQL
users, but it sure does open up the possibilities for making it easier
to probe the backend.

On a related note, any thoughts on the SHOW stuff given that the topic
has come back to life on -hackers? -sc

--
Sean Chittenden

On Mon, 2003-04-21 at 16:26, scott.marlowe wrote:
> Yet there is no equivalent materialized view that puts the data together
> for the user. I don't know about you, but show table tablename is a bit
> easier to grasp for beginners than the above sequence of SQL statements.

Granted -- but I don't think that replacing or augmenting the system
catalogs with a set of SHOW commands is a good idea (which is what you
suggested originally). IMHO enhancing the system catalogs by adding
views that encapsulate more of the \ command functionality into the
backend is a good idea, and one that should be implemented eventually.
AFAIK that's been the consensus for some time...

Cheers,

Neil

Hi,

> On Mon, 2003-04-21 at 16:26, scott.marlowe wrote:
> > Yet there is no equivalent materialized view that puts the data together
> > for the user. I don't know about you, but show table tablename is a bit
> > easier to grasp for beginners than the above sequence of SQL statements.
>
> Granted -- but I don't think that replacing or augmenting the system
> catalogs with a set of SHOW commands is a good idea (which is what you
> suggested originally). IMHO enhancing the system catalogs by adding
> views that encapsulate more of the \ command functionality into the
> backend is a good idea, and one that should be implemented eventually.
> AFAIK that's been the consensus for some time...

I think the SHOW commands won't be neccesary when there are views to use.
There is already a good SQL command to get data/information from the
databaseserver: SELECT. Adding SHOW commands to the backend that essentially
do a SELECT on a system view are a bad thing IMHO. The user can just as easy
do a SELECT on the view himself.

All just IMHO ofcourse :)
Sander.

On Tue, Apr 15, 2003 at 08:12:04AM +0200, Tony Grant wrote:
> Lets forget the "replace MySQL with PostgreSQL" stuff and go looking for
> higher end converts. Our marketing push should be "replace Oracle with
> PostgreSQL and replace Access with MySQL". This puts the emphasis on
> which database can do what...

Except going from MS Access to MySQL would be a step backwards. :P
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Neil Conway writes:

> IMHO enhancing the system catalogs by adding
> views that encapsulate more of the \ command functionality into the
> backend is a good idea, and one that should be implemented eventually.

That would be very nice.

Tilo

Done. Added to 1.15) How can I financially assist PostgreSQL?

<P>Also, if you have a success story about PostgreSQL, please submit
it to our advocacy site at <a href="http://advocacy.postgresql.org">
http://advocacy.postgresql.org</a>.

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

Shridhar Daithankar wrote:
> Hi,
>
> We should probably put out an FAQ saying, if you have success story, please
> make a write-up and send to us at http://advocacy.postgresql.org.
>
> Shridhar
>
> On Tuesday 15 April 2003 18:42, Ericson Smith wrote:
> > As one of the top search engine campaign optimization companies in the
> > space, we here at Did-it.com have been using Postgresql for over a year
> > now. We had serious locking problems with MySQL and even switching to
> > their Innodb handler did not solve all the issues.
> >
> > As the DB administrator, I recommended we switch when it came time to
> > re-write our client platform. That we did, and we have not looked back.
> > We have millions of listings, keywords and we perform live visitor
> > tracking in our database. We capture on the order of about 1 million
> > visitors every day, with each hit making updates, selects and possibly
> > inserts.
> >
> > We could not have done this in mySQL. Basically when I see silly posts
> > over on Slashdot about MySQL being as good a sliced bread, you can check
> > out the debunking posts that I make as "esconsult1".
> >
> > Yes, perhaps Postgresql needs a central org that manages press and so
> > on, but we know that we dont get the press that MySQL does, but quietly
> > in the background Postgresql is handling large important things.
> >
> > - Ericson Smith
> > Web Developer
> > Db Admin
> > http://www.did-it.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

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

Where do we want to go with this? It is interesting that it maps MySQL
SHOW commands on top of our existing SHOW syntax in psql. The patch
doesn't look too big.

Should this be applied? Sean, I know you have a newer patch the the URL
you posted isn't good anymore. This also contains GUC change. Sean,
would you show each one you added and we can discuss if any are
inappropriate.

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

Sean Chittenden wrote:
> > > That's a pretty reasonable thought. I work for a shop that sells
> > > Postgres support, and even we install MySQL for the Q&D ticket
> > > tracking system we recommend because we can't justify the cost to
> > > port it to postgres. If the postgres support were there, we would
> > > surely be using it.
> >
> > > How to fix such a situation, I'm not sure. "MySQL Compatability
> > > Mode," anyone? :-)
> >
> > What issues are creating a compatibility problem for you?
>
> I don't think these should be hacked into the backend/libpq, but I
> think it'd be a huge win to hack in "show *" support into psql for
> MySQL users so they can type:
>
> SHOW (databases|tables|views|functions|triggers|schemas);
>
> I have yet to meet a MySQL user who understands the concept of system
> catalogs even though it's just the 'mysql' database (this irritates me
> enough as is)... gah, f- it: mysql users be damned, I have three
> developers that think that postgresql is too hard to use because they
> can't remember "\d [table name]" and I'm tired of hearing them bitch
> when I push using PostgreSQL instead of MySQL. I have better things
> to do with my time than convert their output to PostgreSQL. Here goes
> nothing...
>
> I've tainted psql and added a MySQL command compatibility layer for
> the family of SHOW commands (psql [-m | --mysql]).
>
>
> The attached patch does a few things:
>
> 1) Implements quite a number of SHOW commands (AGGREGATES, CASTS,
> CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES,
> DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS,
> PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS,
> TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS)
>
> SHOW thing
> SHOW thing LIKE pattern
> SHOW thing FROM pattern
> SHOW HELP ON (topic || ALL);
> etc.
>
> Some of these don't have \ command eqiv's. :( I was tempted to add
> them, but opted not to for now, but it'd certainly be a nice to
> have.
>
> 2) Implements the necessary tab completion for the SHOW commands for
> the tab happy newbies/folks out there. psql is more friendly than
> mysql's CLI now in terms of tab completion for the show commands.
>
> 3) Few trailing whitespace characters were nuked
>
> 4) guc.c is now in sync with the list of available variables used for
> tab completion
>
>
> Few things to note:
>
> 1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in
> this regard and that it should be INDEXES to be plural along with
> the rest of the types, but INDEX is preserved for compatibility.
>
> 2) There are two bugs that I have yet to address
>
> 1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does
> 2) "SHOW [variable_of_choice];" doesn't work, but "SHOW
> [variable_of_choice]\n;" does work... not sure where this
> problem is coming from
>
> 3) I think psql is more usable as a result of this more verbose
> syntax, but it's not the prettiest thing on the planet (wrote a
> small parser outside of the backend or libraries: I don't want to
> get those dirty with MySQL's filth).
>
> 4) In an attempt to wean people over to PostgreSQL's syntax, I
> included translation tips on how to use the psql equiv of the SHOW
> commands. Going from SHOW foo to \d foo is easy, going from \d foo
> to SHOW foo is hard and drives me nuts. This'll help userbase
> retention of newbies/converts. :)
>
> 5) The MySQL mode is just a bounce layer that provides different
> syntax wrapping exec_command() so it should provide little in the
> way of maintenance headaches. Some of the SHOW commands, however,
> don't have \ couterparts, but once they do and that code is
> centralized, this feature should come for zero cost.
>
> 6) As an administrator, I'd be interested in having an environment
> variable that I could set that'd turn on MySQL mode for some of my
> bozo users that way they don't complain if they forget the -m
> switch. Thoughts?
>
>
> I'll try and iron out the last of those two bugs/features, but at this
> point, would like to see this patch get wider testing/feedback.
> Comments, as always, are welcome.
>
> PostgreSQL_usability++
>
> -sc
>
> --
> Sean Chittenden

[ Attachment, skipping... ]

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

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

Hi everyone,

As an additional point of interest, we're still processing all of the
Case Study submissions received from the last call around February, and
should be beginning translations within the next week or two.

There is a lot of very good news contained in the submissions, and they
will definitely assist in bringing into the open just how good a
database system and Community we truly have.

Hope everyone will be as amazed as I was at just where PostgreSQL is
being used already. There are some very clued-in people out there.

Regards and best wishes,

Justin Clift

Bruce Momjian wrote:
> Done. Added to 1.15) How can I financially assist PostgreSQL?
>
> <P>Also, if you have a success story about PostgreSQL, please submit
> it to our advocacy site at <a href="http://advocacy.postgresql.org">
> http://advocacy.postgresql.org</a>.
>
>
> ---------------------------------------------------------------------------
>
> Shridhar Daithankar wrote:
>
>>Hi,
>>
>>We should probably put out an FAQ saying, if you have success story, please
>>make a write-up and send to us at http://advocacy.postgresql.org.
>>
>> Shridhar
>>
>>On Tuesday 15 April 2003 18:42, Ericson Smith wrote:
>>
>>>As one of the top search engine campaign optimization companies in the
>>>space, we here at Did-it.com have been using Postgresql for over a year
>>>now. We had serious locking problems with MySQL and even switching to
>>>their Innodb handler did not solve all the issues.
>>>
>>>As the DB administrator, I recommended we switch when it came time to
>>>re-write our client platform. That we did, and we have not looked back.
>>>We have millions of listings, keywords and we perform live visitor
>>>tracking in our database. We capture on the order of about 1 million
>>>visitors every day, with each hit making updates, selects and possibly
>>>inserts.
>>>
>>>We could not have done this in mySQL. Basically when I see silly posts
>>>over on Slashdot about MySQL being as good a sliced bread, you can check
>>>out the debunking posts that I make as "esconsult1".
>>>
>>>Yes, perhaps Postgresql needs a central org that manages press and so
>>>on, but we know that we dont get the press that MySQL does, but quietly
>>>in the background Postgresql is handling large important things.
>>>
>>>- Ericson Smith
>>>Web Developer
>>>Db Admin
>>>http://www.did-it.com
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>
>
>

Bruce Momjian writes:

> Where do we want to go with this? It is interesting that it maps MySQL
> SHOW commands on top of our existing SHOW syntax in psql. The patch
> doesn't look too big.

The response to "Are we losing momentum?" isn't to add redundant syntax
for nonstandard features that we have no control over.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Bruce Momjian writes:
>> Where do we want to go with this? It is interesting that it maps MySQL
>> SHOW commands on top of our existing SHOW syntax in psql. The patch
>> doesn't look too big.

> The response to "Are we losing momentum?" isn't to add redundant syntax
> for nonstandard features that we have no control over.

I'm of two minds about it myself. I don't like trying to play follow-the-
leader with a moving target. But if you think of it as trying to win
over converts from MySQL, it seems a lot more palatable.

It would also be interesting to combine this with Rod's idea of driving
describe-type queries by table instead of hardwired code. Imagine that
the backend's "show foo" command first looks for "foo" as a GUC
variable, as it does now, but upon failing to find one it looks in a
system table for a query associated with the name "foo". If it finds
such a query, it runs it and sends back the result. Now, not only can
we emulate "show tables", but people can easily add application-specific
"show whatever" commands, which seems tremendously cool.

There are some safety and protection issues to be solved here (probably
only superusers should be allowed to modify the query table, and we
should restrict the form of the query to be a single SELECT command)
but I can't think of any showstoppers. Now that we've abandoned backend
autocommit there are no technical reasons that SHOW shouldn't be allowed
to run a SELECT query.

I did not care for the original patch (which IIRC made psql recognize
"show" commands, rather than doing it in the backend) but something like
the above seems reasonably clean.

regards, tom lane

> >> Where do we want to go with this? It is interesting that it maps
> >> MySQL SHOW commands on top of our existing SHOW syntax in psql.
> >> The patch doesn't look too big.
> >
> > The response to "Are we losing momentum?" isn't to add redundant
> > syntax for nonstandard features that we have no control over.
>
> I'm of two minds about it myself. I don't like trying to play
> follow-the- leader with a moving target. But if you think of it as
> trying to win over converts from MySQL, it seems a lot more
> palatable.

The _only_ reason I added this was to aid in the conversion of MySQL
users who don't know how to navigate their way through psql and
PostgreSQL. If you read through the patch, the extended SHOW commands
are only enabled if you turn them on via the --mysql option or by
setting an option (don't recall at the moment) that way you can turn
these on by default in a person's .psqlrc file. With Win32 coming
down the pipe (right??? *cough cough* :-P), converting existing users
is going to be important for PostgreSQL's long term success if it
seeks to gain market share and momentum.

> It would also be interesting to combine this with Rod's idea of
> driving describe-type queries by table instead of hardwired code.
> Imagine that the backend's "show foo" command first looks for "foo"
> as a GUC variable, as it does now, but upon failing to find one it
> looks in a system table for a query associated with the name "foo".
> If it finds such a query, it runs it and sends back the result.
> Now, not only can we emulate "show tables", but people can easily
> add application-specific "show whatever" commands, which seems
> tremendously cool.

I really like the ability to program in queries or syntaxes into the
backend, but as it stands, SHOW foo would have to be pretty smart to
handle the LIKE clauses and other bits. And how would tab completion
be handled in psql? I can't remember if I implemented stuff like,
SHOW TABLES IN SCHEMA foo, but 'SHOW TABLES LIKE a%' was quite a hit
to the people I have using the patch and I don't see how that'd be
applicable with what has been proposed so far. The extra verbosity
and tab completion-ability is important for the newbie, it's an
interactive environment that lets them learn and explore in a
proactive manner. Having IN SCHEMA vs LIKE handled on the backend
would be a rather complex generic model, but in an ideal world, one
that I'd prefer if done right.

> There are some safety and protection issues to be solved here
> (probably only superusers should be allowed to modify the query
> table, and we should restrict the form of the query to be a single
> SELECT command) but I can't think of any showstoppers. Now that
> we've abandoned backend autocommit there are no technical reasons
> that SHOW shouldn't be allowed to run a SELECT query.
>
> I did not care for the original patch (which IIRC made psql
> recognize "show" commands, rather than doing it in the backend) but
> something like the above seems reasonably clean.

Well, unless something is done very cleanly and abstractly, and can be
extended to handle IN SCHEMA, LIKE, etc., I'm of the opinion that this
kinda stuff belongs on the client side of things and _not_ in the
backend. Putting MySQL's brokenness in the backend seems, well, to
perpetuate the brokenness and acknowledge that their brokenness isn't
really that broken: not a message I'm fond of. Putting MySQL's
brokenness in psql and hiding it behind the --mysql CLI option,
however, is tolerably broken, IMHO and of the same use as ora2pg or
mysql2pg.

Bruce wrote:
> Where do we want to go with this? It is interesting that it maps
> MySQL SHOW commands on top of our existing SHOW syntax in psql. The
> patch doesn't look too big.
>
> Should this be applied? Sean, I know you have a newer patch the the
> URL you posted isn't good anymore. This also contains GUC change.
> Sean, would you show each one you added and we can discuss if any
> are inappropriate.

The GUC change was just flushing out various GUC options that weren't
available as tab completion options... the usefulness of those GUCs
could be debated, but at least there's a complete list in the CLI
now... having this pulled from the server would be wise, IMHO, but I
didn't spend the time to add that at the time. If there's interest, I
can do that.

I have the patch someplace, don't worry about that. The patch isn't
100% correct as it stands: there is a problem in recognizing the end
of a SHOW command so that the query buffer was considered non-empty
(wasn't scanning properly for ;'s to emulate the end of a query:
something that's not necessary with the normal \ syntax). Other than
that, there are no bugs that I'm aware of and the work around is to
issue a \r: not an earth shattering problem given psql has a history.
Before I finished, I wanted to get some review/thoughts on the patch
before I completed the work and sent it off only to have it rejected.

I know Tom doesn't like the idea because it's not "clean," but I can't
help but feel that adding similar functionality to the backend is some
how contaminating PostgreSQL and acknowledges MySQL's done something
creditable, which is too big of an admission, especially since their
product is pretty fundamentally flawed in terms of spec compliance.
The last thing we want is to have their brokenness turn into spec and
for that to become the norm. So, as life would have it, I'm an
advocate of adding the SHOW syntax to psql and an opponent of having it
in the backend, the core developers would rather have the SHOW syntax
in the backend because it's not clean to have it in the front end.
*shrug*

psql has to have a quasi-parser in the front end as is to handle tab
completion, so I don't really buy the argument that it's bad to have a
parser in the front end (sorry Tom). In fact, I actually think that
the parser in psql needs to be _beefed up_ and made _smarter_ that way
psql's more user friendly, even if that means increasing the
complexity of psql. At the moment, there are short falls with psql's
ability to perform tab completion of column names on aliased tables,
etc. Some of it's non-trivial and won't ever see the light of day in
psql, but there is room for improvement. Anyway, usability is a
problem for clients, not for servers. MySQL usability belongs in the
client, not the backend.... and hidden behind a CLI flag at that
'cause I don't available in my personal development environment.

And before someone says, "that's fine and dandy, who's going to do the
work on psql," I'll gladly work on psql and even go so far as to clean
up the code now that someone's around to apply the patches. :)

*listens and waits before updating the patch and fixing the above
bugs* -sc

--
Sean Chittenden

> It would also be interesting to combine this with Rod's idea of driving
> describe-type queries by table instead of hardwired code. Imagine that
> the backend's "show foo" command first looks for "foo" as a GUC
> variable, as it does now, but upon failing to find one it looks in a
> system table for a query associated with the name "foo". If it finds
> such a query, it runs it and sends back the result. Now, not only can
> we emulate "show tables", but people can easily add application-specific
> "show whatever" commands, which seems tremendously cool.

Easy enough to accomplish for the most part. I suppose the most
difficult part is whether we support arbitrary syntax?

SHOW INDEXES ON TABLE <bleah>;
SHOW TABLES;
SHOW DATABASES;
SHOW COLUMNS FROM <table>; <-- Long form of DESCRIBE <table>

I believe all of the above is valid MySQL syntax, so we would need to be
able to work with a list of colId, rather than a single one.

This does not help with tab completion or help for the above items.
Though one could certainly argue help on available commands should come
from the backend, and psql could be taught how to read the table to
determine how to deal with tab completion.

One significant downside is that describe commands would require an
initdb when updated.

Oh, if it's the backend doing the work, the queries should probably be
functions, not free-form queries. Simply makes it easier to inject
variables into the right place. In the initial patch, psql is using a
prepared statement for this work with the side benefit that the plan is
cached. An SQL function would accomplish the same thing.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

[ moving this thread to a more appropriate place ]

Sean Chittenden <sean(at)chittenden(dot)org> writes:
>> It would also be interesting to combine this with Rod's idea of
>> driving describe-type queries by table instead of hardwired code.
>> Imagine that the backend's "show foo" command first looks for "foo"
>> as a GUC variable, as it does now, but upon failing to find one it
>> looks in a system table for a query associated with the name "foo".
>> If it finds such a query, it runs it and sends back the result.
>> Now, not only can we emulate "show tables", but people can easily
>> add application-specific "show whatever" commands, which seems
>> tremendously cool.

> I really like the ability to program in queries or syntaxes into the
> backend, but as it stands, SHOW foo would have to be pretty smart to
> handle the LIKE clauses and other bits.

It's certainly doable. I thought more about how to handle parameters
and such, and came up with this sketch:

1. We generalize the SHOW syntax to accept 1 or more identifiers (might
as well allow strings too). The existing special cases like SHOW TIME
ZONE would be taken out of the grammar and checked for at runtime.

2. The "key" field of the show_queries table is an array of one or more
strings that can be either keywords or parameter placeholders ($n).
There must be at least one keyword. Then SHOW matches a particular
table entry if there are the right number of words and all the keyword
strings match the corresponding words. The other words become the
parameter values.

3. The "query" field of the table is a SELECT possibly containing
parameter references $n. This can be handled the same way as a
preparable statement (we already have mechanisms for resolving the types
of the parameters).

While I haven't studied the MySQL manual to see what-all they allow,
this certainly seems sufficient to support "SHOW TABLE foo" and similar
variants. And the possibility of user-added extensions to the table
seems really cool.

> And how would tab completion be handled in psql?

You look at the table to see what can come after SHOW. We already have
database-driven completion, so this doesn't seem out of reach.

One thing that is doable with psql's current hard-wired approach, but
doesn't seem easy to do with this solution, is automatic localization
of strings such as column headings. Rod had looked at that a little
in his trial patch to convert psql's \d stuff to table-driven form,
but AFAIR he didn't have a satisfactory answer.

regards, tom lane

Tom Lane writes:

> Now, not only can we emulate "show tables", but people can easily add
> application-specific "show whatever" commands, which seems tremendously
> cool.

We already have that. They're called views.

--
Peter Eisentraut peter_e(at)gmx(dot)net

> > Now, not only can we emulate "show tables", but people can easily
> > add application-specific "show whatever" commands, which seems
> > tremendously cool.
>
> We already have that. They're called views.

Um, I'm interested in aiding in the conversion of users from MySQL to
PostgreSQL (how ever it happens, I don't really care). Tom and Rod
are interested in an extensible/programmable SHOW syntax (very cool
and much more interesting than the hack I put together). Views solve
none of the above problems. -sc

--
Sean Chittenden

> One thing that is doable with psql's current hard-wired approach, but
> doesn't seem easy to do with this solution, is automatic localization
> of strings such as column headings. Rod had looked at that a little
> in his trial patch to convert psql's \d stuff to table-driven form,
> but AFAIR he didn't have a satisfactory answer.

I've yet to come up with anything better. Best answer I've come up with
is to make the column headings a separate query result set and let the
back-end do the translations.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Sean Chittenden writes:

> Um, I'm interested in aiding in the conversion of users from MySQL to
> PostgreSQL (how ever it happens, I don't really care).

Your approach to that reminds me of those

alias dir='ls'
alias md='mkdir'

things that Linux distributors once stuck (or still stick?) in the default
profile files, presumably to help conversion from DOS. It's pretty
pointless, because Linux is still very different from DOS, and once you
want to do something besides showing or changing directories, you will
need documentation and training. That is what the MySQL conversion
process needs as well, otherwise you're not converting, you're emulating,
and that is not a game you can win.

--
Peter Eisentraut peter_e(at)gmx(dot)net

> > Um, I'm interested in aiding in the conversion of users from MySQL
> > to PostgreSQL (how ever it happens, I don't really care).
>
> Your approach to that reminds me of those
>
> alias dir='ls'
> alias md='mkdir'
>
> things that Linux distributors once stuck (or still stick?) in the
> default profile files, presumably to help conversion from DOS. It's
> pretty pointless, because Linux is still very different from DOS,
> and once you want to do something besides showing or changing
> directories, you will need documentation and training.

Well, interestingly enough, those commands work for getting people in
the door and to the point that they're able to learn more. The first
step to any kind of adult education or reeducation is to have concepts
that the people are familiar with (in this case MySQL) be translated
into the concepts of the area that they're trying to learn. If you'd
have read the original patch that I'd posted, you'd see that I'd done
that by adding a TIP section to the top of the response.

*SNIP*
SHOW COLUMNS FROM [tblname];

TIP: In psql, "SHOW COLUMNS FROM [tblname]" is natively written as \dt [tblname]

[normal output from \dt tblname]
*END SNIP*

The point of my patch was to aid the conversion, not to gimp along
b0rk3d habits from MySQL.

> That is what the MySQL conversion process needs as well, otherwise
> you're not converting, you're emulating, and that is not a game you
> can win.

Emulation within reason. dir->ls and md->mkdir worked for a handful
of people that I've transitioned into the UNIX world from Win32 land,
in fact, I have one friend from school who's been so successful that
he's converted from using Win32 on his desktop to using Linux, worked
with me on a job where we were hacking mod_perl on a site pushing in
excess of 80Mbps to 25M people a day, but still types dir to this day.
Not bad for an aero student who graduated with a 4.0 in his major,
exceedingly bright, adaptive, learns fast, etc. My point is that
regardless of how bright the person or what the right invocation, aids
like these help get people in the door and if they like what they see
once they're through the door, they'll stay. Once people try and use
PostgreSQL, they stay. When people try MySQL, they're left wanting or
needing more and are bound by the limits of the software... that's not
really the case with PostgreSQL.

To get people to try, you play the association or emulation game and
it works. Ask anyone in adult education and they will say the same.
The Internet was an "information super highway" because that was
something that people could grasp, regardless of how flawed it really
is. Bandwidth is thought of as pipes in various sizes diameters, a
much better analogy. To geeks, broadcast is explained as the same as
radio and unicast as satellite TV. To communication majors, TCP is
descried as a letter that's been chopped up into a thousand numbered
pieces and sent to the other side of the US via the postal mail.
Leche is milk to English speakers learning Spanish. One way or
another, you have to play the game of working within the understanding
of the target audience, in this case, MySQL users who use a SHOW
TABLES type syntax.

-sc

--
Sean Chittenden

Wow, this is a good argument! I must admit I made 'ps -ef' work on BSD
because I was just so used to it on mainframe Unix. There is that
'fingers type without thinking' thing, and I think that is what he is
talking about.

I wonder if we should just support SHOW TABLES or the most common ones.

Maybe emulation is the wrong approach --- maybe we just need 'finger
thinking' shortcuts.

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

Sean Chittenden wrote:
> > > Um, I'm interested in aiding in the conversion of users from MySQL
> > > to PostgreSQL (how ever it happens, I don't really care).
> >
> > Your approach to that reminds me of those
> >
> > alias dir='ls'
> > alias md='mkdir'
> >
> > things that Linux distributors once stuck (or still stick?) in the
> > default profile files, presumably to help conversion from DOS. It's
> > pretty pointless, because Linux is still very different from DOS,
> > and once you want to do something besides showing or changing
> > directories, you will need documentation and training.
>
> Well, interestingly enough, those commands work for getting people in
> the door and to the point that they're able to learn more. The first
> step to any kind of adult education or reeducation is to have concepts
> that the people are familiar with (in this case MySQL) be translated
> into the concepts of the area that they're trying to learn. If you'd
> have read the original patch that I'd posted, you'd see that I'd done
> that by adding a TIP section to the top of the response.
>
> *SNIP*
> SHOW COLUMNS FROM [tblname];
>
> TIP: In psql, "SHOW COLUMNS FROM [tblname]" is natively written as \dt [tblname]
>
> [normal output from \dt tblname]
> *END SNIP*
>
> The point of my patch was to aid the conversion, not to gimp along
> b0rk3d habits from MySQL.
>
> > That is what the MySQL conversion process needs as well, otherwise
> > you're not converting, you're emulating, and that is not a game you
> > can win.
>
> Emulation within reason. dir->ls and md->mkdir worked for a handful
> of people that I've transitioned into the UNIX world from Win32 land,
> in fact, I have one friend from school who's been so successful that
> he's converted from using Win32 on his desktop to using Linux, worked
> with me on a job where we were hacking mod_perl on a site pushing in
> excess of 80Mbps to 25M people a day, but still types dir to this day.
> Not bad for an aero student who graduated with a 4.0 in his major,
> exceedingly bright, adaptive, learns fast, etc. My point is that
> regardless of how bright the person or what the right invocation, aids
> like these help get people in the door and if they like what they see
> once they're through the door, they'll stay. Once people try and use
> PostgreSQL, they stay. When people try MySQL, they're left wanting or
> needing more and are bound by the limits of the software... that's not
> really the case with PostgreSQL.
>
> To get people to try, you play the association or emulation game and
> it works. Ask anyone in adult education and they will say the same.
> The Internet was an "information super highway" because that was
> something that people could grasp, regardless of how flawed it really
> is. Bandwidth is thought of as pipes in various sizes diameters, a
> much better analogy. To geeks, broadcast is explained as the same as
> radio and unicast as satellite TV. To communication majors, TCP is
> descried as a letter that's been chopped up into a thousand numbered
> pieces and sent to the other side of the US via the postal mail.
> Leche is milk to English speakers learning Spanish. One way or
> another, you have to play the game of working within the understanding
> of the target audience, in this case, MySQL users who use a SHOW
> TABLES type syntax.
>
> -sc
>
> --
> Sean Chittenden
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

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

I assume we agreed against adding a MySQL mode --- just verifying.

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

Sean Chittenden wrote:
> > > That's a pretty reasonable thought. I work for a shop that sells
> > > Postgres support, and even we install MySQL for the Q&D ticket
> > > tracking system we recommend because we can't justify the cost to
> > > port it to postgres. If the postgres support were there, we would
> > > surely be using it.
> >
> > > How to fix such a situation, I'm not sure. "MySQL Compatability
> > > Mode," anyone? :-)
> >
> > What issues are creating a compatibility problem for you?
>
> I don't think these should be hacked into the backend/libpq, but I
> think it'd be a huge win to hack in "show *" support into psql for
> MySQL users so they can type:
>
> SHOW (databases|tables|views|functions|triggers|schemas);
>
> I have yet to meet a MySQL user who understands the concept of system
> catalogs even though it's just the 'mysql' database (this irritates me
> enough as is)... gah, f- it: mysql users be damned, I have three
> developers that think that postgresql is too hard to use because they
> can't remember "\d [table name]" and I'm tired of hearing them bitch
> when I push using PostgreSQL instead of MySQL. I have better things
> to do with my time than convert their output to PostgreSQL. Here goes
> nothing...
>
> I've tainted psql and added a MySQL command compatibility layer for
> the family of SHOW commands (psql [-m | --mysql]).
>
>
> The attached patch does a few things:
>
> 1) Implements quite a number of SHOW commands (AGGREGATES, CASTS,
> CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES,
> DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS,
> PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS,
> TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS)
>
> SHOW thing
> SHOW thing LIKE pattern
> SHOW thing FROM pattern
> SHOW HELP ON (topic || ALL);
> etc.
>
> Some of these don't have \ command eqiv's. :( I was tempted to add
> them, but opted not to for now, but it'd certainly be a nice to
> have.
>
> 2) Implements the necessary tab completion for the SHOW commands for
> the tab happy newbies/folks out there. psql is more friendly than
> mysql's CLI now in terms of tab completion for the show commands.
>
> 3) Few trailing whitespace characters were nuked
>
> 4) guc.c is now in sync with the list of available variables used for
> tab completion
>
>
> Few things to note:
>
> 1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in
> this regard and that it should be INDEXES to be plural along with
> the rest of the types, but INDEX is preserved for compatibility.
>
> 2) There are two bugs that I have yet to address
>
> 1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does
> 2) "SHOW [variable_of_choice];" doesn't work, but "SHOW
> [variable_of_choice]\n;" does work... not sure where this
> problem is coming from
>
> 3) I think psql is more usable as a result of this more verbose
> syntax, but it's not the prettiest thing on the planet (wrote a
> small parser outside of the backend or libraries: I don't want to
> get those dirty with MySQL's filth).
>
> 4) In an attempt to wean people over to PostgreSQL's syntax, I
> included translation tips on how to use the psql equiv of the SHOW
> commands. Going from SHOW foo to \d foo is easy, going from \d foo
> to SHOW foo is hard and drives me nuts. This'll help userbase
> retention of newbies/converts. :)
>
> 5) The MySQL mode is just a bounce layer that provides different
> syntax wrapping exec_command() so it should provide little in the
> way of maintenance headaches. Some of the SHOW commands, however,
> don't have \ couterparts, but once they do and that code is
> centralized, this feature should come for zero cost.
>
> 6) As an administrator, I'd be interested in having an environment
> variable that I could set that'd turn on MySQL mode for some of my
> bozo users that way they don't complain if they forget the -m
> switch. Thoughts?
>
>
> I'll try and iron out the last of those two bugs/features, but at this
> point, would like to see this patch get wider testing/feedback.
> Comments, as always, are welcome.
>
> PostgreSQL_usability++
>
> -sc
>
> --
> Sean Chittenden

[ Attachment, skipping... ]

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

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