Skip site navigation (1) Skip section navigation (2)

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

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Usability write-up - looking at Pg, especially PgAdmin-III and Pg on Windows, from an inexperienced user PoV
Date: 2012-05-25 09:56:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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.

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 

- 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


pgsql-general by date

Next:From: Marti RaudseppDate: 2012-05-25 10:55:26
Subject: Re: Usability write-up - looking at Pg, especially PgAdmin-III and Pg on Windows, from an inexperienced user PoV
Previous:From: Raymond O'DonnellDate: 2012-05-25 08:49:18
Subject: Re: Naming conventions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group