Thread: incorrect exit code from psql with single transaction + violation of deferred FK constraint

Hi

I've noticed that executing a sql script such with psql with -1
-vON_ERROR_STOP=on where the script causes a deferred foreign key
constraint to be violated returns 0 rather than the expected 3. I have
reproduced this in psql 8.4.1, 8.3.3 and 8.2.9, which does lead me to
wonder whether it is expected behaviour. However...

Sample code to reproduce:

-- test.sql
create table foo (id int primary key, foo_id int);
alter table foo add constraint fk1 foreign key (foo_id) references
foo(id) deferrable initially deferred;
insert into foo select 1,2;

for which:

psql -1 -vON_ERROR_STOP=on -f test.sql

returns 0 (but with message detailing the constraint violation)

psql -vON_ERROR_STOP=on -f test.sql

returns 3 (as expected).

However, with the constraint immediate, i.e.

-- test.sql
create table foo (id int primary key, foo_id int);
alter table foo add constraint fk1 foreign key (foo_id) references foo(id);
insert into foo select 1,2;

psql -1 -vON_ERROR_STOP=on -f test.sql

and

psql -vON_ERROR_STOP=on -f test.sql

both return 3 (which is the expected behaviour on my reading of the docs).

Also, interestingly, if I wrap the first script in begin; ... commit; I
always get 3 returned.

Thanks,

Dominic.

Dominic Bevacqua
Director
BPM Logic Ltd.
http://www.bpmlogic.com