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

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?

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
indecesand 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
columnand 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
andxids are int4 so you can have only as many as ~4billion of them (not as bad as it sounds. Especially oids will just
wraparound 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
becausePG 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
partitiona 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
anotherbut 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

  At any given time, I'm fairly likely to see one or more postmasters
which are sitting around, doing absolutely nothing.  They're not taking
any CPU time, nor *have* they taken even a second of the CPU.  They just
sit there, idle.  There may be up to six or seven of them.

   The machine is a quad Xeon, and the CPU usage rarely exceeds 30%.  It
hasn't touched swap in months, and has more than enough RAM, so they
processes don't seem to be waiting for CPU or I/O.  The PG version is
7.0.2.

  Any ideas what's going on?

steve

> 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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Let's go over the "No's" that still remain:

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

Neither did Oracle until version 8.1.5.

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

PostgreSQL supports ALTER TABLE ADD, but not ALTER TABLE MODIFY. Oracle
8.0.5 doesn't allow for the modification of column data types if there
is data in the table.

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

...

> > 13. Are there tools to check and/or repair a corrupt database.
>
> No, little demand for them.

It's true. There is very little demand for them. I've been using
PostgreSQL for at least 3 years and have never suffered data corruption.
Index corruption in *much older* versions yes, but not data. There is a
utility to recover data straight from the database files (including dead
tuples), but, never having had corrupt data, I've never used it. I've
only seen a couple of posters reference it when they incorrectly used
Unix (mv, cp, tar) commands for B & R on either a running database or
without tarring the whole 'data' tree.

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

...

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

As Bruce points out, PostgreSQL is like Oracle WRT versioning and
therefore doesn't use page/row locks.

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

The use of the term *all* is questionable. I'm not suggesting that the
few "No's" that remain shouldn't be addressed. But using your minimal
requirements implies that Oracle 8 before 8.1.5 wasn't a "standard
commercial RDBMS". You might also wan't to ask a few more:

"Are DDL statements ROLLBACK-able?"

"Does the database support TCL and Perl procedural languages?"

"Does the database support object features like multiple inheritance?"

"Can the database use subselects as column expressions? in the FROM
clause?"

Just my opinion,

Mike Mascari
mascarm@mascari.com

Bruce Momjian wrote:
>

> >
> > 6. Can databases be partitioned over multiple physical files. Can
>
> You have to use symlinks to move to other file systems.

That's not what he asked.  He asked about files, and the answer is yes.


--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

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

Svenne Krap <usenet@krap.dk> wrote in message news:<u22ukt8iqtvof4k9jkphh6o4i8gkuot32i@4ax.com>...
[re metadata]

Gee, thanks for taking the time and trouble to do that. Time to
download PG and the ODBC driver and get it all working........

Andy Burns <andy@burns.net> wrote in message news:<8pctktk42efme9orfcck7mrn2ikn779v7r@4ax.com>...
> why not run VMWare on NT4/W2K on the laptop, then run linux inside the
> virtual machine and run PGSQL there, you sacrifice some speed, but are
> running PGSQL on a real O/S that it is designed to run on, not
> shoehorning it into cygwin/NT ...

Good point. I must look into that because having Linux available at
the same time as windows would be very handy sometimes.