Thread: Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
I just got an autoreply from David stating he will be away until August 9 if we want this functionality we have to code it ourselves. If not it can wait until the next major release. If anyone wants the original patch I can supply it. --------------------------------------------------------------------------- Tom Lane wrote: > "David F. Skoll" <dfs@roaringpenguin.com> writes: > > On Wed, 21 Jul 2004, Tom Lane wrote: > >> pg_dump -t s1.t1 -t s2.t2 -- Dump s1.t1 and s2.t2 > > > That's a good idea, but then it's questionable whether we need the -n > > switch at all. > > Sure we do --- for backwards compatibility if nothing else. > > > It might be simpler to extend the -t switch to accept: > > pg-dump -t 's1.*' > > That would not be the same thing --- that would mean to dump *only tables* > from s1, rather than objects of all types. Anyway, I think it's a bit > late in this cycle to be proposing to implement wild-card matching. > Maybe for next time someone can do that, but for 7.5 I think we should > limit ourselves to cleaning up any design flaws of the already-submitted > patch. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I just got an autoreply from David stating he will be away until August
> 9 if we want this functionality we have to code it ourselves. If not it
> can wait until the next major release.
It can wait --- it was submitted after feature freeze anyway, and we
certainly have more than enough other things to do in the next couple days.
regards, tom lane
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
From
Christopher Kings-Lynne
Date:
>>I just got an autoreply from David stating he will be away until August >>9 if we want this functionality we have to code it ourselves. If not it >>can wait until the next major release. > > It can wait --- it was submitted after feature freeze anyway, and we > certainly have more than enough other things to do in the next couple days. I have a plan to allow pg_dump to dump any object in the next version - i suspect these two ideas will need reconciliation. Chris
TODO item? --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > >>I just got an autoreply from David stating he will be away until August > >>9 if we want this functionality we have to code it ourselves. If not it > >>can wait until the next major release. > > > > It can wait --- it was submitted after feature freeze anyway, and we > > certainly have more than enough other things to do in the next couple days. > > I have a plan to allow pg_dump to dump any object in the next version - > i suspect these two ideas will need reconciliation. > > Chris > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
From
Christopher Kings-Lynne
Date:
Perhaps. I was also thinking that maybe it's time to combine pg_dumpall and pg_dump into a single utility. At the moment, I can't see how pg_dumpall can ever have a -Fc option, since it will be messy to interact with the pg_dump processes. I was thinking a pg_export utility that can output to a range of other databases SQL formats would also be a good idea. It would share about 90% of the pg_dump code, but I'm trying to think of how to avoid duplicating the code. How about we have a whole pg_dump/dumpall/restore/backup section in the TODO file? Chris Bruce Momjian wrote: > TODO item? > > --------------------------------------------------------------------------- > > Christopher Kings-Lynne wrote: > >>>>I just got an autoreply from David stating he will be away until August >>>>9 if we want this functionality we have to code it ourselves. If not it >>>>can wait until the next major release. >>> >>>It can wait --- it was submitted after feature freeze anyway, and we >>>certainly have more than enough other things to do in the next couple days. >> >>I have a plan to allow pg_dump to dump any object in the next version - >>i suspect these two ideas will need reconciliation. >> >>Chris >> > >
Yes, shoot over that the section should contain. --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > Perhaps. I was also thinking that maybe it's time to combine pg_dumpall > and pg_dump into a single utility. At the moment, I can't see how > pg_dumpall can ever have a -Fc option, since it will be messy to > interact with the pg_dump processes. > > I was thinking a pg_export utility that can output to a range of other > databases SQL formats would also be a good idea. It would share about > 90% of the pg_dump code, but I'm trying to think of how to avoid > duplicating the code. > > How about we have a whole pg_dump/dumpall/restore/backup section in the > TODO file? > > Chris > > Bruce Momjian wrote: > > > TODO item? > > > > --------------------------------------------------------------------------- > > > > Christopher Kings-Lynne wrote: > > > >>>>I just got an autoreply from David stating he will be away until August > >>>>9 if we want this functionality we have to code it ourselves. If not it > >>>>can wait until the next major release. > >>> > >>>It can wait --- it was submitted after feature freeze anyway, and we > >>>certainly have more than enough other things to do in the next couple days. > >> > >>I have a plan to allow pg_dump to dump any object in the next version - > >>i suspect these two ideas will need reconciliation. > >> > >>Chris > >> > > > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > TODO item? On that note several prior conversations I had here ended with WIBNI conclusions that really ought to be TODO items, in my humble opinion. Two come to mind off the top of my head resulting in: . "SELECT * FROM x JOIN y USING (b) WHERE a=?" could use an index on y(a,b) since for a constant value of "a" the index traversalwould be effectively equivalent just be "b". This could result in an efficient merge join avoiding an unnecessarysort. . The semantics for row-value expressions is wrong. (a,b) < (x,y) should be true if a<x or if a=x and b<y. Currently it expandsto a<x and b<y. . Fix row-value expression handling to not depend on the operator names and instead use btree access method strategy valuesinstead, allowing row-value expressions on other operators with <,=,> behaviour (ie btree indexable behaviour). . Allow multi-column indexes to be used to optimize row-value expressions. Ie, allow a btree index on a,b to be used to executean expression like (a,b) < (x,y). -- greg
Greg Stark wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > TODO item? > > On that note several prior conversations I had here ended with WIBNI > conclusions that really ought to be TODO items, in my humble opinion. Two come > to mind off the top of my head resulting in: > > . "SELECT * FROM x JOIN y USING (b) WHERE a=?" could use an index on y(a,b) > since for a constant value of "a" the index traversal would be effectively > equivalent just be "b". This could result in an efficient merge join > avoiding an unnecessary sort. > > . The semantics for row-value expressions is wrong. (a,b) < (x,y) should be > true if a<x or if a=x and b<y. Currently it expands to a<x and b<y. > > . Fix row-value expression handling to not depend on the operator names and > instead use btree access method strategy values instead, allowing row-value > expressions on other operators with <,=,> behaviour (ie btree indexable > behaviour). > > . Allow multi-column indexes to be used to optimize row-value expressions. Ie, > allow a btree index on a,b to be used to execute an expression like (a,b) < > (x,y). I have not heard of any of those so I have not been actively excluding them from the TODO list. However, I need someone with optimizer experience to make a recommendation. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > > . Allow multi-column indexes to be used to optimize row-value expressions. Ie, > > allow a btree index on a,b to be used to execute an expression like (a,b) < > > (x,y). > > I have not heard of any of those so I have not been actively excluding > them from the TODO list. However, I need someone with optimizer > experience to make a recommendation. Well the row-value expression stuff comes out of a discussion just this week. You could check messages on pgsql-performance with the subject "[PERFORM] best way to fetch next/prev record based on index". In particular Tom's messages from Wednesday the 28th might be relevant. The optimization in the first one came up in a conversation that was quite a bit older. I don't have that thread saved. It seems like an obvious optimization if it can be done efficiently. -- greg
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
From
Christopher Kings-Lynne
Date:
OK, everything for pg_dump TODO I can think of: pg_dump/pg_dumpall/pg_restore * Add dumping of comments on composite type columns * Add dumping of comments on index columns * Replace crude DELETE FROM way of dumping cleaning of users and groups with separate DROP commands * Add dumping and restoring of LOB comments * Stop dumping CASCADE on DROP TYPE commands in clean mode * Add full object name to the tag field. eg. for operators we need '=(integer, integer)', instead of just '='. * Add pg_dumpall custom format dumps. This is probably best done by: * Combining pg_dump and pg_dumpall into a single binary * Export to other database (eg. Oracle, MySQL and DB2) formats I'm hopefully getting the first 4 in for 8.0 release... The full names in tags one should be done really as well at some point. Anyone got anything else? Chris Bruce Momjian wrote: > Yes, shoot over that the section should contain. > > --------------------------------------------------------------------------- > > Christopher Kings-Lynne wrote: > >>Perhaps. I was also thinking that maybe it's time to combine pg_dumpall >>and pg_dump into a single utility. At the moment, I can't see how >>pg_dumpall can ever have a -Fc option, since it will be messy to >>interact with the pg_dump processes. >> >>I was thinking a pg_export utility that can output to a range of other >>databases SQL formats would also be a good idea. It would share about >>90% of the pg_dump code, but I'm trying to think of how to avoid >>duplicating the code. >> >>How about we have a whole pg_dump/dumpall/restore/backup section in the >>TODO file? >> >>Chris >> >>Bruce Momjian wrote: >> >> >>>TODO item? >>> >>>--------------------------------------------------------------------------- >>> >>>Christopher Kings-Lynne wrote: >>> >>> >>>>>>I just got an autoreply from David stating he will be away until August >>>>>>9 if we want this functionality we have to code it ourselves. If not it >>>>>>can wait until the next major release. >>>>> >>>>>It can wait --- it was submitted after feature freeze anyway, and we >>>>>certainly have more than enough other things to do in the next couple days. >>>> >>>>I have a plan to allow pg_dump to dump any object in the next version - >>>>i suspect these two ideas will need reconciliation. >>>> >>>>Chris >>>> >>> >>> >
And I forgot to add: * Allow dumping/restoring of any number of specific objects and types Chris Christopher Kings-Lynne wrote: > OK, everything for pg_dump TODO I can think of: > > pg_dump/pg_dumpall/pg_restore > > * Add dumping of comments on composite type columns > * Add dumping of comments on index columns > * Replace crude DELETE FROM way of dumping cleaning of users and groups > with separate DROP commands > * Add dumping and restoring of LOB comments > * Stop dumping CASCADE on DROP TYPE commands in clean mode > * Add full object name to the tag field. eg. for operators we need > '=(integer, integer)', instead of just '='. > * Add pg_dumpall custom format dumps. This is probably best done by: > * Combining pg_dump and pg_dumpall into a single binary > * Export to other database (eg. Oracle, MySQL and DB2) formats > > I'm hopefully getting the first 4 in for 8.0 release... The full names > in tags one should be done really as well at some point. > > Anyone got anything else? > > Chris > > Bruce Momjian wrote: > >> Yes, shoot over that the section should contain. >> >> --------------------------------------------------------------------------- >> >> >> Christopher Kings-Lynne wrote: >> >>> Perhaps. I was also thinking that maybe it's time to combine >>> pg_dumpall and pg_dump into a single utility. At the moment, I can't >>> see how pg_dumpall can ever have a -Fc option, since it will be messy >>> to interact with the pg_dump processes. >>> >>> I was thinking a pg_export utility that can output to a range of >>> other databases SQL formats would also be a good idea. It would >>> share about 90% of the pg_dump code, but I'm trying to think of how >>> to avoid duplicating the code. >>> >>> How about we have a whole pg_dump/dumpall/restore/backup section in >>> the TODO file? >>> >>> Chris >>> >>> Bruce Momjian wrote: >>> >>> >>>> TODO item? >>>> >>>> --------------------------------------------------------------------------- >>>> >>>> >>>> Christopher Kings-Lynne wrote: >>>> >>>> >>>>>>> I just got an autoreply from David stating he will be away until >>>>>>> August >>>>>>> 9 if we want this functionality we have to code it ourselves. If >>>>>>> not it >>>>>>> can wait until the next major release. >>>>>> >>>>>> >>>>>> It can wait --- it was submitted after feature freeze anyway, and we >>>>>> certainly have more than enough other things to do in the next >>>>>> couple days. >>>>> >>>>> >>>>> I have a plan to allow pg_dump to dump any object in the next >>>>> version - i suspect these two ideas will need reconciliation. >>>>> >>>>> Chris >>>>> >>>> >>>> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Do you want any of these added to the TODO? --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > And I forgot to add: > > * Allow dumping/restoring of any number of specific objects and types > > Chris > > Christopher Kings-Lynne wrote: > > > OK, everything for pg_dump TODO I can think of: > > > > pg_dump/pg_dumpall/pg_restore > > > > * Add dumping of comments on composite type columns > > * Add dumping of comments on index columns > > * Replace crude DELETE FROM way of dumping cleaning of users and groups > > with separate DROP commands > > * Add dumping and restoring of LOB comments > > * Stop dumping CASCADE on DROP TYPE commands in clean mode > > * Add full object name to the tag field. eg. for operators we need > > '=(integer, integer)', instead of just '='. > > * Add pg_dumpall custom format dumps. This is probably best done by: > > * Combining pg_dump and pg_dumpall into a single binary > > * Export to other database (eg. Oracle, MySQL and DB2) formats > > > > I'm hopefully getting the first 4 in for 8.0 release... The full names > > in tags one should be done really as well at some point. > > > > Anyone got anything else? > > > > Chris > > > > Bruce Momjian wrote: > > > >> Yes, shoot over that the section should contain. > >> > >> --------------------------------------------------------------------------- > >> > >> > >> Christopher Kings-Lynne wrote: > >> > >>> Perhaps. I was also thinking that maybe it's time to combine > >>> pg_dumpall and pg_dump into a single utility. At the moment, I can't > >>> see how pg_dumpall can ever have a -Fc option, since it will be messy > >>> to interact with the pg_dump processes. > >>> > >>> I was thinking a pg_export utility that can output to a range of > >>> other databases SQL formats would also be a good idea. It would > >>> share about 90% of the pg_dump code, but I'm trying to think of how > >>> to avoid duplicating the code. > >>> > >>> How about we have a whole pg_dump/dumpall/restore/backup section in > >>> the TODO file? > >>> > >>> Chris > >>> > >>> Bruce Momjian wrote: > >>> > >>> > >>>> TODO item? > >>>> > >>>> --------------------------------------------------------------------------- > >>>> > >>>> > >>>> Christopher Kings-Lynne wrote: > >>>> > >>>> > >>>>>>> I just got an autoreply from David stating he will be away until > >>>>>>> August > >>>>>>> 9 if we want this functionality we have to code it ourselves. If > >>>>>>> not it > >>>>>>> can wait until the next major release. > >>>>>> > >>>>>> > >>>>>> It can wait --- it was submitted after feature freeze anyway, and we > >>>>>> certainly have more than enough other things to do in the next > >>>>>> couple days. > >>>>> > >>>>> > >>>>> I have a plan to allow pg_dump to dump any object in the next > >>>>> version - i suspect these two ideas will need reconciliation. > >>>>> > >>>>> Chris > >>>>> > >>>> > >>>> > >> > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Yep, a whole section for pg_dump features and bugs would be nice. Bruce Momjian wrote: > Do you want any of these added to the TODO? > > --------------------------------------------------------------------------- > > Christopher Kings-Lynne wrote: > >>And I forgot to add: >> >>* Allow dumping/restoring of any number of specific objects and types >> >>Chris >> >>Christopher Kings-Lynne wrote: >> >> >>>OK, everything for pg_dump TODO I can think of: >>> >>>pg_dump/pg_dumpall/pg_restore >>> >>>* Add dumping of comments on composite type columns >>>* Add dumping of comments on index columns >>>* Replace crude DELETE FROM way of dumping cleaning of users and groups >>>with separate DROP commands >>>* Add dumping and restoring of LOB comments >>>* Stop dumping CASCADE on DROP TYPE commands in clean mode >>>* Add full object name to the tag field. eg. for operators we need >>>'=(integer, integer)', instead of just '='. >>>* Add pg_dumpall custom format dumps. This is probably best done by: >>>* Combining pg_dump and pg_dumpall into a single binary >>>* Export to other database (eg. Oracle, MySQL and DB2) formats >>> >>>I'm hopefully getting the first 4 in for 8.0 release... The full names >>>in tags one should be done really as well at some point. >>> >>>Anyone got anything else? >>> >>>Chris >>> >>>Bruce Momjian wrote: >>> >>> >>>>Yes, shoot over that the section should contain. >>>> >>>>--------------------------------------------------------------------------- >>>> >>>> >>>>Christopher Kings-Lynne wrote: >>>> >>>> >>>>>Perhaps. I was also thinking that maybe it's time to combine >>>>>pg_dumpall and pg_dump into a single utility. At the moment, I can't >>>>>see how pg_dumpall can ever have a -Fc option, since it will be messy >>>>>to interact with the pg_dump processes. >>>>> >>>>>I was thinking a pg_export utility that can output to a range of >>>>>other databases SQL formats would also be a good idea. It would >>>>>share about 90% of the pg_dump code, but I'm trying to think of how >>>>>to avoid duplicating the code. >>>>> >>>>>How about we have a whole pg_dump/dumpall/restore/backup section in >>>>>the TODO file? >>>>> >>>>>Chris >>>>> >>>>>Bruce Momjian wrote: >>>>> >>>>> >>>>> >>>>>>TODO item? >>>>>> >>>>>>--------------------------------------------------------------------------- >>>>>> >>>>>> >>>>>>Christopher Kings-Lynne wrote: >>>>>> >>>>>> >>>>>> >>>>>>>>>I just got an autoreply from David stating he will be away until >>>>>>>>>August >>>>>>>>>9 if we want this functionality we have to code it ourselves. If >>>>>>>>>not it >>>>>>>>>can wait until the next major release. >>>>>>>> >>>>>>>> >>>>>>>>It can wait --- it was submitted after feature freeze anyway, and we >>>>>>>>certainly have more than enough other things to do in the next >>>>>>>>couple days. >>>>>>> >>>>>>> >>>>>>>I have a plan to allow pg_dump to dump any object in the next >>>>>>>version - i suspect these two ideas will need reconciliation. >>>>>>> >>>>>>>Chris >>>>>>> >>>>>> >>>>>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 3: if posting/reading through Usenet, please send an appropriate >>> subscribe-nomail command to majordomo@postgresql.org so that your >>> message can get through to the mailing list cleanly >> >
Christopher Kings-Lynne wrote: > OK, everything for pg_dump TODO I can think of: > > [snip] > * Export to other database (eg. Oracle, MySQL and DB2) formats > > This strikes me as a can of worms, or to mix metaphors a bit, a rathole from which we might never emerge. I did have a thought the other day - now that we have COPY in/out talking CSV format, it might be nice to have an option on pg_dump to use CSV format rather than our own native text format. That should make exporting to other DBs a lot easier. Of course, that could be cutting our own throat too ... cheers andrew
> That should make exporting to other DBs a lot easier. Of course, that > could be cutting our own throat too ... It won't make any difference to anything. You can already dump in INSERT format. Being able to export to other dbs will get us _more_ users, not less. Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> That should make exporting to other DBs a lot easier. Of course, that
>> could be cutting our own throat too ...
> It won't make any difference to anything. You can already dump in
> INSERT format.
And anyway, the DDL peculiarities would be the hard part for someone to
fix, not the data formatting.
regards, tom lane
Tom Lane wrote: >Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > >>>That should make exporting to other DBs a lot easier. Of course, that >>>could be cutting our own throat too ... >>> >>> > > > >>It won't make any difference to anything. You can already dump in >>INSERT format. >> >> > >And anyway, the DDL peculiarities would be the hard part for someone to >fix, not the data formatting. > > > > Yes, both of these are true. cheers andrew
On Friday 13 August 2004 00:13, Christopher Kings-Lynne wrote:
> > That should make exporting to other DBs a lot easier. Of course, that
> > could be cutting our own throat too ...
>
> It won't make any difference to anything. You can already dump in
> INSERT format.
>
> Being able to export to other dbs will get us _more_ users, not less.
>
Without some type of corresponding import utility that seems logically false.
--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL
>>Being able to export to other dbs will get us _more_ users, not less. > > Without some type of corresponding import utility that seems logically false. Nope. Consider it like this. How many companies are going to move to PostgreSQL from Oracle if they cannot dump their data back to Oracle as a failsafe? Very few. How many people will use PostgreSQL if they know they cannot dump it over to MySQL or anything easily if they want to. Trying to "tie in" users is pre-opensource / big commercial company thinking. What if they have a mixed environment, and they want to be able to give data and dumps from postgres to their MySQL people? Consider that no companies switched to MS Excel when it was first invented UNTIL it could export BACK to Lotus. Otherwise, how could they give their Excel files to other users in the office who hadn't upgraded yet? Chris
OK, I have added a new pg_dump TODO section with adjustments based on
feedback from original list:
* pg_dump o Allow pg_dumpall to use non-text output formats o Have pg_dump use multi-statement transactions
forINSERT dumps o -Allow pg_dump to dump CREATE CONVERSION (Christopher) o -Make pg_restore continue after
errors,so it acts more like pg_dump scripts o Allow pg_dump to use multiple -t and -n switches
This should be done by allowing a '-t schema.table' syntax.
o Add dumping of comments on composite type columns o Add dumping of comments on index columns o
Replacecrude DELETE FROM method of pg_dumpall for cleaning of users and groups with separate DROP commands
o Add dumping and restoring of LOB comments o Stop dumping CASCADE on DROP TYPE commands in clean mode o
Addfull object name to the tag field. eg. for operators we need '=(integer, integer)', instead of just '='.
o Add pg_dumpall custom format dumps. This is probably best done by combining pg_dump and pg_dumpall into a
singlebinary o Add CSV output format
---------------------------------------------------------------------------
Andrew Dunstan wrote:
>
>
> Christopher Kings-Lynne wrote:
>
> > OK, everything for pg_dump TODO I can think of:
> >
> > [snip]
> > * Export to other database (eg. Oracle, MySQL and DB2) formats
> >
> >
>
> This strikes me as a can of worms, or to mix metaphors a bit, a rathole
> from which we might never emerge.
>
> I did have a thought the other day - now that we have COPY in/out
> talking CSV format, it might be nice to have an option on pg_dump to use
> CSV format rather than our own native text format.
>
> That should make exporting to other DBs a lot easier. Of course, that
> could be cutting our own throat too ...
>
> cheers
>
> andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073