Re: Postgresql -- initial impressions and comments

On Mon, 2 Dec 2002, j.random.programmer wrote:

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

No, it's not.  It's not the user that isn't trusted, it's j.random.cracker
that isn't to be trusted.  with the current setup, if j.random.cracker
finds an exploit in Postrgresql, they can trash your database, but they
can't root your box.  If Postgresql were run as root, then
j.random.cracker could then proceed to destroy the whole machine
(including any protected backups, syslogs, etc...) thus covering his
tracks.

This feature (mysql mis-feature?) isn't likely to change any time soon.

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

In the database, on the wire.

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

Yes.  You need a fairly late model jdbc driver.

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

I'm not sure.

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

Good news!  You can submit changes and have them accpeted.  Seriously,
it's much easier to get changes accepted into postgresql than mysql.

For the latest version of the docs, take a look at:

http://developer.postgresql.org/docs/postgres/index.html

They are much more up to date than the ones that came with 7.2.x

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

Just because that's how MySQL does it doesn't make it right (TM rights
reserved, etc...)

NULL is not the same as DEFAULT.  Treating it the same is not correct.
Getting used to incorrect behaviour could result in unexpected results
when dealing with other databases.  MySQL seems to train people to expect
what should not be.

Before 7.3, you HAD to do it the old fashioned way, i.e. either have a
list of fields and leave out the autoincrement field, or put your
autoincrement id at the end of your fields (that's how I always did it)

As of 7.3 you can now use the DEFAULT keyword.  I.e. like so:

insert into table values (DEFAULT,1,2,'howdy')

will work.  7.3 was just released.  It rocks. :-)

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

It certainly sounds useful.  It's not on the todo list, and I don't think
it's scratched and itch for anyone I know of.  Might be a nice place to
get your feet wet if you want to take part in hacking postgresql.

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

Tab delimited it easy, just type '\h copy' from the psql monitor to see
the online (i.e. very short) docs for this, or go here:

http://developer.postgresql.org/docs/postgres/populate.html
or
http://developer.postgresql.org/docs/postgres/sql-copy.html
To see the status of things to be done, i.e. csv copy, see:
http://developer.postgresql.org/todo.php

(i.e. CSV copy is on the list)
>
> 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.

It'll feel more comfortable as time goes by.  Funny thing is, after using
it for a while, when you go back to MySQL for some project, the sticking
points will be MUCH bigger.  I.e. things like "well, if I functional
indexes, I could do this..."

Good luck on using postgresql, and definitely feel free to contribute to
the documentation, it could use several people with a "newbie" point of
view writing and re-writing parts of it.