Re: [HACKERS] PC Week Labs benchmark results

Timothy Dyck <Timothy_Dyck(at)zd(dot)com> writes:
> Here's a list of things about PostgreSQL I had problems with:

> 1. "Null" is not accepted keyword on "create table" ("not null" is ok)

AFAICT from the SQL92 spec, NULL is not a legal column constraint.
I know some DBMSs accept it anyway, but we don't because it creates
grammatical ambiguities.

> 2. copy command 'with null as' option not functional

It looks like this has been added for 7.0 ... I haven't tried it
but I see the syntax is there.

> 3. try to create an index on a numeric and "no operator class for
> 'numeric' data type" error message results. Numerics not indexable?

Oversight in 6.5.* ... fixed for 7.0.

> 4. no outer join -- I had to drop one query because of this

Thomas is working on outer joins, but I'm not sure if it'll be ready
for 7.0. 7.1 for sure though; this is our most-requested missing SQL92
feature.

> 5. no alter table add constraint

Not there yet (but Peter E. was working on it when last seen...)

> 6. select count(distinct *) from a view gives a parser error on distinct
> -- distinct keyword not supported here?

No, but it is for 7.0.

> 7. one query (dss_select_05) has an avg on a numeric field. I got an
> overflow error (is there a cast to a longer type?). When the avg on
> numeric field is removed, the query consumes memory rapidly and doesn't
> terminate. I dropped this query.

Bug. I posted a patch for this and a couple of other NUMERIC problems
a few weeks ago; it'll be in 7.0 of course, and you can get the patch
off the pgsql-patches list archives if you need it to work in 6.5.*.

> 8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
> 1: couldn't initialize shared buffer pool Hash Tbl". Variable overflow?

Probably. Hadn't occurred to me that we need to check for a sane upper
bound on the number of buffers, but I guess we do. (You do realize that
would be half a gig of in-memory buffers, right? If you've actually got
that much RAM, it's probably better to let the OS use it for general-
purpose disk buffers instead of dedicating it all to Postgres.)

> Also, is PostgreSQL 7 expected to be SQL-92 compliant? It's pretty close
> now.

We're getting closer all the time, but I wouldn't want to promise that
we'll ever have everything that's in SQL92.

Thanks for the report! I don't suppose you'd be interested in rerunning
your tests on current (pre-beta-7.0) sources?

regards, tom lane