Re: [HACKERS] psql -f inconsistency with "copy from stdin"

Of course, the question is which way is right...

I can see the potential usefulness of doing
psql -f driving.script <data.file
but on the other hand, it bothers me a good deal that a script
containing
COPY table FROM STDIN;
... data here ...
\.
(as generated by such unheard-of, seldom-used utilities as pg_dump)
would work when sourced by psql <pgdump.script and *fail* when sourced
by psql -f pgdump.script. But that's what will happen if we change
it back.

I suspect the change in behavior from 6.4 to 6.5 may have been a
deliberate change to avoid this failure mode. It'd be worth checking
the archives to see if you can find any discussion about it.

It seems to me that we ought to provide both behaviors, but make sure
that the one that supports data-in-the-script is the one invoked by
COPY FROM STDIN (since that's what pg_dump uses). Perhaps psql's \copy
command can be set up to support the other alternative.

But isn't there a greater difference between copy and \copy than this?
Doesn't one act on the frontend and one on the backend? There needs
to be a mechanism for copying data in through the front end without
special permissions.

Also, it seems unfortunate from a semantics point of view to have COPY
FROM STDIN not actually refer to the stdin file of the process.
Perhaps that is necessary to preserve compatability with old pg_dump
(new versions could be changed in this regard of course), but it is
not what I would naturally expect STDIN to mean in the context of 30
years of Unix development. Further, this use of STDIN clearly
conflicts with the meaning of STDOUT in the analogous copy out command
which doesn't insert the output into the script file but rather
directs it to the stdout file.

In order to maintain some compatability with these broader uses of the
terms STDIN/STDOUT (while still supporting previous pg_dump scripts,
at least for awhile), I think it is worth exploring some options. A
few ideas are:

- Introduce a new syntax for the 6.5.2 here-doc semantics.
Possibilities might include COPY FROM HERE (copy ends at EOF or \.)
or COPY UNTIL <tag> (copy ends at matching <tag>, like shell
here-docs). pg_dump would have to be changed to correspond.

- Introduce a new flag to psql to differentiate the interpretation of
COPY FROM STDIN. This seems confusing to users, but might be
worthwhile (but become deprecated after a few releases) if the
syntax is changed and old pg_dump scripts need supporting. New
scripts and new pg_dump needn't worry about this if they use the new
syntax.

Cheers,
Brook