Skip site navigation (1) Skip section navigation (2)

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

From: Dominic Bevacqua <dominic(dot)bevacqua(at)bpmlogic(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: incorrect exit code from psql with single transaction + violation of deferred FK constraint
Date: 2009-10-08 15:29:51
Message-ID: 4ACE056F.9040106@bpmlogic.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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










Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2009-10-08 15:32:17
Subject: Re: COPY enhancements
Previous:From: Alvaro HerreraDate: 2009-10-08 15:29:01
Subject: Re: COPY enhancements

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-10-08 15:38:12
Subject: Re: BUG #5102: Silent IN (list of strings) failure to detect syntax error when list is linewrapped
Previous:From: Kevin GrittnerDate: 2009-10-08 15:28:51
Subject: Re: BUG #5105: "Select Into Strict" does not throw NO_DATA_FOUND

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group