pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstrange "not deferrable" behaviour
3+ messages / 2 participants
[nested] [flat]
* strange "not deferrable" behaviour
@ 2003-11-17 13:59 Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tomasz Myrta @ 2003-11-17 13:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: strange "not deferrable" behaviour
@ 2003-11-17 15:16 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2003-11-17 15:16 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: strange "not deferrable" behaviour
@ 2003-11-17 15:35 Tomasz Myrta <jasiek@klaster.net>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tomasz Myrta @ 2003-11-17 15:35 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-11-17 15:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-17 13:59 strange "not deferrable" behaviour Tomasz Myrta <jasiek@klaster.net>
2003-11-17 15:16 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-11-17 15:35 ` Tomasz Myrta <jasiek@klaster.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox