pg_dump --where option

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_dump --where option

Surafel Temesgen

Internally pg_dump have capability to filter the table data to dump by same filter clause but it have no interface to use it and the patch here [1] adds interface to it but it have at-least two issue, one is error message in case of incorrect where clause specification is somehow hard to debug and strange to pg_dump .Other issue is it applies the same filter clause to multiple tables if pattern matching return multiple tables and it seems undesired behavior to me because mostly we don’t want to applied the same where clause specification to multiple table. The attached patch contain a fix for both issue

[1].https://www.postgresql.org/message-id/flat/CAGiT_HNav5B=OfCdfyFoqTa+oe5W1vG=PXkTETCxXg4kcUTktA@...


regards

Surafel


pg_dump_where_clause-v1.patch (17K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump --where option

Cary Huang-2
The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:       tested, failed
Spec compliant:           tested, failed
Documentation:            tested, failed

Hi

I had a look at the patch and it cleanly applies to postgres master branch. I tried to do a quick test on the new "where clause" functionality and for the most part it does the job as described and I'm sure some people will find this feature useful to their database dump needs. However I tried the feature with a case where I have a subquery in the where clause, but it seems to be failing to dump the data. I ran the pg_dump like:

  $ pg_dump -d cary --where="test1:a3 = ( select max(aa1) from test2 )" > testdump2
  $ pg_dump: error: processing of table "public.test1" failed

both test1 and test2 exist in the database and the same subquery works under psql.
 
I also notice that the regression tests for pg_dump is failing due to the patch, I think it is worth looking into the failure messages and also add some test cases on the new "where" clause to ensure that it can cover as many use cases as possible.

thank you
Best regards

Cary Huang
-------------
HighGo Software Inc. (Canada)
[hidden email]
www.highgo.ca
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump --where option

Daniel Gustafsson
> On 10 Jul 2020, at 02:03, Cary Huang <[hidden email]> wrote:
>
> The following review has been posted through the commitfest application:
> make installcheck-world:  tested, failed
> Implements feature:       tested, failed
> Spec compliant:           tested, failed
> Documentation:            tested, failed
>
> Hi
>
> I had a look at the patch and it cleanly applies to postgres master branch. I tried to do a quick test on the new "where clause" functionality and for the most part it does the job as described and I'm sure some people will find this feature useful to their database dump needs. However I tried the feature with a case where I have a subquery in the where clause, but it seems to be failing to dump the data. I ran the pg_dump like:
>
>  $ pg_dump -d cary --where="test1:a3 = ( select max(aa1) from test2 )" > testdump2
>  $ pg_dump: error: processing of table "public.test1" failed
>
> both test1 and test2 exist in the database and the same subquery works under psql.
>
> I also notice that the regression tests for pg_dump is failing due to the patch, I think it is worth looking into the failure messages and also add some test cases on the new "where" clause to ensure that it can cover as many use cases as possible.

As this is being reviewed, but time is running out in this CF, I'm moving this
to the next CF.  The entry will be moved to Waiting for Author based on the
above review.

cheers ./daniel