From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Marc Evans <Marc(at)SoftwareHackery(dot)Com> |
Cc: | pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Odd behavior observed |
Date: | 2006-09-19 17:58:40 |
Message-ID: | 14998.1158688720@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
I wrote:
> ... I think maybe
> something is applying an UPDATE to the row and losing the new value
> at that point. Are any of the FKs non-default actions (ON ... SET NULL
> or some such that would try to alter data instead of just erroring)?
I've been able to reproduce a problem that may or may not be Marc's
problem, but it's definitely a bug:
regression=# create table foo(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
regression=# create table bar(f1 int references foo on delete set null);
CREATE TABLE
regression=# insert into foo values(1);
INSERT 0 1
regression=# insert into bar values(1);
INSERT 0 1
regression=# delete from foo;
DELETE 1
regression=# select * from bar;
f1
----
(1 row)
regression=# alter table bar add column f2 int;
ALTER TABLE
regression=# insert into foo values(1);
INSERT 0 1
regression=# insert into bar values(1,2);
INSERT 0 1
regression=# select * from bar;
f1 | f2
----+----
|
1 | 2
(2 rows)
regression=# delete from foo;
DELETE 1
regression=# select * from bar;
f1 | f2
----+----
|
|
(2 rows)
regression=#
f2 should clearly not have gotten set to null there. I believe the
problem is that we have a stale cached plan for the ON DELETE SET NULL
referential action. Still another reason why we need a plan
invalidation mechanism :-(
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-09-19 18:09:05 | Re: Odd behavior observed |
Previous Message | Marc Evans | 2006-09-19 17:56:47 | Re: Odd behavior observed |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-09-19 18:04:29 | Re: Getting rid of cmin and cmax |
Previous Message | Marc Evans | 2006-09-19 17:56:47 | Re: Odd behavior observed |