Обсуждение: Add --include-table-data-where option to pg_dump, to export only asubset of table data

Greetings,

* Carter Thaxton (carter.thaxton@gmail.com) wrote:
> Many times I've wanted to export a subset of a database, using some sort of
> row filter condition on some of the large tables.  E.g. copying a
> production database to a staging environment, but with some time series
> data only from the past month.
>
> We have the existing options:
>   --include-table=table    (and its -t synonym)
>   --exclude-table=table
>   --exclude-table-data=table
>
> I propose a new option:
>   --include-table-data-where=table:filter_clause
>
> One would use this option as follows:
>
>   pg_dump --include-table-data-where=largetable:"created_at >=
> '2018-05-01'" database_name
>
> The filter_clause is used as the contents of a WHERE clause when querying
> the data to generate the COPY statement produced by pg_dump.

I've wanted something similar to this in the past as well, and, as
you've seen, we have some support for this kind of thing in pg_dump
already and what you're doing is exposing that.

> I've prepared a proposed patch for this, which is attached.  The code
> changes are rather straightforward.  I did have to add the ability to carry
> around an extra pointer-sized object to the simple_list implementation, in
> order to allow the filter clause to be associated to the matching oids of
> the table pattern.  It seemed the best way to augment the existing
> simple_list implementation, but change as little as possible elsewhere in
> the codebase.  (Note that SimpleOidList is actually only used by pg_dump).
>
> Feel free to review and propose any amendments.

I've only taken a quick look but I don't see any regression tests, for
starters, and it's not clear if this can be passed multiple times for
one pg_dump run (I'd certainly hope that it could be...).

Also, if you haven't already, this should be registered on the
commitfest app, so we don't lose track of it.

Thanks!

Stephen

On Tue, May 22, 2018 at 4:05 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Carter Thaxton (carter.thaxton@gmail.com) wrote:
>>   pg_dump --include-table-data-where=largetable:"created_at >=
>> '2018-05-01'" database_name
>
> I've wanted something similar to this in the past as well, and, as
> you've seen, we have some support for this kind of thing in pg_dump
> already and what you're doing is exposing that.

+1

>> I've prepared a proposed patch for this, which is attached.  The code
>> changes are rather straightforward.  I did have to add the ability to carry
>> around an extra pointer-sized object to the simple_list implementation, in
>> order to allow the filter clause to be associated to the matching oids of
>> the table pattern.  It seemed the best way to augment the existing
>> simple_list implementation, but change as little as possible elsewhere in
>> the codebase.  (Note that SimpleOidList is actually only used by pg_dump).
>>
>> Feel free to review and propose any amendments.
>
> I've only taken a quick look but I don't see any regression tests, for
> starters, and it's not clear if this can be passed multiple times for
> one pg_dump run (I'd certainly hope that it could be...).
>
> Also, if you haven't already, this should be registered on the
> commitfest app, so we don't lose track of it.

Thanks for doing that.  Unfortunately the patch seems to be corrupted
in some way, maybe ANSI control characters or something... perhaps you
set colour.ui = always in your git config, instead of auto?  You might
also consider using git format-patch so you can include a brief commit
message that explains the feature.

-- 
Thomas Munro
http://www.enterprisedb.com

2018-05-20 20:48 GMT-03:00 Carter Thaxton <carter.thaxton@gmail.com>:
> Many times I've wanted to export a subset of a database, using some sort of
> row filter condition on some of the large tables.  E.g. copying a production
> database to a staging environment, but with some time series data only from
> the past month.
>
How would you handle foreign keys? It seems easier to produce a dump
that won't restore.

> We have the existing options:
>   --include-table=table    (and its -t synonym)
>   --exclude-table=table
>   --exclude-table-data=table
>
> I propose a new option:
>   --include-table-data-where=table:filter_clause
>
I remembered an old thread [1]. At that time pg_dump was not so
decoupled from the backend. We are far from being decoupled in a way
that someone can write his own pg_dump using only calls from a
library. I'm not sure pg_dump is the right place to add another ETL
parameter. We already have too much parameters that could break a
restore (flexibility is always welcome but too much is not so good).

> One would use this option as follows:
>
>   pg_dump --include-table-data-where=largetable:"created_at >= '2018-05-01'"
> database_name
>
How would you check that that expression is correct? Every parameter
could quote its value. It means that your parameter have to escape the
quote in '2018-05-01'. Another problem is that your spec does not show
us how you would handle tables like Foo.Bar or "foo:bar" (colon have
to be escaped)?

> The filter_clause is used as the contents of a WHERE clause when querying
> the data to generate the COPY statement produced by pg_dump.
>
You are forgetting about --inserts parameter. Could I use
--include-table-data-where and --inserts?


[1] https://www.postgresql.org/message-id/1212299813.17810.17.camel%40ubuntu


-- 
   Euler Taveira                                   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

On Wed, May 23, 2018 at 5:18 PM, Carter Thaxton
<carter.thaxton@gmail.com> wrote:
> Ah yes, thanks.  I did in fact have colors enabled.
> I've attached a new patch generated by `git format-patch`.  Hopefully that's
> correct.

pg_dump.c:2323:2: warning: ISO C90 forbids mixed declarations and code
[-Wdeclaration-after-statement]
  char *filter_clause = NULL;
  ^

You need to declare this variable at the top of its scope.  If you're
using GCC or Clang you might consider building with COPT=-Werror so
that any compiler warnings will stop the build from succeeding.

This doesn't build on Windows[1], probably for the same reason.

 /*
  * Is OID present in the list?
+ * Also return extra pointer-sized data by setting extra_data paramter
  */
 bool
-simple_oid_list_member(SimpleOidList *list, Oid val)
+simple_oid_list_member2(SimpleOidList *list, Oid val, void **extra_data)

I feel like that isn't in the spirit of Lisp "member".  It's now a
kind of association list.  I wonder if we are really constrained to
use the cave-man facilities in fe_utils anyway.  Though I suppose this
list is never going to be super large so maybe the data structure
doesn't matter too much (famous last words).

+ char *where_clause = pg_malloc(strlen(filter_clause) + 8 + 1);
+ strcpy(where_clause, "WHERE (");
+ strcat(where_clause, filter_clause);
+ strcat(where_clause, ")");

pg_dump.c seems to be allowed to use psprintf() which'd be less
fragile than the above code.

+ /* When match_data is set, split the pattern on the ':' chararcter,

typo

+ * Also return extra pointer-sized data by setting extra_data paramter

typo

[1] https://ci.appveyor.com/project/postgresql-cfbot/postgresql/build/1.0.311

-- 
Thomas Munro
http://www.enterprisedb.com

On Fri, Jun 29, 2018 at 8:09 AM, Surafel Temesgen <surafel3000@gmail.com> wrote:
> hey,
> i am reviewing this patch
> On Thu, May 31, 2018 at 4:49 AM, Carter Thaxton <carter.thaxton@gmail.com>
> wrote:
>>
>>
>>   pg_dump --where '"foo:bar":created_at >= '2018-05-01'" dbname
>
> it would be more sqlish if it specified like:
> --table=foo --where ="bar created_at >= 2018-05-01"
> and i don't like the idea of duplicating the existing --table behavior it
> may confuse user
> i rather recommend extending it. And when i test it with --table option the
> content of dump
> file depend on the option specified first.

But you can specify multiple tables.  You wouldn't want the same WHERE
clause to apply to all of them.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jul 02, 2018 at 03:11:46PM -0700, Carter Thaxton wrote:
> Also, there may be some misunderstanding about "foo:bar" above.  That's an
> example of using a namespaced table, where "bar" is a table in the
> namespace "foo".  Normally, assuming your table is named "bar" in the
> default namespace, you would just say something like:
>
>   pg_dump --where "bar:created_at >= 2018-05-01'"

I am wondering how this works at parsing if the table name, or one of
the columns includes a colon character :)
--
Michael

On Tue, Jul 3, 2018 at 6:31 AM, Surafel Temesgen <surafel3000@gmail.com> wrote:
> On Mon, Jul 2, 2018 at 9:27 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> But you can specify multiple tables.  You wouldn't want the same WHERE
>> clause to apply to all of them.
>>
> also with this new --where option you can specify multiple table using
> wildcard and it
> try to apply the same where clause to each table. may be its a desirable
> feature
> because such kind of table can be structurally similar too.

I don't think that's likely to be very useful.  I think Carter Thaxton
has the right idea, although using foo:bar to mean foo.bar doesn't
seem like a great plan.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

Unfortunately, current version of this patch has some conflicts, could you
rebase it?

Hi,

On 2018-11-29 13:50:21 +0100, Dmitry Dolgov wrote:
> Unfortunately, current version of this patch has some conflicts, could you
> rebase it?

Given this patch has not been updated, I'm marking this as returned with feedback.

Greetings,

Andres Freund