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

Re: Pet Peeves?

From: Steve Atkins <steve(at)blighty(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Pet Peeves?
Date: 2009-01-29 18:22:16
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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  

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  

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.


In response to

  • Pet Peeves? at 2009-01-29 13:16:17 from Gregory Stark


pgsql-general by date

Next:From: Greg SmithDate: 2009-01-29 18:39:08
Subject: Re: Pet Peeves?
Previous:From: Steve CrawfordDate: 2009-01-29 18:18:49
Subject: Re: Pet Peeves?

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