The following bug has been logged on the website:
Bug reference: 17504
Logged by: Christoph Berg
Email address: christoph.berg@credativ.de
PostgreSQL version: 14.3
Operating system: any
Description:
A customer is reporting the following script has not the intended effect:
Preparation:
psql -c 'create table bar (id int)'
psql -c 'insert into bar values(1)'
-- bar now contains "1"
Test script truncate-and-copy.sql:
truncate bar;
\copy bar from bar.txt
select * from bar;
psql --single-transaction -vON_ERROR_STOP=1 -Xf truncate-and-copy.sql
TRUNCATE TABLE
psql:truncate-and-copy.sql:2: error: bar.txt: No such file or directory
The last "select" is correctly not executed anymore, i.e. the script
execution is correcty aborted, but bar is now empty, i.e. `psql -1` did
commit even when the script was erroring out:
psql -c 'select * from bar'
id
────
(0 rows)
The expectation would be that the script is either fully committed, or not
at all.
The problem is not limited to \copy; \i has the same problem.
A workaround is to drop -1, and use an explicit transaction in the script.
Christoph
