Re: [HACKERS] Possible TODO item: copy to/from pipe
Andreas Pflug <[EMAIL PROTECTED]> writes: > Bruce Momjian wrote: > > > > For use case, consider this: > > > > COPY mytable TO '| rsh [EMAIL PROTECTED] > 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Possible TODO item: copy to/from pipe
Bruce Momjian wrote: For use case, consider this: COPY mytable TO '| rsh [EMAIL PROTECTED] > test '; so you can COPY to another server directly. Why not rsh psql -c "\copy foobar to test" ? Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 [EMAIL PROTECTED] > test '; so you can COPY to another server directly. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Possible TODO item: copy to/from pipe
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" <[EMAIL PROTECTED]> 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 EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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? 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 ... ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Possible TODO item: copy to/from pipe
On 5/31/06, Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Possible TODO item: copy to/from pipe
> > >>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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Possible TODO item: copy to/from pipe
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" <[EMAIL PROTECTED]> 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 http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Possible TODO item: copy to/from pipe
On 31/5/06 21:10, "Tom Lane" <[EMAIL PROTECTED]> wrote: > Dave Page 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Possible TODO item: copy to/from pipe
Dave Page 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Possible TODO item: copy to/from pipe
Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Possible TODO item: copy to/from pipe
> > 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
On May 31, 2006, at 12:58 PM, Dave Page wrote: On 31/5/06 19:13, "Andreas Pflug" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Possible TODO item: copy to/from pipe
On 31/5/06 19:13, "Andreas Pflug" <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Possible TODO item: copy to/from pipe
On 31/5/06 18:28, "Magnus Hagander" <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
Andreas Pflug wrote: Chris Browne wrote: [EMAIL PROTECTED] (Andreas Pflug) writes: Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
Chris Browne wrote: [EMAIL PROTECTED] (Andreas Pflug) writes: Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Possible TODO item: copy to/from pipe
[EMAIL PROTECTED] (Andreas Pflug) writes: > Dave Page wrote: >> >>>-Original Message- >>> From: [EMAIL PROTECTED] >>> [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Possible TODO item: copy to/from pipe
> >>>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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Possible TODO item: copy to/from pipe
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Possible TODO item: copy to/from pipe
Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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. Regards, Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 1000 |'; 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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible TODO item: copy to/from pipe
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
