Thread: Re: [PATCHES] Current-stream read for psql's \copy

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.

[ Moved to hackers list.]

Actually, I am confused by our current \copy behavior.  Given the
following file:
CREATE TABLE test(x INT);\copy test FROM STDIN444\.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,
Pennsylvania19073
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> '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?

Actually, that behavior is extremely useful.  Considerprogram-that-generates-data | psql -f somescript
The script can issue a COPY FROM STDIN to insert the data coming through
the pipe.

> I propose we just fix this and document it in the release notes.

I propose we not break existing applications.  If we change it the way
you suggest, there'll be no way to do the above.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > '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?
> 
> Actually, that behavior is extremely useful.  Consider
>     program-that-generates-data | psql -f somescript
> The script can issue a COPY FROM STDIN to insert the data coming through
> the pipe.
> 
> > I propose we just fix this and document it in the release notes.
> 
> I propose we not break existing applications.  If we change it the way
> you suggest, there'll be no way to do the above.

But does anyone use such a capability?  The program has to generate \.
to terminate the input, so why not have it issue the \copy too?  Seems
cleaner.

--  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:
> Tom Lane wrote:
>> I propose we not break existing applications.  If we change it the way
>> you suggest, there'll be no way to do the above.

> But does anyone use such a capability?

What, you're going to remove a feature because you don't think it's
used?  We've been burnt by that approach before.

> The program has to generate \. to terminate the input

No it doesn't.  EOF will do fine.  The source program doesn't
necessarily have to know anything about COPY, as long as its output is
in a format COPY can cope with (eg, tab-delimited).
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> I propose we not break existing applications.  If we change it the way
> >> you suggest, there'll be no way to do the above.
> 
> > But does anyone use such a capability?
> 
> What, you're going to remove a feature because you don't think it's
> used?  We've been burnt by that approach before.

Yes.  We get burnt by it, but we also remove stuff we don't get burnt
by, so it is a value judgement.

> > The program has to generate \. to terminate the input
> 
> No it doesn't.  EOF will do fine.  The source program doesn't
> necessarily have to know anything about COPY, as long as its output is
> in a format COPY can cope with (eg, tab-delimited).

The current behavior seems quite strange and counter-intuitive.  We
might break the code for 1-2 people, but we will make it more
predicable for everyone using it.

--  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