ON_ERROR_EXIT and interactive mode (or, how to force interactive mode off)

On Fri, Dec 27, 2013 at 7:30 AM, Tim Kane <tim.kane@gmail.com> wrote:

HI all,

I have a fairly complex python script which calls a number of ‘psql’
sessions via a subprocess to perform bulk load operations.
The bulk loads are generated on the fly, and are written directly to the
stdin of the subprocess.

I would like to rely on the ON_ERROR_STOP=1 functionality to force the
process to exit with an error code – however, psql rightly believes the
process it is operating under is an interactive one – since it is receiving
its input from stdin.

For performance (and disk) reasons, I’d rather not have to rely on writing
my input to a file, before passing it to psql with –f

This part of the man page gave me hope…

-f filename
--file filename

Use the file filename as the source of commands instead of reading
commands interactively. After the file is processed, psql terminates.
This is in many ways equivalent to the internal command \i.

If filename is - (hyphen), then standard input is read.

Using this option is subtly different from writing psql < filename. In
general, both will do what you expect, but using -f enables some nice
features such as error messages with line numbers. There is also a slight
chance that using this option will reduce the start-up overhead. On the
other hand, the variant using the shell's input redirection is (in theory)
guaranteed to yield exactly the same output that you would have gotten had
you entered everything by hand.

Eg:
psql –v ON_ERROR_STOP=1 –f -

But it seems this is still understood by psql to be an interactive
session, and fails to exit the process on an error.

Is there any way to force psql to read from stdin, in a non-interactive
manner? (that is, to ensure it exits the process on an error condition?)

Thanks for any advice

Tim

I'm afraid I can't answer your question directly (see my sig), but if

you're already using Python to create the files, would you be better off
using psycopg2's COPY interfaces? If use just connect directly from Python
to Postgres, I would think that your success and error handling would be
much simpler.

http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from

--
I asked the Internet how to train my cat, and the Internet told me to get a
dog.

Thanks Chris. I didn’t realise psycopg2 had a COPY interface, that’s quite
handy. I’ll have a play, cheers.

Tim

From: Chris Curvey <chris@chriscurvey.com>
Reply-To: <chris@chriscurvey.com>
Date: Friday, 27 December 2013 21:18
To: Tim Kane <tim.kane@gmail.com>
Cc: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] ON_ERROR_EXIT and interactive mode (or, how to force
interactive mode off)

On Fri, Dec 27, 2013 at 7:30 AM, Tim Kane <tim.kane@gmail.com> wrote:

HI all,

I have a fairly complex python script which calls a number of ‘psql’ sessions
via a subprocess to perform bulk load operations.
The bulk loads are generated on the fly, and are written directly to the stdin
of the subprocess.

I would like to rely on the ON_ERROR_STOP=1 functionality to force the process
to exit with an error code – however, psql rightly believes the process it is
operating under is an interactive one – since it is receiving its input from
stdin.

For performance (and disk) reasons, I’d rather not have to rely on writing my
input to a file, before passing it to psql with –f

This part of the man page gave me hope…

-f filename
--file filename
Use the file filename as the source of commands instead of reading commands
interactively. After the file is processed, psql terminates. This is in many
ways equivalent to the internal command \i.
If filename is - (hyphen), then standard input is read.
Using this option is subtly different from writing psql < filename. In
general, both will do what you expect, but using -f enables some nice
features such as error messages with line numbers. There is also a slight
chance that using this option will reduce the start-up overhead. On the other
hand, the variant using the shell's input redirection is (in theory)
guaranteed to yield exactly the same output that you would have gotten had
you entered everything by hand.

Eg:
psql –v ON_ERROR_STOP=1 –f -

But it seems this is still understood by psql to be an interactive session,
and fails to exit the process on an error.

Is there any way to force psql to read from stdin, in a non-interactive
manner? (that is, to ensure it exits the process on an error condition?)

Thanks for any advice

Tim

I'm afraid I can't answer your question directly (see my sig), but if you're
already using Python to create the files, would you be better off using
psycopg2's COPY interfaces? If use just connect directly from Python to
Postgres, I would think that your success and error handling would be much
simpler.

http://initd.org/psycopg/docs/usage.html#using-copy-to-and-copy-from

--
I asked the Internet how to train my cat, and the Internet told me to get a
dog.