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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
