Re: Postgresql revisited. Some questions about the product

Andrew Mayo wrote:
>
> Some time ago I posted to comp.databases a list of requirements which
> IMHO any RDBMS product must meet to be generally useful in commercial
> applications.
>
> I got some responses back regarding Postgresql but a lot of
> improvements have since been made, so I am reposting the original list
> of questions and wondering if anyone out there would be able to
> provide up-to-date answers on them.
>
> I think a lot of people are interested in PG given the recent Red Hat
> announcement, so this is a good time to re-evaluate the product.
>

I haven't looked up everything, but I'll try.

> Questions:-
>
> 1. Does it support the full ANSI-92 SQL syntax especially left, right
> outer join functionality. If not, does it even support outer joins?
>
Inner- and outer (left, right and full) joins are fully supported in
PostgreSQL 7.1.x.

> 2. Is there full support for declarative constraints including
> primary,
> unique, foreign key, and check constraints?

Yeps.

> Does it support indexes
> and
> if so, just b-tree or does it support bit and hash indices.
>

Dunno that, maybe someone else could answer that one.

> 3. Does it support ALTER TABLE ... DROP , ALTER TABLE .... ADD (and, a
> la SQL Server 7) ALTER TABLE ... MODIFY?. (the last option can
> actually change a column datatype without destroying data - very nice)

Limited. From the /h ALTER TABLE command in psql:

Command: ALTER TABLE
Description: Modifies table properties
Syntax:
ALTER TABLE [ ONLY ] table [ * ]
ADD [ COLUMN ] column type
ALTER TABLE [ ONLY ] table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD table constraint definition
ALTER TABLE table
OWNER TO new owner

> 4. If there are significent SQL limitations, what are they. For
> instance, MySQL fails to support correlated subqueries (can they
> *really* call it an RDBMS, I wonder, given this). Does Postgresql
> support this. As a general rule of thumb, would Joe Celko's "SQL for
> Smarties" queries, which push standard SQL to the limits, work on
> Postgresql - they wouldn't on MySQL.
>
> (an example of the sort of queries I mean may be found at
> http://www.sys-con.com/pbdj/source/196/celko.htm)

No limitations, I think all of the queries mentioned on the page above
could work in PostgreSQL 7.1.x. GROUP BY tends to be a bit slow though.

> 5. How solid is the ODBC driver and can database management tasks such
> as creating a database be handled programmatically through it. What
> ODBC level does the driver conform to (e.g level 2, level 3).
>

I use (apart from direct table links, passtrough-) queries via ODBC in
MS Access to my PostgreSQL database without any problems.

> 6. Can databases be partitioned over multiple physical files. Can
> multiple databases share a single file. Can a database be mounted on a
> read-only medium such as a CDROM?
>
Nope.

> 7. Does it run cleanly on NT or just Unix; are there any significant
> limitations under NT.
>

You would have to use Cygwin, but even then it is mainly a UNIX based
database server.

> 8. Is there a stored procedure language?. Can Java be used as in
> Oracle, for instance?. (i.e can you write stored procedures in Java?)

PL/pgSQL gets you quite far, but then you can load different support
languages to write functions in (Perl, C/C++, tcl/tk). Maybe Java in the
future I don't know how difficult it is to integrate a programming
language in the database environment.

> 9. Can you easily import and export data via flat files - i.e, with
> bcp-
> like tools or are you on your own?
>

I don't know what 'bcp-like' tools are, but pg_dump and 'copy from' work
well for me.

> 10. Does it support Unicode. If not, does it support locale-specific
> collation sequences and/or sort orders. If so, can you restore
> databases across locale boundaries i.e created under one locale,
> restored under another (SQL Server can't do this).
>

That a good question I didn't yet delved into this but on a not so long
term I'll need support for arabic texts, anyone know if this is
supported by PostgreSQL?

> 11. Can you ask it to explain optimiser choices and show query
> processing statistics, and/or use hints to override them.
>

Yes.

> 12. Are there a reasonable range of coercion functions etc. that can
> be
> used in SQL (as in, for instance, SQL Server's string functions etc)
>

PostgreSQL is really flexible in this area. You can not only write your
own functions, you can define your own datatypes as well.

> 13. Are there tools to check and/or repair a corrupt database.

Dunno, I never had any corrupt database.

> 14. Does it support triggers. If so, are there any significant
> limitations?

Yeps, no limitations I can think of.

> 15. Do you have control over transaction logging e.g turn it off for
> bulk copy operations etc. Can this be done programmatically.
>

The WAL logging facility is rather new, I still have to delve in that one.

> 16. Are there facilities for monitoring database activity e.g open
> transactions, deadlocks etc.
>

Not that I know of.

> 17. Can you do hot backups.

I don't know if pg_dump locks the database.

> 18. What is the granularity of locking (page/row) or can you do what
> Oracle does, where repeatable reads are possible even when
> transactions
> are open against a database. Can you set lock timeouts?

PostgreSQL uses row level locking.

> Without all these features it's a useful product but not a replacement
> for any of the standard commercial RDBMS products, no matter how
> elegant it might be. Any thoughts, PostgresGurus?

Not from a guru, just from a humble user. I disagree with your assertion
that the following points are vital:

1. There is no RDMBS on the market (also not commercial) that is _fully_
SQL92 compatible (though PostgreSQL is getting close).
3. A limited ALTER TABLE should be no showstopper since ALTER TABLE is
only useful in a development stage of the database, not in the
production phase. It's conveniant, but not a necesity.
7. Although it is possible to run PostgreSQL on NT via Cygwin, I think
it's rediculous to say that NT support is vital for a good RDBMS.
8. Java support would be nice but it's not vital.

Of course if you want top of the bill, take Oracle. But often Oracle is
simply too heavy weight for many projects. I would say that PostgreSQL
is a quite mature RDBMS for most small to middle sized projects.

Regards,

Nils Zonneveld

--
Alles van waarde is weerloos
Lucebert