Re: Postgresql -- initial impressions and comments

Hi, thanks for your comments. Just a couple quick thoughts in response.

Regarding documentation, did you find http://techdocs.postgresql.org? It
has lots of fill-in-the-gaps documents contributed by various readers,
including several that cover different aspects of moving from mysql to
postgresql. There's also an FAQ early on that I think might help answer
your questions about md5. That may be a lot less of an issue in postgresql
7.3, if I understand the release notes correctly...

As far as doing things like MySQL does... Postgresql's design goal has
generally been to comply with SQL 92 and/or SQL 99 standards as much as
possible. MySQL does a lot of sloppy stuff (IMHO) that isn't standards
compliant in the least. Seems better to let ANSI set the standards. As for
inserting null values into primary key fields and expecting the database to
fill in the next 'autoincrement' value... that just seems very
non-intuitive to me, as well as non-portable. If I write a SQL statement
that says to insert a null value into a column, I want the database to try
and insert a null value into the column, not some autoincrement value or
other default. For any column that has a default specified (as postgresql's
serial columns do), it seems more reasonable for the database to use the
default when the field isn't specified, and to use 'null' if the insert
statement says null. Relying on the database's column order in the
application's SQL statements creates a lot of extra coupling between the
application and database structure. I once worked on an application that
was done that way, using MySQL, and it was a major pain paying attention to
column order when we wanted to make a small change to a table's schema.
Spelling out the field names would have saved some grief.

In any case, I'm glad you're exploring postgresql. This doc points out a
few other gotchas you may run across:
http://techdocs.postgresql.org/techdocs/mysql2postgresql.php. Good luck!

Wes Sheldahl



"j.random.programmer" <javadesigner@yahoo.com>@postgresql.org on 12/02/2002
09:44:03 PM

Sent by:    pgsql-general-owner@postgresql.org


To:    pgsql-general@postgresql.org
cc:
Subject:    [GENERAL] Postgresql -- initial impressions and comments


Hi:

I am a long time MySQL/Innodb user. I just installed
postgres 7.3 on my linux box and thought I'd post
some comments:

1)
Running postgres as non-root is understandable but
should not be _mandated_ (for totally private
networks,
it's overkill). Trust the user...

2)
It's not clear what md5 password auth does.

(a) Is the password stored as md5 in the database
itself, or only md5's on the wire (from client to
server) and then thereafter stored as plain text in
the db itself ?

(b) If the client is responsible for the md5
encryption,
then does the JDBC driver do this for us automatically
?

(c) Is there a md5 function in the database ?

The docs don't really answer these points at all -
especially (b) and (c).

3) The documentation needs to be radically improved.
The mysql docs are much more comprehensive. Postgres
wins hands down in the database internals
documentation
(mysql doesn't have any) but loses in the userland
documentation.

4) The auto-increment ("serial") fields are very
badly documented.

Normally, I want to say something like:

  INSERT into foo values (null, 'a', 'b',...)

where the first field is defined as serial. However
I can't send null to that field and expect it to be
auto incremented - I have to either list out my
fields in the statment (and omit the serial field):

  INSERT into foo
   (field_a, field_b,...) values  ('a', 'b', ...)

which is a drag or I have to use a funky nextval
command. Why can't postgres simply accept null for
the serial field and simply fill in the next value ?
This would make it easier to use (don't have to type
in the field list) and more consistent (with mysql and
perhaps other databases).

5) There is no way to grant permissions on all tables
within a database to some user. You have to grant
permissions on each table one-by-one. What I want
to do (and mysql allows this) is something like:

GRANT ALL on foodb.* to user_bar;

where 'foodb' is the name of a database. This sucks.

6) Well, docs again: there are no examples or
techniques for importing/exporting comma or tab
delimited files from/to tables ? Is this possible but
I just didn't find it ?

Well, having played with postgres for the first time,
these were the initial (after 4-5 hours) points that
came up. But overall, it looks and feels like a solid
product. The hot dump is a nice feature - I can
dump the db without shutting it down - innodb in
contrast charges $$ for this feature.

Best regards,

javadesigner@yahoo.com

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly