Re: Adding WHERE clause to pg_dump

On Fri, Jul 25, 2008 at 08:26:35PM +0100, Simon Riggs wrote:
>
> On Fri, 2008-07-25 at 11:46 -0700, Joshua D. Drake wrote:
> > On Fri, 2008-07-25 at 19:33 +0100, Simon Riggs wrote:
> > > On Fri, 2008-07-25 at 13:31 -0400, Tom Lane wrote:
> > > > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > > > > Attached patch implements WHERE clauses for pg_dump.
> > > >
> > > > I still have serious reservations about adding such an ugly,
> > > > non-orthogonal wart to pg_dump. Why is it not appropriate to just
> > > > do a COPY (SELECT ...) TO STDOUT when you need this?
> > >
> > > So you can dump a coherent sample database in one command, not 207.
> > >
> > > Every user of PostgreSQL wants a dev/test database. If the database is
> > > large it isn't practical to take a complete copy. Nor is it practical to
> > > hand-write a data sampling extraction program and if you do, its usually
> > > imperfect in many ways.
> > >
> > > Adding this feature gives a very fast capability to create sample
> > > databases, or incremental backups for many cases.
> >
> > Not sure I buy this argument. I am all for usability and I would be the
> > first to shout about the general ridiculousness of pg_dump/all/restore
> > but in this case I think Tom is right. This feature could easily be done
> > in a script without harassing pg_dump.
>
> You can do it, yes. But it takes a lot longer. If the time to implement
> was similar, then I would immediately agree "feature available already".
>
> pg_dump is not "harassed" by this. What is lost by adding this feature?

This was discussed at the beginning of June on patches, Dave Durham submitted
a patch to add where clauses via a -w option and then in response to feedback
to add it to each each table of -t. See discussion here:

http://archives.postgresql.org/pgsql-patches/2008-06/msg00001.php

and final patch here:

http://archives.postgresql.org/pgsql-patches/2008-06/msg00026.php.

We now have two patches on this topic from different submitters with
different use cases supplied as justification. I have yet another use case
not mentioned by either of the submitters and will probably hand patch
pg_dump locally to do so.

I don't think at this point we should wave this off under the impression
that no one really wants or needs it as obviously some people want it enough
to code it. The other objections seem to be based on the themes:

- code complexity.

Davy's patch is quite simple. I have looked at Simon's yet.

- we need an ETL tool so this should be preempted by that.
- pg_dump should be made into a library so this can be done separately.

We don't generally allow imaginary futures to prevent us from adding
useful functionality on other topics.

- This can be done with a script.

Not really. The script would pretty much have to contain most of
pg_dump. That's more than a script.

- users could make partial dumps and be confused and lose data.

Yes, but they can already do that with -n, -t, and the new pre-data
and post-data switches. This is one more case where the default is
a full dump but you one can specificly request less.

I think that once COPY sprouted a WHERE clause it becomes almost inevitable
that pg_dump will take advantage of them. How many patches on this topic do
we want to ignore?

As you may have guessed by this point:
+1

-dg

--
David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.