Add partial :-variable expansion to psql \copy

Hello,

I've been biten by psql's \copy lack of variable expansion, in a limited-access docker-inside-VM context where COPY is not a viable option and hardwired names are not desirable. The attached patch allows \copy to use variable's values in place of table and file names:

```psql
\set table 'some table'
\set input 'some file name.csv'
\copy :"table" from :'input' with (format csv)
```

--
Fabien.

Always better with a file attached :-/

Sorry for the noise.

--
Fabien.
________________________________
De : Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Envoyé : lundi 31 mars 2025 13:48
À : PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Objet : Add partial :-variable expansion to psql \copy

Hello,

I've been biten by psql's \copy lack of variable expansion, in a limited-access docker-inside-VM context where COPY is not a viable option and hardwired names are not desirable. The attached patch allows \copy to use variable's values in place of table and file names:

```psql
\set table 'some table'
\set input 'some file name.csv'
\copy :"table" from :'input' with (format csv)
```

--
Fabien.

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> writes:
> I've been biten by psql's \copy lack of variable expansion, in a limited-access docker-inside-VM context where COPY is not a viable option and hardwired names are not desirable. The attached patch allows \copy to use variable's values in place of table and file names:

Hm ... I'm on board with the general idea of the feature, but I find
this implementation quite horrid. I would rather see us adjust the
lexing rules in psqlscanslash.l so that variable expansion happens
there when collecting \copy arguments. This would eliminate at
least part of the distinction between OT_WHOLE_LINE and OT_NORMAL
modes, and we'd have to have some discussion about how far to go
there. Or maybe just change exec_command_copy to use OT_NORMAL
not OT_WHOLE_LINE? If we modify the behavior of OT_WHOLE_LINE
then the ability to expand variables would start to apply in the
other commands using that, notably \!. I think that's at least
potentially good, but perhaps the blast radius of such a change
is too large.

Anyway, my feeling about it is that \copy parsing is a huge hack
right now, and I'd rather see it become less of a hack, that is
more like other psql commands, instead of getting even hackier.

regards, tom lane

>
> Anyway, my feeling about it is that \copy parsing is a huge hack
> right now, and I'd rather see it become less of a hack, that is
> more like other psql commands, instead of getting even hackier.
>

I wasn't as horrified as Tom, but it did have the feeling of it solving
half the problem.

We can already do this

COPY (SELECT :foo FROM :bar WHERE :condition) TO STDOUT \g :"myfilename"

So it seems that what we need is a good way to pipe local data to a
standard COPY command, which is then free to use the existing variable
interpolations.

If we could do this:

COPY :"myschema".:"mytable" FROM STDIN \g < :"myfilename"

that would fit our patterns most cleanly, but we would probably create a
parsing hassle for ourselves if we ever wanted to mix pipe-to with
pipe-from. It would also require checking on every command, when uploaded
\copy commands make up a very small percentage of commands issued. So I
don't think there's a good way around the asymmetry of COPY TO being a
regular \g-able command, whereas COPY FROM will always require some other
send-command.

Perhaps we create a new command \copyfrom:

COPY :"myschema".:"mytable" :options FROM STDIN \copyfrom :"myfilename"

COPY :"myschema".:"mytable" :options FROM STDIN \copyfrom
:"my_complex_command" |

If we had something like that we might be able to replace all existing uses
of \copy.

On 31/03/2025 17:09, Tom Lane wrote:

> Fabien COELHO<coelho(at)cri(dot)ensmp(dot)fr> writes:
>> [...] The attached patch allows \copy to use variable's values in place of table and file names:
> Hm ... I'm on board with the general idea of the feature, but I find
> this implementation quite horrid.

Indeed, I just added the stuff in the already quite peculiar manual
lexer/parser for \copy. I did not think of addressing the why it is like
that issue and try to fix it :-)

> I would rather see us adjust the
> lexing rules in psqlscanslash.l so that variable expansion happens
> there when collecting \copy arguments. This would eliminate at
> least part of the distinction between OT_WHOLE_LINE and OT_NORMAL
> modes, and we'd have to have some discussion about how far to go
> there. Or maybe just change exec_command_copy to use OT_NORMAL
> not OT_WHOLE_LINE? If we modify the behavior of OT_WHOLE_LINE
> then the ability to expand variables would start to apply in the
> other commands using that, notably \!. I think that's at least
> potentially good, but perhaps the blast radius of such a change
> is too large.

I'm not sure that such \copy salvage to using lex is easy because:

(1) it seems that is the only command which is really full SQL hidden in
a backslash command

(2) on one line without requiring a final ';',

(3) the client needs to actually parse it and modify it to some degree
before sending it to the server.

so the implication for trying to maintain compatibility without adding
weirdness seem slim.

> Anyway, my feeling about it is that \copy parsing is a huge hack
> right now,
Yes.
> and I'd rather see it become less of a hack, that is
> more like other psql commands, instead of getting even hackier.

I think that I'll have a try with Corey suggestion to extend COPY rather
than change \copy.

--

Fabien.

Hello Corey,

> If we could do this:
>
>     COPY :"myschema".:"mytable" FROM STDIN \g < :"myfilename"
>
> that would fit our patterns most cleanly, but we would probably create
> a parsing hassle for ourselves if we ever wanted to mix pipe-to with
> pipe-from. It would also require checking on every command, when
> uploaded \copy commands make up a very small percentage of commands
> issued. So I don't think there's a good way around the asymmetry of
> COPY TO being a regular \g-able command, whereas COPY FROM will always
> require some other send-command.
>
> Perhaps we create a new command \copyfrom:
>
>     COPY :"myschema".:"mytable" :options FROM STDIN \copyfrom
> :"myfilename"
>
>     COPY :"myschema".:"mytable" :options FROM STDIN \copyfrom
> :"my_complex_command" |
>
> If we had something like that we might be able to replace all existing
> uses of \copy.
>
Indeed, I like the idea of extending psql handling of COPY rather than
trying to salvage \copy. I do not like that it is probably more work for
significantly larger patch.

There are 4 cases to address: input/output cross join file/program, and
as you pointed out the output ones are already handled.

I'm hesitating about the right syntax, though, for an input backslash
command which in effect would really only apply to COPY? ISTM that \g*
is used for "go", i.e. a semi-colon replacement which executes the SQL,
and we should want the same thing, which suggests:

COPY "foo" FROM STDIN \gi filename

COPY "foo" FROM STDIN \gi command...|

Another drawback is that it creates an error path:

COPY "foo" FROM 'server-side-file' \gi 'client-side-file'

--

Fabien.

Re: Fabien Coelho
> (1) it seems that is the only command which is really full SQL hidden in a
> backslash command

Perhaps this form could be improved by changing `\copy (select) to file`
to something like `select \gcopy (to file)`. That might make :expansion
in the "select" part easier to handle.

I've heard several complaints that `\copy (select)` can't be wrapped
over several lines, so offering the alternative syntax in parallel to
\copy would also solve another problem.

Christoph

Hi

út 1. 4. 2025 v 12:00 odesílatel Christoph Berg <myon(at)debian(dot)org> napsal:

> Re: Fabien Coelho
> > (1) it seems that is the only command which is really full SQL hidden in
> a
> > backslash command
>
> Perhaps this form could be improved by changing `\copy (select) to file`
> to something like `select \gcopy (to file)`. That might make :expansion
> in the "select" part easier to handle.
>
> I've heard several complaints that `\copy (select)` can't be wrapped
> over several lines, so offering the alternative syntax in parallel to
> \copy would also solve another problem.
>

What is the reason to use \copy (select) to ?

psql (on client side) supports csv format pretty well with single line
switching to this format (i know so it not have the full functionality of
COPY statement).

(2025-04-01 12:08:36) postgres=# select 1,2 \g (format=csv) output.csv
(2025-04-01 12:08:53) postgres=#

For me, the original proposal has interesting benefits (Tom wrote about
it). Inconsistency where psql's variable can or cannot be used is unhappy.
It is always bad surprising when you find some inconsistencies

Regards

Pavel

>
> Christoph
>
>
>

Christoph Berg wrote:

> Perhaps this form could be improved by changing `\copy (select) to file`
> to something like `select \gcopy (to file)`. That might make :expansion
> in the "select" part easier to handle.

In this direction (COPY TO), it was already taken care of by
commit 6d3ede5f1c654f923b2767b0b0c3b09569adaa18 [1]
a few years ago.

That is, the following sequence already works fine:

\set filename '/tmp/foo'
\set column relname
\set table pg_class

COPY (select :"column" from :"table") TO STDOUT \g :filename

It's also mentioned in the manual through that paragraph in \copy
"
Tip
Another way to obtain the same result as \copy ... to is to use the
SQL COPY ... TO STDOUT command and terminate it with \g filename or
\g |program. Unlike \copy, this method allows the command to span
multiple lines; also, variable interpolation and backquote expansion
can be used.
"

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6d3ede5f1c654f923b2767b0b0c3b09569adaa18

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/

>
> I'm hesitating about the right syntax, though, for an input backslash
> command which in effect would really only apply to COPY? ISTM that \g* is
> used for "go", i.e. a semi-colon replacement which executes the SQL, and we
> should want the same thing, which suggests:
>
making it a \g-variant does seem to be natural.

> COPY "foo" FROM STDIN \gi filename
>
> COPY "foo" FROM STDIN \gi command...|
>
> Another drawback is that it creates an error path:
>
> COPY "foo" FROM 'server-side-file' \gi 'client-side-file'
>
Consider the case:

INSERT INTO mytable (x) VALUES(1) \gi '/path/to/local/file'

Is this an error because we'd be teeing up a file for a command that cannot
consume one? How much do we parse the buffer to learn whether we have a
query or a COPY command in the buffer? Maybe in the future other commands
will take uploaded files, but I would imagine those operations would just
leverage the existing COPY functionality inside whatever additional
benefits they provide. Until then, this command can only really be used for
single COPY foo FROM STDIN statements. With that in mind, I think the name
\copyfrom reflects the highly specific utility of the command, and sets
boundaries for what is reasonable to have in the query buffer (i.e. one
COPY statement, possibly multiline), leaving \gi open for later, more
flexible uses.

Looking at the code a bit, \copyfrom would have a variant of do_copy() with
a much abbreviated variant parse_slash_copy(), no construction of the copy
statement whatsoever, just use the query buffer and let regular SendQuery()
error handling take over.

Hello Tom and Corey,

> [...] Anyway, my feeling about it is that \copy parsing is a huge hack
> right now, and I'd rather see it become less of a hack, that is
> more like other psql commands, instead of getting even hackier.

After giving it some thoughts, I concluded that trying to salvage \copy
is not the
way to go and I have followed Corey's suggestion to extend the standard
SQL COPY
handling by providing an alternate input stream with "\gi file" or "\gi
cmd|".

This has lead to significant restructuring so as to simplify \copy
handling to use
the \g and \gi existing infrastructure. I've moved checked performed
only for \copy
so that they are now also done with \g, and error messages are more
systematically
shown. The pipe char used to mark a command instead of a file is
switched to a
boolean, which is more consistent with other places and how it can be
managed with
"\gi command|" as the pipe char is at the end instead of the start. The
patch also
includes tests and some doc.

If this is accepted, ISTM that \copy could be retired and even removed at a
later stage, which would solve elegantly its debatable implementation.

--
Fabien.

Hello,

Here is a rebased and updated version of adding \gi:

- improved documentation
- some refactoring to share one function
- signal trap disabled on \gi as well

The thread subject is now a misnomer, I'm not sure whether I should
update it or start a new thread.

On 05/04/2025 22:14, Fabien Coelho wrote:
> Hello Tom and Corey,
>
>> [...] Anyway, my feeling about it is that \copy parsing is a huge hack
>> right now, and I'd rather see it become less of a hack, that is
>> more like other psql commands, instead of getting even hackier.
>
> After giving it some thoughts, I concluded that trying to salvage
> \copy is not the
> way to go and I have followed Corey's suggestion to extend the
> standard SQL COPY
> handling by providing an alternate input stream with "\gi file" or
> "\gi cmd|".
>
> This has lead to significant restructuring so as to simplify \copy
> handling to use
> the \g and \gi existing infrastructure. I've moved checked performed
> only for \copy
> so that they are now also done with \g, and error messages are more
> systematically
> shown. The pipe char used to mark a command instead of a file is
> switched to a
> boolean, which is more consistent with other places and how it can be
> managed with
> "\gi command|" as the pipe char is at the end instead of the start.
> The patch also
> includes tests and some doc.
>
> If this is accepted, ISTM that \copy could be retired and even removed
> at a
> later stage, which would solve elegantly its debatable implementation.
>
--
Fabien.

Hi,
I am unable to apply the patch on master. Can you please confirm if the patch is rebased?

Regards,
Akshat Jaimini

Hi,

On 2025-05-01 16:37:18 +0000, Akshat Jaimini wrote:
> I am unable to apply the patch on master. Can you please confirm if the patch is rebased?

cfbot seems to be able to apply the patch.

However, the tests have not passed in quite a while:

https://cirrus-ci.com/task/5702107409416192
https://api.cirrus-ci.com/v1/task/5702107409416192/logs/cores.log

Greetings,

Andres Freund

Hi,

On 2025-05-13 09:57:46 -0400, Andres Freund wrote:
> On 2025-05-01 16:37:18 +0000, Akshat Jaimini wrote:
> > I am unable to apply the patch on master. Can you please confirm if the patch is rebased?
>
> cfbot seems to be able to apply the patch.
>
> However, the tests have not passed in quite a while:

Since that's still the case, I've now marked the patch as returned with
feedback.

Greetings,

Andres Freund