Thread: psql \set vs \copy - bug or expected behaviour?

psql \set vs \copy - bug or expected behaviour?

From
Richard Huxton
Date:
It looks like \copy is just passing the text of the query unadjusted to 
"COPY". I get a syntax error on ":x" with the \copy below on both 9.0 
and 9.1

=== test script ===
\set x '''HELLO'''
-- Works
\echo :x
-- Works
\o '/tmp/test1.txt'
COPY (SELECT :x) TO STDOUT;
-- Doesn't work
\copy (SELECT :x) TO '/tmp/test2.txt'
=== end script ===

--   Richard Huxton  Archonet Ltd


Re: psql \set vs \copy - bug or expected behaviour?

From
Robert Haas
Date:
On Fri, Oct 21, 2011 at 7:24 AM, Richard Huxton <dev@archonet.com> wrote:
> It looks like \copy is just passing the text of the query unadjusted to
> "COPY". I get a syntax error on ":x" with the \copy below on both 9.0 and
> 9.1
>
> === test script ===
> \set x '''HELLO'''
> -- Works
> \echo :x
> -- Works
> \o '/tmp/test1.txt'
> COPY (SELECT :x) TO STDOUT;
> -- Doesn't work
> \copy (SELECT :x) TO '/tmp/test2.txt'
> === end script ===

I'm not sure whether that's a bug per se, but I can see where a
behavior change might be an improvement.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: psql \set vs \copy - bug or expected behaviour?

From
Bruce Momjian
Date:
On Fri, Oct 21, 2011 at 05:31:41PM -0400, Robert Haas wrote:
> On Fri, Oct 21, 2011 at 7:24 AM, Richard Huxton <dev@archonet.com> wrote:
> > It looks like \copy is just passing the text of the query unadjusted to
> > "COPY". I get a syntax error on ":x" with the \copy below on both 9.0 and
> > 9.1
> >
> > === test script ===
> > \set x '''HELLO'''
> > -- Works
> > \echo :x
> > -- Works
> > \o '/tmp/test1.txt'
> > COPY (SELECT :x) TO STDOUT;
> > -- Doesn't work
> > \copy (SELECT :x) TO '/tmp/test2.txt'
> > === end script ===
>
> I'm not sure whether that's a bug per se, but I can see where a
> behavior change might be an improvement.

I did some research on this and learned a little more about flex rules.

Turns out we can allow variable substitution in psql whole-line
commands, like \copy and \!, by sharing the variable expansion flex
rules with the code that does argument processing.

What we can't easily do is to allow quotes to prevent variable
substitution in these whole-line commands because we can't process the
quotes because that will remove them.

Here are some examples;  \copy and \! behave the same:

    test=> \set x abc
    test=> \echo :x
    abc
    test=> \echo ":x"
-->    ":x"
    test=> \! echo :x
    abc
    test=> \! echo ":x"
-->    abc

Notice the last line has expanded :x even though it is in quotes.

So, what do we want?  The attached patch is pretty short.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Attachment

Re: psql \set vs \copy - bug or expected behaviour?

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> On Fri, Oct 21, 2011 at 05:31:41PM -0400, Robert Haas wrote:
>> I'm not sure whether that's a bug per se, but I can see where a
>> behavior change might be an improvement.

> I did some research on this and learned a little more about flex rules.

> Turns out we can allow variable substitution in psql whole-line
> commands, like \copy and \!, by sharing the variable expansion flex
> rules with the code that does argument processing.  

Well, it'd be nice to allow substitution there ...

> What we can't easily do is to allow quotes to prevent variable
> substitution in these whole-line commands because we can't process the
> quotes because that will remove them.

... but if there is then no way to prevent it, that's absolutely
unacceptable.
        regards, tom lane



Re: psql \set vs \copy - bug or expected behaviour?

From
Richard Huxton
Date:
On 17/08/12 18:38, Tom Lane wrote:
> Bruce Momjian<bruce@momjian.us>  writes:
>> On Fri, Oct 21, 2011 at 05:31:41PM -0400, Robert Haas wrote:
>>> I'm not sure whether that's a bug per se, but I can see where a
>>> behavior change might be an improvement.
>
>> I did some research on this and learned a little more about flex rules.
>
>> Turns out we can allow variable substitution in psql whole-line
>> commands, like \copy and \!, by sharing the variable expansion flex
>> rules with the code that does argument processing.
>
> Well, it'd be nice to allow substitution there ...
>
>> What we can't easily do is to allow quotes to prevent variable
>> substitution in these whole-line commands because we can't process the
>> quotes because that will remove them.
>
> ... but if there is then no way to prevent it, that's absolutely
> unacceptable.

If I'm understanding this correctly, \copy parsing just passes the query 
part unaltered as part of a COPY statement back into the top-level 
parser. Likewise with the \!shell stuff (but presumably to execve).

To handle variable-substitution correctly for \copy we'd need to 
duplicate the full parsing for COPY. For \! we'd need something which 
understood shell-syntax (for the various shells out there). Ick.

Or you'd need a separate variable-bracketing {{:x}} syntax that could 
work like reverse dollar-quoting. Also Ick.

As far as we know this has only inconvenienced one person (me) badly 
enough to report a maybe-bug. Thanks for trying Bruce, but I fear this 
is one itch that'll go unscratched.

Rest assured I'm not about to storm off and replace all my installations 
with MySQL :-)

--   Richard Huxton  Archonet Ltd



Re: psql \set vs \copy - bug or expected behaviour?

From
Bruce Momjian
Date:
On Fri, Aug 17, 2012 at 06:55:14PM +0100, Richard Huxton wrote:
> >Well, it'd be nice to allow substitution there ...
> >
> >>What we can't easily do is to allow quotes to prevent variable
> >>substitution in these whole-line commands because we can't process the
> >>quotes because that will remove them.
> >
> >... but if there is then no way to prevent it, that's absolutely
> >unacceptable.
> 
> If I'm understanding this correctly, \copy parsing just passes the
> query part unaltered as part of a COPY statement back into the
> top-level parser. Likewise with the \!shell stuff (but presumably to
> execve).
> 
> To handle variable-substitution correctly for \copy we'd need to
> duplicate the full parsing for COPY. For \! we'd need something
> which understood shell-syntax (for the various shells out there).
> Ick.
> 
> Or you'd need a separate variable-bracketing {{:x}} syntax that
> could work like reverse dollar-quoting. Also Ick.
> 
> As far as we know this has only inconvenienced one person (me) badly
> enough to report a maybe-bug. Thanks for trying Bruce, but I fear
> this is one itch that'll go unscratched.
> 
> Rest assured I'm not about to storm off and replace all my
> installations with MySQL :-)

Good analysis.  Basically we can't hope to fully understand COPY or
shell quoting syntax well enough to properly replace only unquoted psql
variable references.

Therefore, unless I hear otherwise, I will just document the limitation
and withdraw the patch.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +



Re: psql \set vs \copy - bug or expected behaviour?

From
Bruce Momjian
Date:
On Fri, Aug 17, 2012 at 02:01:25PM -0400, Bruce Momjian wrote:
> On Fri, Aug 17, 2012 at 06:55:14PM +0100, Richard Huxton wrote:
> > >Well, it'd be nice to allow substitution there ...
> > >
> > >>What we can't easily do is to allow quotes to prevent variable
> > >>substitution in these whole-line commands because we can't process the
> > >>quotes because that will remove them.
> > >
> > >... but if there is then no way to prevent it, that's absolutely
> > >unacceptable.
> > 
> > If I'm understanding this correctly, \copy parsing just passes the
> > query part unaltered as part of a COPY statement back into the
> > top-level parser. Likewise with the \!shell stuff (but presumably to
> > execve).
> > 
> > To handle variable-substitution correctly for \copy we'd need to
> > duplicate the full parsing for COPY. For \! we'd need something
> > which understood shell-syntax (for the various shells out there).
> > Ick.
> > 
> > Or you'd need a separate variable-bracketing {{:x}} syntax that
> > could work like reverse dollar-quoting. Also Ick.
> > 
> > As far as we know this has only inconvenienced one person (me) badly
> > enough to report a maybe-bug. Thanks for trying Bruce, but I fear
> > this is one itch that'll go unscratched.
> > 
> > Rest assured I'm not about to storm off and replace all my
> > installations with MySQL :-)
> 
> Good analysis.  Basically we can't hope to fully understand COPY or
> shell quoting syntax well enough to properly replace only unquoted psql
> variable references.
> 
> Therefore, unless I hear otherwise, I will just document the limitation
> and withdraw the patch.

Patch withdrawn.  Seems documentation was already in place --- I
clarified \! limitations match \copy.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +