On Oct. 30 and 31 I attended OSDN's rather grandiosely named "Open Source
Database Summit" (despite what you might infer from the name, it was just
a small, open-to-the-public conference).  Their info about the conference
is at http://www.osdn.com/conf/osd/conf_index.shtml, though I'm not sure
how long that page will remain up.  OSDN invited a number of the principal
suspects from each major open-source database project to speak, and paid
for airfare and hotel rooms for the speakers.  The invited speakers were
Bruce Momjian and myself from Postgres, David Axmark and Monty Widenius
from MySQL, Mike Olson and Mike Ubell from Sleepycat (Berkeley DB), and
Ann Harrison from InterBa^H^H^H^H IBPhoenix; also Britt Johnston, who as
CTO of NuSphere can fairly be ranked in the MySQL camp; plus Tim Perdue
and Rob Ferber as representative application-builders.  Total attendance
was about forty or fifty, so we had a pretty good crowd of interested
people.  Ned Lilly of Great Bridge was also there (on GB's dime), as well
as two or three more NuSphere people, but mostly it seemed to be users
and potential users of open-source databases.

Sunday evening, Bruce and Ned and I filtered in at different times.
OSDN had laid out a spread of free food in one of the meeting rooms,
but the hotel staff didn't tell any of us about it, so we ended up
hanging out in the hotel bar with a number of similarly ill-informed
souls.  It was particularly interesting to talk to John Scott, who is
working for Verizon Wireless on redoing the software for their nationwide
paging service.  It turns out they are looking at using Postgres for the
customer database, and either Postgres or Berkeley DB for the realtime
database that handles paging messages being pumped through the system.
That'll be a feather in our caps if it happens!

Monday, Britt Johnston opened the formal proceedings with what amounted
to a pep talk for OS DB work.  I have a fairly interesting table in my
notes, giving current total Web-search hits for various databases:
	Oracle		3.0 mil
	MySQL		2.3 mil
	Postgres	0.7 mil
	SQL Server	0.6 mil
	IBM DB2		0.5 mil
	Interbase	0.1 mil
(I had to copy the last half of the table from memory, so it may not be
exactly what he said, but it's close.)  This says that MySQL+PG together
are *already* as interesting as Oracle for web work.  He also announced
that NuSphere would be financing substantial work on MySQL --- I have a
note about 10000 concurrent transactions on a single server, which'd be
pretty impressive (he didn't say what size server, though).

The conference format alternated between group-wide sessions and pairs
of concurrent workshop talks, so after that we split into two groups.
I went to hear Tim Perdue talk, while Bruce and Ned listened to Mike
Ubell; they'll have to report on what Mike said.  Tim's discussion was
about building apps atop PHP and a database.  He pointed out that for
most website builders, the path of least resistance given their existing
skills is to construct an "application heavy" system in which most of
the logic is in application code.  He contrasted this with "database
heavy" design, in which more reliance is put on database functionality,
such as constraints, triggers, views, etc.  Unfortunately (from our
point of view) Postgres excels for the database-heavy style, whereas
MySQL's lean feature set is sufficient or at least self-reinforcing
for the application-heavy style.  It'll be difficult for PG to achieve
world domination until Web developers become more database-savvy ;-).
Tim encouraged a great deal of comment from the audience, and went so far
as to make everyone introduce themselves first.  (One interesting thing
that emerged at that point was that there were *very* few MySQL users,
and no MySQL developers, at this talk --- though I guess that just meant
that the MySQL people all wanted to hear what Mike Ubell had to say,
since he was talking about a directly-MySQL-related subject.)  One of the
longest-running parts of the discussion had to do with giving good error
messages and how it is hard to get friendly messages when you rely on the
database to do error checking.  I thought this pointed up the need we've
been aware of for awhile to overhaul our error reporting.  Tim also had
a "wish list" for PG that included better admin tools, such as a way to
see exactly what queries are running; and a way to retrieve all the
database-generated items in a just-inserted row, not only the OID.
Both of these have also been on the radar screen for awhile.

After a fine lunch (all the food was superb BTW; OSDN made an excellent
choice of hotel), we reconvened to hear David Axmark talk about the
history and philosophy of MySQL.  The only thing that really surprised me
is that that project is quite young: it started in 1995.  Given that Monty
seems to do the vast majority of the development work, there are not many
man-years in it, certainly far less than in Postgres.  They've done well
to come as far as they have.

The subsequent breakout was between Rob Ferber talking about shedding
database processing load to stateless clients, and me talking about
Postgres' transaction model.  I was quite annoyed that I couldn't go
hear Rob, because his talk abstract sounded very interesting :-(.
You can find the slides from my talks (also Bruce's) at
http://www.postgresql.org/osdn/index.html, so I won't go into detail,
but I hope Bruce will report on Rob's talk.

That evening there was a cocktail hour in the hotel's library (free
booze, courtesy of the conference) followed by dinner at the hotel's
better restaurant.  I spent a good part of the cocktail hour talking
with Ann Harrison and several other people about organizing some sort
of open-source database benchmarking project.  It turns out that DEC's
(now Compaq's) performance measurement group has a nearly-done reference
implementation of AS3AP, which they're thinking of releasing as an open
source project.  Everyone agreed that would be a fine starting point.
We also got to hear Ann's version of the InterBase situation --- more
about that later.  Towards the end of the hour I wandered over and started
to talk to Monty and David.  That stretched into eating dinner with them.
Since I'd had a couple glasses of wine already, and a couple more during
dinner, while they'd started with vodka and then joined in on the wine,
I doubt that either side could repeat much of the conversation word-for-
word ;-).  But it was all pleasant and perhaps will serve to dispel some
of the bad blood that's existed between the two projects for awhile.

The next morning, the opening speaker was me, with a presentation on the
internals of Postgres (see slides at above URL).  The subsequent breakout
had Bruce giving a talk on the history and project-management practices
of Postgres (see slides), while I went to hear Ann Harrison talk about
integrity checking in databases.  Before she could get into her promised
topic, the audience pretty much forced her to give a rundown on the
InterBase situation.  Bottom line: it's a mess.  She feels Borland were
being unreasonable (and in her telling of it, they indeed seem to be)
while they felt, or said they felt, that she was.  She thinks that once
she and others had come up with a business plan for doing something with
InterBase rather than dropping it, Borland/Inprise decided they could
execute the business plan without her --- and that may be pretty accurate.
Anyway, Inprise now has a small in-house development team with few if any
original developers, Ann has only jawbone control over a dozen or so
open-source developers (these also with little or no deep knowledge of the
source, apparently), and there's a code fork between the Inprise version
and the "Firebird" open-source project.  The two groups are apparently
talking enough to try to keep their trees from diverging too much, in the
hopes that the fork might be reunited someday, but Ann didn't sound all
that hopeful about it.  Things sound mighty bleak to me --- but perhaps
InterBase is just going through a transition comparable to Postgres'
transition from a Berkeley project to an open project.

To get back to the technical part of Ann's talk, the thing I came away
with is a realization that IB did a lot of things pretty similar to
Postgres.  In particular, it sounds like they have a multi-versioning
model nearly identical to Postgres'.  They also have some ideas we might
be able to adopt --- for example, their indexes point only to the newest
version of a row, not all versions.  It'd be worth our while to dig
through their code for ideas.  However, Ann admitted that they are
woefully short on internals documentation, so extracting useful ideas
promises to be painful :-(

The final group-wide session featured Mike Olson of Sleepycat as speaker.
Most of you know that Mike was part of the Berkeley Postgres team years
ago (if you don't, try scanning our sources for the initials mao) so I
count him still a Postgres man, even though Sleepycat is currently in bed
with MySQL.  Mike had some *extremely* interesting things to say about
the prospects for open-source databases making inroads against commercial
competition.  He pointed out that the notion that we have any chance of
doing so is mostly founded on the success Linux has been having competing
with Windows --- but that success is founded on (a) a cost advantage,
(b) a reliability advantage, and (c) an advantage in the applications
space: Linux runs sendmail, bind, Apache, and all the other core Internet
server apps, whereas Windows doesn't run them especially well.  Mike
pointed out that Oracle could *easily* afford to give away their software
for free and make all their money on support contracts (license fees are
already only 1/3rd of their revenue, so it wouldn't be that big a
switch).  That would make the cost advantage a harder sell.  We could
still make a good case for open databases on total cost of ownership,
but a key ball to keep our eye on is the ease of installation and
administration of our servers.  Much of the differential comes from the
fact that qualified Oracle DBAs are scarce and obscenely well-paid.
We have to be sure that Joe Average Unix Sysadmin can deal with our
servers without much trouble.  As for point (b), the news is bad:
we are *not* up to Oracle standards on reliability.  (Mike only said
that it's unproven that we are up to commercial standards, but from
here in the trenches I'd say we ain't.)  We need to keep our noses to
the grindstone on this issue, and even so it's unlikely that we'll ever
have the same sort of obvious reliability advantage that Linux has over
Windows, simply because the commercial databases aren't anywhere near
as bad as Windows.  That leaves point (c) --- we have to exploit the
open-source nature of our systems to encourage a flowering of compatible
applications.  And we'd better make sure that people can make money
building apps atop open-source databases, or that flowering won't happen.
A thought-provoking talk indeed; probably the best one at the conference,
IMHO.

The final pair of speakers were Monty on the history and
project-management practices of MySQL, and Rob Ferber on Open Sales'
^H^H^H^H Zelerate's way of building distributed transaction processing.
Bruce went to hear Monty, I went to hear Rob.  It was pretty interesting:
basically, they do not try to replicate state, but instead distribute
"events" --- maybe better called "actions", since the events are things
like "decrement available-stock by 1".  Each server in their network is
"authoritative" for events that it originates, and is responsible for
transmitting those events to other servers.  Each server maintains state
tables that represent the integral of all the events it knows of so far,
but it's explicitly recognized that these state tables may be out of sync
due to network latency, communication failures, etc.  With appropriate
application programming it's possible to build a highly robust distributed
system, sitting atop non-distributed database servers.  Their system is
open source and all coded in Perl, so you can go have a look if you want
to learn more.

Bruce and John Scott and I wasted most of Tuesday evening in a fruitless
search for the Computer Literacy bookstore that used to exist near Apple
headquarters, so I can't say if anything interesting happened around the
hotel then.  But it seemed that things were winding down and a lot of
people were departing that evening, so probably not...

Overall it was a very interesting and worthwhile conference.  I have to
congratulate Mark Stone and Christine Dzierzeski of OSDN on organizing
a great conference on little time and minimal budget.  If they invite
me to the next one, I'll be there.

			regards, tom lane

Thu, 02 Nov 2000 23:47:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
OSDN Database conference report (long)
Hi all,

I'm one of the accidentally-subscribed readers to the Hackers list.  I
mainly wanted to thank Tom for typing this up.  I had a great time reading
it and learned a lot.  I also wanted to try and help with some personal
experience since God knows I wouldn't be able to code in C or whatever PG is
in ;)

> database to do error checking.  I thought this pointed up the need we've
> been aware of for awhile to overhaul our error reporting.  Tim also had
> a "wish list" for PG that included better admin tools, such as a way to
> see exactly what queries are running; and a way to retrieve all the
> database-generated items in a just-inserted row, not only the OID.
> Both of these have also been on the radar screen for awhile.

I'm not sure exactly where the error checking comes in.  I've been using
Postgres in two places - at home (Apache/Tomcat) and at work (Apache/iASP)
for the last 8 months or so.  The only gripe I have with error messages is
that they could be more specific.  "Error near <some character that occurs
20+ times in the query>" is usually pretty useless =)  Otherwise, I can't
recall a single time where I said, "man that message should be more clear".

Then again, I'm positive I'm not the hardcore DB-user that lots of Oracle
DBAs are, or most all of you!  In your collective journeys, I'm sure there
are some other ~bad ones, or else Tom wouldn't have mentioned that.  Just
wanted to say in my xp, nothing bad to say.

> We have to be sure that Joe Average Unix Sysadmin can deal with our
> servers without much trouble.

I'm much less competant than your Joe Six-pack *nix admin.  Hell, I probably
even spelled competant wrong.  I downloaded, compiled, and installed PG in
no time flat.  I was querying via JSP less than 30 minutes after I
downloaded the database.  However, I've never really done anything past
that.  Would I be considered your 'typical' PG user?  Dunno...  I've written
a lot of little apps that use Postgres, but it's all your routine stuff.
Some INSERTs, some SELECTs, etc.  Nothing mind-blowing, that's for sure.  I
don't use any of the management tools, never any need to.  Although, I have
made several mental-notes to figure out just where the "Object" stuff comes
into place w.r.t. "Object-Relational" tho' =)

I wish I could offer some better feedback, but from my point of view,
Postgres has been great.  Never crashed, takes up pretty much no system
resources, great docs, etc.

Keep up the great work guys!  There are countless people appreciating it
every single day that you'll never hear from.  Not my boss for sure.  That
guy's unorganized as hell.  Good guy, but you'd never get a thanks from him
;)  Mental note:  make sure Dave doesn't read pgsql-hackers.

Take care!

- r


Thu, 2 Nov 2000 22:29:50 -0800
"Rob S." <rslifka@home.com>
RE: [HACKERS] OSDN Database conference report (long)
Hi,

Help me please.
How to get previouse wonth in query?



Fri, 3 Nov 2000 11:50:36 +0300
igor <igor_kh@mailru.com>
DateTime functions
Very nice paper.
Thanks Tom!
And thanks all the Postgres developer! Great job!

Laser


Fri, 03 Nov 2000 18:06:15 +0800
"He Weiping (Laser Henry)" <laser@zhengmai.com.cn>
Re: OSDN Database conference report (long)
"Rob S." <rslifka@home.com> writes:
>> ...  I thought this pointed up the need we've
>> been aware of for awhile to overhaul our error reporting.

> I'm not sure exactly where the error checking comes in.  I've been using
> Postgres in two places - at home (Apache/Tomcat) and at work (Apache/iASP)
> for the last 8 months or so.  The only gripe I have with error messages is
> that they could be more specific.  "Error near <some character that occurs
> 20+ times in the query>" is usually pretty useless =)  Otherwise, I can't
> recall a single time where I said, "man that message should be more clear".

The thing is that any error that the database itself issues is probably
database-centric; it may be helpful to the person coding the application,
but is unlikely to make a lot of sense to an end user.  So well-coded
apps typically want to substitute their own error messages --- say,
"please enter a positive value" rather than "rejected due to CHECK
constraint foo".  We need to provide more support for that.  A
consistent numbering scheme for error codes would help, for instance,
so that apps could just look at the error number and not be dependent
on pattern-matching against strings that the developers might reword
from time to time.

As I said, this has been on the todo list for awhile...

			regards, tom lane

Fri, 03 Nov 2000 11:08:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] OSDN Database conference report (long) 
Sorry - I mean the previous _month_ ,
like IncMonth(Now(),-1) function in C++ Builder

>>
>> Help me please.
>> How to get previouse wonth in query?
IR> What? О©╫О©╫О©╫ О©╫ О©╫О©╫О©╫О©╫О©╫О©╫? 



Fri, 3 Nov 2000 20:25:58 +0300
Igor Khanjine <igor_kh@mailru.com>
Re[2]: DateTime functions
At 11:08 AM 11/3/2000 -0500, Tom Lane wrote:
>"Rob S." <rslifka@home.com> writes:
> >> ...  I thought this pointed up the need we've
> >> been aware of for awhile to overhaul our error reporting.
>
> > I'm not sure exactly where the error checking comes in.  I've been using
> > Postgres in two places - at home (Apache/Tomcat) and at work (Apache/iASP)
> > for the last 8 months or so.  The only gripe I have with error messages is
> > that they could be more specific.  "Error near <some character that occurs
> > 20+ times in the query>" is usually pretty useless =)  Otherwise, I can't
> > recall a single time where I said, "man that message should be more clear".

As opposed to SQL server which tends to be extrememly cryptic and goes out 
of it's way to hide information from you. Once I was importing some records 
from a text file and it kept stopping in the middle with a dialog box that 
said "overflow" and that's all. It would not tell me the line number or the 
field it was having trouble with. What I did was to recreate the structure 
of the table in postgres and try to import into there. Postgres told me 
that I had an invalid date on line number whatever. I guess you cna say I 
used postgres as a debugging tool for ms-sql server.

BTW did you know that it's impossible to store dates before 1700 on ms-sql 
server? It's datetime datatype will not support older dates.
----------------------------------------------
              Tim Uckun
       Mobile Intelligence Unit.
----------------------------------------------
    "There are some who call me TIM?"
----------------------------------------------

Fri, 03 Nov 2000 10:45:54 -0800
Tim Uckun <tim@diligence.com>
Re: Re: [HACKERS] OSDN Database conference report
  (long)
Tom Lane wrote:
> 
Thanks for doing it ;)

I have some comments too ...

> One of the
> longest-running parts of the discussion had to do with giving good error
> messages and how it is hard to get friendly messages when you rely on the
> database to do error checking.  I thought this pointed up the need we've
> been aware of for awhile to overhaul our error reporting. 

One thing I have wished for a long time is _configurable_ error
reporting, 
so that I could tell the backent to "set errorlevel to N" and not
receive 
any errors below level N

> Tim also had
> a "wish list" for PG that included better admin tools, such as a way to
> see exactly what queries are running; and a way to retrieve all the
> database-generated items in a just-inserted row, not only the OID.
> Both of these have also been on the radar screen for awhile.

or at least allow something like "select * from table where
LAST_INSERTED_BY_ME"
to be fast (are we currently smaprt enoug to stop scam after finding an
unique item, 
or is it so, that "select ... where oid=N" still does a full table scan,
even thoug 
oid's are uniqe")

> After a fine lunch (all the food was superb BTW; OSDN made an excellent
> choice of hotel), we reconvened to hear David Axmark talk about the
> history and philosophy of MySQL.  The only thing that really surprised me
> is that that project is quite young: it started in 1995.  Given that Monty
> seems to do the vast majority of the development work, there are not many
> man-years in it, certainly far less than in Postgres.  They've done well
> to come as far as they have.

IIRC they already had an ISAM library and mSQL as a sampole
implementation.

> To get back to the technical part of Ann's talk, the thing I came away
> with is a realization that IB did a lot of things pretty similar to
> Postgres.  In particular, it sounds like they have a multi-versioning
> model nearly identical to Postgres'.

I thought that was common knowledge here ;)

At least I mentioned it on this list several times in early days (maybe
95-96) 
and I'm pretty sure that also Vadim wrote something about their approah
back 
then ?

>  They also have some ideas we might
> be able to adopt --- for example, their indexes point only to the newest
> version of a row, not all versions. 

I seems to be good as an idea, but woul make it somewhat harder to find
tuples 
in transactions that are started earlyer than the newest tuple,
specifically in 
cases where they have planned to find the tuple using the index.

> It'd be worth our while to dig
> through their code for ideas.  However, Ann admitted that they are
> woefully short on internals documentation, so extracting useful ideas
> promises to be painful :-(

Probably it would be easier to have someone spy on their mailing lists
;)

> The final group-wide session featured Mike Olson of Sleepycat as speaker.
> Most of you know that Mike was part of the Berkeley Postgres team years
> ago (if you don't, try scanning our sources for the initials mao) so I
> count him still a Postgres man, even though Sleepycat is currently in bed
> with MySQL.  Mike had some *extremely* interesting things to say about
> the prospects for open-source databases making inroads against commercial
> competition.  He pointed out that the notion that we have any chance of
> doing so is mostly founded on the success Linux has been having competing
> with Windows --- but that success is founded on (a) a cost advantage,
> (b) a reliability advantage, and (c) an advantage in the applications
> space: Linux runs sendmail, bind, Apache, and all the other core Internet
> server apps, whereas Windows doesn't run them especially well.  Mike
> pointed out that Oracle could *easily* afford to give away their software
> for free and make all their money on support contracts (license fees are
> already only 1/3rd of their revenue, so it wouldn't be that big a
> switch). 

That implies that their software is _not_ easy to install/maintain.

I have a frustrated friend who set up a linux box for a company to
replace 
an win2000 server which did not work (sendmail, squid, firewall, dns,
...)
and who was promised a support contract as well, but as things just work
for 
more than ahalf a year without him, he did not get any contract.

> That would make the cost advantage a harder sell.  We could
> still make a good case for open databases on total cost of ownership,
> but a key ball to keep our eye on is the ease of installation and
> administration of our servers. 

IMHO postgres is easy to install, not only from .rpm biut also using 
configure, make, make install

> Much of the differential comes from the
> fact that qualified Oracle DBAs are scarce and obscenely well-paid.
> We have to be sure that Joe Average Unix Sysadmin can deal with our
> servers without much trouble.  As for point (b), the news is bad:
> we are *not* up to Oracle standards on reliability.  (Mike only said
> that it's unproven that we are up to commercial standards, but from
> here in the trenches I'd say we ain't.) 

In my experience "commercial standards" here means usually that if you 
have that 2/3 of price support contract, someone will come running to
fix it
if something happens, not that it is bullet-proof.

But I still need that 7.0.3 releas so that my database would allow me to 
do a vacuum without restarting after some amount of heavy work ;)

> We need to keep our noses to
> the grindstone on this issue, and even so it's unlikely that we'll ever
> have the same sort of obvious reliability advantage that Linux has over
> Windows, simply because the commercial databases aren't anywhere near
> as bad as Windows. 

True. Or at least partially true ;) Some of it can be attributed to the 
human nature and to the nature of software failures - if you have a
mission 
critical application and you have payd humongous amounts of money for
your 
software (and hardware) you are more likely to test thing very
thoroughly 
before going production. Also you are less likely to blame (or allowed
to 
blame) an expensive product you may have yoursef recommended. 

PostgreSQL and other OSS will never have that defence; so we must be
_more_ 
reliable in order to be perceived as being "as reliable as".

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

Sat, 04 Nov 2000 20:10:49 +0000
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] OSDN Database conference report (long)