Postgres Pro
Downloads
Home   >   mailing lists

Thread: strange "not deferrable" behaviour

strange "not deferrable" behaviour

From
Tomasz Myrta
Date:
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

Re: strange "not deferrable" behaviour

From
Stephan Szabo
Date:
On Mon, 17 Nov 2003, Tomasz Myrta wrote:

> 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?

Hmm, I'd suggest turning on query logging to make sure pgAdmin is doing
what you expect.

Re: strange "not deferrable" behaviour

From
Tomasz Myrta
Date:
Dnia 2003-11-17 16:16, Użytkownik Stephan Szabo napisał:
> Hmm, I'd suggest turning on query logging to make sure pgAdmin is doing
> what you expect.

Well, I've already done this. I didn't check query log, but a result by 
selecting rows from modified table.

Anyway I opened a pgAdmin log and didn't find anything special.

I also found something interesting in pgAdmin work. When constraint is 
defined as "NOT DEFERRABLE", I get "OK" only when I execute whole query 
at once. PgAdmin probably executes this query using only one statement - 
it separates each sql command using semicolon delimiter.

If I try to split my query and execute each command separately - I get 
the same "integrity violation" error as in psql.

I tried also change constraint into "DEFERRABLE". Behaviours of psql and 
PgAdmin are the same - query executes fine, doesn't matter whether it is 
executed as single or several statements.

Regards,
Tomasz Myrta