Thread

Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-04-14T22:37:57Z

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

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

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

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

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

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Re: Are we losing momentum?

Dennis Gearon <gearond@cvc.net> — 2003-04-14T23:41:28Z

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

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

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

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

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

Re: Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-14T23:54:27Z

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

Re: Are we losing momentum?

Gavin Sherry <swm@linuxworld.com.au> — 2003-04-15T00:18:59Z

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

Re: Are we losing momentum?

Kevin Brown <kevin@sysexperts.com> — 2003-04-15T00:30:27Z

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

Re: Are we losing momentum?

Gavin Sherry <swm@linuxworld.com.au> — 2003-04-15T00:38:05Z

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

Re: Are we losing momentum?

Larry Rosenman <ler@lerctr.org> — 2003-04-15T01:37:00Z


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

Re: Are we losing momentum?

Brent Verner <brent@rcfile.org> — 2003-04-15T01:52:17Z

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

Re: Are we losing momentum?

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2003-04-15T02:15:03Z

>   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

Re: Are we losing momentum?

Your Name <cbbrowne@cbbrowne.com> — 2003-04-15T02:24:39Z

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

Re: Are we losing momentum?

Curt Sampson <cjs@cynic.net> — 2003-04-15T02:36:07Z

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

Re: Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-04-15T02:48:30Z

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

Re: Are we losing momentum?

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2003-04-15T02:50:03Z

> 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

Re: Are we losing momentum?

Mike Mascari <mascarm@mascari.com> — 2003-04-15T02:51:41Z

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

Re: Are we losing momentum?

Your Name <cbbrowne@cbbrowne.com> — 2003-04-15T03:12:35Z

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

Re: Are we losing momentum?

Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> — 2003-04-15T03:13:55Z

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

Re: Are we losing momentum?

Jeff Hoffmann <jeff@propertykey.com> — 2003-04-15T03:54:11Z

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

Re: Are we losing momentum?

Ron Mayer <ron@intervideo.com> — 2003-04-15T04:38:19Z

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

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

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

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

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

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

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


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


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

    Ron

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

Re: Are we losing momentum?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-04-15T06:08:05Z

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

Re: Are we losing momentum?

tony <tony@tgds.net> — 2003-04-15T06:12:04Z

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

Re: Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-15T06:40:37Z

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

Re: Are we losing momentum?

Wayne Armstrong <wdarmst@bacchus.com.au> — 2003-04-15T08:30:25Z

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

Re: [GENERAL] Are we losing momentum?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-04-15T08:42:35Z

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

Re: Are we losing momentum?

Kevin Brown <kevin@sysexperts.com> — 2003-04-15T09:10:42Z

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

Re: Are we losing momentum?

Curt Sampson <cjs@cynic.net> — 2003-04-15T09:28:01Z

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

Re: Are we losing momentum?

Thierry Missimilly <thierry.missimilly@bull.net> — 2003-04-15T09:41:47Z

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

    Regards,
    Thierry

Wayne Armstrong wrote:

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

Re: Are we losing momentum?

Wayne Armstrong <wdarmst@bacchus.com.au> — 2003-04-15T10:10:48Z

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

Re: Are we losing momentum?

Mark Woodward <pgsql@mohawksoft.com> — 2003-04-15T11:51:30Z

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.

Re: Are we losing momentum?

Mark Woodward <pgsql@mohawksoft.com> — 2003-04-15T12:07:37Z


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.

Re: Are we losing momentum?

Tatsuo Ishii <t-ishii@sra.co.jp> — 2003-04-15T12:09:31Z

> 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

PostgreSQL vs. MySQL in Japan (was: Are we losing momentum?)

Ned Lilly <ned@nedscape.com> — 2003-04-15T12:38:05Z

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

Re: Are we losing momentum?

Ericson Smith <eric@did-it.com> — 2003-04-15T13:12:19Z

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>

Re: [GENERAL] Are we losing momentum?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-04-15T13:18:46Z

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

Re: Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-15T14:03:19Z

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

Re: Are we losing momentum?

Robert Treat <xzilla@users.sourceforge.net> — 2003-04-15T14:19:11Z

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

Re: Are we losing momentum?

scott.marlowe <scott.marlowe@ihs.com> — 2003-04-15T16:18:28Z

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.

Re: Are we losing momentum?

Kurt Roeckx <q@ping.be> — 2003-04-15T16:36:17Z

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

Re: Are we losing momentum?

Lamar Owen <lamar.owen@wgcr.org> — 2003-04-15T16:43:39Z

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

Re: Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-04-15T16:50:07Z

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

Re: Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-04-15T16:52:27Z

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

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

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

Re: Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-04-15T16:55:38Z

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

Re: Are we losing momentum?

John Liu <johnl@emrx.com> — 2003-04-15T17:21:15Z

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

Re: Are we losing momentum?

Steve Wampler <swampler@noao.edu> — 2003-04-15T17:23:04Z

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

Re: Are we losing momentum?

ow <oneway_111@yahoo.com> — 2003-04-15T17:30:19Z

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

Re: Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-04-15T18:29:49Z

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

Re: Are we losing momentum?

Robert Treat <xzilla@users.sourceforge.net> — 2003-04-15T18:50:10Z

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

Re: Are we losing momentum?

Mike Benoit <mikeb@netnation.com> — 2003-04-15T18:56:50Z

>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

Re: Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-15T19:17:17Z

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

Re: Are we losing momentum?

ow <oneway_111@yahoo.com> — 2003-04-15T19:35:18Z

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

Re: Are we losing momentum?

Neil Conway <neilc@samurai.com> — 2003-04-15T20:27:07Z

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

Re: Are we losing momentum?

Michael Paesold <mpaesold@gmx.at> — 2003-04-15T21:51:00Z

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

Re: Are we losing momentum?

ow <oneway_111@yahoo.com> — 2003-04-15T21:58:24Z

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

Re: Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-15T23:28:27Z

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

Re: Are we losing momentum?

ow <oneway_111@yahoo.com> — 2003-04-15T23:56:24Z

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

Re: Are we losing momentum?

Rob Butler <robert.butler5@verizon.net> — 2003-04-16T00:08:44Z

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

Re: Are we losing momentum?

Your Name <cbbrowne@cbbrowne.com> — 2003-04-16T00:28:25Z

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?

Re: PostgreSQL vs. MySQL in Japan

Tatsuo Ishii <t-ishii@sra.co.jp> — 2003-04-16T05:43:08Z

> 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

Re: Are we losing momentum?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-04-16T07:20:33Z

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

Re: Are we losing momentum?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-04-16T07:55:14Z

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

Re: Are we losing momentum?

ow <oneway_111@yahoo.com> — 2003-04-16T12:20:17Z

> 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

Re: Are we losing momentum?

Greg Sabino Mullane <greg@turnstep.com> — 2003-04-16T13:51:28Z

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

Re: Are we losing momentum?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-04-16T14:16:37Z

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

Re: Are we losing momentum?

Hannu Krosing <hannu@tm.ee> — 2003-04-16T14:17:47Z

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

Re: Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-04-16T15:45:17Z

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

Re: [HACKERS] Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-16T15:53:07Z

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

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-04-16T16:17:25Z

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

Re: [HACKERS] Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-16T16:26:58Z

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

Re: Are we losing momentum?

Ron Mayer <ron@intervideo.com> — 2003-04-16T22:38:31Z

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

Re: Are we losing momentum?

Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-04-17T11:35:07Z

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

Re: [HACKERS] Are we losing momentum?

Peter Eisentraut <peter_e@gmx.net> — 2003-04-17T12:45:26Z

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

Re: Are we losing momentum?

Ian Barwick <barwick@gmx.net> — 2003-04-17T12:50:47Z

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

Re: [HACKERS] Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-17T14:05:20Z

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

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-04-17T19:48:03Z

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

Re: [HACKERS] Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-17T19:52:06Z

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

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-04-17T21:09:41Z

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

Re: [HACKERS] Are we losing momentum?

Rod Taylor <rbt@rbt.ca> — 2003-04-17T21:25:15Z

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

Re: [HACKERS] Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-04-17T21:40:47Z

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

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-04-17T21:55:31Z

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

Re: [HACKERS] Are we losing momentum?

Peter Eisentraut <peter_e@gmx.net> — 2003-04-18T00:21:22Z

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

Re: Are we losing momentum?

scott.marlowe <scott.marlowe@ihs.com> — 2003-04-21T15:30:49Z

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.

Re: Are we losing momentum?

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-04-21T15:44:33Z

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

Re: Are we losing momentum?

Steve Wampler <swampler@noao.edu> — 2003-04-21T15:45:12Z

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

Re: Are we losing momentum?

Neil Conway <neilc@samurai.com> — 2003-04-21T17:06:01Z

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

Re: Are we losing momentum?

scott.marlowe <scott.marlowe@ihs.com> — 2003-04-21T20:26:20Z

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.

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-04-21T21:17:55Z

> 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

Re: Are we losing momentum?

Neil Conway <neilc@samurai.com> — 2003-04-21T21:47:08Z

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

Re: Are we losing momentum?

Sander Steffann <sander@steffann.nl> — 2003-04-21T22:48:10Z

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.

Re: Are we losing momentum?

Jim Nasby <jim@nasby.net> — 2003-04-22T07:07:01Z

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

Re: Are we losing momentum?

Tilo Schwarz <mail@tilo-schwarz.de> — 2003-04-22T15:14:07Z

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

Re: [GENERAL] Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-05-31T02:26:10Z

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

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


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

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

Re: [HACKERS] Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-05-31T03:07:00Z

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

Re: [GENERAL] Are we losing momentum?

Justin Clift <justin@postgresql.org> — 2003-05-31T07:39:34Z

Hi everyone,

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

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

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

Regards and best wishes,

Justin Clift


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

Re: [HACKERS] Are we losing momentum?

Peter Eisentraut <peter_e@gmx.net> — 2003-05-31T15:55:16Z

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

Re: [HACKERS] Are we losing momentum?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-31T16:49:00Z

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

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-06-01T01:43:23Z

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

Re: [HACKERS] Are we losing momentum?

Rod Taylor <rbt@rbt.ca> — 2003-06-01T03:08:49Z

> 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

Table-driven SHOW (was Re: Are we losing momentum?)

Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-01T16:03:08Z

[ 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

Re: [HACKERS] Are we losing momentum?

Peter Eisentraut <peter_e@gmx.net> — 2003-06-01T21:52:45Z

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

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-06-01T21:57:27Z

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

Re: Table-driven SHOW (was Re: Are we losing momentum?)

Rod Taylor <rbt@rbt.ca> — 2003-06-02T00:02:45Z

> 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

Re: [HACKERS] Are we losing momentum?

Peter Eisentraut <peter_e@gmx.net> — 2003-06-02T19:07:11Z

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

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-06-02T19:37:55Z

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

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

*SNIP*
SHOW COLUMNS FROM [tblname];

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

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

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

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

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

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

-sc

-- 
Sean Chittenden

Re: [HACKERS] Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-06-02T19:53:10Z

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

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

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

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

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

Re: [HACKERS] Are we losing momentum?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-08-16T23:22:47Z

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

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

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

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@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

Re: [HACKERS] Are we losing momentum?

Jan Wieck <janwieck@yahoo.com> — 2003-08-17T02:41:39Z

Bruce Momjian wrote:

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

We agreed that applications that need schema information are much better 
off using the schema views.


Jan

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


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

Re: [HACKERS] Are we losing momentum?

Marc G. Fournier <scrappy@postgresql.org> — 2003-08-17T02:48:59Z

Personally, I think adding the SHOW commands would be a good thing ...
psql is nice with its \df to get information without having to learn all
the JOINs required ... having that ability easily from any of the
interfaces would definitely be a plus ... to me, its not about MySQL
compatibility, but about a small improvement to ease of use :)

On Sat, 16 Aug 2003, Bruce Momjian wrote:

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

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Re: [HACKERS] Are we losing momentum?

Sean Chittenden <sean@chittenden.org> — 2003-08-17T05:48:16Z

> Bruce Momjian wrote:
> >I assume we agreed against adding a MySQL mode --- just verifying.
> 
> We agreed that applications that need schema information are much better 
> off using the schema views.
> 
> Jan

Heh, I don't think there was any agreement on anything in that thread,
everyone had their own view (no pun intended).

> From: The Hermit Hacker <scrappy@postgresql.org>
>
> Personally, I think adding the SHOW commands would be a good thing
> ...  psql is nice with its \df to get information without having to
> learn all the JOINs required ... having that ability easily from any
> of the interfaces would definitely be a plus ... to me, its not
> about MySQL compatibility, but about a small improvement to ease of
> use :)

Which goes back to the point about there being little agreement on
this patch or its issues.  A handful of folks think it's a _user
interface_ issue (read: psql, phppgadmin, pgadminIII, etc) and would
be good for converting MySQL users to PostgreSQL (or simply because
its easy and less obtuse than a \ command), others thought it was a
fugly hack to have a parser in the front end and that it should be
handled on the backend by extending SQL to conform to MySQL's
interface (that some argue is incorrect and would unjustly bloat the
backend) that way all clients have the SHOW syntax (thus averting a
possible FAQ), and others took a more elitist mindset and simply
thought that everyone should just select from the information schemas.

*shrug* I tabled working on the patch until there was some kind of
agreement from someone with commit privs and am waiting to pick up
quashing the remaining parser state bug until after 7.4's out the door
or there's renewed interest from non-users.

-sc

-- 
Sean Chittenden

ISO 8601 "Time Intervals" of the "format with time-unit deignators"

Ron Mayer <ron@intervideo.com> — 2003-09-08T04:50:49Z

Short summary:

   This patch allows ISO 8601 "time intervals" using the "format 
   with time-unit designators" to specify postgresql "intervals".

   Below I have (A) What these time intervals are, (B) What I
   modified to support them, (C) Issues with intervals I want
   to bring up, and (D) a patch supporting them.

   It's helpful to me.  Any feedback is appreciated.  If you 
   did want to consider including it, let me know what to clean 
   up.  If not, I thought I'd just put it here if anyone else finds
   it useful too.

   Thanks for your time,
  
      Ron Mayer

Longer:

(A) What these intervals are.

   ISO 8601, the standard from which PostgreSQL gets some of it's 
   time syntax, also has a specification for "time-intervals".
  
   In particular, section 5.5.4.2 has a "Representation of
   time-interval by duration only" which I believe maps
   nicely to ISO intervals.

   Compared to the ISO 8601 time interval specification, the
   postgresql interval syntax is quite verbose.  For example:

     Postgresql interval:              ISO8601 Interval
     ---------------------------------------------------
     '1 year 6 months'                'P1Y6M'
     '3 hours 25 minutes 42 seconds'  'PT3H25M42S'

   Yeah, it's uglier, but it sure is short which can make
   for quicker typing and shorter scripts, and if for some
   strange reason you had an application using this format
   it's nice not to have to translate.

   The syntax is as follows:
       Basic extended format:  PnYnMnDTnHnMnS
                               PnW

       Where everything before the "T" is a date-part and everything
       after is a time-part.  W is for weeks.
       In the date-part, Y=Year, M=Month,  D=Day
       In the time-part, H=Hour, M=Minute, S=Second

   Much more info can be found from the draft standard
   ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
   The final standard's only available for $$$ so I didn't
   look at it.  Some other sites imply that this part didn't
   change from the last draft to the standard.


(B) This change was made by adding two functions to "datetime.c"
    next to where DecodeInterval parses the normal interval syntax.

    A total of 313 lines were added, including comments and sgml docs.
    Of these only 136 are actual code, the rest, comments, whitespace, etc.


    One new function "DecodeISO8601Interval" follows the style of
    "DecodeInterval" below it, and trys to strictly follow the ISO
    syntax.  If it doesn't match, it'll return -1 and the old syntax
    will be checked as before.

    The first test (first character of the first field must be 'P', 
    and second character must be 'T' or '\0') should be fast so I don't
    think this will impact performance of existing code.


    The second function ("adjust_fval") is just a small helper-function
    to remove some of the cut&paste style that DecodeInterval used.

    It seems to work.
    =======================================================================
    betadb=# select 'P1M15DT12H30M7S'::interval;
            interval        
    ------------------------
     1 mon 15 days 12:30:07
    (1 row)

    betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
	    interval        
    ------------------------
    1 mon 15 days 12:30:07
    (1 row)
    =====================================================================



(C) Open issues with intervals, and questions I'd like to ask.

    1.  DecodeInterval seems to have a hardcoded '.' for specifying
        fractional times.  ISO 8601 states that both '.' and ',' are
        ok, but "of these, the comma is the preferred sign".

        In DecodeISO8601Interval I loosened the test to allow
        both but left it as it was in DecodeInterval.  Should
        both be changed to make them more consistant?

    2.  In "DecodeInterval", fractional weeks and fractional months
        can produce seconds; but fractional years can not (rounded
        to months).  I didn't understand the reasoning for this, so
        I left it the same, and followed the same convention for
        ISO intervals.  Should I change this?

    3.  I could save a bunch of copy-paste-lines-of-code from the
        pre-existing DecodeInterval by calling the adjust_fval helper
        function.  The tradeoff is a few extra function-calls when
        decoding an interval.  However I didn't want to risk changes
        to the existing part unless you guys encourage me to do so.


(D) The patch.


Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
retrieving revision 1.123
diff -u -1 -0 -r1.123 datatype.sgml
--- doc/src/sgml/datatype.sgml	31 Aug 2003 17:32:18 -0000	1.123
+++ doc/src/sgml/datatype.sgml	8 Sep 2003 04:04:58 -0000
@@ -1735,20 +1735,71 @@
       Quantities of days, hours, minutes, and seconds can be specified without
       explicit unit markings.  For example, <literal>'1 12:59:10'</> is read
       the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
      </para>
 
      <para>
       The optional precision
       <replaceable>p</replaceable> should be between 0 and 6, and
       defaults to the precision of the input literal.
      </para>
+
+
+     <para>
+      Alternatively, <type>interval</type> values can be written as 
+      ISO 8601 time intervals, using the "Format with time-unit designators".
+      This format always starts with the character <literal>'P'</>, followed 
+      by a string of values followed by single character time-unit designators.
+      A <literal>'T'</> separates the date and time parts of the interval.
+     </para>
+
+     <para>
+       Format:  PnYnMnDTnHnMnS
+     </para>
+     <para>
+       In this format, <literal>'n'</> gets replaced by a number, and 
+       <literal>Y</> represents years, 
+       <literal>M</> (in the date part) months,
+       <literal>D</> months,
+       <literal>H</> hours,
+       <literal>M</> (in the time part) minutes,
+       and <literal>S</> seconds.
+     </para>
+      
+
+     <table id="interval-example-table">
+	   <title>Interval Example</title>
+	   <tgroup cols="2">
+		<thead>
+		 <row>
+		  <entry>Traditional</entry>
+		  <entry>ISO-8601 time-interval</entry>
+		 </row>
+		</thead>
+		<tbody>
+		 <row>
+		  <entry>1 month</entry>
+		  <entry>P1M</entry>
+		 </row>
+		 <row>
+		  <entry>1 hour 30 minutes</entry>
+		  <entry>PT1H30M</entry>
+		 </row>
+		 <row>
+		  <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
+		  <entry>P2Y10M15DT10H30M20S</entry>
+		 </row>
+		</tbody>
+	   </thead>
+	  </table>
+	  
+     </para>
     </sect3>
 
     <sect3>
      <title>Special Values</title>
 
      <indexterm>
       <primary>time</primary>
       <secondary>constants</secondary>
      </indexterm>
 
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
retrieving revision 1.116
diff -u -1 -0 -r1.116 datetime.c
--- src/backend/utils/adt/datetime.c	27 Aug 2003 23:29:28 -0000	1.116
+++ src/backend/utils/adt/datetime.c	8 Sep 2003 04:04:59 -0000
@@ -30,20 +30,21 @@
 			 struct tm * tm, fsec_t *fsec, int *is2digits);
 static int DecodeNumberField(int len, char *str,
 				  int fmask, int *tmask,
 				  struct tm * tm, fsec_t *fsec, int *is2digits);
 static int DecodeTime(char *str, int fmask, int *tmask,
 		   struct tm * tm, fsec_t *fsec);
 static int	DecodeTimezone(char *str, int *tzp);
 static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
 static int	DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
 static void TrimTrailingZeros(char *str);
+static int  DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
 
 
 int			day_tab[2][13] = {
 	{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
 {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
 
 char	   *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
 "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
 
 char	   *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
@@ -2872,30 +2873,271 @@
 			default:
 				*val = tp->value;
 				break;
 		}
 	}
 
 	return type;
 }
 
 
+void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
+{
+	int	sec;
+	fval	   *= scale;
+	sec		    = fval;
+	tm->tm_sec += sec;
+#ifdef HAVE_INT64_TIMESTAMP
+	*fsec	   += ((fval - sec) * 1000000);
+#else
+	*fsec	   += (fval - sec);
+#endif
+}
+
+
+/* DecodeISO8601Interval()
+ *
+ *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
+ *  time-interval by duration only." 
+ *  Basic extended format:  PnYnMnDTnHnMnS
+ *                          PnW
+ *  For more info.
+ *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
+ *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
+ *
+ *  Examples:  P1D  for 1 day
+ *             PT1H for 1 hour
+ *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
+ *
+ *  The first field is exactly "p" or "pt" it may be of this type.
+ *
+ *  Returns -1 if the field is not of this type.
+ *
+ *  It pretty strictly checks the spec, with the two exceptions
+ *  that a week field ('W') may coexist with other units, and that
+ *  this function allows decimals in fields other than the least
+ *  significant units.
+ */
+int
+DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec) 
+{
+	char	   *cp;
+	int			fmask = 0,
+				tmask;
+	int			val;
+	double		fval;
+	int			arg;
+	int			datepart;
+
+    /*
+	 * An ISO 8601 "time-interval by duration only" must start
+	 * with a 'P'.  If it contains a date-part, 'p' will be the
+	 * only character in the field.  If it contains no date part
+	 * it will contain exactly to characters 'PT' indicating a
+	 * time part.
+	 * Anything else is illegal and will be treated like a 
+	 * traditional postgresql interval.
+	 */
+    if (!(field[0][0] == 'p' &&
+          ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
+	{
+	  return -1;
+	}
+
+
+    /*
+	 * If the first field is exactly 1 character ('P'), it starts
+	 * with date elements.  Otherwise it's two characters ('PT');
+	 * indicating it starts with a time part.
+	 */
+	datepart = (field[0][1] == 0);
+
+	/*
+	 * Every value must have a unit, so we require an even
+	 * number of value/unit pairs. Therefore we require an
+	 * odd nubmer of fields, including the prefix 'P'.
+	 */
+	if ((nf & 1) == 0)
+		return -1;
+
+	/*
+	 * Process pairs of fields at a time.
+	 */
+	for (arg = 1 ; arg < nf ; arg+=2) 
+	{
+		char * value = field[arg  ];
+		char * units = field[arg+1];
+
+		/*
+		 * The value part must be a number.
+		 */
+		if (ftype[arg] != DTK_NUMBER) 
+			return -1;
+
+		/*
+		 * extract the number, almost exactly like the non-ISO interval.
+		 */
+		val = strtol(value, &cp, 10);
+
+		/*
+		 * One difference from the normal postgresql interval below...
+		 * ISO 8601 states that "Of these, the comma is the preferred 
+		 * sign" so I allow it here for locales that support it.
+		 * Note: Perhaps the old-style interval code below should
+		 * allow for this too, but I didn't want to risk backward
+		 * compatability.
+		 */
+		if (*cp == '.' || *cp == ',') 
+		{
+			fval = strtod(cp, &cp);
+			if (*cp != '\0')
+				return -1;
+
+			if (val < 0)
+				fval = -(fval);
+		}
+		else if (*cp == '\0')
+			fval = 0;
+		else
+			return -1;
+
+
+		if (datepart)
+		{
+			/*
+			 * All the 8601 unit specifiers are 1 character, but may
+			 * be followed by a 'T' character if transitioning between
+			 * the date part and the time part.  If it's not either
+			 * one character or two characters with the second being 't'
+			 * it's an error.
+			 */
+			if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
+				return -1;
+
+			if (units[1] == 't')
+				datepart = 0;
+
+			switch (units[0]) /* Y M D W */
+			{
+				case 'd':
+					tm->tm_mday += val;
+					if (fval != 0)
+					  adjust_fval(fval,tm,fsec, 86400);
+					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
+					break;
+
+				case 'w':
+					tm->tm_mday += val * 7;
+					if (fval != 0)
+					  adjust_fval(fval,tm,fsec,7 * 86400);
+					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
+					break;
+
+				case 'm':
+					tm->tm_mon += val;
+					if (fval != 0)
+					  adjust_fval(fval,tm,fsec,30 * 86400);
+					tmask = DTK_M(MONTH);
+					break;
+
+				case 'y':
+					/*
+					 * Why can fractional months produce seconds,
+					 * but fractional years can't?  Well the older
+					 * interval code below has the same property
+					 * so this one follows the other one too.
+					 */
+					tm->tm_year += val;
+					if (fval != 0)
+						tm->tm_mon += (fval * 12);
+					tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
+					break;
+
+				default:
+					return -1;  /* invald date unit prefix */
+			}
+		}
+		else
+		{
+			/*
+			 * ISO 8601 time part.
+			 * In the time part, only one-character
+			 * unit prefixes are allowed.  If it's more
+			 * than one character, it's not a valid ISO 8601
+			 * time interval by duration.
+			 */
+			if (units[1] != 0)
+				return -1;
+
+			switch (units[0]) /* H M S */
+			{
+				case 's':
+					tm->tm_sec += val;
+#ifdef HAVE_INT64_TIMESTAMP
+					*fsec += (fval * 1000000);
+#else
+					*fsec += fval;
+#endif
+					tmask = DTK_M(SECOND);
+					break;
+
+				case 'm':
+					tm->tm_min += val;
+					if (fval != 0)
+					  adjust_fval(fval,tm,fsec,60);
+					tmask = DTK_M(MINUTE);
+					break;
+
+				case 'h':
+					tm->tm_hour += val;
+					if (fval != 0)
+					  adjust_fval(fval,tm,fsec,3600);
+					tmask = DTK_M(HOUR);
+					break;
+
+				default:
+					return -1; /* invald time unit prefix */
+			}
+		}
+		fmask |= tmask;
+	}
+
+	if (*fsec != 0)
+	{
+		int			sec;
+
+#ifdef HAVE_INT64_TIMESTAMP
+		sec = (*fsec / INT64CONST(1000000));
+		*fsec -= (sec * INT64CONST(1000000));
+#else
+		TMODULO(*fsec, sec, 1e0);
+#endif
+		tm->tm_sec += sec;
+	}
+	return (fmask != 0) ? 0 : -1;
+}
+
+
 /* DecodeInterval()
  * Interpret previously parsed fields for general time interval.
  * Returns 0 if successful, DTERR code if bogus input detected.
  *
  * Allow "date" field DTK_DATE since this could be just
  *	an unsigned floating point number. - thomas 1997-11-16
  *
  * Allow ISO-style time span, with implicit units on number of days
  *	preceding an hh:mm:ss field. - thomas 1998-04-30
+ * 
+ * Allow ISO-8601 style "Representation of time-interval by duration only"
+ *  of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
  */
+
 int
 DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
 {
 	int			is_before = FALSE;
 	char	   *cp;
 	int			fmask = 0,
 				tmask,
 				type;
 	int			i;
 	int			dterr;
@@ -2906,20 +3148,37 @@
 
 	type = IGNORE_DTF;
 	tm->tm_year = 0;
 	tm->tm_mon = 0;
 	tm->tm_mday = 0;
 	tm->tm_hour = 0;
 	tm->tm_min = 0;
 	tm->tm_sec = 0;
 	*fsec = 0;
 
+	/*
+	 *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
+     *  time-interval by duration only." 
+	 *  Basic extended format:  PnYnMnDTnHnMnS
+	 *                          PnW
+	 *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
+	 *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
+	 *  Examples:  P1D  for 1 day
+	 *             PT1H for 1 hour
+	 *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
+	 *
+	 *  The first field is exactly "p" or "pt" it may be of this type.
+	 */
+	if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
+	    return 0;
+    }
+
 	/* read through list backwards to pick up units before values */
 	for (i = nf - 1; i >= 0; i--)
 	{
 		switch (ftype[i])
 		{
 			case DTK_TIME:
 				dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
 				if (dterr)
 					return dterr;
 				type = DTK_DAY;
@@ -2983,20 +3242,21 @@
 				}
 				/* DROP THROUGH */
 
 			case DTK_DATE:
 			case DTK_NUMBER:
 				val = strtol(field[i], &cp, 10);
 
 				if (type == IGNORE_DTF)
 					type = DTK_SECOND;
 
+				/* should this allow ',' for locales that use it ? */
 				if (*cp == '.')
 				{
 					fval = strtod(cp, &cp);
 					if (*cp != '\0')
 						return DTERR_BAD_FORMAT;
 
 					if (val < 0)
 						fval = -(fval);
 				}
 				else if (*cp == '\0')

===================================================================

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-08T05:47:17Z

"Ron Mayer" <ron@intervideo.com> writes:
>    Compared to the ISO 8601 time interval specification, the
>    postgresql interval syntax is quite verbose.  For example:

>      Postgresql interval:              ISO8601 Interval
>      ---------------------------------------------------
>      '1 year 6 months'                'P1Y6M'
>      '3 hours 25 minutes 42 seconds'  'PT3H25M42S'

Er, don't we support that already?  I know I saw code to support
something much like that syntax last time I looked into the datetime
routines.

			regards, tom lane

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

Andrew Dunstan <andrew@dunslane.net> — 2003-09-08T06:32:17Z

Is there a way of producing as well as reading this format? Or did I miss
something?

cheers

andrew

Ron Mayer said:
> Short summary:
>
>   This patch allows ISO 8601 "time intervals" using the "format
>   with time-unit designators" to specify postgresql "intervals".
>
>   Below I have (A) What these time intervals are, (B) What I
>   modified to support them, (C) Issues with intervals I want
>   to bring up, and (D) a patch supporting them.
>
>   It's helpful to me.  Any feedback is appreciated.  If you
>   did want to consider including it, let me know what to clean
>   up.  If not, I thought I'd just put it here if anyone else finds it
>   useful too.
>
>   Thanks for your time,
>
>      Ron Mayer
>
> Longer:
>
> (A) What these intervals are.
>
>   ISO 8601, the standard from which PostgreSQL gets some of it's  time
>   syntax, also has a specification for "time-intervals".
>
>   In particular, section 5.5.4.2 has a "Representation of
>   time-interval by duration only" which I believe maps
>   nicely to ISO intervals.
>
>   Compared to the ISO 8601 time interval specification, the
>   postgresql interval syntax is quite verbose.  For example:
>
>     Postgresql interval:              ISO8601 Interval
>     ---------------------------------------------------
>     '1 year 6 months'                'P1Y6M'
>     '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
>
>   Yeah, it's uglier, but it sure is short which can make
>   for quicker typing and shorter scripts, and if for some
>   strange reason you had an application using this format
>   it's nice not to have to translate.
>
>   The syntax is as follows:
>       Basic extended format:  PnYnMnDTnHnMnS
>                               PnW
>
>       Where everything before the "T" is a date-part and everything
>       after is a time-part.  W is for weeks.
>       In the date-part, Y=Year, M=Month,  D=Day
>       In the time-part, H=Hour, M=Minute, S=Second
>
>   Much more info can be found from the draft standard
>   ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
>   The final standard's only available for $$$ so I didn't
>   look at it.  Some other sites imply that this part didn't
>   change from the last draft to the standard.
>
>
> (B) This change was made by adding two functions to "datetime.c"
>    next to where DecodeInterval parses the normal interval syntax.
>
>    A total of 313 lines were added, including comments and sgml docs.
>    Of these only 136 are actual code, the rest, comments, whitespace,
>    etc.
>
>
>    One new function "DecodeISO8601Interval" follows the style of
>    "DecodeInterval" below it, and trys to strictly follow the ISO
>    syntax.  If it doesn't match, it'll return -1 and the old syntax
>    will be checked as before.
>
>    The first test (first character of the first field must be 'P',  and
>    second character must be 'T' or '\0') should be fast so I don't
>    think this will impact performance of existing code.
>
>
>    The second function ("adjust_fval") is just a small helper-function
>    to remove some of the cut&paste style that DecodeInterval used.
>
>    It seems to work.
>
=======================================================================
>    betadb=# select 'P1M15DT12H30M7S'::interval;
>            interval
>    ------------------------
>     1 mon 15 days 12:30:07
>    (1 row)
>
>    betadb=# select '1 month 15 days 12 hours 30 minutes 7
>    seconds'::interval;
> 	    interval
>    ------------------------
>    1 mon 15 days 12:30:07
>    (1 row)
>    =====================================================================
>
>
>
> (C) Open issues with intervals, and questions I'd like to ask.
>
>    1.  DecodeInterval seems to have a hardcoded '.' for specifying
>        fractional times.  ISO 8601 states that both '.' and ',' are ok,
>        but "of these, the comma is the preferred sign".
>
>        In DecodeISO8601Interval I loosened the test to allow
>        both but left it as it was in DecodeInterval.  Should
>        both be changed to make them more consistant?
>
>    2.  In "DecodeInterval", fractional weeks and fractional months
>        can produce seconds; but fractional years can not (rounded to
>        months).  I didn't understand the reasoning for this, so I left
>        it the same, and followed the same convention for
>        ISO intervals.  Should I change this?
>
>    3.  I could save a bunch of copy-paste-lines-of-code from the
>        pre-existing DecodeInterval by calling the adjust_fval helper
>        function.  The tradeoff is a few extra function-calls when
>        decoding an interval.  However I didn't want to risk changes to
>        the existing part unless you guys encourage me to do so.
>
>
> (D) The patch.
>
>
[snip]

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

Ron Mayer <ron@intervideo.com> — 2003-09-08T18:59:50Z

Tom wrote:
> "Ron Mayer" <ron@intervideo.com> writes:
> >    Compared to the ISO 8601 time interval specification, the
> >    postgresql interval syntax is quite verbose.  For example:
> 
> >      Postgresql interval:              ISO8601 Interval
> >      ---------------------------------------------------
> >      '1 year 6 months'                'P1Y6M'
> >      '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
> 
> Er, don't we support that already?  I know I saw code to support
> something much like that syntax last time I looked into the datetime
> routines.
> 

Nope.

Postgresql supports a rather bizzare shorthand that has a similar
syntax, but AFAICT, doesn't match ISO 8601 in any way that makes 
it practical.

A disclaimer,  I have the "Final Draft" (ISO/TC 154N 362 
of 2000-12-19) of the spec; but have not seen the official,
expensive, version. 
ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF

For example, if I read it right, I have differences
like this:

     Interval            ISO             Postgres
                         8601            shorthand
     -----------------------------------------------------
     '1 year 1 minute'   'P1YT1M'         '1Y1M'
     '1 year 1 month'    'P1Y1M'          N/A

The best part about the postgresql syntax is that
they omit the required 'P', so it's easy to differentiate
between the two.  :-)

Perhaps one could argue that the postgres shorthand should 
follow the ISO conventions, but I'd not want to break backward
compatability, incase someone out there is using '1H30M' and
expecting minutes instead of months.   If we didn't want to
support two syntaxes, I wouldn't mind eventually depricating
the less-standard one.

   Ron

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

Ron Mayer <ron@intervideo.com> — 2003-09-08T19:16:50Z

andrew@dunslane.net wrote:
> 
> Is there a way of producing as well as reading this format? Or did I miss
> something?

Not yet, but I'd be happy to add it.  

My immediate problem was having some 'P1Y6M' intervals to load. 
I posted this much largely because it was useful to me so might 
help others, and to see if it was of interest to others and 
get feedback on what else to change.

I'd be happy to make it produce the output, and have some style
questions for doing so.

I'd hate to trigger this output on the already-existing 'datestyle' 
of 'ISO', since that would break backward compatability.
I do notice that 8601 has both "basic" and "extended" formats.
The "basic format" is more terse ('19980115' instead of '1998-01-15').

Would it be useful if I added a 'datestyle' of 'ISO basic' which
would produce the most terse formats ('19980115' for dates, 
and 'P1Y1M' for intervals)?

   Ron

PS: What's the best inexpenive way for me to know if this changed 
at all between the final draft and the published standard? 

> Ron Mayer said:
> >   This patch allows ISO 8601 "time intervals" using the "format
> >   with time-unit designators" to specify postgresql "intervals".
> >...
> >   Much more info can be found from the draft standard
> >   ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> >   The final standard's only available for $$$ so I didn't
> >   look at it.

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-08T19:59:20Z

"Ron Mayer" <ron@intervideo.com> writes:
> Tom wrote:
>> Er, don't we support that already?

> Postgresql supports a rather bizzare shorthand that has a similar
> syntax, but AFAICT, doesn't match ISO 8601 in any way that makes 
> it practical.

Well, it's *supposed* to match ISO, AFAICT (the comments in the code
talk about "ISO dates").  Unless ISO has put out multiple specs that
cover this?

> Perhaps one could argue that the postgres shorthand should 
> follow the ISO conventions, but I'd not want to break backward
> compatability, incase someone out there is using '1H30M' and
> expecting minutes instead of months.

I doubt anyone is using it, because it's completely undocumented.
If we're going to support the real ISO spec, I'd suggest ripping
out any not-quite-there variant.  (Especially so noting that your
code seems a lot cleaner than the ptype stuff.)

The datetime code is kind of a mess right now, because Thomas Lockhart
walked away from the project while only partway through some significant
additions.  He left some incomplete features and quite a number of bugs
in new-and-untested code.  We've been gradually cleaning up the problems,
but if if you find something that doesn't seem to make sense, it's
likely a bug rather than anything we want to preserve.  In particular,
given the knowledge that it doesn't meet the ISO spec, I'd judge that
the existing code for the ISO shorthand was a work-in-progress.

			regards, tom lane

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-08T20:17:19Z

"Ron Mayer" <ron@intervideo.com> writes:
> Would it be useful if I added a 'datestyle' of 'ISO basic' which
> would produce the most terse formats ('19980115' for dates, 
> and 'P1Y1M' for intervals)?

I don't really care for using that name for it --- for one thing, you
couldn't do
	set datestyle to iso basic;
because of syntax limitations.  A one-word name is a much better idea.

Perhaps call it "compact" or "terse" datestyle?

> PS: What's the best inexpenive way for me to know if this changed 
> at all between the final draft and the published standard? 

ANSI sells PDFs of ISO specs at their online store
http://webstore.ansi.org/ansidocstore/default.asp
although it looks like they want $81 for 8601, which is not my idea of
"inexpensive".

Usually ISO final drafts differ very little from the published specs;
I think you could just work from the draft and no one would complain.

			regards, tom lane

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-09-08T20:27:16Z

Tom Lane wrote:
> > Perhaps one could argue that the postgres shorthand should 
> > follow the ISO conventions, but I'd not want to break backward
> > compatability, incase someone out there is using '1H30M' and
> > expecting minutes instead of months.
> 
> I doubt anyone is using it, because it's completely undocumented.
> If we're going to support the real ISO spec, I'd suggest ripping
> out any not-quite-there variant.  (Especially so noting that your
> code seems a lot cleaner than the ptype stuff.)

Agreed.  Let me put your code in the queue for 7.5.

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

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-09-08T20:29:56Z

This has been saved for the 7.5 release:

	http:/momjian.postgresql.org/cgi-bin/pgpatches2

Feel free to submit an updated patch that rips out the old syntax, as
discussed, or replace this patch with a more comprehensive one.

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

Ron Mayer wrote:
> Short summary:
> 
>    This patch allows ISO 8601 "time intervals" using the "format 
>    with time-unit designators" to specify postgresql "intervals".
> 
>    Below I have (A) What these time intervals are, (B) What I
>    modified to support them, (C) Issues with intervals I want
>    to bring up, and (D) a patch supporting them.
> 
>    It's helpful to me.  Any feedback is appreciated.  If you 
>    did want to consider including it, let me know what to clean 
>    up.  If not, I thought I'd just put it here if anyone else finds
>    it useful too.
> 
>    Thanks for your time,
>   
>       Ron Mayer
> 
> Longer:
> 
> (A) What these intervals are.
> 
>    ISO 8601, the standard from which PostgreSQL gets some of it's 
>    time syntax, also has a specification for "time-intervals".
>   
>    In particular, section 5.5.4.2 has a "Representation of
>    time-interval by duration only" which I believe maps
>    nicely to ISO intervals.
> 
>    Compared to the ISO 8601 time interval specification, the
>    postgresql interval syntax is quite verbose.  For example:
> 
>      Postgresql interval:              ISO8601 Interval
>      ---------------------------------------------------
>      '1 year 6 months'                'P1Y6M'
>      '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
> 
>    Yeah, it's uglier, but it sure is short which can make
>    for quicker typing and shorter scripts, and if for some
>    strange reason you had an application using this format
>    it's nice not to have to translate.
> 
>    The syntax is as follows:
>        Basic extended format:  PnYnMnDTnHnMnS
>                                PnW
> 
>        Where everything before the "T" is a date-part and everything
>        after is a time-part.  W is for weeks.
>        In the date-part, Y=Year, M=Month,  D=Day
>        In the time-part, H=Hour, M=Minute, S=Second
> 
>    Much more info can be found from the draft standard
>    ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
>    The final standard's only available for $$$ so I didn't
>    look at it.  Some other sites imply that this part didn't
>    change from the last draft to the standard.
> 
> 
> (B) This change was made by adding two functions to "datetime.c"
>     next to where DecodeInterval parses the normal interval syntax.
> 
>     A total of 313 lines were added, including comments and sgml docs.
>     Of these only 136 are actual code, the rest, comments, whitespace, etc.
> 
> 
>     One new function "DecodeISO8601Interval" follows the style of
>     "DecodeInterval" below it, and trys to strictly follow the ISO
>     syntax.  If it doesn't match, it'll return -1 and the old syntax
>     will be checked as before.
> 
>     The first test (first character of the first field must be 'P', 
>     and second character must be 'T' or '\0') should be fast so I don't
>     think this will impact performance of existing code.
> 
> 
>     The second function ("adjust_fval") is just a small helper-function
>     to remove some of the cut&paste style that DecodeInterval used.
> 
>     It seems to work.
>     =======================================================================
>     betadb=# select 'P1M15DT12H30M7S'::interval;
>             interval        
>     ------------------------
>      1 mon 15 days 12:30:07
>     (1 row)
> 
>     betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
> 	    interval        
>     ------------------------
>     1 mon 15 days 12:30:07
>     (1 row)
>     =====================================================================
> 
> 
> 
> (C) Open issues with intervals, and questions I'd like to ask.
> 
>     1.  DecodeInterval seems to have a hardcoded '.' for specifying
>         fractional times.  ISO 8601 states that both '.' and ',' are
>         ok, but "of these, the comma is the preferred sign".
> 
>         In DecodeISO8601Interval I loosened the test to allow
>         both but left it as it was in DecodeInterval.  Should
>         both be changed to make them more consistant?
> 
>     2.  In "DecodeInterval", fractional weeks and fractional months
>         can produce seconds; but fractional years can not (rounded
>         to months).  I didn't understand the reasoning for this, so
>         I left it the same, and followed the same convention for
>         ISO intervals.  Should I change this?
> 
>     3.  I could save a bunch of copy-paste-lines-of-code from the
>         pre-existing DecodeInterval by calling the adjust_fval helper
>         function.  The tradeoff is a few extra function-calls when
>         decoding an interval.  However I didn't want to risk changes
>         to the existing part unless you guys encourage me to do so.
> 
> 
> (D) The patch.
> 
> 
> Index: doc/src/sgml/datatype.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
> retrieving revision 1.123
> diff -u -1 -0 -r1.123 datatype.sgml
> --- doc/src/sgml/datatype.sgml	31 Aug 2003 17:32:18 -0000	1.123
> +++ doc/src/sgml/datatype.sgml	8 Sep 2003 04:04:58 -0000
> @@ -1735,20 +1735,71 @@
>        Quantities of days, hours, minutes, and seconds can be specified without
>        explicit unit markings.  For example, <literal>'1 12:59:10'</> is read
>        the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
>       </para>
>  
>       <para>
>        The optional precision
>        <replaceable>p</replaceable> should be between 0 and 6, and
>        defaults to the precision of the input literal.
>       </para>
> +
> +
> +     <para>
> +      Alternatively, <type>interval</type> values can be written as 
> +      ISO 8601 time intervals, using the "Format with time-unit designators".
> +      This format always starts with the character <literal>'P'</>, followed 
> +      by a string of values followed by single character time-unit designators.
> +      A <literal>'T'</> separates the date and time parts of the interval.
> +     </para>
> +
> +     <para>
> +       Format:  PnYnMnDTnHnMnS
> +     </para>
> +     <para>
> +       In this format, <literal>'n'</> gets replaced by a number, and 
> +       <literal>Y</> represents years, 
> +       <literal>M</> (in the date part) months,
> +       <literal>D</> months,
> +       <literal>H</> hours,
> +       <literal>M</> (in the time part) minutes,
> +       and <literal>S</> seconds.
> +     </para>
> +      
> +
> +     <table id="interval-example-table">
> +	   <title>Interval Example</title>
> +	   <tgroup cols="2">
> +		<thead>
> +		 <row>
> +		  <entry>Traditional</entry>
> +		  <entry>ISO-8601 time-interval</entry>
> +		 </row>
> +		</thead>
> +		<tbody>
> +		 <row>
> +		  <entry>1 month</entry>
> +		  <entry>P1M</entry>
> +		 </row>
> +		 <row>
> +		  <entry>1 hour 30 minutes</entry>
> +		  <entry>PT1H30M</entry>
> +		 </row>
> +		 <row>
> +		  <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
> +		  <entry>P2Y10M15DT10H30M20S</entry>
> +		 </row>
> +		</tbody>
> +	   </thead>
> +	  </table>
> +	  
> +     </para>
>      </sect3>
>  
>      <sect3>
>       <title>Special Values</title>
>  
>       <indexterm>
>        <primary>time</primary>
>        <secondary>constants</secondary>
>       </indexterm>
>  
> Index: src/backend/utils/adt/datetime.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
> retrieving revision 1.116
> diff -u -1 -0 -r1.116 datetime.c
> --- src/backend/utils/adt/datetime.c	27 Aug 2003 23:29:28 -0000	1.116
> +++ src/backend/utils/adt/datetime.c	8 Sep 2003 04:04:59 -0000
> @@ -30,20 +30,21 @@
>  			 struct tm * tm, fsec_t *fsec, int *is2digits);
>  static int DecodeNumberField(int len, char *str,
>  				  int fmask, int *tmask,
>  				  struct tm * tm, fsec_t *fsec, int *is2digits);
>  static int DecodeTime(char *str, int fmask, int *tmask,
>  		   struct tm * tm, fsec_t *fsec);
>  static int	DecodeTimezone(char *str, int *tzp);
>  static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
>  static int	DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
>  static void TrimTrailingZeros(char *str);
> +static int  DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
>  
>  
>  int			day_tab[2][13] = {
>  	{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
>  {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
>  
>  char	   *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
>  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
>  
>  char	   *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
> @@ -2872,30 +2873,271 @@
>  			default:
>  				*val = tp->value;
>  				break;
>  		}
>  	}
>  
>  	return type;
>  }
>  
>  
> +void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
> +{
> +	int	sec;
> +	fval	   *= scale;
> +	sec		    = fval;
> +	tm->tm_sec += sec;
> +#ifdef HAVE_INT64_TIMESTAMP
> +	*fsec	   += ((fval - sec) * 1000000);
> +#else
> +	*fsec	   += (fval - sec);
> +#endif
> +}
> +
> +
> +/* DecodeISO8601Interval()
> + *
> + *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> + *  time-interval by duration only." 
> + *  Basic extended format:  PnYnMnDTnHnMnS
> + *                          PnW
> + *  For more info.
> + *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
> + *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> + *
> + *  Examples:  P1D  for 1 day
> + *             PT1H for 1 hour
> + *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> + *
> + *  The first field is exactly "p" or "pt" it may be of this type.
> + *
> + *  Returns -1 if the field is not of this type.
> + *
> + *  It pretty strictly checks the spec, with the two exceptions
> + *  that a week field ('W') may coexist with other units, and that
> + *  this function allows decimals in fields other than the least
> + *  significant units.
> + */
> +int
> +DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec) 
> +{
> +	char	   *cp;
> +	int			fmask = 0,
> +				tmask;
> +	int			val;
> +	double		fval;
> +	int			arg;
> +	int			datepart;
> +
> +    /*
> +	 * An ISO 8601 "time-interval by duration only" must start
> +	 * with a 'P'.  If it contains a date-part, 'p' will be the
> +	 * only character in the field.  If it contains no date part
> +	 * it will contain exactly to characters 'PT' indicating a
> +	 * time part.
> +	 * Anything else is illegal and will be treated like a 
> +	 * traditional postgresql interval.
> +	 */
> +    if (!(field[0][0] == 'p' &&
> +          ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
> +	{
> +	  return -1;
> +	}
> +
> +
> +    /*
> +	 * If the first field is exactly 1 character ('P'), it starts
> +	 * with date elements.  Otherwise it's two characters ('PT');
> +	 * indicating it starts with a time part.
> +	 */
> +	datepart = (field[0][1] == 0);
> +
> +	/*
> +	 * Every value must have a unit, so we require an even
> +	 * number of value/unit pairs. Therefore we require an
> +	 * odd nubmer of fields, including the prefix 'P'.
> +	 */
> +	if ((nf & 1) == 0)
> +		return -1;
> +
> +	/*
> +	 * Process pairs of fields at a time.
> +	 */
> +	for (arg = 1 ; arg < nf ; arg+=2) 
> +	{
> +		char * value = field[arg  ];
> +		char * units = field[arg+1];
> +
> +		/*
> +		 * The value part must be a number.
> +		 */
> +		if (ftype[arg] != DTK_NUMBER) 
> +			return -1;
> +
> +		/*
> +		 * extract the number, almost exactly like the non-ISO interval.
> +		 */
> +		val = strtol(value, &cp, 10);
> +
> +		/*
> +		 * One difference from the normal postgresql interval below...
> +		 * ISO 8601 states that "Of these, the comma is the preferred 
> +		 * sign" so I allow it here for locales that support it.
> +		 * Note: Perhaps the old-style interval code below should
> +		 * allow for this too, but I didn't want to risk backward
> +		 * compatability.
> +		 */
> +		if (*cp == '.' || *cp == ',') 
> +		{
> +			fval = strtod(cp, &cp);
> +			if (*cp != '\0')
> +				return -1;
> +
> +			if (val < 0)
> +				fval = -(fval);
> +		}
> +		else if (*cp == '\0')
> +			fval = 0;
> +		else
> +			return -1;
> +
> +
> +		if (datepart)
> +		{
> +			/*
> +			 * All the 8601 unit specifiers are 1 character, but may
> +			 * be followed by a 'T' character if transitioning between
> +			 * the date part and the time part.  If it's not either
> +			 * one character or two characters with the second being 't'
> +			 * it's an error.
> +			 */
> +			if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
> +				return -1;
> +
> +			if (units[1] == 't')
> +				datepart = 0;
> +
> +			switch (units[0]) /* Y M D W */
> +			{
> +				case 'd':
> +					tm->tm_mday += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec, 86400);
> +					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> +					break;
> +
> +				case 'w':
> +					tm->tm_mday += val * 7;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,7 * 86400);
> +					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> +					break;
> +
> +				case 'm':
> +					tm->tm_mon += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,30 * 86400);
> +					tmask = DTK_M(MONTH);
> +					break;
> +
> +				case 'y':
> +					/*
> +					 * Why can fractional months produce seconds,
> +					 * but fractional years can't?  Well the older
> +					 * interval code below has the same property
> +					 * so this one follows the other one too.
> +					 */
> +					tm->tm_year += val;
> +					if (fval != 0)
> +						tm->tm_mon += (fval * 12);
> +					tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
> +					break;
> +
> +				default:
> +					return -1;  /* invald date unit prefix */
> +			}
> +		}
> +		else
> +		{
> +			/*
> +			 * ISO 8601 time part.
> +			 * In the time part, only one-character
> +			 * unit prefixes are allowed.  If it's more
> +			 * than one character, it's not a valid ISO 8601
> +			 * time interval by duration.
> +			 */
> +			if (units[1] != 0)
> +				return -1;
> +
> +			switch (units[0]) /* H M S */
> +			{
> +				case 's':
> +					tm->tm_sec += val;
> +#ifdef HAVE_INT64_TIMESTAMP
> +					*fsec += (fval * 1000000);
> +#else
> +					*fsec += fval;
> +#endif
> +					tmask = DTK_M(SECOND);
> +					break;
> +
> +				case 'm':
> +					tm->tm_min += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,60);
> +					tmask = DTK_M(MINUTE);
> +					break;
> +
> +				case 'h':
> +					tm->tm_hour += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,3600);
> +					tmask = DTK_M(HOUR);
> +					break;
> +
> +				default:
> +					return -1; /* invald time unit prefix */
> +			}
> +		}
> +		fmask |= tmask;
> +	}
> +
> +	if (*fsec != 0)
> +	{
> +		int			sec;
> +
> +#ifdef HAVE_INT64_TIMESTAMP
> +		sec = (*fsec / INT64CONST(1000000));
> +		*fsec -= (sec * INT64CONST(1000000));
> +#else
> +		TMODULO(*fsec, sec, 1e0);
> +#endif
> +		tm->tm_sec += sec;
> +	}
> +	return (fmask != 0) ? 0 : -1;
> +}
> +
> +
>  /* DecodeInterval()
>   * Interpret previously parsed fields for general time interval.
>   * Returns 0 if successful, DTERR code if bogus input detected.
>   *
>   * Allow "date" field DTK_DATE since this could be just
>   *	an unsigned floating point number. - thomas 1997-11-16
>   *
>   * Allow ISO-style time span, with implicit units on number of days
>   *	preceding an hh:mm:ss field. - thomas 1998-04-30
> + * 
> + * Allow ISO-8601 style "Representation of time-interval by duration only"
> + *  of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
>   */
> +
>  int
>  DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
>  {
>  	int			is_before = FALSE;
>  	char	   *cp;
>  	int			fmask = 0,
>  				tmask,
>  				type;
>  	int			i;
>  	int			dterr;
> @@ -2906,20 +3148,37 @@
>  
>  	type = IGNORE_DTF;
>  	tm->tm_year = 0;
>  	tm->tm_mon = 0;
>  	tm->tm_mday = 0;
>  	tm->tm_hour = 0;
>  	tm->tm_min = 0;
>  	tm->tm_sec = 0;
>  	*fsec = 0;
>  
> +	/*
> +	 *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> +     *  time-interval by duration only." 
> +	 *  Basic extended format:  PnYnMnDTnHnMnS
> +	 *                          PnW
> +	 *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
> +	 *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> +	 *  Examples:  P1D  for 1 day
> +	 *             PT1H for 1 hour
> +	 *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> +	 *
> +	 *  The first field is exactly "p" or "pt" it may be of this type.
> +	 */
> +	if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
> +	    return 0;
> +    }
> +
>  	/* read through list backwards to pick up units before values */
>  	for (i = nf - 1; i >= 0; i--)
>  	{
>  		switch (ftype[i])
>  		{
>  			case DTK_TIME:
>  				dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
>  				if (dterr)
>  					return dterr;
>  				type = DTK_DAY;
> @@ -2983,20 +3242,21 @@
>  				}
>  				/* DROP THROUGH */
>  
>  			case DTK_DATE:
>  			case DTK_NUMBER:
>  				val = strtol(field[i], &cp, 10);
>  
>  				if (type == IGNORE_DTF)
>  					type = DTK_SECOND;
>  
> +				/* should this allow ',' for locales that use it ? */
>  				if (*cp == '.')
>  				{
>  					fval = strtod(cp, &cp);
>  					if (*cp != '\0')
>  						return DTERR_BAD_FORMAT;
>  
>  					if (val < 0)
>  						fval = -(fval);
>  				}
>  				else if (*cp == '\0')
> 
> ===================================================================
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.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

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

Ron Mayer <ron@intervideo.com> — 2003-09-08T20:42:11Z

Tom wrote: 
> "Ron Mayer" <ron@intervideo.com> writes:
> > Tom wrote:
> >> Er, don't we support that already?
> > ...AFAICT, doesn't match ISO 8601...
> 
> Well, it's *supposed* to match ISO....  Unless ISO has put out 
> multiple specs that cover this?

Any way to tell if this is the case.  
8601's the one I see cited the most.

> > ...I'd not want to break backward compatability...'1H30M'
>
> I doubt anyone is using it, because it's completely undocumented.
> If we're going to support the real ISO spec, I'd suggest ripping
> out any not-quite-there variant.

I'm happy to look into it.  Rip out completely?  Ifdef? 

> We've been gradually cleaning up the problems, but if if you find 
> something that doesn't seem to make sense, it's likely a bug rather
> than anything we want to preserve. 

I've seen a few more cases that don't make sense.

For example "why is 0.001 years less than 0.001 months".

  betadb=# select '0.01 years'::interval
   interval
  ----------
   00:00:00

  betadb=# select '0.01 months'::interval
   interval
  ----------
   07:12:00

If I'm breaking backward compatability anyway, I'd be happy to tweak
things like this one too.  Unless, of course someone can give me a 
reason why we want fractional years rounded to months, but fractional 
months are rounded to fractions of a second.

   Ron Mayer.

PS: mailinglist etiquite question... for discussion, should I
    more this to hackers, or continue it here.

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-09-08T21:05:14Z

Ron Mayer wrote:
> If I'm breaking backward compatability anyway, I'd be happy to tweak
> things like this one too.  Unless, of course someone can give me a 
> reason why we want fractional years rounded to months, but fractional 
> months are rounded to fractions of a second.
> 
>    Ron Mayer.
> 
> PS: mailinglist etiquite question... for discussion, should I
>     more this to hackers, or continue it here.

Your choice, but you get a larger audience on hackers.  I usually keep
things on patches when I have lots of code to post, and other times move
to hackers.

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

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-08T21:19:13Z

"Ron Mayer" <ron@intervideo.com> writes:
> For example "why is 0.001 years less than 0.001 months".

And look at this:

regression=# select '0.99 years'::interval;
 interval
----------
 11 mons
(1 row)

regression=#  select '0.99 months'::interval;
     interval
------------------
 29 days 16:48:00
(1 row)

It kinda looks like fractional years are converted to integer months
(truncating) while fractional months are moved to the seconds part
of the interval.  Ick.  The handling ought to be consistent if you
ask me.

> If I'm breaking backward compatability anyway, I'd be happy to tweak
> things like this one too.  Unless, of course someone can give me a 
> reason why we want fractional years rounded to months, but fractional 
> months are rounded to fractions of a second.

Actually, what I'd like to see done with interval is re-implement it as
a three-field entity, separately storing months, days, and seconds.
The separation between months and smaller units is good because a month
isn't a fixed number of any smaller unit, but the same holds true for
days and smaller units (days are not always 24 hours, consider DST
transitions).  This would no doubt cause some backwards compatibility
problems, but overall it would fix many more cases than it breaks.
We see complaints about related issues regularly, every spring and fall...

I'm unsure whether fractional months or fractional days are sensible
to accept, but surely we should accept both or reject both.  (This might
suggest that the underlying storage for the month and day fields should
be float not int, btw, but I am not sure about it.)

> PS: mailinglist etiquite question... for discussion, should I
>     more this to hackers, or continue it here.

At this point it should move to pghackers, I think.

			regards, tom lane

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

Peter Eisentraut <peter_e@gmx.net> — 2003-09-08T21:58:09Z

Tom Lane writes:

> "Ron Mayer" <ron@intervideo.com> writes:
> > Would it be useful if I added a 'datestyle' of 'ISO basic' which
> > would produce the most terse formats ('19980115' for dates,
> > and 'P1Y1M' for intervals)?
>
> I don't really care for using that name for it --- for one thing, you
> couldn't do
> 	set datestyle to iso basic;
> because of syntax limitations.  A one-word name is a much better idea.

iso8601

Keep in mind that SQL itself is also a kind of ISO, so being more specific
is useful.

-- 
Peter Eisentraut   peter_e@gmx.net

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-08T22:06:11Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> I don't really care for using that name for it ---

> iso8601

> Keep in mind that SQL itself is also a kind of ISO, so being more specific
> is useful.

Yes, but by the same token "iso8601" isn't specific enough either.
Several of the other input formats we support have at least as good a
claim on that name.

			regards, tom lane

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

Peter Eisentraut <peter_e@gmx.net> — 2003-09-08T22:39:44Z

Tom Lane writes:

> Yes, but by the same token "iso8601" isn't specific enough either.
> Several of the other input formats we support have at least as good a
> claim on that name.

The only input formats we support are along the lines of

@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs
@ 1 year 2 mons 3 days 04:05:06

These are also the supported output formats (the first you get for 'sql',
'postgres', and 'german' formats; the second is 'iso').  A quick check of
ISO 8601 shows, however, that neither of these are close to anything
specified in that standard.

-- 
Peter Eisentraut   peter_e@gmx.net

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-08T22:45:03Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Yes, but by the same token "iso8601" isn't specific enough either.
>> Several of the other input formats we support have at least as good a
>> claim on that name.

> The only input formats we support are along the lines of

> @ 1 year 2 mons 3 days 4 hours 5 mins 6 secs
> @ 1 year 2 mons 3 days 04:05:06

Sorry, I was thinking of timestamp formats not intervals.
You're right that we don't have anything else particularly ISO-standard
for intervals, but my understanding is that formats like '2003-09-08
18:43:31.046283-04' are ISO8601 compatible for timestamps.  (Possibly
you need to put a T in there for strict compatibility, not sure.)

			regards, tom lane

Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with time-unit designators"

Ron Mayer <ron@intervideo.com> — 2003-09-08T23:05:38Z

Tom wrote...
> At this point it should move to pghackers, I think.

Background for pghackers first, open issues below...

   Over on pgpatches we've been discussing ISO syntax for
   time intervals of the format with time-unit designators.
    http://archives.postgresql.org/pgsql-patches/2003-09/msg00103.php
   A short summary is that Ive submitted a patch that
   accepts intervals of this format..
     Postgresql interval:              ISO8601 Interval
     ---------------------------------------------------
     '1 year 6 months'                'P1Y6M'
     '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
   The final draft is here
     ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF

   This patch was backward-compatable, but further improvements
   discussed on patches may break compatability so I wanted to
   discuss them here before implementing them.   Ill also
   be submitting a new datestyle iso8601 to output these intervals.

Open issues:

1. Postgresql supported a shorthand for intervals that had
   a similar, but not compatable syntax:
     Interval            ISO             Existing postgres
                         8601            shorthand
     -----------------------------------------------------
     '1 year 1 minute'   'P1YT1M'         '1Y1M'
     '1 year 1 month'    'P1Y1M'          N/A

   The current thinking of the thread in pgpatches is to remove
   the existing (undocumented) syntax.

   Removing this will break backward compatability if anyone
   used this feature.  Let me know if you needed it.

2. Some of the parsing for intervals is inconsistant and
   confusing.  For example, note that 0.01 years is
   less than 0.01 months.

  betadb=# select '0.01 month'::interval as hundredth_of_month,
  betadb-#        '0.01 year'::interval  as hundredth_of_year;
   hundredth_of_month | hundredth_of_year
  --------------------+-------------------
   07:12:00           | 00:00:00

   This occurs because the current interval parsing rounds
   fractional years to the month, but fractional months
   to the fraction of a second.

   The current thinking on the thread in patches is
   at the very least to make these consistant, but with
   some open-issues because months arent a fixed number
   of days, and days arent a fixed number of seconds.

   The easiest and most minimal change would be to assume
   that any fractional part automatically gets turned
   into seconds, assuming things like 30 seconds/month,
   24 hrs/day.  Since all units except years work that way
   today, itd would have the least impact on existing code.

   A probably better way that Tom recommended would remember
   fractional months and fractional days.  This has the
   advantage that unlike today,
     .5 months::interval + .5 months::interval
   would then equal 1 month.

   So what should .5 years be?

   Today, its 6 mons.  But I could just as easily
   argue that it should be 365.2425/2 days, or 4382.91
   seconds.  Each of these will be different (the last
   two are different durring daylight savings).

3. This all is based on the final draft standard of
   ISO 8601, but I havent seen the actual expensive
   standard.  If anyone has it handy...

   Also, Im curious to know what if anything the SQL
   spec says about intervals and units.  Any pointers.

  Ron

Any other interval annoyances I should hit at the same time?

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

Ron Mayer <ron@intervideo.com> — 2003-09-08T23:46:13Z

Tom wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Tom Lane writes:
> >> Yes, but by the same token "iso8601" isn't specific enough either.

ISO 8601 gives more specific names.

  ISO 8601 Basic Format:         P2Y10M15DT10H20M30S
  ISO 8601 Alternative Format:   P00021015T102030
  ISO 8601 Extended Format:      P0002-10-15T10:20:30

In a way, the Extended Format is kinda nice, since its
almost human readable.

I could put in both the basic and extended ones, and
call the dateformats iso8601basic and iso8601extended.
The negative is that to do iso8601basic right, Id also
have to tweak the date and time parts of the code too.

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-09T04:19:48Z

[ backtracking a little ]

"Ron Mayer" <ron@intervideo.com> writes:
> Tom wrote: 
>> I doubt anyone is using it, because it's completely undocumented.
>> If we're going to support the real ISO spec, I'd suggest ripping
>> out any not-quite-there variant.

> I'm happy to look into it.  Rip out completely?  Ifdef? 

"Rip" was what I had in mind --- the idea is to simplify the code,
which you will surely agree is too complicated as it stands.  ifdefs
won't simplify the code or make it more understandable, rather the
reverse.

I have no problem with complex code when it's needed, but in this case
the ptype implementation of almost-ISO notation seems to me to affect
much more of the code than it has any right to on a usefulness basis.

			regards, tom lane

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-09-26T22:49:08Z

Where did we leave this?

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

Ron Mayer wrote:
> 
> Tom wrote:
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > Tom Lane writes:
> > >> Yes, but by the same token "iso8601" isn't specific enough either.
> 
> ISO 8601 gives more specific names.
> 
>   ISO 8601 Basic Format:         P2Y10M15DT10H20M30S
>   ISO 8601 Alternative Format:   P00021015T102030
>   ISO 8601 Extended Format:      P0002-10-15T10:20:30
> 
> In a way, the Extended Format is kinda nice, since it?s
> almost human readable.
> 
> I could put in both the basic and extended ones, and
> call the dateformats ?iso8601basic? and ?iso8601extended?.
> The negative is that to do ?iso8601basic? right, I?d also
> have to tweak the ?date? and ?time? parts of the code too.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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

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

Tom Lane <tgl@sss.pgh.pa.us> — 2003-09-26T22:54:09Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Where did we leave this?

I thought it was proposed work for 7.5.

			regards, tom lane

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

Ron Mayer <ron@intervideo.com> — 2003-09-26T22:54:58Z

So far,

  I have submitted the input-part.

  I have a working output-part (attached below, but I'm
  still cleaning up the documentation so I'll submit another
  one later).  The output is chosen by setting
  the datestyle to 'iso8601basic'.

  Those two changes don't break backward compatability
  but don't fix too much odd behavior except ISO time interval I/O.

  I was encouraged to look into changing the way timestamp
  math is done (keeping month, and day, and second separate
  until the end).  This is a bigger change and I don't have
  a stable version yet, and it breaks backward compatability. 
  I hope to submit a proposal for changes early enough in the
  7.5 timeframe to submit fixes then as well.  At the very least
  I will fully document the existing interval-math as part of
  this proposal so the docs can be updated even if the proposal
  gets rejected.

     Ron

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, September 26, 2003 3:54 PM
> To: Bruce Momjian
> Cc: Ron Mayer; Peter Eisentraut; andrew@dunslane.net;
> pgsql-patches@postgresql.org
> Subject: Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with
> time-unit deignators' 
> 
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Where did we leave this?
> 
> I thought it was proposed work for 7.5.
> 
> 			regards, tom lane
> 
>

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

Ron Mayer <ron@intervideo.com> — 2003-09-26T23:04:37Z

>   I have a working output-part (attached below, but I'm
>   still cleaning up the documentation so I'll submit another
>   one later)

Ugh.  Something in this pc quoted some characters in the attachment.
Rather than trying to apply it, wait a couple days and I'll submit
an update where the docs match.

  Ron

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

Ron Mayer <ron@intervideo.com> — 2003-09-26T23:04:37Z

Oh... and I'm pretty sure noone expects this before 7.5. :-)
Looks like I'm  the only one with input files that have these 
wierd-but-iso8601 'P1Y6M' and 'PT30M' inputs; so I can just 
use my own patch. :-)


> 
> So far,
> 
>   I have submitted the input-part.
> 
>   I have a working output-part (attached below, but I'm
>   still cleaning up the documentation so I'll submit another
>   one later).  The output is chosen by setting
>   the datestyle to 'iso8601basic'.
> 
>   Those two changes don't break backward compatability
>   but don't fix too much odd behavior except ISO time interval I/O.
> 
> 
>   I was encouraged to look into changing the way timestamp
>   math is done (keeping month, and day, and second separate
>   until the end).  This is a bigger change and I don't have
>   a stable version yet, and it breaks backward compatability. 
>   I hope to submit a proposal for changes early enough in the
>   7.5 timeframe to submit fixes then as well.  At the very least
>   I will fully document the existing interval-math as part of
>   this proposal so the docs can be updated even if the proposal
>   gets rejected.
> 
>      Ron
> 
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Friday, September 26, 2003 3:54 PM
> > To: Bruce Momjian
> > Cc: Ron Mayer; Peter Eisentraut; andrew@dunslane.net;
> > pgsql-patches@postgresql.org
> > Subject: Re: [PATCHES] ISO 8601 'Time Intervals' of the 'format with
> > time-unit deignators' 
> > 
> > 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Where did we leave this?
> > 
> > I thought it was proposed work for 7.5.
> > 
> > 			regards, tom lane
> > 
> >

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-01T04:52:10Z

Is this ready for application?  It looks good to me.  However, there is
an "Open issues" section.

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

Ron Mayer wrote:
> Short summary:
> 
>    This patch allows ISO 8601 "time intervals" using the "format 
>    with time-unit designators" to specify postgresql "intervals".
> 
>    Below I have (A) What these time intervals are, (B) What I
>    modified to support them, (C) Issues with intervals I want
>    to bring up, and (D) a patch supporting them.
> 
>    It's helpful to me.  Any feedback is appreciated.  If you 
>    did want to consider including it, let me know what to clean 
>    up.  If not, I thought I'd just put it here if anyone else finds
>    it useful too.
> 
>    Thanks for your time,
>   
>       Ron Mayer
> 
> Longer:
> 
> (A) What these intervals are.
> 
>    ISO 8601, the standard from which PostgreSQL gets some of it's 
>    time syntax, also has a specification for "time-intervals".
>   
>    In particular, section 5.5.4.2 has a "Representation of
>    time-interval by duration only" which I believe maps
>    nicely to ISO intervals.
> 
>    Compared to the ISO 8601 time interval specification, the
>    postgresql interval syntax is quite verbose.  For example:
> 
>      Postgresql interval:              ISO8601 Interval
>      ---------------------------------------------------
>      '1 year 6 months'                'P1Y6M'
>      '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
> 
>    Yeah, it's uglier, but it sure is short which can make
>    for quicker typing and shorter scripts, and if for some
>    strange reason you had an application using this format
>    it's nice not to have to translate.
> 
>    The syntax is as follows:
>        Basic extended format:  PnYnMnDTnHnMnS
>                                PnW
> 
>        Where everything before the "T" is a date-part and everything
>        after is a time-part.  W is for weeks.
>        In the date-part, Y=Year, M=Month,  D=Day
>        In the time-part, H=Hour, M=Minute, S=Second
> 
>    Much more info can be found from the draft standard
>    ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
>    The final standard's only available for $$$ so I didn't
>    look at it.  Some other sites imply that this part didn't
>    change from the last draft to the standard.
> 
> 
> (B) This change was made by adding two functions to "datetime.c"
>     next to where DecodeInterval parses the normal interval syntax.
> 
>     A total of 313 lines were added, including comments and sgml docs.
>     Of these only 136 are actual code, the rest, comments, whitespace, etc.
> 
> 
>     One new function "DecodeISO8601Interval" follows the style of
>     "DecodeInterval" below it, and trys to strictly follow the ISO
>     syntax.  If it doesn't match, it'll return -1 and the old syntax
>     will be checked as before.
> 
>     The first test (first character of the first field must be 'P', 
>     and second character must be 'T' or '\0') should be fast so I don't
>     think this will impact performance of existing code.
> 
> 
>     The second function ("adjust_fval") is just a small helper-function
>     to remove some of the cut&paste style that DecodeInterval used.
> 
>     It seems to work.
>     =======================================================================
>     betadb=# select 'P1M15DT12H30M7S'::interval;
>             interval        
>     ------------------------
>      1 mon 15 days 12:30:07
>     (1 row)
> 
>     betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
> 	    interval        
>     ------------------------
>     1 mon 15 days 12:30:07
>     (1 row)
>     =====================================================================
> 
> 
> 
> (C) Open issues with intervals, and questions I'd like to ask.
> 
>     1.  DecodeInterval seems to have a hardcoded '.' for specifying
>         fractional times.  ISO 8601 states that both '.' and ',' are
>         ok, but "of these, the comma is the preferred sign".
> 
>         In DecodeISO8601Interval I loosened the test to allow
>         both but left it as it was in DecodeInterval.  Should
>         both be changed to make them more consistant?
> 
>     2.  In "DecodeInterval", fractional weeks and fractional months
>         can produce seconds; but fractional years can not (rounded
>         to months).  I didn't understand the reasoning for this, so
>         I left it the same, and followed the same convention for
>         ISO intervals.  Should I change this?
> 
>     3.  I could save a bunch of copy-paste-lines-of-code from the
>         pre-existing DecodeInterval by calling the adjust_fval helper
>         function.  The tradeoff is a few extra function-calls when
>         decoding an interval.  However I didn't want to risk changes
>         to the existing part unless you guys encourage me to do so.
> 
> 
> (D) The patch.
> 
> 
> Index: doc/src/sgml/datatype.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
> retrieving revision 1.123
> diff -u -1 -0 -r1.123 datatype.sgml
> --- doc/src/sgml/datatype.sgml	31 Aug 2003 17:32:18 -0000	1.123
> +++ doc/src/sgml/datatype.sgml	8 Sep 2003 04:04:58 -0000
> @@ -1735,20 +1735,71 @@
>        Quantities of days, hours, minutes, and seconds can be specified without
>        explicit unit markings.  For example, <literal>'1 12:59:10'</> is read
>        the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
>       </para>
>  
>       <para>
>        The optional precision
>        <replaceable>p</replaceable> should be between 0 and 6, and
>        defaults to the precision of the input literal.
>       </para>
> +
> +
> +     <para>
> +      Alternatively, <type>interval</type> values can be written as 
> +      ISO 8601 time intervals, using the "Format with time-unit designators".
> +      This format always starts with the character <literal>'P'</>, followed 
> +      by a string of values followed by single character time-unit designators.
> +      A <literal>'T'</> separates the date and time parts of the interval.
> +     </para>
> +
> +     <para>
> +       Format:  PnYnMnDTnHnMnS
> +     </para>
> +     <para>
> +       In this format, <literal>'n'</> gets replaced by a number, and 
> +       <literal>Y</> represents years, 
> +       <literal>M</> (in the date part) months,
> +       <literal>D</> months,
> +       <literal>H</> hours,
> +       <literal>M</> (in the time part) minutes,
> +       and <literal>S</> seconds.
> +     </para>
> +      
> +
> +     <table id="interval-example-table">
> +	   <title>Interval Example</title>
> +	   <tgroup cols="2">
> +		<thead>
> +		 <row>
> +		  <entry>Traditional</entry>
> +		  <entry>ISO-8601 time-interval</entry>
> +		 </row>
> +		</thead>
> +		<tbody>
> +		 <row>
> +		  <entry>1 month</entry>
> +		  <entry>P1M</entry>
> +		 </row>
> +		 <row>
> +		  <entry>1 hour 30 minutes</entry>
> +		  <entry>PT1H30M</entry>
> +		 </row>
> +		 <row>
> +		  <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
> +		  <entry>P2Y10M15DT10H30M20S</entry>
> +		 </row>
> +		</tbody>
> +	   </thead>
> +	  </table>
> +	  
> +     </para>
>      </sect3>
>  
>      <sect3>
>       <title>Special Values</title>
>  
>       <indexterm>
>        <primary>time</primary>
>        <secondary>constants</secondary>
>       </indexterm>
>  
> Index: src/backend/utils/adt/datetime.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
> retrieving revision 1.116
> diff -u -1 -0 -r1.116 datetime.c
> --- src/backend/utils/adt/datetime.c	27 Aug 2003 23:29:28 -0000	1.116
> +++ src/backend/utils/adt/datetime.c	8 Sep 2003 04:04:59 -0000
> @@ -30,20 +30,21 @@
>  			 struct tm * tm, fsec_t *fsec, int *is2digits);
>  static int DecodeNumberField(int len, char *str,
>  				  int fmask, int *tmask,
>  				  struct tm * tm, fsec_t *fsec, int *is2digits);
>  static int DecodeTime(char *str, int fmask, int *tmask,
>  		   struct tm * tm, fsec_t *fsec);
>  static int	DecodeTimezone(char *str, int *tzp);
>  static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
>  static int	DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
>  static void TrimTrailingZeros(char *str);
> +static int  DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
>  
>  
>  int			day_tab[2][13] = {
>  	{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
>  {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
>  
>  char	   *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
>  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
>  
>  char	   *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
> @@ -2872,30 +2873,271 @@
>  			default:
>  				*val = tp->value;
>  				break;
>  		}
>  	}
>  
>  	return type;
>  }
>  
>  
> +void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
> +{
> +	int	sec;
> +	fval	   *= scale;
> +	sec		    = fval;
> +	tm->tm_sec += sec;
> +#ifdef HAVE_INT64_TIMESTAMP
> +	*fsec	   += ((fval - sec) * 1000000);
> +#else
> +	*fsec	   += (fval - sec);
> +#endif
> +}
> +
> +
> +/* DecodeISO8601Interval()
> + *
> + *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> + *  time-interval by duration only." 
> + *  Basic extended format:  PnYnMnDTnHnMnS
> + *                          PnW
> + *  For more info.
> + *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
> + *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> + *
> + *  Examples:  P1D  for 1 day
> + *             PT1H for 1 hour
> + *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> + *
> + *  The first field is exactly "p" or "pt" it may be of this type.
> + *
> + *  Returns -1 if the field is not of this type.
> + *
> + *  It pretty strictly checks the spec, with the two exceptions
> + *  that a week field ('W') may coexist with other units, and that
> + *  this function allows decimals in fields other than the least
> + *  significant units.
> + */
> +int
> +DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec) 
> +{
> +	char	   *cp;
> +	int			fmask = 0,
> +				tmask;
> +	int			val;
> +	double		fval;
> +	int			arg;
> +	int			datepart;
> +
> +    /*
> +	 * An ISO 8601 "time-interval by duration only" must start
> +	 * with a 'P'.  If it contains a date-part, 'p' will be the
> +	 * only character in the field.  If it contains no date part
> +	 * it will contain exactly to characters 'PT' indicating a
> +	 * time part.
> +	 * Anything else is illegal and will be treated like a 
> +	 * traditional postgresql interval.
> +	 */
> +    if (!(field[0][0] == 'p' &&
> +          ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
> +	{
> +	  return -1;
> +	}
> +
> +
> +    /*
> +	 * If the first field is exactly 1 character ('P'), it starts
> +	 * with date elements.  Otherwise it's two characters ('PT');
> +	 * indicating it starts with a time part.
> +	 */
> +	datepart = (field[0][1] == 0);
> +
> +	/*
> +	 * Every value must have a unit, so we require an even
> +	 * number of value/unit pairs. Therefore we require an
> +	 * odd nubmer of fields, including the prefix 'P'.
> +	 */
> +	if ((nf & 1) == 0)
> +		return -1;
> +
> +	/*
> +	 * Process pairs of fields at a time.
> +	 */
> +	for (arg = 1 ; arg < nf ; arg+=2) 
> +	{
> +		char * value = field[arg  ];
> +		char * units = field[arg+1];
> +
> +		/*
> +		 * The value part must be a number.
> +		 */
> +		if (ftype[arg] != DTK_NUMBER) 
> +			return -1;
> +
> +		/*
> +		 * extract the number, almost exactly like the non-ISO interval.
> +		 */
> +		val = strtol(value, &cp, 10);
> +
> +		/*
> +		 * One difference from the normal postgresql interval below...
> +		 * ISO 8601 states that "Of these, the comma is the preferred 
> +		 * sign" so I allow it here for locales that support it.
> +		 * Note: Perhaps the old-style interval code below should
> +		 * allow for this too, but I didn't want to risk backward
> +		 * compatability.
> +		 */
> +		if (*cp == '.' || *cp == ',') 
> +		{
> +			fval = strtod(cp, &cp);
> +			if (*cp != '\0')
> +				return -1;
> +
> +			if (val < 0)
> +				fval = -(fval);
> +		}
> +		else if (*cp == '\0')
> +			fval = 0;
> +		else
> +			return -1;
> +
> +
> +		if (datepart)
> +		{
> +			/*
> +			 * All the 8601 unit specifiers are 1 character, but may
> +			 * be followed by a 'T' character if transitioning between
> +			 * the date part and the time part.  If it's not either
> +			 * one character or two characters with the second being 't'
> +			 * it's an error.
> +			 */
> +			if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
> +				return -1;
> +
> +			if (units[1] == 't')
> +				datepart = 0;
> +
> +			switch (units[0]) /* Y M D W */
> +			{
> +				case 'd':
> +					tm->tm_mday += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec, 86400);
> +					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> +					break;
> +
> +				case 'w':
> +					tm->tm_mday += val * 7;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,7 * 86400);
> +					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> +					break;
> +
> +				case 'm':
> +					tm->tm_mon += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,30 * 86400);
> +					tmask = DTK_M(MONTH);
> +					break;
> +
> +				case 'y':
> +					/*
> +					 * Why can fractional months produce seconds,
> +					 * but fractional years can't?  Well the older
> +					 * interval code below has the same property
> +					 * so this one follows the other one too.
> +					 */
> +					tm->tm_year += val;
> +					if (fval != 0)
> +						tm->tm_mon += (fval * 12);
> +					tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
> +					break;
> +
> +				default:
> +					return -1;  /* invald date unit prefix */
> +			}
> +		}
> +		else
> +		{
> +			/*
> +			 * ISO 8601 time part.
> +			 * In the time part, only one-character
> +			 * unit prefixes are allowed.  If it's more
> +			 * than one character, it's not a valid ISO 8601
> +			 * time interval by duration.
> +			 */
> +			if (units[1] != 0)
> +				return -1;
> +
> +			switch (units[0]) /* H M S */
> +			{
> +				case 's':
> +					tm->tm_sec += val;
> +#ifdef HAVE_INT64_TIMESTAMP
> +					*fsec += (fval * 1000000);
> +#else
> +					*fsec += fval;
> +#endif
> +					tmask = DTK_M(SECOND);
> +					break;
> +
> +				case 'm':
> +					tm->tm_min += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,60);
> +					tmask = DTK_M(MINUTE);
> +					break;
> +
> +				case 'h':
> +					tm->tm_hour += val;
> +					if (fval != 0)
> +					  adjust_fval(fval,tm,fsec,3600);
> +					tmask = DTK_M(HOUR);
> +					break;
> +
> +				default:
> +					return -1; /* invald time unit prefix */
> +			}
> +		}
> +		fmask |= tmask;
> +	}
> +
> +	if (*fsec != 0)
> +	{
> +		int			sec;
> +
> +#ifdef HAVE_INT64_TIMESTAMP
> +		sec = (*fsec / INT64CONST(1000000));
> +		*fsec -= (sec * INT64CONST(1000000));
> +#else
> +		TMODULO(*fsec, sec, 1e0);
> +#endif
> +		tm->tm_sec += sec;
> +	}
> +	return (fmask != 0) ? 0 : -1;
> +}
> +
> +
>  /* DecodeInterval()
>   * Interpret previously parsed fields for general time interval.
>   * Returns 0 if successful, DTERR code if bogus input detected.
>   *
>   * Allow "date" field DTK_DATE since this could be just
>   *	an unsigned floating point number. - thomas 1997-11-16
>   *
>   * Allow ISO-style time span, with implicit units on number of days
>   *	preceding an hh:mm:ss field. - thomas 1998-04-30
> + * 
> + * Allow ISO-8601 style "Representation of time-interval by duration only"
> + *  of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
>   */
> +
>  int
>  DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
>  {
>  	int			is_before = FALSE;
>  	char	   *cp;
>  	int			fmask = 0,
>  				tmask,
>  				type;
>  	int			i;
>  	int			dterr;
> @@ -2906,20 +3148,37 @@
>  
>  	type = IGNORE_DTF;
>  	tm->tm_year = 0;
>  	tm->tm_mon = 0;
>  	tm->tm_mday = 0;
>  	tm->tm_hour = 0;
>  	tm->tm_min = 0;
>  	tm->tm_sec = 0;
>  	*fsec = 0;
>  
> +	/*
> +	 *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> +     *  time-interval by duration only." 
> +	 *  Basic extended format:  PnYnMnDTnHnMnS
> +	 *                          PnW
> +	 *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
> +	 *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> +	 *  Examples:  P1D  for 1 day
> +	 *             PT1H for 1 hour
> +	 *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> +	 *
> +	 *  The first field is exactly "p" or "pt" it may be of this type.
> +	 */
> +	if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
> +	    return 0;
> +    }
> +
>  	/* read through list backwards to pick up units before values */
>  	for (i = nf - 1; i >= 0; i--)
>  	{
>  		switch (ftype[i])
>  		{
>  			case DTK_TIME:
>  				dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
>  				if (dterr)
>  					return dterr;
>  				type = DTK_DAY;
> @@ -2983,20 +3242,21 @@
>  				}
>  				/* DROP THROUGH */
>  
>  			case DTK_DATE:
>  			case DTK_NUMBER:
>  				val = strtol(field[i], &cp, 10);
>  
>  				if (type == IGNORE_DTF)
>  					type = DTK_SECOND;
>  
> +				/* should this allow ',' for locales that use it ? */
>  				if (*cp == '.')
>  				{
>  					fval = strtod(cp, &cp);
>  					if (*cp != '\0')
>  						return DTERR_BAD_FORMAT;
>  
>  					if (val < 0)
>  						fval = -(fval);
>  				}
>  				else if (*cp == '\0')
> 
> ===================================================================
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.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

Re: [PATCHES] ISO 8601 "Time Intervals" of the "format with

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-01T04:53:13Z

Here is an email on the open issues.

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

Ron Mayer wrote:
> Tom wrote...
> > At this point it should move to pghackers, I think.
> 
> Background for pghackers first, open issues below...
> 
>    Over on pgpatches we've been discussing ISO syntax for
>    ?time intervals? of the ?format with time-unit designators?.
>     http://archives.postgresql.org/pgsql-patches/2003-09/msg00103.php
>    A short summary is that I?ve submitted a patch that
>    accepts intervals of this format..
>      Postgresql interval:              ISO8601 Interval
>      ---------------------------------------------------
>      '1 year 6 months'                'P1Y6M'
>      '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
>    The final draft is here
>      ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> 
>    This patch was backward-compatable, but further improvements
>    discussed on patches may break compatability so I wanted to
>    discuss them here before implementing them.   I?ll also
>    be submitting a new datestyle ?iso8601? to output these intervals.
> 
> Open issues:
> 
> 1. Postgresql supported a shorthand for intervals that had
>    a similar, but not compatable syntax:
>      Interval            ISO             Existing postgres
>                          8601            shorthand
>      -----------------------------------------------------
>      '1 year 1 minute'   'P1YT1M'         '1Y1M'
>      '1 year 1 month'    'P1Y1M'          N/A
> 
>    The current thinking of the thread in pgpatches is to remove
>    the existing (undocumented) syntax.
> 
>    Removing this will break backward compatability if anyone
>    used this feature.  Let me know if you needed it.
> 
> 2. Some of the parsing for intervals is inconsistant and
>    confusing.  For example, note that ?0.01 years? is
>    less than ?0.01 months?.
> 
>   betadb=# select '0.01 month'::interval as hundredth_of_month,
>   betadb-#        '0.01 year'::interval  as hundredth_of_year;
>    hundredth_of_month | hundredth_of_year
>   --------------------+-------------------
>    07:12:00           | 00:00:00
> 
>    This occurs because the current interval parsing rounds
>    fractional years to the month, but fractional months
>    to the fraction of a second.
> 
>    The current thinking on the thread in patches is
>    at the very least to make these consistant, but with
>    some open-issues because months aren?t a fixed number
>    of days, and days aren?t a fixed number of seconds.
> 
>    The easiest and most minimal change would be to assume
>    that any fractional part automatically gets turned
>    into seconds, assuming things like 30 seconds/month,
>    24 hrs/day.  Since all units except years work that way
>    today, it?d would have the least impact on existing code.
> 
>    A probably better way that Tom recommended would remember
>    fractional months and fractional days.  This has the
>    advantage that unlike today,
>      ?.5 months?::interval + ?.5 months?::interval
>    would then equal 1 month.
> 
>    So what should ?.5 years? be?
> 
>    Today, it?s ?6 mons?.  But I could just as easily
>    argue that it should be 365.2425/2 days, or 4382.91
>    seconds.  Each of these will be different (the last
>    two are different durring daylight savings).
> 
> 3. This all is based on the final draft standard of
>    ISO 8601, but I haven?t seen the actual expensive
>    standard.  If anyone has it handy...
> 
>    Also, I?m curious to know what if anything the SQL
>    spec says about intervals and units.  Any pointers.
> 
>   Ron
> 
> Any other interval annoyances I should hit at the same time?
> 
> 
> ---------------------------(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

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-01T04:53:22Z

And another open issues email.

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

Ron Mayer wrote:
> 
> Tom wrote:
> > Peter Eisentraut <peter_e@gmx.net> writes:
> > > Tom Lane writes:
> > >> Yes, but by the same token "iso8601" isn't specific enough either.
> 
> ISO 8601 gives more specific names.
> 
>   ISO 8601 Basic Format:         P2Y10M15DT10H20M30S
>   ISO 8601 Alternative Format:   P00021015T102030
>   ISO 8601 Extended Format:      P0002-10-15T10:20:30
> 
> In a way, the Extended Format is kinda nice, since it?s
> almost human readable.
> 
> I could put in both the basic and extended ones, and
> call the dateformats ?iso8601basic? and ?iso8601extended?.
> The negative is that to do ?iso8601basic? right, I?d also
> have to tweak the ?date? and ?time? parts of the code too.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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

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

Peter Eisentraut <peter_e@gmx.net> — 2003-12-01T06:20:21Z

Bruce Momjian writes:

> Is this ready for application?  It looks good to me.  However, there is
> an "Open issues" section.

It would be more useful to implement the SQL standard for intervals first
instead of inventing more nonstandard formats for it.

>
> ---------------------------------------------------------------------------
>
> Ron Mayer wrote:
> > Short summary:
> >
> >    This patch allows ISO 8601 "time intervals" using the "format
> >    with time-unit designators" to specify postgresql "intervals".
> >
> >    Below I have (A) What these time intervals are, (B) What I
> >    modified to support them, (C) Issues with intervals I want
> >    to bring up, and (D) a patch supporting them.
> >
> >    It's helpful to me.  Any feedback is appreciated.  If you
> >    did want to consider including it, let me know what to clean
> >    up.  If not, I thought I'd just put it here if anyone else finds
> >    it useful too.
> >
> >    Thanks for your time,
> >
> >       Ron Mayer
> >
> > Longer:
> >
> > (A) What these intervals are.
> >
> >    ISO 8601, the standard from which PostgreSQL gets some of it's
> >    time syntax, also has a specification for "time-intervals".
> >
> >    In particular, section 5.5.4.2 has a "Representation of
> >    time-interval by duration only" which I believe maps
> >    nicely to ISO intervals.
> >
> >    Compared to the ISO 8601 time interval specification, the
> >    postgresql interval syntax is quite verbose.  For example:
> >
> >      Postgresql interval:              ISO8601 Interval
> >      ---------------------------------------------------
> >      '1 year 6 months'                'P1Y6M'
> >      '3 hours 25 minutes 42 seconds'  'PT3H25M42S'
> >
> >    Yeah, it's uglier, but it sure is short which can make
> >    for quicker typing and shorter scripts, and if for some
> >    strange reason you had an application using this format
> >    it's nice not to have to translate.
> >
> >    The syntax is as follows:
> >        Basic extended format:  PnYnMnDTnHnMnS
> >                                PnW
> >
> >        Where everything before the "T" is a date-part and everything
> >        after is a time-part.  W is for weeks.
> >        In the date-part, Y=Year, M=Month,  D=Day
> >        In the time-part, H=Hour, M=Minute, S=Second
> >
> >    Much more info can be found from the draft standard
> >    ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> >    The final standard's only available for $$$ so I didn't
> >    look at it.  Some other sites imply that this part didn't
> >    change from the last draft to the standard.
> >
> >
> > (B) This change was made by adding two functions to "datetime.c"
> >     next to where DecodeInterval parses the normal interval syntax.
> >
> >     A total of 313 lines were added, including comments and sgml docs.
> >     Of these only 136 are actual code, the rest, comments, whitespace, etc.
> >
> >
> >     One new function "DecodeISO8601Interval" follows the style of
> >     "DecodeInterval" below it, and trys to strictly follow the ISO
> >     syntax.  If it doesn't match, it'll return -1 and the old syntax
> >     will be checked as before.
> >
> >     The first test (first character of the first field must be 'P',
> >     and second character must be 'T' or '\0') should be fast so I don't
> >     think this will impact performance of existing code.
> >
> >
> >     The second function ("adjust_fval") is just a small helper-function
> >     to remove some of the cut&paste style that DecodeInterval used.
> >
> >     It seems to work.
> >     =======================================================================
> >     betadb=# select 'P1M15DT12H30M7S'::interval;
> >             interval
> >     ------------------------
> >      1 mon 15 days 12:30:07
> >     (1 row)
> >
> >     betadb=# select '1 month 15 days 12 hours 30 minutes 7 seconds'::interval;
> > 	    interval
> >     ------------------------
> >     1 mon 15 days 12:30:07
> >     (1 row)
> >     =====================================================================
> >
> >
> >
> > (C) Open issues with intervals, and questions I'd like to ask.
> >
> >     1.  DecodeInterval seems to have a hardcoded '.' for specifying
> >         fractional times.  ISO 8601 states that both '.' and ',' are
> >         ok, but "of these, the comma is the preferred sign".
> >
> >         In DecodeISO8601Interval I loosened the test to allow
> >         both but left it as it was in DecodeInterval.  Should
> >         both be changed to make them more consistant?
> >
> >     2.  In "DecodeInterval", fractional weeks and fractional months
> >         can produce seconds; but fractional years can not (rounded
> >         to months).  I didn't understand the reasoning for this, so
> >         I left it the same, and followed the same convention for
> >         ISO intervals.  Should I change this?
> >
> >     3.  I could save a bunch of copy-paste-lines-of-code from the
> >         pre-existing DecodeInterval by calling the adjust_fval helper
> >         function.  The tradeoff is a few extra function-calls when
> >         decoding an interval.  However I didn't want to risk changes
> >         to the existing part unless you guys encourage me to do so.
> >
> >
> > (D) The patch.
> >
> >
> > Index: doc/src/sgml/datatype.sgml
> > ===================================================================
> > RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/datatype.sgml,v
> > retrieving revision 1.123
> > diff -u -1 -0 -r1.123 datatype.sgml
> > --- doc/src/sgml/datatype.sgml	31 Aug 2003 17:32:18 -0000	1.123
> > +++ doc/src/sgml/datatype.sgml	8 Sep 2003 04:04:58 -0000
> > @@ -1735,20 +1735,71 @@
> >        Quantities of days, hours, minutes, and seconds can be specified without
> >        explicit unit markings.  For example, <literal>'1 12:59:10'</> is read
> >        the same as <literal>'1 day 12 hours 59 min 10 sec'</>.
> >       </para>
> >
> >       <para>
> >        The optional precision
> >        <replaceable>p</replaceable> should be between 0 and 6, and
> >        defaults to the precision of the input literal.
> >       </para>
> > +
> > +
> > +     <para>
> > +      Alternatively, <type>interval</type> values can be written as
> > +      ISO 8601 time intervals, using the "Format with time-unit designators".
> > +      This format always starts with the character <literal>'P'</>, followed
> > +      by a string of values followed by single character time-unit designators.
> > +      A <literal>'T'</> separates the date and time parts of the interval.
> > +     </para>
> > +
> > +     <para>
> > +       Format:  PnYnMnDTnHnMnS
> > +     </para>
> > +     <para>
> > +       In this format, <literal>'n'</> gets replaced by a number, and
> > +       <literal>Y</> represents years,
> > +       <literal>M</> (in the date part) months,
> > +       <literal>D</> months,
> > +       <literal>H</> hours,
> > +       <literal>M</> (in the time part) minutes,
> > +       and <literal>S</> seconds.
> > +     </para>
> > +
> > +
> > +     <table id="interval-example-table">
> > +	   <title>Interval Example</title>
> > +	   <tgroup cols="2">
> > +		<thead>
> > +		 <row>
> > +		  <entry>Traditional</entry>
> > +		  <entry>ISO-8601 time-interval</entry>
> > +		 </row>
> > +		</thead>
> > +		<tbody>
> > +		 <row>
> > +		  <entry>1 month</entry>
> > +		  <entry>P1M</entry>
> > +		 </row>
> > +		 <row>
> > +		  <entry>1 hour 30 minutes</entry>
> > +		  <entry>PT1H30M</entry>
> > +		 </row>
> > +		 <row>
> > +		  <entry>2 years 10 months 15 days 10 hours 30 minutes 20 seconds</entry>
> > +		  <entry>P2Y10M15DT10H30M20S</entry>
> > +		 </row>
> > +		</tbody>
> > +	   </thead>
> > +	  </table>
> > +
> > +     </para>
> >      </sect3>
> >
> >      <sect3>
> >       <title>Special Values</title>
> >
> >       <indexterm>
> >        <primary>time</primary>
> >        <secondary>constants</secondary>
> >       </indexterm>
> >
> > Index: src/backend/utils/adt/datetime.c
> > ===================================================================
> > RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
> > retrieving revision 1.116
> > diff -u -1 -0 -r1.116 datetime.c
> > --- src/backend/utils/adt/datetime.c	27 Aug 2003 23:29:28 -0000	1.116
> > +++ src/backend/utils/adt/datetime.c	8 Sep 2003 04:04:59 -0000
> > @@ -30,20 +30,21 @@
> >  			 struct tm * tm, fsec_t *fsec, int *is2digits);
> >  static int DecodeNumberField(int len, char *str,
> >  				  int fmask, int *tmask,
> >  				  struct tm * tm, fsec_t *fsec, int *is2digits);
> >  static int DecodeTime(char *str, int fmask, int *tmask,
> >  		   struct tm * tm, fsec_t *fsec);
> >  static int	DecodeTimezone(char *str, int *tzp);
> >  static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
> >  static int	DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
> >  static void TrimTrailingZeros(char *str);
> > +static int  DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec);
> >
> >
> >  int			day_tab[2][13] = {
> >  	{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0},
> >  {31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, 0}};
> >
> >  char	   *months[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun",
> >  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", NULL};
> >
> >  char	   *days[] = {"Sunday", "Monday", "Tuesday", "Wednesday",
> > @@ -2872,30 +2873,271 @@
> >  			default:
> >  				*val = tp->value;
> >  				break;
> >  		}
> >  	}
> >
> >  	return type;
> >  }
> >
> >
> > +void adjust_fval(double fval,struct tm * tm, fsec_t *fsec, int scale);
> > +{
> > +	int	sec;
> > +	fval	   *= scale;
> > +	sec		    = fval;
> > +	tm->tm_sec += sec;
> > +#ifdef HAVE_INT64_TIMESTAMP
> > +	*fsec	   += ((fval - sec) * 1000000);
> > +#else
> > +	*fsec	   += (fval - sec);
> > +#endif
> > +}
> > +
> > +
> > +/* DecodeISO8601Interval()
> > + *
> > + *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> > + *  time-interval by duration only."
> > + *  Basic extended format:  PnYnMnDTnHnMnS
> > + *                          PnW
> > + *  For more info.
> > + *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
> > + *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> > + *
> > + *  Examples:  P1D  for 1 day
> > + *             PT1H for 1 hour
> > + *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> > + *
> > + *  The first field is exactly "p" or "pt" it may be of this type.
> > + *
> > + *  Returns -1 if the field is not of this type.
> > + *
> > + *  It pretty strictly checks the spec, with the two exceptions
> > + *  that a week field ('W') may coexist with other units, and that
> > + *  this function allows decimals in fields other than the least
> > + *  significant units.
> > + */
> > +int
> > +DecodeISO8601Interval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> > +{
> > +	char	   *cp;
> > +	int			fmask = 0,
> > +				tmask;
> > +	int			val;
> > +	double		fval;
> > +	int			arg;
> > +	int			datepart;
> > +
> > +    /*
> > +	 * An ISO 8601 "time-interval by duration only" must start
> > +	 * with a 'P'.  If it contains a date-part, 'p' will be the
> > +	 * only character in the field.  If it contains no date part
> > +	 * it will contain exactly to characters 'PT' indicating a
> > +	 * time part.
> > +	 * Anything else is illegal and will be treated like a
> > +	 * traditional postgresql interval.
> > +	 */
> > +    if (!(field[0][0] == 'p' &&
> > +          ((field[0][1] == 0) || (field[0][1] == 't' && field[0][2] == 0))))
> > +	{
> > +	  return -1;
> > +	}
> > +
> > +
> > +    /*
> > +	 * If the first field is exactly 1 character ('P'), it starts
> > +	 * with date elements.  Otherwise it's two characters ('PT');
> > +	 * indicating it starts with a time part.
> > +	 */
> > +	datepart = (field[0][1] == 0);
> > +
> > +	/*
> > +	 * Every value must have a unit, so we require an even
> > +	 * number of value/unit pairs. Therefore we require an
> > +	 * odd nubmer of fields, including the prefix 'P'.
> > +	 */
> > +	if ((nf & 1) == 0)
> > +		return -1;
> > +
> > +	/*
> > +	 * Process pairs of fields at a time.
> > +	 */
> > +	for (arg = 1 ; arg < nf ; arg+=2)
> > +	{
> > +		char * value = field[arg  ];
> > +		char * units = field[arg+1];
> > +
> > +		/*
> > +		 * The value part must be a number.
> > +		 */
> > +		if (ftype[arg] != DTK_NUMBER)
> > +			return -1;
> > +
> > +		/*
> > +		 * extract the number, almost exactly like the non-ISO interval.
> > +		 */
> > +		val = strtol(value, &cp, 10);
> > +
> > +		/*
> > +		 * One difference from the normal postgresql interval below...
> > +		 * ISO 8601 states that "Of these, the comma is the preferred
> > +		 * sign" so I allow it here for locales that support it.
> > +		 * Note: Perhaps the old-style interval code below should
> > +		 * allow for this too, but I didn't want to risk backward
> > +		 * compatability.
> > +		 */
> > +		if (*cp == '.' || *cp == ',')
> > +		{
> > +			fval = strtod(cp, &cp);
> > +			if (*cp != '\0')
> > +				return -1;
> > +
> > +			if (val < 0)
> > +				fval = -(fval);
> > +		}
> > +		else if (*cp == '\0')
> > +			fval = 0;
> > +		else
> > +			return -1;
> > +
> > +
> > +		if (datepart)
> > +		{
> > +			/*
> > +			 * All the 8601 unit specifiers are 1 character, but may
> > +			 * be followed by a 'T' character if transitioning between
> > +			 * the date part and the time part.  If it's not either
> > +			 * one character or two characters with the second being 't'
> > +			 * it's an error.
> > +			 */
> > +			if (!(units[1] == 0 || (units[1] == 't' && units[2] == 0)))
> > +				return -1;
> > +
> > +			if (units[1] == 't')
> > +				datepart = 0;
> > +
> > +			switch (units[0]) /* Y M D W */
> > +			{
> > +				case 'd':
> > +					tm->tm_mday += val;
> > +					if (fval != 0)
> > +					  adjust_fval(fval,tm,fsec, 86400);
> > +					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> > +					break;
> > +
> > +				case 'w':
> > +					tm->tm_mday += val * 7;
> > +					if (fval != 0)
> > +					  adjust_fval(fval,tm,fsec,7 * 86400);
> > +					tmask = ((fmask & DTK_M(DAY)) ? 0 : DTK_M(DAY));
> > +					break;
> > +
> > +				case 'm':
> > +					tm->tm_mon += val;
> > +					if (fval != 0)
> > +					  adjust_fval(fval,tm,fsec,30 * 86400);
> > +					tmask = DTK_M(MONTH);
> > +					break;
> > +
> > +				case 'y':
> > +					/*
> > +					 * Why can fractional months produce seconds,
> > +					 * but fractional years can't?  Well the older
> > +					 * interval code below has the same property
> > +					 * so this one follows the other one too.
> > +					 */
> > +					tm->tm_year += val;
> > +					if (fval != 0)
> > +						tm->tm_mon += (fval * 12);
> > +					tmask = ((fmask & DTK_M(YEAR)) ? 0 : DTK_M(YEAR));
> > +					break;
> > +
> > +				default:
> > +					return -1;  /* invald date unit prefix */
> > +			}
> > +		}
> > +		else
> > +		{
> > +			/*
> > +			 * ISO 8601 time part.
> > +			 * In the time part, only one-character
> > +			 * unit prefixes are allowed.  If it's more
> > +			 * than one character, it's not a valid ISO 8601
> > +			 * time interval by duration.
> > +			 */
> > +			if (units[1] != 0)
> > +				return -1;
> > +
> > +			switch (units[0]) /* H M S */
> > +			{
> > +				case 's':
> > +					tm->tm_sec += val;
> > +#ifdef HAVE_INT64_TIMESTAMP
> > +					*fsec += (fval * 1000000);
> > +#else
> > +					*fsec += fval;
> > +#endif
> > +					tmask = DTK_M(SECOND);
> > +					break;
> > +
> > +				case 'm':
> > +					tm->tm_min += val;
> > +					if (fval != 0)
> > +					  adjust_fval(fval,tm,fsec,60);
> > +					tmask = DTK_M(MINUTE);
> > +					break;
> > +
> > +				case 'h':
> > +					tm->tm_hour += val;
> > +					if (fval != 0)
> > +					  adjust_fval(fval,tm,fsec,3600);
> > +					tmask = DTK_M(HOUR);
> > +					break;
> > +
> > +				default:
> > +					return -1; /* invald time unit prefix */
> > +			}
> > +		}
> > +		fmask |= tmask;
> > +	}
> > +
> > +	if (*fsec != 0)
> > +	{
> > +		int			sec;
> > +
> > +#ifdef HAVE_INT64_TIMESTAMP
> > +		sec = (*fsec / INT64CONST(1000000));
> > +		*fsec -= (sec * INT64CONST(1000000));
> > +#else
> > +		TMODULO(*fsec, sec, 1e0);
> > +#endif
> > +		tm->tm_sec += sec;
> > +	}
> > +	return (fmask != 0) ? 0 : -1;
> > +}
> > +
> > +
> >  /* DecodeInterval()
> >   * Interpret previously parsed fields for general time interval.
> >   * Returns 0 if successful, DTERR code if bogus input detected.
> >   *
> >   * Allow "date" field DTK_DATE since this could be just
> >   *	an unsigned floating point number. - thomas 1997-11-16
> >   *
> >   * Allow ISO-style time span, with implicit units on number of days
> >   *	preceding an hh:mm:ss field. - thomas 1998-04-30
> > + *
> > + * Allow ISO-8601 style "Representation of time-interval by duration only"
> > + *  of the format 'PnYnMnDTnHnMnS' and 'PnW' - ron 2003-08-30
> >   */
> > +
> >  int
> >  DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec)
> >  {
> >  	int			is_before = FALSE;
> >  	char	   *cp;
> >  	int			fmask = 0,
> >  				tmask,
> >  				type;
> >  	int			i;
> >  	int			dterr;
> > @@ -2906,20 +3148,37 @@
> >
> >  	type = IGNORE_DTF;
> >  	tm->tm_year = 0;
> >  	tm->tm_mon = 0;
> >  	tm->tm_mday = 0;
> >  	tm->tm_hour = 0;
> >  	tm->tm_min = 0;
> >  	tm->tm_sec = 0;
> >  	*fsec = 0;
> >
> > +	/*
> > +	 *  Check if it's a ISO 8601 Section 5.5.4.2 "Representation of
> > +     *  time-interval by duration only."
> > +	 *  Basic extended format:  PnYnMnDTnHnMnS
> > +	 *                          PnW
> > +	 *  http://www.astroclark.freeserve.co.uk/iso8601/index.html
> > +	 *  ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF
> > +	 *  Examples:  P1D  for 1 day
> > +	 *             PT1H for 1 hour
> > +	 *             P2Y6M7DT1H30M for 2 years, 6 months, 7 days 1 hour 30 min
> > +	 *
> > +	 *  The first field is exactly "p" or "pt" it may be of this type.
> > +	 */
> > +	if (DecodeISO8601Interval(field,ftype,nf,dtype,tm,fsec) == 0) {
> > +	    return 0;
> > +    }
> > +
> >  	/* read through list backwards to pick up units before values */
> >  	for (i = nf - 1; i >= 0; i--)
> >  	{
> >  		switch (ftype[i])
> >  		{
> >  			case DTK_TIME:
> >  				dterr = DecodeTime(field[i], fmask, &tmask, tm, fsec);
> >  				if (dterr)
> >  					return dterr;
> >  				type = DTK_DAY;
> > @@ -2983,20 +3242,21 @@
> >  				}
> >  				/* DROP THROUGH */
> >
> >  			case DTK_DATE:
> >  			case DTK_NUMBER:
> >  				val = strtol(field[i], &cp, 10);
> >
> >  				if (type == IGNORE_DTF)
> >  					type = DTK_SECOND;
> >
> > +				/* should this allow ',' for locales that use it ? */
> >  				if (*cp == '.')
> >  				{
> >  					fval = strtod(cp, &cp);
> >  					if (*cp != '\0')
> >  						return DTERR_BAD_FORMAT;
> >
> >  					if (val < 0)
> >  						fval = -(fval);
> >  				}
> >  				else if (*cp == '\0')
> >
> > ===================================================================
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
>
>

-- 
Peter Eisentraut   peter_e@gmx.net

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-01T14:12:35Z

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > Is this ready for application?  It looks good to me.  However, there is
> > an "Open issues" section.
> 
> It would be more useful to implement the SQL standard for intervals first
> instead of inventing more nonstandard formats for it.

OK, patch removed from queue.

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

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

Ron Mayer <ron@intervideo.com> — 2003-12-01T20:50:47Z

> -----Original Message-----
> 
> Is this ready for application?  It looks good to me.  However, there is
> an "Open issues" section.

In my mind there were two categories of open issues
  a) ones that are 100% backward (such as the comment about 
     outputting this format)
and
  b) ones that aren't (such as deprecating the current
     postgresql shorthand of 
         '1Y1M'::interval = 1 year 1 minute
     in favor of the ISO-8601
         'P1Y1M'::interval = 1 year 1 month.

Attached is a patch that addressed all the discussed issues that
did not break backward compatability, including the ability to
output ISO-8601 compliant intervals by setting datestyle to
iso8601basic.

   Ron

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

Ron Mayer <ron@intervideo.com> — 2003-12-01T21:18:20Z

Peter wrote:
> 
> It would be more useful to implement the SQL standard for intervals first
> instead of inventing more nonstandard formats for it.

Much of the postgresql docs talks about ISO-8601 formats, so I would think
of the patch more as a standards-based improvemnt for the current interval 
shortand.  

For example, where today postgresql accepts "1Y1M" as '1 year 1 minute',
with the patch, the ISO-8601-standard "P1Y1M" would mean '1 year 1 month'.

I would be happy to implement the SQL-standard-intervals as well if 
someone can point me to that spec.  It's just that the system I worked
with happend to exchange data with ISO8601 time intervals.

   Ron Mayer

[Moderators of psql-patches... I first replied to Peter from a
separate (personal) account.  I think this reply is now in the 
moderation queue). In retrospect I thought it might be better if 
the archives had the whole thread from the original email address.  
If it's not too late, could you reject my post in the moderation queue?
If not, sorry for the spam.]

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

Randolf Richardson <rr@8x.ca> — 2003-12-03T10:20:23Z

[sNip]
> ISO 8601 gives more specific names.
> 
>   ISO 8601 Basic Format:         P2Y10M15DT10H20M30S
>   ISO 8601 Alternative Format:   P00021015T102030
>   ISO 8601 Extended Format:      P0002-10-15T10:20:30
> 
> In a way, the Extended Format is kinda nice, since its
> almost human readable.
> 
> I could put in both the basic and extended ones, and
> call the dateformats iso8601basic and iso8601extended.
> The negative is that to do iso8601basic right, Id also
> have to tweak the date and time parts of the code too.

    	Perhaps all three formats should be supported, and if the following 
names were all valid things could be simplified further too:

    	    	iso8601basic
    	    	iso8601bas
    	    	iso8601alternative
    	    	iso8601alt
    	    	iso8601extended
    	    	iso8601ext

    	The reason for allowing shorter names is to simplify database 
management for anyone who may need to store the format name in a column for 
some reason (I can't think of one now, but I get a feeling that someone 
will want to do this type of thing in the future).

    	For that matter, the first letter could be used instead of the first 
three for the short versions.  Any thoughts on this?

-- 
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada

Please do not eMail me directly when responding
to my postings in the newsgroups.

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-09T23:28:20Z

Randolf Richardson wrote:
> [sNip]
> > ISO 8601 gives more specific names.
> > 
> >   ISO 8601 Basic Format:         P2Y10M15DT10H20M30S
> >   ISO 8601 Alternative Format:   P00021015T102030
> >   ISO 8601 Extended Format:      P0002-10-15T10:20:30
> > 
> > In a way, the Extended Format is kinda nice, since its
> > almost human readable.
> > 
> > I could put in both the basic and extended ones, and
> > call the dateformats iso8601basic and iso8601extended.
> > The negative is that to do iso8601basic right, Id also
> > have to tweak the date and time parts of the code too.
> 
>     	Perhaps all three formats should be supported, and if the following 
> names were all valid things could be simplified further too:
> 
>     	    	iso8601basic
>     	    	iso8601bas
>     	    	iso8601alternative
>     	    	iso8601alt
>     	    	iso8601extended
>     	    	iso8601ext
> 
>     	The reason for allowing shorter names is to simplify database 
> management for anyone who may need to store the format name in a column for 
> some reason (I can't think of one now, but I get a feeling that someone 
> will want to do this type of thing in the future).
> 
>     	For that matter, the first letter could be used instead of the first 
> three for the short versions.  Any thoughts on this?

Just go with the full spellings.


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

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-15T23:30:09Z

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

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


Ron Mayer wrote:
> > -----Original Message-----
> > 
> > Is this ready for application?  It looks good to me.  However, there is
> > an "Open issues" section.
> 
> In my mind there were two categories of open issues
>   a) ones that are 100% backward (such as the comment about 
>      outputting this format)
> and
>   b) ones that aren't (such as deprecating the current
>      postgresql shorthand of 
>          '1Y1M'::interval = 1 year 1 minute
>      in favor of the ISO-8601
>          'P1Y1M'::interval = 1 year 1 month.
> 
> Attached is a patch that addressed all the discussed issues that
> did not break backward compatability, including the ability to
> output ISO-8601 compliant intervals by setting datestyle to
> iso8601basic.
> 
>    Ron

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

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

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

Peter Eisentraut <peter_e@gmx.net> — 2003-12-16T00:06:32Z

Bruce Momjian wrote:
> Your patch has been added to the PostgreSQL unapplied patches list
> at:
>
> 	http://momjian.postgresql.org/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.

I keep reading about open issues, and deprecating certain things, and 
patch removed, and patch readded.  What is going on?

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-16T00:09:15Z

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > Your patch has been added to the PostgreSQL unapplied patches list
> > at:
> >
> > 	http://momjian.postgresql.org/cgi-bin/pgpatches
> >
> > I will try to apply it within the next 48 hours.
> 
> I keep reading about open issues, and deprecating certain things, and 
> patch removed, and patch readded.  What is going on?

I think the patch just added is OK, no?

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

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

Peter Eisentraut <peter_e@gmx.net> — 2003-12-16T00:37:50Z

Bruce Momjian wrote:
> > I keep reading about open issues, and deprecating certain things,
> > and patch removed, and patch readded.  What is going on?
>
> I think the patch just added is OK, no?

I don't know, but earlier the identical patch was rejected by you.

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-16T00:39:00Z

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > > I keep reading about open issues, and deprecating certain things,
> > > and patch removed, and patch readded.  What is going on?
> >
> > I think the patch just added is OK, no?
> 
> I don't know, but earlier the identical patch was rejected by you.

I thought he made an adjustment so no backward compatibility was broken.


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

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

Peter Eisentraut <peter_e@gmx.net> — 2003-12-16T00:42:09Z

Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > Bruce Momjian wrote:
> > > > I keep reading about open issues, and deprecating certain
> > > > things, and patch removed, and patch readded.  What is going
> > > > on?
> > >
> > > I think the patch just added is OK, no?
> >
> > I don't know, but earlier the identical patch was rejected by you.
>
> I thought he made an adjustment so no backward compatibility was
> broken.

Then I wouldn't have said "identical".

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-16T00:44:11Z

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > Peter Eisentraut wrote:
> > > Bruce Momjian wrote:
> > > > > I keep reading about open issues, and deprecating certain
> > > > > things, and patch removed, and patch readded.  What is going
> > > > > on?
> > > >
> > > > I think the patch just added is OK, no?
> > >
> > > I don't know, but earlier the identical patch was rejected by you.
> >
> > I thought he made an adjustment so no backward compatibility was
> > broken.
> 
> Then I wouldn't have said "identical".

OK, can anyone raise an objection to the patch.  The new description
means to me that he addressed our concerns and that my original
hesitation was unwarranted.

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

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-12-20T15:32:45Z

Patch applied.  Thanks.

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


Ron Mayer wrote:
> > -----Original Message-----
> > 
> > Is this ready for application?  It looks good to me.  However, there is
> > an "Open issues" section.
> 
> In my mind there were two categories of open issues
>   a) ones that are 100% backward (such as the comment about 
>      outputting this format)
> and
>   b) ones that aren't (such as deprecating the current
>      postgresql shorthand of 
>          '1Y1M'::interval = 1 year 1 minute
>      in favor of the ISO-8601
>          'P1Y1M'::interval = 1 year 1 month.
> 
> Attached is a patch that addressed all the discussed issues that
> did not break backward compatability, including the ability to
> output ISO-8601 compliant intervals by setting datestyle to
> iso8601basic.
> 
>    Ron

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

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