Are we losing momentum?

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:

Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.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@sysexperts.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@sss.pgh.pa.us>
wrote:

Bruce Momjian <pgman@candle.pha.pa.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@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Gretings!

[2003-04-14 19:54] Tom Lane said:
| Bruce Momjian <pgman@candle.pha.pa.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@cynic.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@sss.pgh.pa.us>
wrote:

Bruce Momjian <pgman@candle.pha.pa.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@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

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

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@cbbrowne.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@mascari.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@sss.pgh.pa.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@cbbrowne.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

-----Original Message-----
From: Jeff Hoffmann [mailto:jeff@propertykey.com]
Sent: Monday, April 14, 2003 8:54 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Are we losing momentum?

Mike Mascari wrote:

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

This is a very, very good idea. In fact IBM has been doing it for
years. For that matter, so has OpenVMS. What's that -- 30 year old
technology?

I have always thought that a native file system should be a hierarchy
like Adabas(IBM Mainframe), DBMS(OpenVMS) or Raima(PC's & UNIX) for a
model. It is a very natural fit. The OS contains disk devices which
contain directories, subdirectories, and files. Set ownership model
seems to fit perfectly.

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.

SQL*Server is a very good database. The optimizer is outstanding for
complex queries.

There are clearly places where PostgreSQL does have a distinct
advantage. Price a 1000 user system for SQL*Server and PostgreSQL and
you will see that we can hire a couple of DBA's just for the price
difference. Since you can purchase PostgreSQL support, that is no
longer a significant advantage for MS.

And about MySQL:
It's also commercial. You are not supposed to use it except for a
single machine for personal use unless you are a non-profit organization
or unless absolutely everything you do is GPL[1]I realize that people cheat on this all the time. In theory, they could all go to jail for it. It is certainly not a risk I would be willing to take. I have also bumped into people who had no idea that commercial use requires a commercial license for MySQL. There are probably lots of people in that boat too.. Hence, you have to
license it to deploy applications. In order to have transactions, you
have to use another commercial product that they bolt into MySQL --
Sleepycat software's database. Now you have two license systems to
worry about.

Compared to PostgreSQL, both of these tools cost an arm and a leg.
SQL*Server is closed. You have to rely on MS to fix any problems that
crop up. MySQL has a very restrictive license [for those who might
happen to bother to read such things] for both modifications to the code
and also redistribution of applications.

[1]: I realize that people cheat on this all the time. In theory, they could all go to jail for it. It is certainly not a risk I would be willing to take. I have also bumped into people who had no idea that commercial use requires a commercial license for MySQL. There are probably lots of people in that boat too.
could all go to jail for it. It is certainly not a risk I would be
willing to take. I have also bumped into people who had no idea that
commercial use requires a commercial license for MySQL. There are
probably lots of people in that boat too.

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&amp;dockey=xml/8/1/816e9b7e50ae92331bb5c47a791a589f@activejobs0&amp;c=1
http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&amp;dockey=xml/c/8/c8dc5841d18329c6c50b55f67a7ff038@activejobs0&amp;c=1
http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&amp;dockey=xml/1/6/168f30dc84b8f195d1fc35feb6a2f67a@activejobs0&amp;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.

And about MySQL:
It's also commercial. You are not supposed to use it except for a
single machine for personal use unless you are a non-profit organization
or unless absolutely everything you do is GPL[1]. Hence, you have to
license it to deploy applications. In order to have transactions, you
have to use another commercial product that they bolt into MySQL --
Sleepycat software's database. Now you have two license systems to
worry about.

Just a correction - you need to use the InnoDB database engine, which is
free and GPL and bundled with MySQL.

Chris

Dann Corbit wrote:

There are clearly places where PostgreSQL does have a distinct
advantage. Price a 1000 user system for SQL*Server and PostgreSQL and
you will see that we can hire a couple of DBA's just for the price
difference. Since you can purchase PostgreSQL support, that is no
longer a significant advantage for MS.

Start looking at "Enterprise" licenses for any of the big guys and the
pricing does get pretty scary.

And about MySQL: It's also commercial. You are not supposed to use it
except for a single machine for personal use unless you are a
non-profit organization or unless absolutely everything you do is
GPL[1].

On the one hand, if they are calling it "Open Source", then this is NOT
a fair statement.

On the other hand, if you look at their web site, they certainly do tip
their hat to the FUD/Paranoia about the "infectiveness" of the GPL.

They don't expressly say:
"Yes, you should be paranoid about the GPL because it infects anything
it ever touches with some frightening license virus worse than SARS."

Instead, they loudly use the line "... for users who prefer not to be
restricted by the terms of the GPL", of course, neither confirming or
denying any particular paranoia about what the impact of those terms may
or may not be.

Hence, you have to license it to deploy applications. In order to
have transactions, you have to use another commercial product that
they bolt into MySQL -- Sleepycat software's database. Now you have
two license systems to worry about.

Incorrect. You have to use another commercial product that they bolt
onto MySQL -- InnoDB, from the Norwegian company, Innobase.
<http://www.innodb.com/&gt;

Sleepycat DB was used to prototype the notion of having transactions,
but since that introduces Yet Another Continent to the set of licensing
complications, and probably wasn't sufficiently 'in their interests,'
it's not the Preferred Transactional Engine...
--
(reverse (concatenate 'string "moc.enworbbc@" "enworbbc"))
http://cbbrowne.com/info/oses.html
Rules of the Evil Overlord #217. "If I'm wearing the key to the hero's
shackles around my neck and his former girlfriend now volunteers to
become my mistress and we are all alone in my bedchamber on my bed and
she offers me a goblet of wine, I will politely decline the offer."
<http://www.eviloverlord.com/&gt;

On Monday 14 April 2003 09:30 pm, Dann Corbit wrote:

And about MySQL:
It's also commercial. You are not supposed to use it except for a
single machine for personal use unless you are a non-profit organization
or unless absolutely everything you do is GPL[1]. Hence, you have to
license it to deploy applications. In order to have transactions, you
have to use another commercial product that they bolt into MySQL --
Sleepycat software's database. Now you have two license systems to
worry about.

Compared to PostgreSQL, both of these tools cost an arm and a leg.
SQL*Server is closed. You have to rely on MS to fix any problems that
crop up. MySQL has a very restrictive license [for those who might
happen to bother to read such things] for both modifications to the code
and also redistribution of applications.

[1] I realize that people cheat on this all the time. In theory, they
could all go to jail for it. It is certainly not a risk I would be
willing to take. I have also bumped into people who had no idea that
commercial use requires a commercial license for MySQL. There are
probably lots of people in that boat too.

Can you point me to the relevent portions of the license?

I tried to go through the license, but it basically seemed free (as in GPL) to
me. My impression is that you can't statically link Sleepycat's Berkeley DB
with software unless it is released under a free license (reasonable, kind of
like the GPL, if you consider that reasonable). They sell a commercial
version, which allows you to statically link it. I sort of get the same idea
from MySQL: as long as you aren't trying to distribute it, you're fine (even
in-house changes).

Also, aren't mysql and sleepycat in the standard distribution of Debian? I
would think the debian developers would be interested to know if the likes of
sleepycat and mysql don't abide by the DFSG. That's actually one of the
things I've always liked about Debian: read one set of guidelines, and trust
the developers to ensure compliance across the entire OS (as long as you stay
our of non-free). At least, so I thought...

Regards,
Jeff Davis

-----Original Message-----
From: Jeff Davis [mailto:jdavis-pgsql@empires.org]
Sent: Monday, April 14, 2003 10:07 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Are we losing momentum?

On Monday 14 April 2003 09:30 pm, Dann Corbit wrote:

And about MySQL:
It's also commercial. You are not supposed to use it except for a
single machine for personal use unless you are a non-profit
organization or unless absolutely everything you do is

GPL[1]. Hence,

you have to license it to deploy applications. In order to have
transactions, you have to use another commercial product that they
bolt into MySQL -- Sleepycat software's database. Now you have two
license systems to worry about.

Compared to PostgreSQL, both of these tools cost an arm and a leg.
SQL*Server is closed. You have to rely on MS to fix any

problems that

crop up. MySQL has a very restrictive license [for those who might
happen to bother to read such things] for both modifications to the
code and also redistribution of applications.

[1] I realize that people cheat on this all the time. In

theory, they

could all go to jail for it. It is certainly not a risk I would be
willing to take. I have also bumped into people who had no

idea that

commercial use requires a commercial license for MySQL. There are
probably lots of people in that boat too.

Can you point me to the relevent portions of the license?

I tried to go through the license, but it basically seemed
free (as in GPL) to
me. My impression is that you can't statically link
Sleepycat's Berkeley DB
with software unless it is released under a free license
(reasonable, kind of
like the GPL, if you consider that reasonable). They sell a
commercial
version, which allows you to statically link it.

As another poster pointed out, Sleepycat is no longer the transaction
engine of choice for MySQL.
Here is the sleepycat license:
http://www.sleepycat.com/docs/sleepycat/license.html

Here is a fragment:
* 3. Redistributions in any form must be accompanied by information on
* how to obtain complete source code for the DB software and any
* accompanying software that uses the DB software. The source code
* must either be included in the distribution or be available for no
* more than the cost of distribution plus a nominal fee, and must be
* freely redistributable under reasonable conditions. For an
* executable file, complete source code means the source code for
all
* modules it contains. It does not include source code for modules
or
* files that typically accompany the major components of the
operating
* system on which the executable file runs.

We also find this on their web site:
http://www.sleepycat.com/company/legal.shtml
Which says (among other things):

"Sleepycat Software Legal Notices

Copyright (c) 1990-2003 Sleepycat Software, Inc., 118 Tower Rd.,
Lincoln, MA 01773, U.S.A. All Rights Reserved.

This product and publication is protected by copyright and distributed
under licenses restricting its use, copying and distribution. Permission
to use this publication or portions of this publication is granted by
Sleepycat Software provided that the above copyright notice appears in
all copies and that use of such publications is for non-commercial use
only and no modifications of the publication is made."

Notice the phrase 'non-commercial use only'

I sort of
get the same idea
from MySQL: as long as you aren't trying to distribute it,
you're fine (even
in-house changes).

Also, aren't mysql and sleepycat in the standard distribution
of Debian? I
would think the debian developers would be interested to know
if the likes of
sleepycat and mysql don't abide by the DFSG. That's actually
one of the
things I've always liked about Debian: read one set of
guidelines, and trust
the developers to ensure compliance across the entire OS (as
long as you stay
our of non-free). At least, so I thought...

For MySQL, form here
http://www.mysql.com/doc/en/Using_the_MySQL_software_under_a_commercial_
license.html we have this:

"1.4.3.1 Using the MySQL Software Under a Commercial License
The GPL license is contagious in the sense that when a program is linked
to a GPL program all the source code for all the parts of the resulting
product must also be released under the GPL. If you do not follow this
GPL requirement, you break the license terms and forfeit your right to
use the GPL program altogether. You also risk damages.

You need a commercial license:

When you link a program with any GPL code from the MySQL software and
don't want the resulting product to be licensed under GPL, perhaps
because you want to build a commercial product or keep the added non-GPL
code closed source for other reasons. When purchasing commercial
licenses, you are not using the MySQL software under GPL even though
it's the same code.
When you distribute a non-GPL application that only works with the MySQL
software and ship it with the MySQL software. This type of solution is
considered to be linking even if it's done over a network.
When you distribute copies of the MySQL software without providing the
source code as required under the GPL license.
When you want to support the further development of the MySQL database
even if you don't formally need a commercial license. Purchasing support
directly from MySQL AB is another good way of contributing to the
development of the MySQL software, with immediate advantages for you.
See section 1.4.1 Support Offered by MySQL AB.
If you require a license, you will need one for each installation of the
MySQL software. This covers any number of CPUs on a machine, and there
is no artificial limit on the number of clients that connect to the
server in any way.

For commercial licenses, please visit our website at
http://www.mysql.com/products/licensing.html. For support contracts, see
http://www.mysql.com/support/. If you have special needs or you have
restricted access to the Internet, please contact our sales staff via
e-mail at sales@mysql.com."
------------------------------------------------------------------------
-----
Note phrases like "you want to build a commercial product" and "When you
distribute a non-GPL application that only works with the MySQL software
and ship it with the MySQL software. This type of solution is considered
to be linking even if it's done over a network."

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 06:07, Jeff Davis wrote:

Also, aren't mysql and sleepycat in the standard distribution of Debian? I
would think the debian developers would be interested to know if the likes of
sleepycat and mysql don't abide by the DFSG. That's actually one of the
things I've always liked about Debian: read one set of guidelines, and trust
the developers to ensure compliance across the entire OS (as long as you stay
our of non-free). At least, so I thought...

I looked at the copyright information on the mysql-server package in
Debian and also at http://www.mysql.com/doc/en/MySQL_licenses.html:

The MySQL documentation is in non-free (and is not therefore officially
part of Debian). MySQL itself is GPL, so you can do what you like with
it, whatever FUD their website puts out, so long as you make your source
code available. If you want to fork MySQL, you can!

Sleepycat is free so long as source code is released.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And they questioned Him, saying "...Is it lawful for
us to pay taxes to Caesar, or not? ...And He said to
them "...render to Caesar the things that are
Caesar's, and to God the things that are God's."
Luke 20:21,22,25

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@cynic.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@tgds.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 Tuesday 15 April 2003 14:00, you wrote:

** Reply to message from Tony Grant <tony@tgds.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@cynic.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@sysexperts.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@cynic.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 Thierry Missimilly <THIERRY.MISSIMILLY@BULL.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

-----Original Message-----
From: Ron Mayer [mailto:ron@intervideo.com]
Sent: 15 April 2003 05:38
To: pgsql-hackers@postgresql.org
Cc: ron@intervideo.com
Subject: Re: [HACKERS] Are we losing momentum?

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?

Cisco use PostgreSQL:
http://www.cisco.com/en/US/products/sw/voicesw/ps4371/products_user_guid
e_chapter09186a00800c252c.html

:-)

But I see your point...

Regards, Dave.

-----Original Message-----
From: Kevin Brown [mailto:kevin@sysexperts.com]
Sent: 15 April 2003 01:30
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Are we losing momentum?

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

Well, we are now actively developing pgAdmin III in C++ using the
wxWindows framework. It's currently known to run on Linux/GTK and Win32
and did run on FreeBSD the last time I had it installed.

If anyone feels like chipping in there is still plenty to be done
including object view/create dialogues, an autoconf build system,
documentation, data editor and more.

Source code is at http://cvs.pgadmin.org in the pgadmin3 module, and the
hackers list is pgadmin-hackers@postgresql.org.

Any new contributors will be most welcome :-)

Regards, Dave.

If anyone feels like chipping in there is still plenty to be done
including object view/create dialogues, an autoconf build system,
documentation, data editor and more.

I'll probably implement a data editor within the next days.

Regards,
Andreas

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@cbbrowne.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@mascari.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@sra.co.jp>
To: <pgsql@mohawksoft.com>
Cc: <chriskl@familyhealth.com.au>; <cjs@cynic.net>; <brent@rcfile.org>; <pgsql-hackers@postgresql.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@did-it.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

mlw <pgsql@mohawksoft.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@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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&amp;dockey=xml/8/1/816e9b7e50ae92331bb5c47a791a589f@activejobs0&amp;c=1
http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&amp;dockey=xml/c/8/c8dc5841d18329c6c50b55f67a7ff038@activejobs0&amp;c=1
http://seeker.dice.com/jobsearch/servlet/JobSearch?op=1002&amp;dockey=xml/1/6/168f30dc84b8f195d1fc35feb6a2f67a@activejobs0&amp;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@postgresql.org)

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

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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

Hello all,

New to the Hackers list, but long time lurker on the archives. Also a member of the Postgres-r list. Just thought I would join in now to give my opinions on this thread.

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.

I disagree, The cross-db operations would indeed be very nice to have, but you COULD make things work across DB's from within your application code if Postgres supported 2PC. Granted this would not be the best, but it could be done.

Also, if you want to start doing cross-db operations you will need 2PC to support this. While 2PC may not be needed to support cross-db operations on two db's on the same server, it would definetly be needed to do cross-db operations with db's on two separate servers. Why limit the cross-db operations only to db's on the same server?

Later
Rob

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@candle.pha.pa.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

--- Rob Butler <robert.butler5@verizon.net> wrote:

I disagree, The cross-db operations would indeed be very nice to
have, but you COULD make things work across DB's from within your
application code if Postgres supported 2PC. Granted this would not
be the best, but it could be done.

Sure, anything could be done given time and money. OTOH, why would one
want to hand-code joins between tables in different dbs, configure
multiple connection pools, etc when all that can be avoided with a
RDBMS that supports cross-db operations? And since mySql supports
cross-db ops now, mySql could become a very intersting option when they
implement all the features they promissed for version 5.0

Also, if you want to start doing cross-db operations you will need
2PC to support this. While 2PC may not be needed to support cross-db
operations on two db's on the same server, it would definetly be
needed to do cross-db operations with db's on two separate servers.
Why limit the cross-db operations only to db's on the same server?

I'm not saying Postgres has to limit itself to cross-db ops on the same
server only. However, if it's much simpler to implement then why not
start there? Cross-db ops have been on the Postgres TODO list for
several years now and, AFAICT, it does not appear that they will be
available any time soon. Thanks

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

Robert Treat <xzilla@users.sourceforge.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

On Tue, 2003-04-15 at 18:43, Rob Butler wrote:

Hello all,

New to the Hackers list, but long time lurker on the archives. Also a member of the Postgres-r list. Just thought I would join in now to give my opinions on this thread.

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.

I disagree, The cross-db operations would indeed be very nice to
have, but you COULD make things work across DB's from within your
application code if Postgres supported 2PC. Granted this would not be
the best, but it could be done.

In any case, we effectively have the equivalent of MySQL's
cross-database access in schemas, which MySQL does not have. So rather
than treating it as a problem we should just advertise it as a
terminological difference.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And they questioned Him, saying "...Is it lawful for
us to pay taxes to Caesar, or not? ...And He said to
them "...render to Caesar the things that are
Caesar's, and to God the things that are God's."
Luke 20:21,22,25

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, April 15, 2003 12:17 PM
To: Robert Treat
Cc: ow; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Are we losing momentum?

Robert Treat <xzilla@users.sourceforge.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.

Because you had to buy several packages to solve your problems. You
have (perhaps) Peoplesoft for HR, and SAP for CRM, Supply Chain and some
others. You bought an Oracle package for manufacturing, and you run
your web server on PostgreSQL. Nearly every large business has this
problem.

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

Heterogenius database access is another realistic scenario. In order to
solve this, you would need to be able to specify an ODBC, JDBC or OLEDB
table as a partner in transactions.

--- Tom Lane <tgl@sss.pgh.pa.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

"Dann Corbit" <DCorbit@connx.com> writes:

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
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.

Because you had to buy several packages to solve your problems.

And?

ISTM you can put 'em in the same database anyway.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, April 15, 2003 12:35 PM
To: Dann Corbit
Cc: Robert Treat; ow; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Are we losing momentum?

"Dann Corbit" <DCorbit@connx.com> writes:

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
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.

Because you had to buy several packages to solve your problems.

And?

ISTM you can put 'em in the same database anyway.

Sure you can. It's not easy of course. That's one of the things that
CONNX software does (I work for CONNX Solutions Inc.). Certainly, the
PostgreSQL group could accomplish the same thing, if they put their mind
to it.

I can easily join PostgreSQL tables to Oracle tables and insert the
result set into DB/2. No programs, no extracts. Just a SQL query.

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

Dann wrote:

Sure you can. It's not easy of course. That's one of the things that
CONNX software does (I work for CONNX Solutions Inc.). Certainly, the
PostgreSQL group could accomplish the same thing, if they put their mind
to it.

I can easily join PostgreSQL tables to Oracle tables and insert the
result set into DB/2. No programs, no extracts. Just a SQL query.

Very interesting... I was just in a conversation here at work about
joining data from an oracle system with data in a postgresql
system...

FWIW, Oracle has something called "Oracle Generic Connectivity"
and "Oracle Transparent Gateways" that do similar.

http://otn.oracle.com/products/oracle9i/datasheets/gateways/gateway_rel2_ds.html

"Oracle Generic Connectivity and Oracle Transparent Gateways provide
the ability to transparently access data in non-Oracle systems from
an Oracle environment....

...Oracle Generic connectivity makes it possible to access low-end
data stores such as Foxpro, Access, dBase and non-relational targets
like Excel. ...

...Oracle has transparent gateways to many sources, Sybase, Informix,
Microsoft SQL Server, Ingres, Teradata to name a few."

I was mildly disapointed I didn't see PostgreSQL on the list. 1/2 :-) 1/2 :-|
Is this similar to what CONNX does?

Ron

-----Original Message-----
From: Ron Mayer [mailto:ron@intervideo.com]
Sent: Tuesday, April 15, 2003 3:07 PM
To: Dann Corbit; Tom Lane
Cc: Robert Treat; ow; pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] Are we losing momentum?

Dann wrote:

Sure you can. It's not easy of course. That's one of the

things that

CONNX software does (I work for CONNX Solutions Inc.).

Certainly, the

PostgreSQL group could accomplish the same thing, if they put their
mind to it.

I can easily join PostgreSQL tables to Oracle tables and insert the
result set into DB/2. No programs, no extracts. Just a SQL query.

Very interesting... I was just in a conversation here at
work about joining data from an oracle system with data in a
postgresql
system...

FWIW, Oracle has something called "Oracle Generic Connectivity"
and "Oracle Transparent Gateways" that do similar.

http://otn.oracle.com/products/oracle9i/datasheets/gateways/gateway_rel2
_ds.html

"Oracle Generic Connectivity and Oracle Transparent Gateways provide
the ability to transparently access data in non-Oracle systems from
an Oracle environment....

...Oracle Generic connectivity makes it possible to access low-end
data stores such as Foxpro, Access, dBase and non-relational targets
like Excel. ...

...Oracle has transparent gateways to many sources, Sybase, Informix,

Microsoft SQL Server, Ingres, Teradata to name a few."

I was mildly disapointed I didn't see PostgreSQL on the list. 1/2 :-)
1/2 :-| Is this similar to what CONNX does?

DRC responds: >>>>-----------------------------

CONNX joins anything to anything. We wrote several of our own ODBC
drivers (including a recent one for PostgreSQL in {soon to be released}
CONNX 8.9) and we also work with ODBC drivers or OLEDB sources written
by other folks. We import every data source into a central repository
of metadata called the CONNX CDD. After that, we can join across
systems transparent to the user.

We also have a data replication system that will mirror data from any
collection of database systems to a single target system or a collection
of target systems. We can replicate only the data that has changed,
which speeds up the synchronizations a lot.

Plenty of other stuff too. Those that are interested can cruise around
our web site. Probably off topic for the list at this point. Further
inquiries would probably be better as private emails.

DRC responds: <<<<-----------------------------

I beg to differ with Tom Lane's opinion, but schemas do not solve the
problem with multi-database queries because of the following reasons:

1) When dealing with large databases, the use of multiple databases
reduces the risk of wiping out all the data, and reduces the recovery
time in case of accidents.

2) Multiple databases allow for different management policies on each
database, whereas schemas require some consistency across them all.
In a heterogeneous working environment, this is a signficant issue.

3) PostgreSQL should strive for heterogeneous multi-database queries,
so that applications currently using other systems could be slowly
migrated to PostgreSQL by moving portions of a database from other
vendors to PostgreSQL. In my work, the lack of PostgreSQL - Oracle
connectivity is a disabling impediment to wider PostgreSQL usage.

--Bob

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| President, Congenomics Inc. | URL:   http://www.congen.com/~bruc |
| P.O. Box 314                | Phone: 609 818 7251                | 
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+

"Robert E. Bruccoleri" <bruc@stone.congenomics.com> writes:

I beg to differ with Tom Lane's opinion, but schemas do not solve the
problem with multi-database queries because of the following reasons:

1) When dealing with large databases, the use of multiple databases
reduces the risk of wiping out all the data, and reduces the recovery
time in case of accidents.

2) Multiple databases allow for different management policies on each
database, whereas schemas require some consistency across them all.
In a heterogeneous working environment, this is a signficant issue.

3) PostgreSQL should strive for heterogeneous multi-database queries,
so that applications currently using other systems could be slowly
migrated to PostgreSQL by moving portions of a database from other
vendors to PostgreSQL. In my work, the lack of PostgreSQL - Oracle
connectivity is a disabling impediment to wider PostgreSQL usage.

Please keep in mind that I was replying to a poster who said "cross-db
queries on the same server (meaning same postmaster, for our purposes)
are trivial; why hasn't Postgres got them when everybody else does?"

Your above arguments are all good ones, but they presume a scenario that
is much different and *MUCH* harder to implement than local "cross
database" queries. My point is that schemas solve the same-server
problems that the original poster was interested in. I did not say,
nor mean, that there is no need for cross-server queries. But that is
a different problem. Today we can only offer dblink; maybe someday
SQL-MED.

regards, tom lane

ow <oneway_111@yahoo.com> writes:

--- Neil Conway <neilc@samurai.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@sss.pgh.pa.us> wrote:

Please keep in mind that I was replying to a poster who said
"cross-db
queries on the same server (meaning same postmaster, for our
purposes)
are trivial; why hasn't Postgres got them when everybody else does?"

I think you're referring to one of my messages. If this is the case,
then you've misquoted me, that was not what I said.

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

--- Tom Lane <tgl@sss.pgh.pa.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@samurai.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?

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

Tom> Please keep in mind that I was replying to a poster who said
Tom> "cross-db queries on the same server (meaning same
Tom> postmaster, for our purposes) are trivial; why hasn't
Tom> Postgres got them when everybody else does?"

BTW, DB2 doesn't have 'em either.

In DB2, you have Database -> Schema -> Objects

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.

Much of the confusion stems from SQL-Server and Sybase having:

Database -> Objects

The Database is used to identify distinct schemas. I'm not sure if in
these systems they are physically separate entities (different lock
manager etc.)

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

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

ow kirjutas K, 16.04.2003 kell 02:51:

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Please keep in mind that I was replying to a poster who said
"cross-db
queries on the same server (meaning same postmaster, for our
purposes)
are trivial; why hasn't Postgres got them when everybody else does?"

I think you're referring to one of my messages. If this is the case,
then you've misquoted me, that was not what I said.

But what did you say then ?

I don't think that what MySQL has as databases is much different from
our schemas, so I too had some difficulty understandin what you were
complaing about

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

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@turnstep.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@turnstep.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@turnstep.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@chittenden.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@chittenden.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

Dear Tom,

Please keep in mind that I was replying to a poster who said "cross-db
queries on the same server (meaning same postmaster, for our purposes)
are trivial; why hasn't Postgres got them when everybody else does?"

My apologies for missing that context.

Your above arguments are all good ones, but they presume a scenario that
is much different and *MUCH* harder to implement than local "cross
database" queries. My point is that schemas solve the same-server
problems that the original poster was interested in. I did not say,
nor mean, that there is no need for cross-server queries. But that is
a different problem. Today we can only offer dblink; maybe someday
SQL-MED.

Agreed. Thanks. --Bob

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| President, Congenomics Inc. | URL:   http://www.congen.com/~bruc |
| P.O. Box 314                | Phone: 609 818 7251                | 
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+

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@turnstep.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@gmx.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@gmx.net

Peter Eisentraut <peter_e@gmx.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@chittenden.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@rbt.ca>

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

Sean Chittenden <sean@chittenden.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@gmx.net

On 16 Apr 2003, Hannu Krosing wrote:

greg@turnstep.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@noao.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@nasby.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

On Tue, 22 Apr 2003, Dustin Sallings wrote:

Around 14:26 on Apr 21, 2003, scott.marlowe said:

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

That's part of your database abstraction layer. JDBC does all of
this stuff for you in a clean way. If you're doing this without a
database abstraction layer, then you're writing non-portable code unless
you try to create identical views on every DB you use.

That said, it's not hard to create a view to do what \dt does. I
still haven't ever had a need to do it, though.

I'm talking more about a setup like what we have here at work. A dozen or
fewer Unix/Linux geeks running the postgresql boxes via ssh with psql who
know SQL and prefer a command line, and about three or four dozen
sales and marketing folks who use Windows programs that access the
database through ODBC.

To the Windows guys, how do I tell them to just create a view
encapsulating:

SELECT c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
AND c.relname !~ '^pg_'
ORDER BY 1;

if they want a list of the tables, sequences, views and indexes in
postgresql.

It's like asking someone if they want an engine with their car, they just
stare at you for a second wondering if you're joking or something.

On Tue, 22 Apr 2003, Dustin Sallings wrote:

Around 11:17 on Apr 22, 2003, scott.marlowe said:

# I'm talking more about a setup like what we have here at work. A dozen or
# fewer Unix/Linux geeks running the postgresql boxes via ssh with psql who
# know SQL and prefer a command line, and about three or four dozen
# sales and marketing folks who use Windows programs that access the
# database through ODBC.

OK, now I know this is fictional. You don't send people (much
less sales and marketing) querying a database without an understanding of
the data model. There's no command that can be added to the DB to change
this.

Hey, stop being an ass and accusing me of lying. Just because in your
perfect world you never have to deal with random events and the corporate
insanity some of the others of us do is not reason to call someone a liar.

I'm not arguing this with you. I will tell you that I hear it over and
over from my users that Postgresql is hard to use, and this is one of the
areas they find hard. You don't care, fine, don't care. Just have enough
courteousy to assume that other people may actually live in a slightly
different world than yours, and aren't necessarily liars just because they
get stuck doing things differently than you.

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, April 22, 2003 4:33 PM
To: Dustin Sallings
Cc: Neil Conway; PostgreSQL Hackers
Subject: Re: [HACKERS] Are we losing momentum?

On Tue, 22 Apr 2003, Dustin Sallings wrote:

Around 11:17 on Apr 22, 2003, scott.marlowe said:

# I'm talking more about a setup like what we have here at work. A
dozen or # fewer Unix/Linux geeks running the postgresql

boxes via ssh

with psql who # know SQL and prefer a command line, and

about three or

four dozen # sales and marketing folks who use Windows

programs that

access the # database through ODBC.

OK, now I know this is fictional. You don't send

people (much less

sales and marketing) querying a database without an

understanding of

the data model. There's no command that can be added to the DB to
change this.

Hey, stop being an ass and accusing me of lying. Just
because in your
perfect world you never have to deal with random events and
the corporate
insanity some of the others of us do is not reason to call
someone a liar.

I'm not arguing this with you. I will tell you that I hear
it over and
over from my users that Postgresql is hard to use, and this
is one of the
areas they find hard. You don't care, fine, don't care.
Just have enough
courteousy to assume that other people may actually live in a
slightly
different world than yours, and aren't necessarily liars just
because they
get stuck doing things differently than you.

One of the major reasons for reporting servers is that people who do not
understand the data (or even SQL very well) will often cause great
problems in ad-hoc query situations.

Those performing the queries typically do not understand the data very
well. This is especially true in a database with hundreds or thousands
of tables. Usually, they will have a pretty good understanding of a
small subset of the tables that contain the information that they are
after, but even that is not always true.

----- Original Message -----
From: "Dann Corbit" <DCorbit@connx.com>

One of the major reasons for reporting servers is that people who do not
understand the data (or even SQL very well) will often cause great
problems in ad-hoc query situations.

Those performing the queries typically do not understand the data very
well. This is especially true in a database with hundreds or thousands
of tables. Usually, they will have a pretty good understanding of a
small subset of the tables that contain the information that they are
after, but even that is not always true.

*nod*

This is something to be addressed by policy rather than technology, I
suspect. One large and famous financial institution I worked at had a simple
policy regarding production DBs: all client access was to be through stored
procedures. This was enforced by the DB's own privileges system - only the
SPs were visible, and they could only be installed by the database group.
This also forced the developers to abstract the DB access into a separate
layer, so that when it was productised only that layer needed to be changed
(this is a Good Thing (tm)).

andrew

On Tuesday 22 April 2003 22:47, scott.marlowe wrote:

To the Windows guys, how do I tell them to just create a view
encapsulating:

SELECT c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
AND c.relname !~ '^pg_'
ORDER BY 1;

if they want a list of the tables, sequences, views and indexes in
postgresql.

Have you used TORA any times? It does support postgresql and it does it pretty
well..

Shridhar

On Wed, 2003-04-23 at 02:15, Shridhar Daithankar wrote:

On Tuesday 22 April 2003 22:47, scott.marlowe wrote:

To the Windows guys, how do I tell them to just create a view
encapsulating:

SELECT c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
AND c.relname !~ '^pg_'
ORDER BY 1;

if they want a list of the tables, sequences, views and indexes in
postgresql.

Have you used TORA any times? It does support postgresql and it does it pretty
well..

http://techdocs.postgresql.org/guides/GUITools

A rather handy list of GUITools available for PostgreSQL.

--
Rod Taylor <rbt@rbt.ca>

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

On Wed, 23 Apr 2003, Shridhar Daithankar wrote:

On Tuesday 22 April 2003 22:47, scott.marlowe wrote:

To the Windows guys, how do I tell them to just create a view
encapsulating:

SELECT c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
AND c.relname !~ '^pg_'
ORDER BY 1;

if they want a list of the tables, sequences, views and indexes in
postgresql.

Have you used TORA any times? It does support postgresql and it does it pretty
well..

Actually, most of the Unix guys are happy with psql, while most of the
Windows guys seem happy with pgadmin II. But some of the developer types
are writing things that issue DDL, and they need to look at the structure
of the database in their code, and for them, the current implementation of
system tables is a bit awkward to grasp.

Plus the fact that the underlying pg_ tables are stable from release to
release makes it a bit awkward to upgrade the servers they play on.
Most of them have gone ahead and created views that give them a consistent
view of the parts of the database they need.

but it looks like there's a good chance of having views in a future
version of pgsql that are just standard built-ins that won't change
(much???) from version to version, so my problems are really not that
great over time.

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: 23 April 2003 16:27
To: Shridhar Daithankar
Cc: PostgreSQL Hackers
Subject: Re: [HACKERS] Are we losing momentum?

Actually, most of the Unix guys are happy with psql, while
most of the
Windows guys seem happy with pgadmin II.

Shouldn't be long before the Unix guys can use pgadmin III if they
want....

Regards, Dave

"scott" == scott marlowe <scott.marlowe> writes:

scott> Plus the fact that the underlying pg_ tables are stable
scott> from release to release makes it a bit awkward to upgrade
scott> the servers they play on. Most of them have gone ahead and
scott> created views that give them a consistent view of the parts
scott> of the database they need.

This is exactly the reason why in db2 _no_ guarantees are made
regarding the constancy of the system catalogs (that are in the SYSIBM
schema). Instead, the equivalent views (in the SYSCAT schema) are
_never_ broken (whether in a point release or a new version). In fact,
the SYSCAT views correspond to the ISO SQL standard.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

On Thu, 2003-04-24 at 01:49, Sailesh Krishnamurthy wrote:

"scott" == scott marlowe <scott.marlowe> writes:

scott> Plus the fact that the underlying pg_ tables are stable
scott> from release to release makes it a bit awkward to upgrade
scott> the servers they play on. Most of them have gone ahead and
scott> created views that give them a consistent view of the parts
scott> of the database they need.

This is exactly the reason why in db2 _no_ guarantees are made
regarding the constancy of the system catalogs (that are in the SYSIBM
schema). Instead, the equivalent views (in the SYSCAT schema) are
_never_ broken (whether in a point release or a new version). In fact,
the SYSCAT views correspond to the ISO SQL standard.

The INFORMATION_SCHEMA? Out of curiousity, how do they handle DB2
extensions? Do they create new views in that schema? Do they ignore
them?

I'm going with the assumptions DB2 has extended SQL specs in some shape
or form.

--
Rod Taylor <rbt@rbt.ca>

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

"Rod" == Rod Taylor <rbt@rbt.ca> writes:

This is exactly the reason why in db2 _no_ guarantees are made
regarding the constancy of the system catalogs (that are in the
SYSIBM schema). Instead, the equivalent views (in the SYSCAT
schema) are _never_ broken (whether in a point release or a new
version). In fact, the SYSCAT views correspond to the ISO SQL
standard.

Rod> The INFORMATION_SCHEMA? Out of curiousity, how do they
Rod> handle DB2 extensions? Do they create new views in that
Rod> schema? Do they ignore them?

Yes, the INFO SCHEMA - thankfully it's long enough since I looked at
the SQL specs .. I've started forgetting terms. If I never have to
write or read any spec material again, it won't be too soon.

Why extensions, even for things like indexes that aren't in the
standard, they create views (SYSCAT.INDEXES, SYSCAT.INDEXAUTH etc.)

Rod> I'm going with the assumptions DB2 has extended SQL specs in
Rod> some shape or form.

Certainly - it's just that the meaning and number of existing columns
and rows in the syscat views are always backward compatible. That
includes support of the info schema - for the sql standard features
that db2 supports.

So if there's something new in the catalog tables that is a result of
an extension and doesn't appear as a column in the syscat views (or
the info schema) then an appropriate column may be added to the view -
provided that this doesn't break the info schema compatibility.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:

Rod> The INFORMATION_SCHEMA? Out of curiousity, how do they
Rod> handle DB2 extensions? Do they create new views in that
Rod> schema? Do they ignore them?

Why extensions, even for things like indexes that aren't in the
standard, they create views (SYSCAT.INDEXES, SYSCAT.INDEXAUTH etc.)
...
Certainly - it's just that the meaning and number of existing columns
and rows in the syscat views are always backward compatible. That
includes support of the info schema - for the sql standard features
that db2 supports.

So if there's something new in the catalog tables that is a result of
an extension and doesn't appear as a column in the syscat views (or
the info schema) then an appropriate column may be added to the view -
provided that this doesn't break the info schema compatibility.

Of course, IBM can afford to keep reps on the SQL standards committee to
make sure that no future spec extension conflicts with the names they've
used for their additions to INFORMATION_SCHEMA. We, on the other hand,
could easily get burnt by spec changes.

regards, tom lane

"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

Tom> Of course, IBM can afford to keep reps on the SQL standards
Tom> committee to make sure that no future spec extension
Tom> conflicts with the names they've used for their additions to
Tom> INFORMATION_SCHEMA. We, on the other hand, could easily get
Tom> burnt by spec changes.

Right it's pretty unfair. I'm not beating any drums here. It's more
than just making sure that no extensions conflict with what they've
used. It's also about makign their extensions the default.

One thing that they _do_ try though is to use very ibm-centric when
possible.

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

On Thu, 2003-04-24 at 18:28, Tom Lane wrote:

Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:

Rod> The INFORMATION_SCHEMA? Out of curiousity, how do they
Rod> handle DB2 extensions? Do they create new views in that
Rod> schema? Do they ignore them?

Why extensions, even for things like indexes that aren't in the
standard, they create views (SYSCAT.INDEXES, SYSCAT.INDEXAUTH etc.)
...
Certainly - it's just that the meaning and number of existing columns
and rows in the syscat views are always backward compatible. That
includes support of the info schema - for the sql standard features
that db2 supports.

So if there's something new in the catalog tables that is a result of
an extension and doesn't appear as a column in the syscat views (or
the info schema) then an appropriate column may be added to the view -
provided that this doesn't break the info schema compatibility.

Of course, IBM can afford to keep reps on the SQL standards committee to
make sure that no future spec extension conflicts with the names they've
used for their additions to INFORMATION_SCHEMA. We, on the other hand,
could easily get burnt by spec changes.

We could probably get away with adding pg_ views to the information
schema though. For extensions of an existing view, simply inherit the
real view into a pg_ labelled view and add the new columns.

--
Rod Taylor <rbt@rbt.ca>

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

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&quot;&gt;
http://advocacy.postgresql.org&lt;/a&gt;.

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

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@postgresql.org

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

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@postgresql.org so that your
message can get through to the mailing list cleanly

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

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:

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@gmx.net

Peter Eisentraut <peter_e@gmx.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@rbt.ca>

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

[ moving this thread to a more appropriate place ]

Sean Chittenden <sean@chittenden.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@gmx.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@rbt.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@gmx.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.