Postgresql revisited. Some questions about the product - Mailing list pgsql-general

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.

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?

2. Is there full support for declarative constraints including
primary,
unique, foreign key, and check constraints? Does it support indexes
and
if so, just b-tree or does it support bit and hash indices.

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)

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)

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

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?

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

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

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

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

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

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)

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

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

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

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

17. Can you do hot backups.

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?

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?