Usability write-up - looking at Pg, especially PgAdmin-III and Pg on Windows, from an inexperienced user PoV

Hi all

I just had the ... pleasure ... of using Windows with Pg again and was
in a usability review frame of mind. I landed up trying to restore my
database using PgAdmin-III, and was astonished at how difficult and
painful it was. The issues weren't all PgAdmin-III either, there are a
few Pg-on-Windows issues and a few plain warts in terms of PostgreSQL
usability in general.

It felt like my first experience with Oracle (ie: screaming, pain and
confusion) not the smooth and pleasurable experience I've come to be so
used to with Pg.

I was sufficiently surprised by some of the issues that I've written up
a post on the matter. I intended it to be a few usability notes, though
it's turned into a bit more than that. I think it's really imporant to
highlight these issues, because if this had been my first experience
with PostgreSQL I would have walked away and never, ever, ever come back.

It might be premature to post this before I've reviewed and re-edited
the post, but hey, a few flames won't hurt. I'm trying to be
constructive in the following, just also trying to express the
frustration that someone new to Pg would experience.

http://blog.ringerc.id.au/2012/05/postgresql-usability-pgadmin-iii-and-pg.html

Brief summary of pain points:

- The need to back up globals separately even when using a custom-format
dump is a giant wart. GIANT. The need to restore them using a completely
separate procedure because you can't use pg_restore just makes it even
uglier.

- Encoding/locale name mismatches between Windows and Linux are really
unpleasant, esp when they prevent the restore of backups w/o
workarounds! Big, big wart here.

- The "restore" dialog in PgAdmin-III needs a lot of love. Harder to
access than it should be, some things very counter-intuitive, blocks UI
while restore in progress, can't cope with "backups" that're really SQL
scripts at all. No interpretation of error codes is offered, which is
particularly important because 0-is-success isn't obvious to most
people. Messages windows aren't updated until the command completes.
The cancel button stays enabled after the command finishes and should be
"Abort" not "Cancel" anyway. No interpretation or hints gets presented
for common errors. It doesn't exclude mutually exclusive options. Etc
etc. Again, see post.

- Running a script from PgAdmin-III is way, way too hard. In fact, as
far as I can tell, you just can't invoke psql with a script w/o
bypassing PgAdmin-III and using the cmdline. Obvious on Linux, but who
wants to use a Windows command line?

- The PgAdmin-III editor can't stream a file to a Pg backend, it has to
load the whole lot into RAM, and it doesn't understand psql syntax even
enough to say "whoops, \connect is only supported by psql, did you mean
to run this as a script in psql?". It also silently wraps everything in
a single transaction, like it or not.

- The PgAdmin-III manual contrasts with the excellent quality and
coverage of the main PostgreSQL docs. Well, the page on the "Restore"
command at least was ... unhelpful. Yes, I'll be submitting improvements.

- Pg's habit of continuing after the first error is really hard on
newbies. This isn't so much something I ran into during this write-up as
something I've been noticing on Stack Overflow. LOTS of people are
confused because they see the last error, not the first error that's the
cause. It's a lot like gcc - the first error is that you forgot to close
a double quote, the rest is garbage you should ignore. A solution to
this is needed from a usability standpoint, but is difficult because so
much existing code relies on psql in particular continuing after errors.
I'd propose having psql cache the first error (ie:non-zero SQLSTATE
response) it gets in memory, and emit that when it exists, eg:

The first error was: ERROR: role "postgres" already exists
... in response to the command: CREATE ROLE postgres;"

Anway, I hope my rant/critique/whine is helpful.

--
Craig Ringer