Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow pg_dump to include/exclude child tables automatically.

  1. [Proposal] Allow pg_dump to include all child tables with the root table

    Gilles Darold <gilles@migops.com> — 2023-01-11T16:59:59Z

    Hi all,


I would like to propose a new pg_dump option called --with-child to 
include or exclude from a dump all child and partition tables when a 
parent table is specified using option -t/--table or -T/--exclude-table. 
The whole tree is dumped with the root table.


To include all partitions or child tables with inheritance in a table 
dump we usually use the wildcard, for example:


     pg_dump -d mydb -t "root_tbname*" > out.sql


This suppose that all child/partition tables use the prefix root_tbname 
in their object name. This is often the case but, if you are as lucky as 
me, the partitions could have a total different name. No need to say 
that for inheritance this is rarely the case. The other problem is that 
with the wildcard you can also dump relations that are not concerned at 
all by what you want to dump. Using the --with-child option will allow 
to just specify the root relation and all child/partition definitions 
and/or data will be parts of dump.


     pg_dump -d mydb --table "root_tbname" --with-childs > out.sql


To exclude a whole inheritance tree from a dump:


     pg_dump -d mydb --exclude-table "root_tbname" --with-childs > out.sql


Here in attachment the patch that adds this feature to pg_dump.


Is there is any interest for this feature?


Best regards,

-- 
Gilles Darold
https://www.migops.com/

  2. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Cary Huang <cary.huang@highgo.ca> — 2023-02-24T22:49:17Z

    The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           not tested
Documentation:            not tested

Hi

the patch applies fine on current master branch and it works as described. However, I would suggest changing the new option name from "--with-childs" to "--with-partitions" for several reasons. 

"childs" is grammatically incorrect and in the PG community, the term "partitioned table" is normally used to denote a parent table, and the term "partition" is used to denote the child table under the parent table. We should use these terms to stay consistent with the community.

Also, I would rephrase the documentation as:

Used in conjunction with <option>-t</option>/<option>--table</option> or <option>-T</option>/<option>--exclude-table</option> options to include or exclude partitions of the specified tables if any.

thank you

Cary Huang
================
HighGo Software Canada
www.highgo.ca

  3. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Stéphane Tachoires <stephane.tachoires@gmail.com> — 2023-02-25T16:40:17Z

    Hi,

I'm not sure about the "child" -> "partition" change as it also selects
childs that are not partitions.
I'm more dubious about the --with-childs option, I'd rather have
--table-with-childs=<PATTERN> and --exclude-table-with-childs=<PATTERN>.
That will be clearer about what is what.

I'm working on that, but have a hard time with test pg_dump/002_pg_dump
(It's brand new to me)

Stéphane

Le ven. 24 févr. 2023 à 23:50, Cary Huang <cary.huang@highgo.ca> a écrit :

> The following review has been posted through the commitfest application:
> make installcheck-world:  tested, passed
> Implements feature:       tested, passed
> Spec compliant:           not tested
> Documentation:            not tested
>
> Hi
>
> the patch applies fine on current master branch and it works as described.
> However, I would suggest changing the new option name from "--with-childs"
> to "--with-partitions" for several reasons.
>
> "childs" is grammatically incorrect and in the PG community, the term
> "partitioned table" is normally used to denote a parent table, and the term
> "partition" is used to denote the child table under the parent table. We
> should use these terms to stay consistent with the community.
>
> Also, I would rephrase the documentation as:
>
> Used in conjunction with <option>-t</option>/<option>--table</option> or
> <option>-T</option>/<option>--exclude-table</option> options to include or
> exclude partitions of the specified tables if any.
>
> thank you
>
> Cary Huang
> ================
> HighGo Software Canada
> www.highgo.ca



-- 
"Où se posaient les hirondelles avant l'invention du téléphone ?"
  -- Grégoire Lacroix

  4. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Gilles Darold <gilles@migops.com> — 2023-02-25T18:59:47Z

    Le 25/02/2023 à 16:40, Stéphane Tachoires a écrit :
> Hi,
>
> I'm not sure about the "child" -> "partition" change as it also 
> selects childs that are not partitions.
> I'm more dubious about the --with-childs option, I'd rather have 
> --table-with-childs=<PATTERN> and 
> --exclude-table-with-childs=<PATTERN>. That will be clearer about what 
> is what.
>
> I'm working on that, but have a hard time with 
> test pg_dump/002_pg_dump (It's brand new to me)
>
> Stéphane
>
> Le ven. 24 févr. 2023 à 23:50, Cary Huang <cary.huang@highgo.ca> a écrit :
>
>     The following review has been posted through the commitfest
>     application:
>     make installcheck-world:  tested, passed
>     Implements feature:       tested, passed
>     Spec compliant:           not tested
>     Documentation:            not tested
>
>     Hi
>
>     the patch applies fine on current master branch and it works as
>     described. However, I would suggest changing the new option name
>     from "--with-childs" to "--with-partitions" for several reasons.
>
>     "childs" is grammatically incorrect and in the PG community, the
>     term "partitioned table" is normally used to denote a parent
>     table, and the term "partition" is used to denote the child table
>     under the parent table. We should use these terms to stay
>     consistent with the community.
>
>     Also, I would rephrase the documentation as:
>
>     Used in conjunction with
>     <option>-t</option>/<option>--table</option> or
>     <option>-T</option>/<option>--exclude-table</option> options to
>     include or exclude partitions of the specified tables if any.
>
>     thank you
>
>     Cary Huang
>     ================
>     HighGo Software Canada
>     www.highgo.ca <http://www.highgo.ca>
>

Hi,


This is right this patch also works with inherited tables so 
--with-partitions can be confusing this is why --with-childs was chosen. 
But I disagree the use of --table-with-childs and 
--exclude-table-with-childs because we already have the --table and 
--exclude-table, and it will add lot of code where we just need a switch 
to include children tables. Actually my first though was that this 
behavior (dump child tables when the root table is dumped using --table) 
should be the default in pg_dump but the problem is that it could break 
existing scripts using pg_dump so I prefer to implement the 
--with-childs options.


I think we can use --with-partitions, provided that it is clear in the 
documentation that this option also works with inheritance.


Attached is a new patch v2 using the --with-partitions and the 
documentation fix.


-- 
Gilles Darold

  5. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-04T19:18:13Z

    Gilles Darold <gilles@migops.com> writes:
> But I disagree the use of --table-with-childs and 
> --exclude-table-with-childs because we already have the --table and 
> --exclude-table, and it will add lot of code where we just need a switch 
> to include children tables.

I quite dislike the idea of a separate --with-whatever switch, because
it will (presumably) apply to all of your --table and --exclude-table
switches, where you may need it to apply to just some of them.
Spelling considerations aside, attaching the property to the
individual switches seems far superior.  And I neither believe that
this would add a lot of code, nor accept that as an excuse even if
it's true.

As noted, "childs" is bad English and "partitions" is flat out wrong
(unless you change it to recurse only to partitions, which doesn't
seem like a better definition).  We could go with
--[exclude-]table-and-children, or maybe
--[exclude-]table-and-child-tables, but those are getting into
carpal-tunnel-syndrome-inducing territory :-(.  I lack a better
naming suggestion offhand.

			regards, tom lane

  6. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Gilles Darold <gilles@migops.com> — 2023-03-05T08:03:33Z

    Le 04/03/2023 à 19:18, Tom Lane a écrit :
> Gilles Darold <gilles@migops.com> writes:
>> But I disagree the use of --table-with-childs and
>> --exclude-table-with-childs because we already have the --table and
>> --exclude-table, and it will add lot of code where we just need a switch
>> to include children tables.
> I quite dislike the idea of a separate --with-whatever switch, because
> it will (presumably) apply to all of your --table and --exclude-table
> switches, where you may need it to apply to just some of them.
> Spelling considerations aside, attaching the property to the
> individual switches seems far superior.  And I neither believe that
> this would add a lot of code, nor accept that as an excuse even if
> it's true.y..


Right, this is not a lot of code but just more code where I think we 
just need a switch. I much prefer that it applies to all --table / 
--exclude-table because this is generally the behavior we want for all 
root/parent tables. But I agree that in some cases users could want that 
this behavior applies to some selected tables only so the proposed new 
options can answer to this need. Even if generally in similar cases 
several pg_dump commands can be used. This is just my opinion, I will 
adapt the patch to use the proposed new options.


But, what do you think about having pg_dump default to dump children 
tables with --table / --exclude-table? I was very surprised that this 
was not the case the first time I see that. In this case we could add 
--[exclude-]table-no-child-tables. I think this form will be less used 
than the form where we need the child tables to be dump with the parent 
table, meaning that most of the time pg_dump commands using --table and 
--exclude-table will be kept untouched and those using more regexp to 
dump child tables could be simplified. I'm not sure that the backward 
compatibility is an argument here to not change the default behavior of 
pg_dump.

--

Gilles



-- 
Gilles Darold

  7. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Gilles Darold <gilles@migops.com> — 2023-03-11T18:51:01Z

    Le 04/03/2023 à 20:18, Tom Lane a écrit :
> As noted, "childs" is bad English and "partitions" is flat out wrong
> (unless you change it to recurse only to partitions, which doesn't
> seem like a better definition).  We could go with
> --[exclude-]table-and-children, or maybe
> --[exclude-]table-and-child-tables, but those are getting into
> carpal-tunnel-syndrome-inducing territory 🙁.  I lack a better
> naming suggestion offhand.


In attachment is version 3 of the patch, it includes the use of options 
suggested by Stephane and Tom:

     --table-and-children,

     --exclude-table-and-children

     --exclude-table-data-and-children.

  Documentation have been updated too.


Thanks

-- 
Gilles Darold

  8. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Gilles Darold <gilles@migops.com> — 2023-03-12T09:04:34Z

    Le 11/03/2023 à 19:51, Gilles Darold a écrit :
> Le 04/03/2023 à 20:18, Tom Lane a écrit :
>> As noted, "childs" is bad English and "partitions" is flat out wrong
>> (unless you change it to recurse only to partitions, which doesn't
>> seem like a better definition).  We could go with
>> --[exclude-]table-and-children, or maybe
>> --[exclude-]table-and-child-tables, but those are getting into
>> carpal-tunnel-syndrome-inducing territory 🙁.  I lack a better
>> naming suggestion offhand.
>
>
> In attachment is version 3 of the patch, it includes the use of 
> options suggested by Stephane and Tom:
>
>     --table-and-children,
>
>     --exclude-table-and-children
>
>     --exclude-table-data-and-children.
>
>  Documentation have been updated too.
>
>
> Thanks
>

New version v4 of the patch attached with a typo in documentation fixed.

-- 
Gilles Darold.

  9. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Stéphane Tachoires <stephane.tachoires@gmail.com> — 2023-03-12T18:05:42Z

    Hi Gilles,

On Ubuntu 22.04.2 all deb's updated, I have an error on a test
I'll try to find where and why, but I think you should know first.

1/1 postgresql:pg_dump / pg_dump/002_pg_dump        ERROR            24.40s
  exit status 1
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
✀
 ――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
stderr:
#   Failed test 'only_dump_measurement: should dump CREATE TABLE
test_compression'
#   at /media/hddisk/stephane/postgresql/src/postgresql/src/bin/pg_dump/t/
002_pg_dump.pl line 4729.
# Review only_dump_measurement results in
/media/hddisk/stephane/postgresql/build/testrun/pg_dump/002_pg_dump/data/tmp_test_iJxJ
# Looks like you failed 1 test of 10264.

(test program exited with status code 1)
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――


Summary of Failures:

1/1 postgresql:pg_dump / pg_dump/002_pg_dump ERROR            24.40s   exit
status 1


Ok:                 0
Expected Fail:      0
Fail:               1
Unexpected Pass:    0
Skipped:            0
Timeout:            0

Join, only_dump_measurement.sql from
/media/hddisk/stephane/postgresql/build/testrun/pg_dump/002_pg_dump/data/tmp_test_iJxJ
If you need more information, please ask...

Stéphane.


Le dim. 12 mars 2023 à 10:04, Gilles Darold <gilles@migops.com> a écrit :

> Le 11/03/2023 à 19:51, Gilles Darold a écrit :
> > Le 04/03/2023 à 20:18, Tom Lane a écrit :
> >> As noted, "childs" is bad English and "partitions" is flat out wrong
> >> (unless you change it to recurse only to partitions, which doesn't
> >> seem like a better definition).  We could go with
> >> --[exclude-]table-and-children, or maybe
> >> --[exclude-]table-and-child-tables, but those are getting into
> >> carpal-tunnel-syndrome-inducing territory 🙁.  I lack a better
> >> naming suggestion offhand.
> >
> >
> > In attachment is version 3 of the patch, it includes the use of
> > options suggested by Stephane and Tom:
> >
> >     --table-and-children,
> >
> >     --exclude-table-and-children
> >
> >     --exclude-table-data-and-children.
> >
> >  Documentation have been updated too.
> >
> >
> > Thanks
> >
>
> New version v4 of the patch attached with a typo in documentation fixed.
>
> --
> Gilles Darold.
>


-- 
"Où se posaient les hirondelles avant l'invention du téléphone ?"
  -- Grégoire Lacroix

  10. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Gilles Darold <gilles@migops.com> — 2023-03-13T15:15:12Z

    Le 12/03/2023 à 19:05, Stéphane Tachoires a écrit :
>
> Hi Gilles,
>
> On Ubuntu 22.04.2 all deb's updated, I have an error on a test
> I'll try to find where and why, but I think you should know first.
>
> 1/1 postgresql:pg_dump / pg_dump/002_pg_dump        ERROR           
>  24.40s   exit status 1
> ―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――― 
> ✀ 
>  ――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
> stderr:
> #   Failed test 'only_dump_measurement: should dump CREATE TABLE 
> test_compression'
> #   at 
> /media/hddisk/stephane/postgresql/src/postgresql/src/bin/pg_dump/t/002_pg_dump.pl 
> <http://002_pg_dump.pl> line 4729.
> # Review only_dump_measurement results in 
> /media/hddisk/stephane/postgresql/build/testrun/pg_dump/002_pg_dump/data/tmp_test_iJxJ
> # Looks like you failed 1 test of 10264.

Hi Stephane,


Odd, I do not have this error when running make installcheck, I have the 
same OS version as you.


     +++ tap check in src/bin/pg_dump +++
     t/001_basic.pl ................ ok
     t/002_pg_dump.pl .............. ok
     t/003_pg_dump_with_server.pl .. ok
     t/010_dump_connstr.pl ......... ok
     All tests successful.
     Files=4, Tests=9531, 11 wallclock secs ( 0.33 usr  0.04 sys + 3.05 
cusr  1.22 csys =  4.64 CPU)
     Result: PASS

Anyway this test must be fixed and this is done in version v5 of the 
patch attached here.


Thanks for the review.

-- 
Gilles Darold

  11. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Stéphane Tachoires <stephane.tachoires@gmail.com> — 2023-03-14T09:49:24Z

    Hi Gilles,
V5 is ok (aside for LLVM 14 deprecated warnings, but that's another
problem) with meson compile and meson test on Ubuntu 20.04.2.
Code fits well and looks standart, --help explain what it does clearly, and
documentation is ok (but as a Français, I'm not an expert in English).

Stéphane

Le lun. 13 mars 2023 à 16:15, Gilles Darold <gilles@migops.com> a écrit :

> Le 12/03/2023 à 19:05, Stéphane Tachoires a écrit :
>
>
> Hi Gilles,
>
> On Ubuntu 22.04.2 all deb's updated, I have an error on a test
> I'll try to find where and why, but I think you should know first.
>
> 1/1 postgresql:pg_dump / pg_dump/002_pg_dump        ERROR
>  24.40s   exit status 1
> ――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
> ✀
>  ――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
> stderr:
> #   Failed test 'only_dump_measurement: should dump CREATE TABLE
> test_compression'
> #   at /media/hddisk/stephane/postgresql/src/postgresql/src/bin/pg_dump/t/
> 002_pg_dump.pl line 4729.
> # Review only_dump_measurement results in
> /media/hddisk/stephane/postgresql/build/testrun/pg_dump/002_pg_dump/data/tmp_test_iJxJ
> # Looks like you failed 1 test of 10264.
>
>
> Hi Stephane,
>
>
> Odd, I do not have this error when running make installcheck, I have the
> same OS version as you.
>
>
>     +++ tap check in src/bin/pg_dump +++
>     t/001_basic.pl ................ ok
>     t/002_pg_dump.pl .............. ok
>     t/003_pg_dump_with_server.pl .. ok
>     t/010_dump_connstr.pl ......... ok
>     All tests successful.
>     Files=4, Tests=9531, 11 wallclock secs ( 0.33 usr  0.04 sys +  3.05
> cusr  1.22 csys =  4.64 CPU)
>     Result: PASS
>
> Anyway this test must be fixed and this is done in version v5 of the patch
> attached here.
>
>
> Thanks for the review.
>
> --
> Gilles Darold
>
>

-- 
"Où se posaient les hirondelles avant l'invention du téléphone ?"
  -- Grégoire Lacroix

  12. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Stéphane Tachoires <stephane.tachoires@gmail.com> — 2023-03-14T09:50:32Z

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

V5 is ok (aside for LLVM 14 deprecated warnings, but that's another problem) with meson compile and meson test on Ubuntu 20.04.2.
Code fits well and looks standart, --help explain what it does clearly, and documentation is ok (but as a Français, I'm not an expert in English).

  13. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Gilles Darold <gilles@migops.com> — 2023-03-14T10:44:59Z

    Le 14/03/2023 à 10:50, stephane tachoires a écrit :
> The following review has been posted through the commitfest application:
> make installcheck-world:  tested, passed
> Implements feature:       tested, passed
> Spec compliant:           tested, passed
> Documentation:            tested, passed
>
> V5 is ok (aside for LLVM 14 deprecated warnings, but that's another problem) with meson compile and meson test on Ubuntu 20.04.2.
> Code fits well and looks standart, --help explain what it does clearly, and documentation is ok (but as a Français, I'm not an expert in English).


Thanks Stepane, I've changed commit fest status to "Ready for committers".

-- 
Gilles Darold

  14. Re: [Proposal] Allow pg_dump to include all child tables with the root table

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-14T20:10:16Z

    Gilles Darold <gilles@migops.com> writes:
> Thanks Stepane, I've changed commit fest status to "Ready for committers".

Pushed with some minor editing.

			regards, tom lane