Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3d009e4

Browse files
committed
Add support for piping COPY to/from an external program.
This includes backend "COPY TO/FROM PROGRAM '...'" syntax, and correspondingpsql \copy syntax. Like with reading/writing files, the backend version issuperuser-only, and in the psql version, the program is run in the client.In the passing, the psql \copy STDIN/STDOUT syntax is subtly changed: if youthe stdin/stdout is quoted, it's now interpreted as a filename. For example,"\copy foo from 'stdin'" now reads from a file called 'stdin', not fromstandard input. Before this, there was no way to specify a filename calledstdin, stdout, pstdin or pstdout.This creates a new function in pgport, wait_result_to_str(), which canbe used to convert the exit status of a process, as returned by wait(3),to a human-readable string.Etsuro Fujita, reviewed by Amit Kapila.
1 parent73dc003 commit3d009e4

File tree

21 files changed

+581
-149
lines changed

21 files changed

+581
-149
lines changed

‎contrib/file_fdw/file_fdw.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -588,6 +588,7 @@ fileBeginForeignScan(ForeignScanState *node, int eflags)
588588
*/
589589
cstate=BeginCopyFrom(node->ss.ss_currentRelation,
590590
filename,
591+
false,
591592
NIL,
592593
options);
593594

@@ -660,6 +661,7 @@ fileReScanForeignScan(ForeignScanState *node)
660661

661662
festate->cstate=BeginCopyFrom(node->ss.ss_currentRelation,
662663
festate->filename,
664+
false,
663665
NIL,
664666
festate->options);
665667
}
@@ -993,7 +995,7 @@ file_acquire_sample_rows(Relation onerel, int elevel,
993995
/*
994996
* Create CopyState from FDW options.
995997
*/
996-
cstate=BeginCopyFrom(onerel,filename,NIL,options);
998+
cstate=BeginCopyFrom(onerel,filename,false,NIL,options);
997999

9981000
/*
9991001
* Use per-tuple memory context to prevent leak of memory used to read

‎doc/src/sgml/keywords.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3513,6 +3513,13 @@
35133513
<entry>reserved</entry>
35143514
<entry>reserved</entry>
35153515
</row>
3516+
<row>
3517+
<entry><token>PROGRAM</token></entry>
3518+
<entry>non-reserved</entry>
3519+
<entry></entry>
3520+
<entry></entry>
3521+
<entry></entry>
3522+
</row>
35163523
<row>
35173524
<entry><token>PUBLIC</token></entry>
35183525
<entry></entry>

‎doc/src/sgml/ref/copy.sgml

Lines changed: 45 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -23,11 +23,11 @@ PostgreSQL documentation
2323
<refsynopsisdiv>
2424
<synopsis>
2525
COPY <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
26-
FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
26+
FROM { '<replaceable class="parameter">filename</replaceable>' |PROGRAM '<replaceable class="parameter">command</replaceable>' |STDIN }
2727
[ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
2828

2929
COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] | ( <replaceable class="parameter">query</replaceable> ) }
30-
TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
30+
TO { '<replaceable class="parameter">filename</replaceable>' |PROGRAM '<replaceable class="parameter">command</replaceable>' |STDOUT }
3131
[ [ WITH ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]
3232

3333
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
@@ -72,6 +72,10 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
7272
<productname>PostgreSQL</productname> server to directly read from
7373
or write to a file. The file must be accessible to the server and
7474
the name must be specified from the viewpoint of the server. When
75+
<literal>PROGRAM</literal> is specified, the server executes the
76+
given command, and reads from its standard input, or writes to its
77+
standard output. The command must be specified from the viewpoint of the
78+
server, and be executable by the <literal>postgres</> user. When
7579
<literal>STDIN</literal> or <literal>STDOUT</literal> is
7680
specified, data is transmitted via the connection between the
7781
client and the server.
@@ -125,6 +129,25 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
125129
</listitem>
126130
</varlistentry>
127131

132+
<varlistentry>
133+
<term><literal>PROGRAM</literal></term>
134+
<listitem>
135+
<para>
136+
A command to execute. In <command>COPY FROM</command>, the input is
137+
read from standard output of the command, and in <command>COPY TO</>,
138+
the output is written to the standard input of the command.
139+
</para>
140+
<para>
141+
Note that the command is invoked by the shell, so if you need to pass
142+
any arguments to shell command that come from an untrusted source, you
143+
must be careful to strip or escape any special characters that might
144+
have a special meaning for the shell. For security reasons, it is best
145+
to use a fixed command string, or at least avoid passing any user input
146+
in it.
147+
</para>
148+
</listitem>
149+
</varlistentry>
150+
128151
<varlistentry>
129152
<term><literal>STDIN</literal></term>
130153
<listitem>
@@ -367,9 +390,13 @@ COPY <replaceable class="parameter">count</replaceable>
367390
they must reside on or be accessible to the database server machine,
368391
not the client. They must be accessible to and readable or writable
369392
by the <productname>PostgreSQL</productname> user (the user ID the
370-
server runs as), not the client. <command>COPY</command> naming a
371-
file is only allowed to database superusers, since it allows reading
372-
or writing any file that the server has privileges to access.
393+
server runs as), not the client. Similarly,
394+
the command specified with <literal>PROGRAM</literal> is executed directly
395+
by the server, not by the client application, must be executable by the
396+
<productname>PostgreSQL</productname> user.
397+
<command>COPY</command> naming a file or command is only allowed to
398+
database superusers, since it allows reading or writing any file that the
399+
server has privileges to access.
373400
</para>
374401

375402
<para>
@@ -393,6 +420,11 @@ COPY <replaceable class="parameter">count</replaceable>
393420
the cluster's data directory), not the client's working directory.
394421
</para>
395422

423+
<para>
424+
Executing a command with <literal>PROGRAM</literal> might be restricted
425+
by operating system's access control mechanisms, such as the SELinux.
426+
</para>
427+
396428
<para>
397429
<command>COPY FROM</command> will invoke any triggers and check
398430
constraints on the destination table. However, it will not invoke rules.
@@ -841,6 +873,14 @@ COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sq
841873
</programlisting>
842874
</para>
843875

876+
<para>
877+
To copy into a compressed file, you can pipe the output through an external
878+
compression program:
879+
<programlisting>
880+
COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';
881+
</programlisting>
882+
</para>
883+
844884
<para>
845885
Here is a sample of data suitable for copying into a table from
846886
<literal>STDIN</literal>:

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 22 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -830,7 +830,7 @@ testdb=&gt;
830830
<varlistentry id="APP-PSQL-meta-commands-copy">
831831
<term><literal>\copy { <replaceable class="parameter">table</replaceable> [ ( <replaceable class="parameter">column_list</replaceable> ) ] | ( <replaceable class="parameter">query</replaceable> ) }
832832
{ <literal>from</literal> | <literal>to</literal> }
833-
{ <replaceable class="parameter">filename</replaceable> | stdin | stdout | pstdin | pstdout }
833+
{ <replaceable class="parameter">'filename'</replaceable> | program <replaceable class="parameter">'command'</replaceable> | stdin | stdout | pstdin | pstdout }
834834
[ [ with ] ( <replaceable class="parameter">option</replaceable> [, ...] ) ]</literal></term>
835835

836836
<listitem>
@@ -847,16 +847,14 @@ testdb=&gt;
847847
</para>
848848

849849
<para>
850-
The syntax of the command is similar to that of the
851-
<acronym>SQL</acronym> <xref linkend="sql-copy">
852-
command, and
853-
<replaceable class="parameter">option</replaceable>
854-
must indicate one of the options of the
855-
<acronym>SQL</acronym> <xref linkend="sql-copy"> command.
856-
Note that, because of this,
857-
special parsing rules apply to the <command>\copy</command>
858-
command. In particular, the variable substitution rules and
859-
backslash escapes do not apply.
850+
When <literal>program</> is specified,
851+
<replaceable class="parameter">command</replaceable> is
852+
executed by <application>psql</application> and the data from
853+
or to <replaceable class="parameter">command</replaceable> is
854+
routed between the server and the client.
855+
This means that the execution privileges are those of
856+
the local user, not the server, and no SQL superuser
857+
privileges are required.
860858
</para>
861859

862860
<para><literal>\copy ... from stdin | to stdout</literal>
@@ -870,6 +868,19 @@ testdb=&gt;
870868
for populating tables in-line within a SQL script file.
871869
</para>
872870

871+
<para>
872+
The syntax of the command is similar to that of the
873+
<acronym>SQL</acronym> <xref linkend="sql-copy">
874+
command, and
875+
<replaceable class="parameter">option</replaceable>
876+
must indicate one of the options of the
877+
<acronym>SQL</acronym> <xref linkend="sql-copy"> command.
878+
Note that, because of this,
879+
special parsing rules apply to the <command>\copy</command>
880+
command. In particular, the variable substitution rules and
881+
backslash escapes do not apply.
882+
</para>
883+
873884
<tip>
874885
<para>
875886
This operation is not as efficient as the <acronym>SQL</acronym>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp