Re: Pet Peeves? - Mailing list pgsql-general

On Jan 29, 2009, at 5:16 AM, Gregory Stark wrote:

>
> I'm putting together a talk on "PostgreSQL Pet Peeves" for
> discussion at
> FOSDEM 2009 this year. I have a pretty good idea what some them are
> of course,
> but I would be interested to hear if people have any complaints from
> personal
> experience. What would be most interesting is if you can explain an
> example of
> when the problem caused real inconvenience to you, since sometimes
> it's hard
> to see from a theoretical description where the real harm lies.
>
> So, what do people say? Is Postgres perfect in your world or does it
> do some
> things which rub you the wrong way?
>
> Feel free to respond on-list or if you prefer in personal emails. I
> do intend
> to use the ideas you give in my presentation so mark anything you
> wouldn't be
> happy to see in a slide at a conference some day.

1. Version upgrades require a dump and restore, which is painfully slow.

2. No distinction between parse errors and execution errors in psql. I
want mistakes that can be detected at parse time (typos, invalid
syntax, non-existent columns) to throw an message, and not affect the
backend state. I want other errors to abort the transaction.
ON_ERROR_ROLLBACK is better than nothing, but I'd like to distinguish
the two cases.

3. Returning free space to the OS when there've been large changes to
a large table. VACUUM FULL is too slow to be useful, CLUSTER requires
an index, and can often be too slow. I tend to resort to CREATE TABLE
AS or pg_dump, and manually handle the constraints, which is ugly. I'd
be happy to have a vacuum equivalent that ran for days, shuffling
empty pages to the end of the table, as long as it didn't interrupt
service.

4. UPSERT. It's a really useful primitive, and we don't have it.

5. Backslash characters in strings. It's tricky to put a string in the
database with a backslash in it, in a way that'll support different
versions of the backend.

6. Where's my CPAN equivalent? Postgresql is extensible, but it's hard
to find the extension you need, and often harder than it should be to
install.

7. Table partitioning. It's nice, but rather limited.

8. pg_dump -d / -D. It's just a footgun.

9. psql vs pg_restore for restoring dumps. It's confusing to explain
to people.

10. Backups in general. There are much better approaches than pg_dump,
but explaining them to users is too complex.

Cheers,
   Steve