X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 5393ED1DA73
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 17 Nov 2003 13:52:47 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 63544-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 17 Nov 2003 09:52:18 -0400 (AST)
Received: from smtp1k.poczta.onet.pl (smtp1k.poczta.onet.pl [213.180.130.35])
	by svr1.postgresql.org (Postfix) with ESMTP id 6D9E0D1DA63
	for <pgsql-sql@postgresql.org>; Mon, 17 Nov 2003 09:52:11 -0400 (AST)
Received: from core-1.citynet.pl ([80.48.135.69]:26886 "EHLO poczta.onet.pl")
	by kps1.test.onet.pl with ESMTP id <S383723AbTKQNwH>;
	Mon, 17 Nov 2003 14:52:07 +0100
Message-ID: <3FB8D27E.7030000@poczta.onet.pl>
Date: Mon, 17 Nov 2003 14:51:58 +0100
From: Tomek <myrcioch@poczta.onet.pl>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.5) Gecko/20031007
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: strange DEFERRABLE behaviour
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/227
X-Sequence-Number: 16042

Hi

I've got some tables defined as:
create table xx (
   some_column references master_table

It means this column is defined by default:
NOT DEFERRABLE, INITIALLY IMMEDIATE

I tried replacing rows in this table by new ones as:
set autocommit=off;
begin;
set constraints all deferred;
delete from ....
insert ...
insert...
commit;
I get "integrity violation"... just after "delete"

If I well understood manual, it works like it should. I'm not sure, 
because if I run this query in pgAdmin2, it works fine (why?) and 
replaces rows as needed.

When I drop this foreign key constraint (not too easy without a 
constraint name) and recreate it as "DEFERRABLE", this query works fine 
also in psql.

My question is: Why my query works fine when using pgAdmin, and it fails 
when using psql?

Regards,
Tomasz Myrta