The MySQL question.

[Patrick]

I recently began using PostgreSQL for a project at work so a few of
these statements jumped out at me as obviously incorrect. My reply
covers 7.4 since I am not yet familiar with what 8.0 beta 2 adds:

On Fri, 2004-09-10 at 17:00, Thomas Zehetbauer wrote:
> On Fre, 2004-09-10 at 17:50 -0400, Lamar Owen wrote:
> > > - MySQL has a fine grained access control
> > 
> > How fine do you need?  Checked out what you can do with pg_hba.conf lately? 
> > What sort of fine-grained control do you need?  You might be surprized at 
> > what you can do now.
> 
> No, but I doubt that you can restrict a user to read-only access of
> single column.

This is true. From the SQL GRANT manual page:
"Currently, to grant privileges in PostgreSQL to only a few columns, you
must create a view having the desired columns and then grant privileges
to that view."

I am curious what column-level access control is used for however. My
impression is that typically column level access control is needed when
all of the logic is stored in the database and in this case isn't MySQL
rather limiting for server-side processing? Do you expose MySQL as the
interface to your data or is all access done through an intermediary
program/script?

> > > - PostgrSQL fail to use the index for a simple 'SELECT MAX(column)'
> > 
> > Really?  Got any EXPLAIN output to prove that?
> 
> Fortunately I have no access to PostgreSQL any more but I have seen an
> EXPLAIN output proving this; Besides it is on the PostgreSQL developers
> todo list for a long time: http://developer.postgresql.org/todo.php

I think this is still true.
The other part of the todo explains the temporary workaround until the
query planner can be enhanced.

"TODO: Use indexes for MIN() and MAX() 

MIN/MAX queries can already be rewritten as SELECT col FROM
table ORDER BY col {DESC} LIMIT 1. Completing this item involves
making this transformation automatically."

> > > - PostgreSQL needs a complete dump restore for at least every minor
> > >   version upgrade
> > 
> > Wrong.  PostgreSQL needs a dump restore for a major version upgrade.  Major is 
> > like 7.2 to 7.3.  PostgreSQL's versioning is more like the Linux kernel than 
> > other packages in versioning.  Even then, the Slony replication engine allows 
> > you to replicate to a newer version and keep both up and running 
> > concurrently.
> 
> Please correct me if I am wrong but I consider 7 to be the current major
> and 4 to be the current minor version. But even when stepping from 7.4.x
> to 7.4.2 the PostgreSQL developers recommend a dump/restore cycle.

Thomas' revised statement is correct. His original assertion is a
hyperbole. From ftp://ftp3.ca.postgresql.org/pub/README.v7.4.3 and
scrolling down the 7.4.2 release notes:

"A dump/restore is not required for those running 7.4.X. However, it may
be advisable as the easiest method of incorporating fixes for two errors
that have been found in the initial contents of 7.4.X system catalogs. A
dump/initdb/reload sequence using 7.4.2's initdb will automatically
correct these problems."

The instructions go on to describe how some SQL commands could be used
to correct a live database instead of doing the dump/restore. The key
point about this recommended dump/restore is that it was because of a
discovered bug not because of a planned dump/restore for every minor
update. The intended plan is that a dump/restore is only needed for
every major upgrade version upgrade (ie. 7.x to 8.x).

> > And PostgreSQL is not owned by or sponsored by any one company; nor is it an 
> > issue to embed PostgreSQL in a commercial product.
> 
> AFAIK PostgreSQL is licensed as GPL with no exceptions, so it is a
> problem for commercial applications.

Following the license link from the webpage
(http://www.postgresql.org/licence.html)
"PostgreSQL is released under the BSD licence."


Patrick