Thread: Possible TODO item: copy to/from pipe
After re-reading what I just wrote to Andreas about how compression of
COPY data would be better done outside the backend than inside, it
struck me that we are missing a feature that's fairly common in Unix
programs. Perhaps COPY ought to have the ability to pipe its output
to a shell command, or read input from a shell command. Maybe something
like
COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz';
(I'm not wedded to the above syntax, it's just an off-the-cuff thought.)
Of course psql would need the same capability, since the server-side
copy would still be restricted to superusers.
You can accomplish COPY piping now through psql, but it's a bit awkward:
psql -c "COPY mytable TO stdout" mydb | gzip ...
Thoughts? Is this worth doing, or is the psql -c approach good enough?
regards, tom lane
Tom Lane wrote: > > You can accomplish COPY piping now through psql, but it's a bit awkward: > > psql -c "COPY mytable TO stdout" mydb | gzip ... > > Thoughts? Is this worth doing, or is the psql -c approach good enough? > > I think it's good enough. And there is also pg_dump -F c -t bigtable -f bigtable.dump cheers andrew
On Wed, May 31, 2006 at 11:03:14AM -0400, Tom Lane wrote: > After re-reading what I just wrote to Andreas about how compression > of COPY data would be better done outside the backend than inside, > it struck me that we are missing a feature that's fairly common in > Unix programs. Perhaps COPY ought to have the ability to pipe its > output to a shell command, or read input from a shell command. > Maybe something like > > COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz'; That's a great syntax :) Similarly, COPY mytable FROM 'create_sample_data --table mytable --rows 10000000 |'; would be cool. > (I'm not wedded to the above syntax, it's just an off-the-cuff > thought.) It will be familiar to Perl users, for better or worse. Come to that, should the prefixes > and >> also mean their corresponding shell things? > Of course psql would need the same capability, since the server-side > copy would still be restricted to superusers. Roight. > You can accomplish COPY piping now through psql, but it's a bit awkward: > > psql -c "COPY mytable TO stdout" mydb | gzip ... > > Thoughts? Is this worth doing, or is the psql -c approach good enough? I think it's worth doing :) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
Tom Lane wrote: > After re-reading what I just wrote to Andreas about how compression of > COPY data would be better done outside the backend than inside, it > struck me that we are missing a feature that's fairly common in Unix > programs. Perhaps COPY ought to have the ability to pipe its output > to a shell command, or read input from a shell command. Maybe something > like > > COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz'; > > (I'm not wedded to the above syntax, it's just an off-the-cuff thought.) > > Of course psql would need the same capability, since the server-side > copy would still be restricted to superusers. Won't help too much, until gzip's output is piped back too, so a replacement for COPY .. TO STDOUT COMPRESSED would be COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to receive the reduced stuff. But clients should be agnostic of server side installed tools, and probably not be able to address them directly. Sounds like a potential security issue. Regards, Andreas
Andreas Pflug wrote: > > Won't help too much, until gzip's output is piped back too, so a > replacement for COPY .. TO STDOUT COMPRESSED would be > COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to receive the > reduced stuff. Forgot to mention: COPY COMPRESSED was also meant to introduce a portable format that's efficient for both text and binary data. Relying on some external XYZzip version seems not too portable to me. Regards, Andreas
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas Pflug > Sent: 31 May 2006 16:41 > Cc: Tom Lane; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe > > Andreas Pflug wrote: > > > > > Won't help too much, until gzip's output is piped back too, so a > > replacement for COPY .. TO STDOUT COMPRESSED would be > > COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to > receive the > > reduced stuff. > > Forgot to mention: > COPY COMPRESSED was also meant to introduce a portable format that's > efficient for both text and binary data. Relying on some > external XYZzip > version seems not too portable to me. It does have that advantage. Gzip and others are not particularly Windows friendly for example. Regards, Dave.
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Forgot to mention:
> COPY COMPRESSED was also meant to introduce a portable format that's
> efficient for both text and binary data. Relying on some external XYZzip
> version seems not too portable to me.
I dislike putting this into the backend precisely because it's trying to
impose a one-size-fits-all compression solution. Someone might wish to
use bzip2 instead of gzip, for instance, or tweak the compression level
options of gzip. It's trivial for the user to do that if the
compression program is separate, not trivial at all if it's wired into
COPY. Also, a pipe feature would have uses unrelated to compression,
such as on-the-fly analysis or generation of data.
regards, tom lane
Dave Page wrote: > > > >>-----Original Message----- >>From: pgsql-hackers-owner@postgresql.org >>[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas Pflug >>Sent: 31 May 2006 16:41 >>Cc: Tom Lane; pgsql-hackers@postgresql.org >>Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe >> >>Andreas Pflug wrote: >> >> >>>Won't help too much, until gzip's output is piped back too, so a >>>replacement for COPY .. TO STDOUT COMPRESSED would be >>>COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to >> >>receive the >> >>>reduced stuff. >> >>Forgot to mention: >>COPY COMPRESSED was also meant to introduce a portable format that's >>efficient for both text and binary data. Relying on some >>external XYZzip >>version seems not too portable to me. > > > It does have that advantage. Gzip and others are not particularly > Windows friendly for example. ... as most windows programs are pipe agnostic. Regards, Andreas
> > I dislike putting this into the backend precisely because it's trying to > impose a one-size-fits-all compression solution. Someone might wish to > use bzip2 instead of gzip, for instance, or tweak the compression level > options of gzip. It's trivial for the user to do that if the > compression program is separate, not trivial at all if it's wired into > COPY. Also, a pipe feature would have uses unrelated to compression, > such as on-the-fly analysis or generation of data. It seems that it would be better to have the options within pg_dump which would give the most flexibility. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Joshua D. Drake wrote: >> >> I dislike putting this into the backend precisely because it's trying to >> impose a one-size-fits-all compression solution. Someone might wish to >> use bzip2 instead of gzip, for instance, or tweak the compression level >> options of gzip. It's trivial for the user to do that if the >> compression program is separate, not trivial at all if it's wired into >> COPY. Also, a pipe feature would have uses unrelated to compression, >> such as on-the-fly analysis or generation of data. > > > It seems that it would be better to have the options within pg_dump > which would give the most flexibility. What about all other client tools? My COPY WITH COMPRESSION is not the same as taking a copy file and zipping it; it creates a copy file with BinarySignature that has compressed bytes in the data part, thus it can be handled by any client app that can stream binary copy files from/to the server. Regards, Andreas
> >>>Won't help too much, until gzip's output is piped back too, so a > >>>replacement for COPY .. TO STDOUT COMPRESSED would be > COPY ... TO '| > >>>/bin/gzip |' STDOUT, to enable clients to > >> > >>receive the > >> > >>>reduced stuff. > >> > >>Forgot to mention: > >>COPY COMPRESSED was also meant to introduce a portable > format that's > >>efficient for both text and binary data. Relying on some external > >>XYZzip version seems not too portable to me. > > > > > > It does have that advantage. Gzip and others are not particularly > > Windows friendly for example. > > ... as most windows programs are pipe agnostic. For the record, gzip on win32 works perfectly fine both as a separate program and running in a pipe. No problem at all. The only issue is that it's not available by default. (And possible issues with programs launching it that don't know how to deal with windows style directory naming) //Magnus
pgadmin@pse-consulting.de (Andreas Pflug) writes: > Dave Page wrote: >> >>>-----Original Message----- >>> From: pgsql-hackers-owner@postgresql.org >>> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas >>> Pflug >>>Sent: 31 May 2006 16:41 >>>Cc: Tom Lane; pgsql-hackers@postgresql.org >>>Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe >>> >>>Andreas Pflug wrote: >>> >>> >>>> Won't help too much, until gzip's output is piped back too, so a >>>> replacement for COPY .. TO STDOUT COMPRESSED would be >>>> COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to >>> >>> receive the >>> >>>>reduced stuff. >>> >>>Forgot to mention: >>> COPY COMPRESSED was also meant to introduce a portable format >>> that's efficient for both text and binary data. Relying on some >>> external XYZzip version seems not too portable to me. >> It does have that advantage. Gzip and others are not particularly >> Windows friendly for example. > > ... as most windows programs are pipe agnostic. Shall we make PostgreSQL less powerful because of that? -- "cbbrowne","@","cbbrowne.com" http://cbbrowne.com/info/advocacy.html "Love is like a snowmobile flying over the frozen tundra that suddenly flips, pinning you underneath. At night, the ice weasels come." -- Matt Groening
Chris Browne wrote: > pgadmin@pse-consulting.de (Andreas Pflug) writes: > >>Dave Page wrote: >> >>>>-----Original Message----- >>>>From: pgsql-hackers-owner@postgresql.org >>>>[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas >>>>Pflug >>>>Sent: 31 May 2006 16:41 >>>>Cc: Tom Lane; pgsql-hackers@postgresql.org >>>>Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe >>>> >>>>Andreas Pflug wrote: >>>> >>>> >>>> >>>>>Won't help too much, until gzip's output is piped back too, so a >>>>>replacement for COPY .. TO STDOUT COMPRESSED would be >>>>>COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to >>>> >>>>receive the >>>> >>>> >>>>>reduced stuff. >>>> >>>>Forgot to mention: >>>>COPY COMPRESSED was also meant to introduce a portable format >>>>that's efficient for both text and binary data. Relying on some >>>>external XYZzip version seems not too portable to me. >>> >>>It does have that advantage. Gzip and others are not particularly >>>Windows friendly for example. >> >>... as most windows programs are pipe agnostic. > > > Shall we make PostgreSQL less powerful because of that? I never said that. We shall seek solutions that run painless on most popular platforms are useful to users. I wonder if we'd be able to ship gzip with the windows installer, to insure proper integration. Regards, Andreas
Andreas Pflug wrote: > Chris Browne wrote: >> pgadmin@pse-consulting.de (Andreas Pflug) writes: >> >>> Dave Page wrote: >>> >>>>> -----Original Message----- >>>>> From: pgsql-hackers-owner@postgresql.org >>>>> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Andreas >>>>> Pflug >>>>> Sent: 31 May 2006 16:41 >>>>> Cc: Tom Lane; pgsql-hackers@postgresql.org >>>>> Subject: Re: [HACKERS] Possible TODO item: copy to/from pipe >>>>> >>>>> Andreas Pflug wrote: >>>>> >>>>> >>>>> >>>>>> Won't help too much, until gzip's output is piped back too, so a >>>>>> replacement for COPY .. TO STDOUT COMPRESSED would be >>>>>> COPY ... TO '| /bin/gzip |' STDOUT, to enable clients to >>>>> >>>>> receive the >>>>> >>>>> >>>>>> reduced stuff. >>>>> >>>>> Forgot to mention: >>>>> COPY COMPRESSED was also meant to introduce a portable format >>>>> that's efficient for both text and binary data. Relying on some >>>>> external XYZzip version seems not too portable to me. >>>> >>>> It does have that advantage. Gzip and others are not particularly >>>> Windows friendly for example. >>> >>> ... as most windows programs are pipe agnostic. >> >> >> Shall we make PostgreSQL less powerful because of that? > > I never said that. We shall seek solutions that run painless on most > popular platforms are useful to users. > I wonder if we'd be able to ship gzip with the windows installer, to > insure proper integration. > I wish somebody would explain to me the compelling use case for this. We do not have to build every possible capability into Postgres. As Tony Hoare said, "Inside every large problem is a small problem struggling to get out." This just seems like creeping featurism to me. cheers andrew
On Wed, May 31, 2006 at 02:46:29PM -0400, Andrew Dunstan wrote: > I wish somebody would explain to me the compelling use case for > this. As with "in-place upgrades,"[1] the compelling use case is being short on disk space. For somebody with a multi-TB (or whatever figure sounds big this week) PostgreSQL database, it may be impossible to get space for twice or more that. Giving people the option to stream COPYs through a pipe would alleviate a lot of pain. Cheers, D [1] A feature people seem to think we don't need, although convincing cases have been made for it. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
David Fetter wrote: > On Wed, May 31, 2006 at 02:46:29PM -0400, Andrew Dunstan wrote: > > >> I wish somebody would explain to me the compelling use case for >> this. >> > > As with "in-place upgrades,"[1] the compelling use case is being short > on disk space. For somebody with a multi-TB (or whatever figure > sounds big this week) PostgreSQL database, it may be impossible to get > space for twice or more that. Giving people the option to stream > COPYs through a pipe would alleviate a lot of pain. > > Cheers, > D > > [1] A feature people seem to think we don't need, although convincing > cases have been made for it. > But why is that hugely better than piping psql output to gzip? The Unix philosophy is to use small chains of tools rather than put everything into one big tool. Thus, this is quite unlike inplace upgrade, which I agree would be great (and where I would far rather see people spend their efforts), because unlike for this "feature" there is no viable alternative. cheers andrew
On 31/5/06 18:28, "Magnus Hagander" <mha@sollentuna.net> wrote: >>>>> Won't help too much, until gzip's output is piped back too, so a >>>>> replacement for COPY .. TO STDOUT COMPRESSED would be >> COPY ... TO '| >>>>> /bin/gzip |' STDOUT, to enable clients to >>>> >>>> receive the >>>> >>>>> reduced stuff. >>>> >>>> Forgot to mention: >>>> COPY COMPRESSED was also meant to introduce a portable >> format that's >>>> efficient for both text and binary data. Relying on some external >>>> XYZzip version seems not too portable to me. >>> >>> >>> It does have that advantage. Gzip and others are not particularly >>> Windows friendly for example. >> >> ... as most windows programs are pipe agnostic. > > For the record, gzip on win32 works perfectly fine both as a separate > program and running in a pipe. No problem at all. The only issue is that > it's not available by default. (And possible issues with programs > launching it that don't know how to deal with windows style directory > naming) Exactly my point; how many production Windows servers do you have with gzip anywhere near them? Andreas' point about pipes is also valid though - it's simply not the norm on Windows as I found when we were porting Slony (more.exe barfs at >8MB being pipe in). Regards, Dave.
On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote: > I wonder if we'd be able to ship gzip with the windows installer, to > insure proper integration. 'Fraid not. It's GPL'd. Regards, Dave.
Andrew Dunstan wrote: > David Fetter wrote: > >As with "in-place upgrades,"[1] the compelling use case is being short > >on disk space. For somebody with a multi-TB (or whatever figure > >sounds big this week) PostgreSQL database, it may be impossible to get > >space for twice or more that. Giving people the option to stream > >COPYs through a pipe would alleviate a lot of pain. > > But why is that hugely better than piping psql output to gzip? psql output has already travelled over the network. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On May 31, 2006, at 12:58 PM, Dave Page wrote: > > > > On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote: > >> I wonder if we'd be able to ship gzip with the windows installer, to >> insure proper integration. > > 'Fraid not. It's GPL'd. Well, one implementation of it is. zlib is new-bsd-ish, though, and includes minigzip, which should be just fine for use in a pipe on windows. (Not that that's an argument one way or the other as to whether this is something we should do). Cheers, Steve
> > I wonder if we'd be able to ship gzip with the windows > installer, to > > insure proper integration. > > 'Fraid not. It's GPL'd. > Well, if we want to go down that route, we could probably hack up something simple around zlib. IIRC, there's even sample code in there for how to write a gzip pipe program... No, not as convenient, but should be handlable. //Magnus
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> My COPY WITH COMPRESSION is not the same as taking a copy file and
> zipping it; it creates a copy file with BinarySignature that has
> compressed bytes in the data part, thus it can be handled by any client
> app that can stream binary copy files from/to the server.
If you mean you're compressing each data field separately, that's surely
a very bad idea. If you mean you're compressing everything except the
file header, I fail to see the value. Binary is binary. I *seriously*
doubt there are clients out there that look for a PGCOPY header before
deciding whether to send the file to the server or not. And a client
that did know that much about a PGCOPY file would likely spit up on a
critical flag it didn't recognize, anyway.
regards, tom lane
Dave Page <dpage@vale-housing.co.uk> writes:
> Exactly my point; how many production Windows servers do you have with gzip
> anywhere near them? Andreas' point about pipes is also valid though - it's
> simply not the norm on Windows as I found when we were porting Slony
> (more.exe barfs at >8MB being pipe in).
I don't see that we should allow Windows' deficiencies to be an argument
against providing a facility that would be useful on all our other platforms.
regards, tom lane
Alvaro Herrera wrote: > Andrew Dunstan wrote: > >> >> But why is that hugely better than piping psql output to gzip? >> > > psql output has already travelled over the network. > > As I understand Tom's suggestion, it does not involve compression of over the wire data. He suggested that on the server you would be able to do: COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz'; and that there could be an equivalent extension on psql's \copy command, as an alternative to doing psql -c "COPY mytable TO stdout" mydb | gzip ... It's the second piece especially that seems to me unnecessary. So I am still unconvinced. cheers andrew
On 31/5/06 21:10, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@vale-housing.co.uk> writes: >> Exactly my point; how many production Windows servers do you have with gzip >> anywhere near them? Andreas' point about pipes is also valid though - it's >> simply not the norm on Windows as I found when we were porting Slony >> (more.exe barfs at >8MB being pipe in). > > I don't see that we should allow Windows' deficiencies to be an argument > against providing a facility that would be useful on all our other platforms. It's not about a primarily GUI based OS not being able to do everything a traditionally command line based OS can do on the command line, it's about providing a solution that will work on either and remain portable. Whilst I agree with your objection to using pg_lzcompress, I for one would rather see a builtin solution that I know will work whatever platform/box I'm on without having to go and download additional tools. Regards, Dave.
On Wed, May 31, 2006 at 01:08:28PM -0700, Steve Atkins wrote: > On May 31, 2006, at 12:58 PM, Dave Page wrote: > >On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote: > > > >>I wonder if we'd be able to ship gzip with the windows installer, to > >>insure proper integration. > > > >'Fraid not. It's GPL'd. > > Well, one implementation of it is. zlib is new-bsd-ish, though, and > includes minigzip, which should be just fine for use in a pipe on > windows. Even then it's not relevent. The Windows Installer is already GPL'd by the fact it includes readline. zlib is indeed straight BSD like. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Dave Page wrote: > It's not about a primarily GUI based OS not being able to do > everything a traditionally command line based OS can do on the > command line, it's about providing a solution that will work on > either and remain portable. Whilst I agree with your objection to > using pg_lzcompress, Well, pg_lzcompress is in the backend for more than 6 years now, strange the objections arise now. However, a replacement for it might be a good idea, since apparently the fastest gzip algorithm is 3x faster for 10% better compression. TOAST write performance would probably profit significantly from a better algorithm. I wonder what other use-cases exist for server side copy filters beyond compression. Regards, Andreas
> > >>I wonder if we'd be able to ship gzip with the windows > installer, to > > >>insure proper integration. > > > > > >'Fraid not. It's GPL'd. > > > > Well, one implementation of it is. zlib is new-bsd-ish, though, and > > includes minigzip, which should be just fine for use in a pipe on > > windows. > > Even then it's not relevent. The Windows Installer is already > GPL'd by the fact it includes readline. zlib is indeed > straight BSD like. Uh. The installer does *not* include readline. We do include PostGIS, but the PostGIS people themselves don't consider us GPLed because of that ;-) Everything else is != GPL. //Magnus
> Uh. The installer does *not* include readline. > > We do include PostGIS, but the PostGIS people themselves don't consider > us GPLed because of that ;-) > That is a tad different. PostgreSQL does not link to Postgis. Readline does :) Sincerely, Joshua D. Drake > Everything else is != GPL. > > //Magnus > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
On Wed, May 31, 2006 at 11:20:21PM +0200, Magnus Hagander wrote: > Uh. The installer does *not* include readline. Terribly sorry, I misinterpreted the thread about it at the beginning of the year. http://archives.postgresql.org/pgsql-hackers/2006-02/msg00539.php Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On 5/31/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > After re-reading what I just wrote to Andreas about how compression of > COPY data would be better done outside the backend than inside, it > struck me that we are missing a feature that's fairly common in Unix > programs. Perhaps COPY ought to have the ability to pipe its output > to a shell command, or read input from a shell command. Maybe something > like > > COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz'; > > (I'm not wedded to the above syntax, it's just an off-the-cuff thought.) > > Of course psql would need the same capability, since the server-side > copy would still be restricted to superusers. > > You can accomplish COPY piping now through psql, but it's a bit awkward: > > psql -c "COPY mytable TO stdout" mydb | gzip ... > > Thoughts? Is this worth doing, or is the psql -c approach good enough? You can accomplish it now with help of FIFOs, like \! mkfifo /tmp/psqlfifo \! chmod 666 /tmp/psqlfifo -- I know 666 is a dangerous number. ;) \! gzip -9 < /tmp/psqlfifo > /tmp/psqlcopy.gz COPY foo TO '/tmp/psqlfifo'; ...though ability to pipe "directly" is desirable feature. Regards, Dawid
> After re-reading what I just wrote to Andreas about how compression of > COPY data would be better done outside the backend than inside, it > struck me that we are missing a feature that's fairly common in Unix > programs. Perhaps COPY ought to have the ability to pipe its output > to a shell command, or read input from a shell command. Maybe something > like > > COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz'; > > (I'm not wedded to the above syntax, it's just an off-the-cuff thought.) > > Of course psql would need the same capability, since the server-side > copy would still be restricted to superusers. > > You can accomplish COPY piping now through psql, but it's a bit awkward: > > psql -c "COPY mytable TO stdout" mydb | gzip ... > > Thoughts? Is this worth doing, or is the psql -c approach good enough? To be honest, I don't see much benefit in it. You can already accomplish what you want to accomplish easily enough. If you want to muck with COPY, I'd like to see it accept a query as: psql -c "COPY select * from mytable where foo='bar' TO stdout" mydb | gzip ...
Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Wed, May 31, 2006 at 01:08:28PM -0700, Steve Atkins wrote: > > On May 31, 2006, at 12:58 PM, Dave Page wrote: > > >On 31/5/06 19:13, "Andreas Pflug" <pgadmin@pse-consulting.de> wrote: > > > > > >>I wonder if we'd be able to ship gzip with the windows installer, to > > >>insure proper integration. > > > > > >'Fraid not. It's GPL'd. > > > > Well, one implementation of it is. zlib is new-bsd-ish, though, and > > includes minigzip, which should be just fine for use in a pipe on > > windows. > > Even then it's not relevent. The Windows Installer is already GPL'd by > the fact it includes readline. zlib is indeed straight BSD like. I assume gzip would be binary in the installer. Does putting a GPL binary in the installer make the entire thing GPL? I don't think so. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Andreas Pflug wrote:
> Tom Lane wrote:
> > After re-reading what I just wrote to Andreas about how compression of
> > COPY data would be better done outside the backend than inside, it
> > struck me that we are missing a feature that's fairly common in Unix
> > programs. Perhaps COPY ought to have the ability to pipe its output
> > to a shell command, or read input from a shell command. Maybe something
> > like
> >
> > COPY mytable TO '| gzip >/home/tgl/mytable.dump.gz';
For use case, consider this:
COPY mytable TO '| rsh x@y.com > test ';
so you can COPY to another server directly.
-- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > > For use case, consider this: > > COPY mytable TO '| rsh x@y.com > test '; > > so you can COPY to another server directly. > Why not rsh psql -c "\copy foobar to test" ? Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes: > Bruce Momjian wrote: > > > > For use case, consider this: > > > > COPY mytable TO '| rsh x@y.com > test '; > > > > so you can COPY to another server directly. > > > Why not rsh psql -c "\copy foobar to test" ? Who knows? The sysadmin could have any reason to prefer one to the other. One that comes to mind is that he or she may want to automate this and may be happier granting password-free access to a filesystem server that just holds encrypted backups than to the live production database. -- greg