Thread: BUG #6301: extra space in psql variable expansion

The following bug has been logged online:

Bug reference:      6301
Logged by:          M
Email address:      sitrash@email.com
PostgreSQL version: 8.4.9
Operating system:   Linux
Description:        extra space in psql variable expansion
Details:

First of all, thank you for supporting a very useful product!

When psql expands a :variable into a string it appends a space to the
expansion string. For example:

psql (8.4.9)
Type "help" for help.

testdb=> \set my_home /home/crazy
testdb=> \echo :my_home/my-script.sql
/home/crazy /my-script.sql

Note the space between /home/crazy and /my-script.sql in the line above,
while there's no space after /home/crazy two lines above where my_home
variable is initialized.

This prevents use of a variable for storing the directory name where a
script to be sourced resides, as in

\i :my_dir/my-script.sql

because the expanded string contains a space between the expansion of
:my_dir and the following /my-script.sql.

I believe psql should expand the string :my_dir/my-script.sql into
/home/crazy/my-script.sql (note the lack of space between crazy and the
following / character).

No popular scripting language I'm aware of appends the space character to an
expanded variable, so psql's behavior is rather unusual. E.g.
${HOME}/my-script.sql expands into /home/crazy/my-script.sql in a Unix shell
script.

On Fri, Nov 18, 2011 at 1:12 PM, M <sitrash@email.com> wrote:
>
> When psql expands a :variable into a string it appends a space to the
> expansion string. For example:
>
> psql (8.4.9)
> Type "help" for help.
>
> testdb=> \set my_home /home/crazy
> testdb=> \echo :my_home/my-script.sql
> /home/crazy /my-script.sql
>
> Note the space between /home/crazy and /my-script.sql in the line above,
> while there's no space after /home/crazy two lines above where my_home
> variable is initialized.

It looks like this is a psql bug which has been fixed recently. I see
the OP's reported behavior on a 9.1.1 client, but in 9.2dev it works
as expected:

test=# \set my_home /home/crazy
test=# \echo :my_home/my-script.sql
/home/crazy/my-script.sql

I don't see the exact commit which fixed this, but I do see some fixes
to psql's lexer done recently, such as
928311a463d480ca566e2905a369ac6aa0c3e210, so maybe this case got fixed
as a nice side-effect.

Josh

On Fri, Nov 18, 2011 at 4:53 PM, Michael Smolsky <sitrash@email.com> wrote:
> Could you please also confirm that variable expansion works correctly for
> psql's \i command as well?
>
> Test case (type on psql client prompt):
>
> \set my_dir /some/path/to/sql/script/dir
> \i :my_dir/my-script.sql
>
> I expect the parser to load and execute my-script.sql located within
> /some/path/to/sql/script/dir

Yup, that works on a 9.2dev client:

test=# \set my_dir /tmp/foo/bar/baz
test=# \i :my_dir/test.sql
 ?column?
----------
        1
(1 row)

with test.sql containing the single line "SELECT 1;"

Josh

On Sat, Nov 19, 2011 at 12:34 AM, Michael Smolsky
> Now waiting for 9.2 package to ship for Ubuntu...

Oh - 9.2 won't be released for a while; I tested on a recent git
checkout of the main branch.

It's possible that the next 9.1 point release (9.1.2) would contain
this fix, but I haven't verified this (the way to check would be to do
a git checkout of the 9_1_STABLE branch from git, and see if the fix
is present there).

Josh

Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Fri, Nov 18, 2011 at 1:12 PM, M <sitrash@email.com> wrote:
>> When psql expands a :variable into a string it appends a space to the
>> expansion string.

It doesn't actually do that, but rather splits the argument into two
arguments.  \echo makes it look like there's spaces between arguments...

> I don't see the exact commit which fixed this, but I do see some fixes
> to psql's lexer done recently, such as
> 928311a463d480ca566e2905a369ac6aa0c3e210, so maybe this case got fixed
> as a nice side-effect.

I think the description of that commit addresses the point exactly:

    ... we considered an argument
    completed as soon as we'd processed one backtick, variable
    reference, or quoted substring.  A string like 'FOO'BAR was thus
    taken as two arguments not one, not exactly what one would
    expect.  In the new coding, an argument is considered terminated
    only by unquoted whitespace or backslash.

            regards, tom lane

Josh Kupershmidt <schmiddy@gmail.com> writes:
> It's possible that the next 9.1 point release (9.1.2) would contain
> this fix, but I haven't verified this (the way to check would be to do
> a git checkout of the 9_1_STABLE branch from git, and see if the fix
> is present there).

No, it's not in any stable branch.  It seemed like enough of a
behavioral change to be too risky to put into minor releases.

            regards, tom lane