Re: [BUGS] psql \i command with a missing file name doesn't rollback the transaction

[Bruce Momjian]

On Fri, Sep 20, 2013 at 11:15:01AM +0200, Alexey Klyukin wrote:
> Hi,
> 
> We've discovered a surprising behavior of psql \i command. What we sometimes 
> to
> add new tables to the database is:
> 
> begin;
> \i /path/to/table/definitions/table1.sql
> \i /path/to/table/definitions/table2.sql
> ...
> \i /path/to/table/definitions/tableN.sql
> commit;
> 
> What we discovered that some files in the /path/to/table/definitions were
> missing (say, table 2,3), but the table 1, 4... N appeared in the database
> after executing the transaction. This is quite a catch, since we cannot rely 
> on
> transaction consistency when using an include directive.
> 
> The test is simple:
> 
> begin;
> \i whatever;
> select 1;
> commit;
> 
> The expected behavior was that select 1 would lead to 'ERROR: current
> transaction is aborted'.
> The current behavior is that it is executed, although a message is emitted to 
> a
> client:
> whatever: No such file or directory
> 
> Would it be possible from the client side to generate the rollback to the
> server on an attempt to include a non-existing file (perhaps only when
> ON_ERROR_STOP is set to 1?).

The problem is how would we decide what psql actions should trigger a
rollback, and how would we show the user we did that.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


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

[BUGS] psql \i command with a missing file name doesn't rollback the transaction

[Alexey Klyukin]

Hi,

We've discovered a surprising behavior of psql \i command. What we
sometimes to add new tables to the database is:

begin;
\i /path/to/table/definitions/table1.sql
\i /path/to/table/definitions/table2.sql
...
\i /path/to/table/definitions/tableN.sql
commit;

What we discovered that some files in the /path/to/table/definitions were
missing (say, table 2,3), but the table 1, 4... N appeared in the database
after executing the transaction. This is quite a catch, since we cannot
rely on transaction consistency when using an include directive.

The test is simple:

begin;
\i whatever;
select 1;
commit;

The expected behavior was that select 1 would lead to 'ERROR: current
transaction is aborted'.
The current behavior is that it is executed, although a message is emitted
to a client:
whatever: No such file or directory

Would it be possible from the client side to generate the rollback to the
server on an attempt to include a non-existing file (perhaps only when
ON_ERROR_STOP is set to 1?).

-- 
Regards,
Alexey Klyukin