Re: Postgresql revisited. Some questions about the product

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

Yes, I think so.

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

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

btree and hash only.

>
> 3. Does it support ALTER TABLE ... DROP ,

No.

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

No.

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

I think most would work.

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

ODBC 2 I think. You can do everything client-side.

>
> 6. Can databases be partitioned over multiple physical files. Can

You have to use symlinks to move to other file systems.

> multiple databases share a single file. Can a database be mounted on a
> read-only medium such as a CDROM?

No cdrom.

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

NT requires Cygwin.

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

Yes, several languages, but not java server-side.

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

Sure, COPY.

>
> 10. Does it support Unicode. If not, does it support locale-specific
> collation sequences and/or sort orders. If so, can you restore

It supports all those.

> databases across locale boundaries i.e created under one locale,
> restored under another (SQL Server can't do this).

No.

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

Yes, EXPLAIN. No hints.

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

Yes, tons.

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

No, little demand for them.

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

No limitations I know of.

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

No, not programatically.

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

Query monitor, no lock monitor yet. Deadlock detection is automatic.

>
> 17. Can you do hot backups.

Yes.

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

We have MVCC, writers don't block readers and readers don't block
writers.

No lock timeout.
>
> 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?

No comment.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026