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

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