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
