Thread: Re: [PATCHES] Current-stream read for psql's \copy
What do people want to do with the current \copy behavior for stdin? Right now if you supply a file name with queries using psql -f, the copy input is read from the terminal, not from the file. I propose changing that so psql reads \copy input from the file so it is consistent with COPY and is more predicable. This does eliminate use of split input where you supply the COPY input from a program, but you could easily do: (echo "\copy test ..."; program) | psql test or something like that. We would document this change in the release notes. --------------------------------------------------------------------------- Bruce Momjian wrote: > Mark Feit wrote: > > This patch against 7.4.1's psql and the documentation adds the option > > of reading rows from the "current" input stream (standard input, -f > > xxx, \i xxx) during a "\copy ... from" operation in psql. The details > > were proposed and discussed (somewhat) here: > > > > http://archives.postgresql.org/pgsql-hackers/2003-12/msg00687.php > > http://archives.postgresql.org/pgsql-hackers/2004-01/msg00056.php > > > > After some consideration, I decided to stick with the > > originally-proposed syntax because I couldn't come up with anything > > that made as much sense. > > > > This patch also includes a change which makes the "enter data to be > > copied..." message appear for both \copy and COPY in an interactive > > setting. > > > > If there's interest, I can build a patch against the current > > development version. > > Actually, I am confused by our current \copy behavior. Given the > following file: > > CREATE TABLE test(x INT); > \copy test FROM STDIN > 444 > \. > SELECT * FROM test; > > 'psql test </tmp/x' works fine, but 'psql -f /tmp/x test' hangs waiting > for input from stdin. Why would we want STDIN to read from the terminal > if all commands are being read from a file with -f? > > Reading the second URL, I see: > > > Peter Eisentraut declared that from that point on, stdin would be > > whatever stream the \copy command came from. I'd like to propose a > > variant on the "FROM" clause which makes good on Peter's declaration > > without breaking anything already using FROM STDIN and expecting it > > to really read from stdin. (I think this is for the better because > > there are lots of good uses for "psql -f foo.sql < foo.dat".) > > I agree with Peter stdin should be where ever the commands are coming > from. I don't see any value to keeping backward compatibility for such > strange behavior, and adding another flag to give the reasonable > behavior seems wrong too. > > I propose we just fix this and document it in the release notes. Heck, > COPY and \copy should behave the same in determining STDIN, and right > now they don't. > > -- > 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, Pennsylvania 19073 > > ---------------------------(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
On Tue, Feb 10, 2004 at 12:33:10PM -0500, Bruce Momjian wrote: > > What do people want to do with the current \copy behavior for stdin? I'd like to hear more discussion of Tom's argument about security before the decision is taken. -- Andrew Sullivan
Andrew Sullivan wrote: > On Tue, Feb 10, 2004 at 12:33:10PM -0500, Bruce Momjian wrote: > > > > What do people want to do with the current \copy behavior for stdin? > > I'd like to hear more discussion of Tom's argument about security > before the decision is taken. Tom's point is that you can feed SQL to psql via -f and have \copy input come from psql's stdin, and you can't insert SQL into psql's stdin: prog | psql -f commands.sql test If commands.sql contains \copy, you can only specify copy data in 'prog', not actual SQL commands. If you want to mix SQL commands and \copy data in the same file, you have to use: psql test < commands.sql -- 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 wrote: > > What do people want to do with the current \copy behavior for stdin? > Right now if you supply a file name with queries using psql -f, the copy > input is read from the terminal, not from the file. Actually, I was wrong. Right now \copy reads from psql's stdin, not always the terminal. It doesn't read from the same descriptor it gets its SQL commands, unless they are the same as psql's stdin, like: psql test < commands.sql You could make STDIN be the command stream, and add 'psqlstdin' for psql's stdin, but it seems like a very little used feature. It doesn't seem worth documenting it, let alone adding code to allow it. I assume \copy is designed primarily to allow reading from _local_ files rather than only files that exist on the database server, as COPY requires. -- 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 wrote: > Bruce Momjian wrote: > > > > What do people want to do with the current \copy behavior for stdin? > > Right now if you supply a file name with queries using psql -f, the copy > > input is read from the terminal, not from the file. > > Actually, I was wrong. Right now \copy reads from psql's stdin, not > always the terminal. It doesn't read from the same descriptor it gets > its SQL commands, unless they are the same as psql's stdin, like: > > psql test < commands.sql > > You could make STDIN be the command stream, and add 'psqlstdin' for > psql's stdin, but it seems like a very little used feature. It doesn't > seem worth documenting it, let alone adding code to allow it. > > I assume \copy is designed primarily to allow reading from _local_ files > rather than only files that exist on the database server, as COPY > requires. Now, I am really confused. First I see the \copy from '-' is already in CVS. I missed that commit message, but it has been in for a few weeks. Second, I think I now see the designer's goal of using stdin/stdout for \copy. \copy is for reading local files rather than only server files via COPY, but for stdin/stdout, there isn't any 'local' file that makes it different than COPY, so I am not sure even why someone would use \copy when they could use COPY. Also, I came upon this gem: $ echo '\\copy test to stdout' | psql -o /tmp/z test444444444444444 Seems 'copy to stdout' also has this split idea of sending \copy output to a different place from other output. I guess my big question now is why someone would use \copy stdin/stdout for reading input from the command stream when they can use COPY? -- 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 wrote:
> Also, I came upon this gem:
>
> $ echo '\\copy test to stdout' | psql -o /tmp/z test
> 444
> 444
> 444
> 444
> 444
>
> Seems 'copy to stdout' also has this split idea of sending \copy output
> to a different place from other output.
>
> I guess my big question now is why someone would use \copy stdin/stdout
> for reading input from the command stream when they can use COPY?
I think the idea was that you could have a program that does something
like (Perl):
my $fh = open('| psql -f commands.sql');
print $fh "1\ta\n";
Where commands.sql contains the \copy.
I'm not saying that was the original intention, but someone pointed out
you could do things that way, and I can see it might (rarely) be useful.
-- Richard Huxton Archonet Ltd