Hi
2015-11-25 17:13 GMT+01:00 Catalin Iacob <[email protected]>:
> On Wed, Nov 18, 2015 at 5:49 PM, Catalin Iacob <[email protected]>
> wrote:
> > On Tue, Nov 17, 2015 at 10:13 PM, Tom Lane <[email protected]> wrote:
> >> 1. -c no longer implies --no-psqlrc. That's a backwards
> incompatibility,
> >> but very easy to explain and very easy to work around.
> >>
> >> 2. You can have multiple -c and/or -f. Each -c is processed in
> >> the traditional way, ie, either it's a single backslash command
> >> or it's sent in a single PQexec. That doesn't seem to me to have
> >> much impact on the behavior of adjacent -c or -f.
> >>
> >> 3. If you combine -1 with -c and/or -f, you get one BEGIN inserted
> >> at the beginning and one COMMIT at the end. Nothing else changes.
>
> > I'll try to write the documentation patch for these semantics sometime
> > next week.
>
> Attached is my attempt at a documentation patch, feedback welcome. I'm
> assuming Pavel will pick up the implementation, if not I could also
> try it.
>
I am sorry for delay - the end of year :(
Attached patch per Tom Lane proposal.
* multiple -c -f options are supported, the order of options is respected
* the statements for one -c options are executed in transactions
* Iacob's doc patch merged
Regards
Pavel
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
new file mode 100644
index 5899bb4..2928c92
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** PostgreSQL documentation
*** 38,46 ****
<productname>PostgreSQL</productname>. It enables you to type in
queries interactively, issue them to
<productname>PostgreSQL</productname>, and see the query results.
! Alternatively, input can be from a file. In addition, it provides a
! number of meta-commands and various shell-like features to
! facilitate writing scripts and automating a wide variety of tasks.
</para>
</refsect1>
--- 38,47 ----
<productname>PostgreSQL</productname>. It enables you to type in
queries interactively, issue them to
<productname>PostgreSQL</productname>, and see the query results.
! Alternatively, input can be from a file or from command line
! arguments. In addition, it provides a number of meta-commands and various
! shell-like features to facilitate writing scripts and automating a wide
! variety of tasks.
</para>
</refsect1>
*************** PostgreSQL documentation
*** 89,126 ****
<term><option>--command=<replaceable class="parameter">command</replaceable></></term>
<listitem>
<para>
! Specifies that <application>psql</application> is to execute one
! command string, <replaceable class="parameter">command</replaceable>,
! and then exit. This is useful in shell scripts. Start-up files
! (<filename>psqlrc</filename> and <filename>~/.psqlrc</filename>) are
! ignored with this option.
</para>
<para>
! <replaceable class="parameter">command</replaceable> must be either
! a command string that is completely parsable by the server (i.e.,
! it contains no <application>psql</application>-specific features),
! or a single backslash command. Thus you cannot mix
! <acronym>SQL</acronym> and <application>psql</application>
! meta-commands with this option. To achieve that, you could
! pipe the string into <application>psql</application>, for example:
! <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>.
(<literal>\\</> is the separator meta-command.)
</para>
<para>
! If the command string contains multiple SQL commands, they are
! processed in a single transaction, unless there are explicit
! <command>BEGIN</>/<command>COMMIT</> commands included in the
! string to divide it into multiple transactions. This is
! different from the behavior when the same string is fed to
! <application>psql</application>'s standard input. Also, only
! the result of the last SQL command is returned.
</para>
<para>
! Because of these legacy behaviors, putting more than one command in
! the <option>-c</option> string often has unexpected results. It's
! better to feed multiple commands to <application>psql</application>'s
! standard input, either using <application>echo</application> as
! illustrated above, or via a shell here-document, for example:
<programlisting>
psql <<EOF
\x
--- 90,134 ----
<term><option>--command=<replaceable class="parameter">command</replaceable></></term>
<listitem>
<para>
! Specifies that <application>psql</application> is to execute the given
! command string, <replaceable class="parameter">command</replaceable>.
! This option can be repeated and combined in any order with
! the <option>-f</option> option.
</para>
<para>
! <replaceable class="parameter">command</replaceable> must be either a
! command string that is completely parsable by the server (i.e., it
! contains no <application>psql</application>-specific features), or a
! single backslash command. Thus you cannot mix
! <acronym>SQL</acronym> and <application>psql</application> meta-commands
! with this option. To achieve that, you could use
! repeated <option>-c</option> options or pipe the string
! into <application>psql</application>, for example:
! <literal>psql -c '\x' -c 'SELECT * FROM foo;'</literal> or
! <literal>echo '\x \\ SELECT * FROM foo;' | psql</literal>
(<literal>\\</> is the separator meta-command.)
</para>
<para>
! Each command string passed to <option>-c</option> is sent to the server
! as a single query. Because of this, the server executes it as a single
! transaction, even if a command string contains
! multiple <acronym>SQL</acronym> commands, unless there are
! explicit <command>BEGIN</>/<command>COMMIT</> commands included in the
! string to divide it into multiple transactions. Also, the server only
! returns the result of the last <acronym>SQL</acronym> command to the
! client. This is different from the behavior when the same string with
! multiple <acronym>SQL</acronym> commands is fed
! to <application>psql</application>'s standard input because
! then <application>psql</application> sends each <acronym>SQL</acronym>
! command separately.
</para>
<para>
! Because of the execution as a single query, putting more than one
! command in the <option>-c</option> string often has unexpected results.
! It's better to use repeated <option>-c</option> commands or feed
! multiple commands to <application>psql</application>'s standard input,
! either using <application>echo</application> as illustrated above, or
! via a shell here-document, for example:
<programlisting>
psql <<EOF
\x
*************** EOF
*** 183,193 ****
<term><option>--file=<replaceable class="parameter">filename</replaceable></></term>
<listitem>
<para>
! Use the file <replaceable class="parameter">filename</replaceable>
! as the source of commands instead of reading commands interactively.
! After the file is processed, <application>psql</application>
! terminates. This is in many ways equivalent to the meta-command
! <command>\i</command>.
</para>
<para>
--- 191,203 ----
<term><option>--file=<replaceable class="parameter">filename</replaceable></></term>
<listitem>
<para>
! Use the file <replaceable class="parameter">filename</replaceable> as
! the source of commands instead of reading commands interactively. This
! option can be repeated and combined in any order with
! the <option>-c</option> option. After the commands in
! every <option>-c</option> command string and <option>-f</option> file
! are processed, <application>psql</application> terminates. This option
! is in many ways equivalent to the meta-command <command>\i</command>.
</para>
<para>
*************** EOF
*** 539,558 ****
<term><option>--single-transaction</option></term>
<listitem>
<para>
! When <application>psql</application> executes a script, adding
! this option wraps <command>BEGIN</>/<command>COMMIT</> around the
! script to execute it as a single transaction. This ensures that
! either all the commands complete successfully, or no changes are
! applied.
</para>
<para>
! If the script itself uses <command>BEGIN</>, <command>COMMIT</>,
or <command>ROLLBACK</>, this option will not have the desired
! effects.
! Also, if the script contains any command that cannot be executed
! inside a transaction block, specifying this option will cause that
! command (and hence the whole transaction) to fail.
</para>
</listitem>
</varlistentry>
--- 549,569 ----
<term><option>--single-transaction</option></term>
<listitem>
<para>
! When <application>psql</application> executes commands from a script
! and/or a <option>-c</option> option, adding this option
! wraps <command>BEGIN</>/<command>COMMIT</> around all of those
! commands as a whole to execute them as a single transaction. This
! ensures that either all the commands complete successfully, or no
! changes are applied.
</para>
<para>
! If the commands themselves
! contain <command>BEGIN</>, <command>COMMIT</>,
or <command>ROLLBACK</>, this option will not have the desired
! effects. Also, if an individual command cannot be executed inside a
! transaction block, specifying this option will cause the whole
! transaction to fail.
</para>
</listitem>
</varlistentry>
*************** PSQL_EDITOR_LINENUMBER_ARG='--line '
*** 3725,3731 ****
<term><filename>psqlrc</filename> and <filename>~/.psqlrc</filename></term>
<listitem>
<para>
! Unless it is passed an <option>-X</option> or <option>-c</option> option,
<application>psql</application> attempts to read and execute commands
from the system-wide startup file (<filename>psqlrc</filename>) and then
the user's personal startup file (<filename>~/.psqlrc</filename>), after
--- 3736,3742 ----
<term><filename>psqlrc</filename> and <filename>~/.psqlrc</filename></term>
<listitem>
<para>
! Unless it is passed an <option>-X</option> option,
<application>psql</application> attempts to read and execute commands
from the system-wide startup file (<filename>psqlrc</filename>) and then
the user's personal startup file (<filename>~/.psqlrc</filename>), after
*************** PSQL_EDITOR_LINENUMBER_ARG='--line '
*** 3819,3824 ****
--- 3830,3842 ----
</para>
</listitem>
+ <listitem>
+ <para>
+ Before <productname>PostgreSQL</productname> 9.6, <option>-c</option>
+ used to imply <option>-X</option> and therefore it wouldn't
+ read <filename>psqlrc</filename> files, that is no longer the case.
+ </para>
+ </listitem>
</itemizedlist>
</refsect1>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 72c00c1..5e3b4ed
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*************** exec_command(const char *cmd,
*** 918,924 ****
include_relative = (strcmp(cmd, "ir") == 0
|| strcmp(cmd, "include_relative") == 0);
expand_tilde(&fname);
! success = (process_file(fname, false, include_relative) == EXIT_SUCCESS);
free(fname);
}
}
--- 918,924 ----
include_relative = (strcmp(cmd, "ir") == 0
|| strcmp(cmd, "include_relative") == 0);
expand_tilde(&fname);
! success = (process_file(fname, include_relative) == EXIT_SUCCESS);
free(fname);
}
}
*************** do_edit(const char *filename_arg, PQExpB
*** 2287,2299 ****
* the file from where the currently processed file (if any) is located.
*/
int
! process_file(char *filename, bool single_txn, bool use_relative_path)
{
FILE *fd;
int result;
char *oldfilename;
char relpath[MAXPGPATH];
- PGresult *res;
if (!filename)
{
--- 2287,2298 ----
* the file from where the currently processed file (if any) is located.
*/
int
! process_file(char *filename, bool use_relative_path)
{
FILE *fd;
int result;
char *oldfilename;
char relpath[MAXPGPATH];
if (!filename)
{
*************** process_file(char *filename, bool single
*** 2338,2374 ****
oldfilename = pset.inputfile;
pset.inputfile = filename;
- if (single_txn)
- {
- if ((res = PSQLexec("BEGIN")) == NULL)
- {
- if (pset.on_error_stop)
- {
- result = EXIT_USER;
- goto error;
- }
- }
- else
- PQclear(res);
- }
-
result = MainLoop(fd);
- if (single_txn)
- {
- if ((res = PSQLexec("COMMIT")) == NULL)
- {
- if (pset.on_error_stop)
- {
- result = EXIT_USER;
- goto error;
- }
- }
- else
- PQclear(res);
- }
-
- error:
if (fd != stdin)
fclose(fd);
--- 2337,2344 ----
diff --git a/src/bin/psql/command.h b/src/bin/psql/command.h
new file mode 100644
index 54385e8..c817600
*** a/src/bin/psql/command.h
--- b/src/bin/psql/command.h
*************** typedef enum _backslashResult
*** 27,33 ****
extern backslashResult HandleSlashCmds(PsqlScanState scan_state,
PQExpBuffer query_buf);
! extern int process_file(char *filename, bool single_txn, bool use_relative_path);
extern bool do_pset(const char *param,
const char *value,
--- 27,33 ----
extern backslashResult HandleSlashCmds(PsqlScanState scan_state,
PQExpBuffer query_buf);
! extern int process_file(char *filename, bool use_relative_path);
extern bool do_pset(const char *param,
const char *value,
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
new file mode 100644
index 0e266a3..fff16c0
*** a/src/bin/psql/common.c
--- b/src/bin/psql/common.c
*************** recognized_connection_string(const char
*** 1886,1888 ****
--- 1886,1911 ----
{
return uri_prefix_length(connstr) != 0 || strchr(connstr, '=') != NULL;
}
+
+ /*
+ * Support for list of actions. The SimpleStringList cannot be used due possible
+ * combination different actions with the requirement to save the order.
+ */
+ void
+ simple_action_list_append(SimpleActionList *list, int atyp, const char *val)
+ {
+ SimpleActionListCell *cell;
+
+ cell = (SimpleActionListCell *)
+ pg_malloc(offsetof(SimpleActionListCell, val) + strlen(val) + 1);
+
+ cell->next = NULL;
+ cell->atyp = atyp;
+ strcpy(cell->val, val);
+
+ if (list->tail)
+ list->tail->next = cell;
+ else
+ list->head = cell;
+ list->tail = cell;
+ }
diff --git a/src/bin/psql/common.h b/src/bin/psql/common.h
new file mode 100644
index caf31d1..0965c35
*** a/src/bin/psql/common.h
--- b/src/bin/psql/common.h
***************
*** 14,21 ****
--- 14,43 ----
#include "print.h"
+ enum _atypes
+ {
+ ACT_SINGLE_QUERY,
+ ACT_SINGLE_SLASH,
+ ACT_FILE
+ };
+
+ typedef struct SimpleActionListCell
+ {
+ struct SimpleActionListCell *next;
+ int atyp;
+ char val[FLEXIBLE_ARRAY_MEMBER];
+ } SimpleActionListCell;
+
+ typedef struct SimpleActionList
+ {
+ SimpleActionListCell *head;
+ SimpleActionListCell *tail;
+ } SimpleActionList;
+
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
+ extern void simple_action_list_append(SimpleActionList *list, int atyp, const char *val);
+
extern bool setQFout(const char *fname);
extern void psql_error(const char *fmt,...) pg_attribute_printf(1, 2);
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
new file mode 100644
index 7aa997d..eda779b
*** a/src/bin/psql/startup.c
--- b/src/bin/psql/startup.c
*************** PsqlSettings pset;
*** 51,60 ****
enum _actions
{
ACT_NOTHING = 0,
- ACT_SINGLE_SLASH,
ACT_LIST_DB,
! ACT_SINGLE_QUERY,
! ACT_FILE
};
struct adhoc_opts
--- 51,58 ----
enum _actions
{
ACT_NOTHING = 0,
ACT_LIST_DB,
! ACT_FILE_STDIN
};
struct adhoc_opts
*************** struct adhoc_opts
*** 69,74 ****
--- 67,73 ----
bool no_readline;
bool no_psqlrc;
bool single_txn;
+ SimpleActionList actions;
};
static void parse_psql_options(int argc, char *argv[],
*************** main(int argc, char *argv[])
*** 159,164 ****
--- 158,166 ----
SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);
+ options.actions.head = NULL;
+ options.actions.tail = NULL;
+
parse_psql_options(argc, argv, &options);
/*
*************** main(int argc, char *argv[])
*** 166,179 ****
* as if the user had specified "-f -". This lets single-transaction mode
* work in this case.
*/
! if (options.action == ACT_NOTHING && pset.notty)
{
! options.action = ACT_FILE;
options.action_string = NULL;
}
/* Bail out if -1 was specified but will be ignored. */
! if (options.single_txn && options.action != ACT_FILE && options.action == ACT_NOTHING)
{
fprintf(stderr, _("%s: -1 can only be used in non-interactive mode\n"), pset.progname);
exit(EXIT_FAILURE);
--- 168,182 ----
* as if the user had specified "-f -". This lets single-transaction mode
* work in this case.
*/
! if (options.action == ACT_NOTHING && options.actions.head == NULL && pset.notty)
{
! options.action = ACT_FILE_STDIN;
options.action_string = NULL;
}
/* Bail out if -1 was specified but will be ignored. */
! if (options.single_txn && options.action != ACT_FILE_STDIN && options.action == ACT_NOTHING
! && options.actions.head == NULL)
{
fprintf(stderr, _("%s: -1 can only be used in non-interactive mode\n"), pset.progname);
exit(EXIT_FAILURE);
*************** main(int argc, char *argv[])
*** 282,332 ****
/*
* Now find something to do
*/
!
! /*
! * process file given by -f
! */
! if (options.action == ACT_FILE)
{
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
! successResult = process_file(options.action_string, options.single_txn, false);
! }
! /*
! * process slash command if one was given to -c
! */
! else if (options.action == ACT_SINGLE_SLASH)
! {
! PsqlScanState scan_state;
! if (pset.echo == PSQL_ECHO_ALL)
! puts(options.action_string);
! scan_state = psql_scan_create();
! psql_scan_setup(scan_state,
! options.action_string,
! strlen(options.action_string));
! successResult = HandleSlashCmds(scan_state, NULL) != PSQL_CMD_ERROR
! ? EXIT_SUCCESS : EXIT_FAILURE;
! psql_scan_destroy(scan_state);
! }
! /*
! * If the query given to -c was a normal one, send it
! */
! else if (options.action == ACT_SINGLE_QUERY)
! {
! if (pset.echo == PSQL_ECHO_ALL)
! puts(options.action_string);
! successResult = SendQuery(options.action_string)
! ? EXIT_SUCCESS : EXIT_FAILURE;
! }
/*
* or otherwise enter interactive main loop
*/
--- 285,368 ----
/*
* Now find something to do
*/
! if (options.actions.head)
{
+ PGresult *res;
+ SimpleActionListCell *cell;
+
if (!options.no_psqlrc)
process_psqlrc(argv[0]);
! successResult = EXIT_SUCCESS; /* be compiler quiete */
! if (options.single_txn)
! {
! if ((res = PSQLexec("BEGIN")) == NULL)
! {
! if (pset.on_error_stop)
! {
! successResult = EXIT_USER;
! goto error;
! }
! }
! else
! PQclear(res);
! }
! for (cell = options.actions.head; cell; cell = cell->next)
! {
! if (cell->atyp == ACT_SINGLE_QUERY)
! {
! if (pset.echo == PSQL_ECHO_ALL)
! puts(cell->val);
! successResult = SendQuery(cell->val)
! ? EXIT_SUCCESS : EXIT_FAILURE;
! }
! else if (cell->atyp == ACT_SINGLE_SLASH)
! {
! PsqlScanState scan_state;
! if (pset.echo == PSQL_ECHO_ALL)
! puts(cell->val);
! scan_state = psql_scan_create();
! psql_scan_setup(scan_state,
! cell->val,
! strlen(cell->val));
! successResult = HandleSlashCmds(scan_state, NULL) != PSQL_CMD_ERROR
! ? EXIT_SUCCESS : EXIT_FAILURE;
! psql_scan_destroy(scan_state);
! }
! else
! {
! /* ACT_FILE */
! successResult = process_file(cell->val, false);
! }
!
! if (successResult != EXIT_SUCCESS && pset.on_error_stop)
! break;
! }
!
! if (options.single_txn)
! {
! if ((res = PSQLexec("COMMIT")) == NULL)
! {
! if (pset.on_error_stop)
! {
! successResult = EXIT_USER;
! goto error;
! }
! }
! else
! PQclear(res);
! }
+ error:
+ ;
+ }
/*
* or otherwise enter interactive main loop
*/
*************** parse_psql_options(int argc, char *argv[
*** 419,432 ****
SetVariable(pset.vars, "ECHO", "errors");
break;
case 'c':
- options->action_string = pg_strdup(optarg);
if (optarg[0] == '\\')
! {
! options->action = ACT_SINGLE_SLASH;
! options->action_string++;
! }
else
! options->action = ACT_SINGLE_QUERY;
break;
case 'd':
options->dbname = pg_strdup(optarg);
--- 455,468 ----
SetVariable(pset.vars, "ECHO", "errors");
break;
case 'c':
if (optarg[0] == '\\')
! simple_action_list_append(&options->actions,
! ACT_SINGLE_SLASH,
! pstrdup(optarg + 1));
else
! simple_action_list_append(&options->actions,
! ACT_SINGLE_QUERY,
! pstrdup(optarg));
break;
case 'd':
options->dbname = pg_strdup(optarg);
*************** parse_psql_options(int argc, char *argv[
*** 438,445 ****
SetVariableBool(pset.vars, "ECHO_HIDDEN");
break;
case 'f':
! options->action = ACT_FILE;
! options->action_string = pg_strdup(optarg);
break;
case 'F':
pset.popt.topt.fieldSep.separator = pg_strdup(optarg);
--- 474,482 ----
SetVariableBool(pset.vars, "ECHO_HIDDEN");
break;
case 'f':
! simple_action_list_append(&options->actions,
! ACT_FILE,
! pg_strdup(optarg));
break;
case 'F':
pset.popt.topt.fieldSep.separator = pg_strdup(optarg);
*************** process_psqlrc_file(char *filename)
*** 674,684 ****
/* check for minor version first, then major, then no version */
if (access(psqlrc_minor, R_OK) == 0)
! (void) process_file(psqlrc_minor, false, false);
else if (access(psqlrc_major, R_OK) == 0)
! (void) process_file(psqlrc_major, false, false);
else if (access(filename, R_OK) == 0)
! (void) process_file(filename, false, false);
free(psqlrc_minor);
free(psqlrc_major);
--- 711,721 ----
/* check for minor version first, then major, then no version */
if (access(psqlrc_minor, R_OK) == 0)
! (void) process_file(psqlrc_minor, false);
else if (access(psqlrc_major, R_OK) == 0)
! (void) process_file(psqlrc_major, false);
else if (access(filename, R_OK) == 0)
! (void) process_file(filename, false);
free(psqlrc_minor);
free(psqlrc_major);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
