Thread: Does psql -f quit insertion after an error in a statement has been detected?

Hi,

I'm using a psql -f command to reload the data from a dump file.  I noticed that some tables are not populated with any
rows(I mean 0 rows), yet, if I manually insert a row (actually just copy an INSERT statement from that input file) in
theinteractive terminal, that row was added with no problem.  So my question "does psql -f quits inserting rows for a
tablewhen it detects there is an error in a statement?".  The impression that I got is that even though other rows
mightnot have any errors, but psql -f seems just quits after it detects an error in a row. 

Am I missing something here?
Thanks
Mary

Wang, Mary Y wrote:
> Hi,
>
> I'm using a psql -f command to reload the data from a dump file.  I noticed that some tables are not populated with
anyrows (I mean 0 rows), yet, if I manually insert a row (actually just copy an INSERT statement from that input file)
inthe interactive terminal, that row was added with no problem.  So my question "does psql -f quits inserting rows for
atable when it detects there is an error in a statement?".  The impression that I got is that even though other rows
mightnot have any errors, but psql -f seems just quits after it detects an error in a row. 
>
> Am I missing something here?
>

if the inserts are in a transaction (eg, after a BEGIN), any error will
cause the entire transaction to rollback and the rest of the transaction
to be 'flushed' until a ROLLBACK or COMMIT statement ends the
transaction block.

On Sun, Jun 6, 2010 at 5:29 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
> I'm using a psql -f command to reload the data from a dump file.  I noticed that some tables are not populated with
anyrows (I mean 0 rows), yet, if I manually insert a row (actually just copy an INSERT statement from that input file)
inthe interactive terminal, that row was added with no problem.  So my question "does psql -f quits inserting rows for
atable when it detects there is an error in a statement?".  The impression that I got is that even though other rows
mightnot have any errors, but psql -f seems just quits after it detects an error in a row. 
>

See the psql man page, and search for "ON_ERROR_STOP". This controls
this behavior.

That's good to know.  I ended up manually copy/paste INSERT statements for each table to another file and rerun the
psql-f again.  It was painful!   

Mary
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Vick Khera
Sent: Monday, June 07, 2010 11:21 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Does psql -f quit insertion after an error in a statement has been detected?

On Sun, Jun 6, 2010 at 5:29 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
> I'm using a psql -f command to reload the data from a dump file.  I noticed that some tables are not populated with
anyrows (I mean 0 rows), yet, if I manually insert a row (actually just copy an INSERT statement from that input file)
inthe interactive terminal, that row was added with no problem.  So my question "does psql -f quits inserting rows for
atable when it detects there is an error in a statement?".  The impression that I got is that even though other rows
mightnot have any errors, but psql -f seems just quits after it detects an error in a row. 
>

See the psql man page, and search for "ON_ERROR_STOP". This controls this behavior.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 06/07/2010 02:10 PM, Wang, Mary Y wrote:
> That's good to know.  I ended up manually copy/paste INSERT statements for each table to another file and rerun the
psql-f again.  It was painful! 
>
> Mary

For future reference you could do something like:

pg_dump -a -t table_name -f table_name_data.sql

This will dump the data only from the table 'table_name' in the original
database to a file you can use to populate the table in the new database.

See here for the gory details:
http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html

--
Adrian Klaver
adrian.klaver@gmail.com

Wang, Mary Y wrote:
> Hi,
>
> I'm using a psql -f command to reload the data from a dump file.  I
> noticed that some tables are not populated with any rows (I mean 0
> rows), yet, if I manually insert a row (actually just copy an INSERT
> statement from that input file) in the interactive terminal, that row
> was added with no problem.  So my question "does psql -f quits inserting
> rows for a table when it detects there is an error in a statement?".
> The impression that I got is that even though other rows might not have
> any errors, but psql -f seems just quits after it detects an error in
> a row.

Unless you set ON_ERROR_STOP in psql, psql will continue reading the
file and executing commands:

        <term><varname>ON_ERROR_STOP</varname></term>
        <listitem>
        <para>
        By default, if non-interactive scripts encounter an error, such
        as a malformed <acronym>SQL</acronym> command or internal
        meta-command, processing continues. This has been the
        traditional behavior of <application>psql</application> but it
        is sometimes not desirable. If this variable is set, script
        processing will immediately terminate. If the script was called
        from another script it will terminate in the same fashion. If
        the outermost script was not called from an interactive
        <application>psql</application> session but rather using the
        <option>-f</option> option, <application>psql</application> will
        return error code 3, to distinguish this case from fatal error
        conditions (error code 1).


--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +