Thread: 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

On Fri, May 25, 2012 at 12:56 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> 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.

Agreed on all points, I've had to help people using pgAdmin with many
of the problems you point out. Note that the pgadmin-hackers list is
also relevant.

Together with an earlier study about common PostgreSQL pitfalls, I've
created an article on the wiki:
https://wiki.postgresql.org/wiki/Usability_reviews

However, I suspect posting long rants like this won't get us very far.
Instead, I think these problems should be posted one by one, with
proposed solutions, so they can be discussed in detail without
distractions. Once there seems to be a consensus for a solution, add a
TODO list item -- I suspect most of these items are quite easy to
solve in code, once it's clear *how* to address them. And the TODO
list has been short of simple items recently.

Regards,
Marti

On 25/05/2012 6:55 PM, Marti Raudsepp wrote:
> Together with an earlier study about common PostgreSQL pitfalls, I've
> created an article on the wiki:
> https://wiki.postgresql.org/wiki/Usability_reviews
>
> However, I suspect posting long rants like this won't get us very far.
> Instead, I think these problems should be posted one by one, with
> proposed solutions, so they can be discussed in detail without
> distractions. Once there seems to be a consensus for a solution, add a
> TODO list item -- I suspect most of these items are quite easy to
> solve in code, once it's clear *how* to address them. And the TODO
> list has been short of simple items recently.

You're quite right. FWIW, it wasn't intended to be a long rant - I
started out writing a point-by-point review of a few usability issues -
though it certainly turned into a long rant as I started seeing more and
more issues.

It's my intention to break that down into specific problem areas and
points, I just thought it was worth getting a few initial impressions too.

--
Craig Ringer

Hi,

On Fri, 2012-05-25 at 17:56 +0800, Craig Ringer wrote:
> 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
>

Didn't yet read the blog post but will do later.

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

+1

I would love to see new pg_dump options that will add the CREATE ROLE
and CREATE TABLESPACE statements needed to restore a specific database
dump.

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

Kinda agree, but don't see a really good solution here.

> - The "restore" dialog in PgAdmin-III needs a lot of love.

+1

>  Harder to access than it should be

I guess you mean that you want the restore option at the server level.
Could be a nice idea, I see how we could do that. It would also allow us
to add an option to create the database, just before restoring the dump.

> , some things very counter-intuitive

Like?

> , blocks UI while restore in progress

Shouldn't do so. Of course, if you restore your dump on the same
computer, you may experience some extreme latency that will make you
think you're blocked.

> , can't cope with "backups" that're really SQL scripts at all

Yes, we don't provide psql that would allow us to restore SQL scripts. I
would make the distribution package bigger, and we don't think it would
be good.

> . No interpretation of error codes is offered

Kinda hard when you're running an command line tool to read and
interpret every error.

> , which is
> particularly important because 0-is-success isn't obvious to most
> people.

Sure.

>  Messages windows aren't updated until the command completes.

It should work (and it works for VACUUM IIRC) but for some reasons,
unknown to me, it doesn't with this specific window.

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

You only have to open the query tool. Doesn't seem that hard to me.

The only thing the query tool cannot do is COPY from stdin, or to
stdout. Which is annoying because you cannot restore a SQL dump this
way.

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

That's right. Find me a good UI for that, and I may be able to do it.

>  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

Yes. That's a good thing and a bad thing. To be able to execute a SQL
file, you need to load it on the query tool. That helps you to review
it, and if the execution goes wrong, it shows you where the first error
is.

And if you want to execute a really big SQL file, pgAdmin is right now
simply the wrong tool. We may had a menu item to select a file and
execute it on a database. Not sure lots of people will find it useful.

> , 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?".

About psql metacommands, the query tool doesn't know them, and won't
know them, unless somone wants to work on this. I simply won't.

But I like the idea of telling the user "...only supported by psql...".
That would be a good thing to have, and something I would like to work
on.

>  It also silently wraps everything in
> a single transaction, like it or not.
>

Actually, it doesn't wrap everything in a transaction, but it executes
all the queries you run at once in a multi-statements query. The result
is the same, actually. The issue is to find a way to split the queries.
We should probably take a look at psql and see how it does that.

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

Let's say the pgadmin manual simply doesn't exist right now.

BTW, we want to do a complete rewrite of the manual. Actually, that's
something I want to work on during the beta phase.

>  Yes, I'll be submitting improvements.
>

You're welcome to do so.

> - 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;"
>

Sounds fine with me.

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

It certainly is, but it will be more helpful if you could work on some
patches :)


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

On Fri, 2012-05-25 at 19:15 +0800, Craig Ringer wrote:
> On 25/05/2012 6:55 PM, Marti Raudsepp wrote:
> > Together with an earlier study about common PostgreSQL pitfalls, I've
> > created an article on the wiki:
> > https://wiki.postgresql.org/wiki/Usability_reviews
> >
> > However, I suspect posting long rants like this won't get us very far.
> > Instead, I think these problems should be posted one by one, with
> > proposed solutions, so they can be discussed in detail without
> > distractions. Once there seems to be a consensus for a solution, add a
> > TODO list item -- I suspect most of these items are quite easy to
> > solve in code, once it's clear *how* to address them. And the TODO
> > list has been short of simple items recently.
>
> You're quite right. FWIW, it wasn't intended to be a long rant - I
> started out writing a point-by-point review of a few usability issues -
> though it certainly turned into a long rant as I started seeing more and
> more issues.
>

And there are certainly more issues than the ones you talked about.

I'm really interested in knowing them, to be able to fix them.

> It's my intention to break that down into specific problem areas and
> points, I just thought it was worth getting a few initial impressions too.
>

Not sure it's the best way because it's difficult, if not impossible, to
answer some points (for example "some things very counter-intuitive").

Anyway, please, start some threads on pgadmin-hackers, so that we can
start fixing issues, and bugs you've found.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com