Re: Postgresql revisited. Some questions about the product0

On 9 Jul 2001, Andrew Mayo wrote:

> Questions:-

Answers:- online documentation covers 90% of what you would think of asking about pg

> 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 it does

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

yes, yes, yes and yes. Yes b-tree and also hash and r-tree and another one I'm forgeting right now. Also functional indeces and lately/maybe partial indeces (?) READ http://www.postgresql.org/idocs/index.php?indices.html

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

The ALTER of pg is a bit crippled. You can add but not drop a column. Add constraints, default value, rename table column and modify user access privileges

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

I don't see anything special about them. Subselects and aggregates are just fine with pg. The only limit is that oids and xids are int4 so you can have only as many as ~4billion of them (not as bad as it sounds. Especially oids will just wrap around and you'll do your job from there with minor disturbances). In general pg is really close to the SQL standard. There is an admin tool (AQT) over ODBC that doesn't officialy support PostgreSQL but worked like a charm just because PG is extremely ANSI conscious.

> 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 think level 3 is supported. There is an odbc+ driver (don't recall were) besides the standard one.

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

You don't get any contact with the way things are done on a file level (at least not that I'm aware of). But pg will partition a db over 1gb files, which is a big deal given the cirrect 2gb filesize limit that linux imposes.

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

no comment

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

nope. You can use c,c++,plpgsql (like oracle's plsql),tcl,perl and...ah! sql :-)

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

yes you can

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

yes it does something in that area but is beyond me. Read the docs?

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

yes, yes and not really. You can change the query syntax in ways that will force the optimizes to chose one plan over another but i don't think there are any explicit hints you can write in.

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

plenty. read the docs.

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

nope. the assumption is that the database doesn't get corrupted 8^)

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

yes

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

don't know

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

nope

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

yes it has MVCC like oracle so you have maximum concurrency.

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

cheers,
thalis