Simple UPDATE runs forever

Andrus Moor wrote:

The command

UPDATE dok SET krdokumnr=NULL WHERE krdokumnr NOT in (select dokumnr from
dok);

That's a rather dangerous query; I'm pretty sure it updates a lot more
than you think.

There is a long standing "bug" in the SQL standard where NOT IN (...,
..., NULL) always evaluates to NULL, causing the WHERE clause to
evaluate to FALSE...

AFAIK, the reason this hasn't been fixed is that many people use it as a
feature, depending on the buggy behaviour of WHERE.

Some quick examples to show what happens:

select 1 NOT IN (2,3,4,5,NULL);
?column?
----------

(1 row)

select 1 NOT IN (1,2,3,4,5,NULL);
?column?
----------
f
(1 row)

select 1 NOT IN (2,3,4,5);
?column?
----------
t
(1 row)

I don't think PostgreSQL will end up in an infinite loop because of
this, as to my knowledge the subquery is evaluated only once, but the
experts here will doubtlesly correct me if I'm wrong.

runs forever. Postgres.exe process takes 90% of CPU time, disk LED is
flashing.
Platform: Win XP Prof SP2, Postgres 8
dok table has only 7651 rows
Killing client application does not help. Only killing postgres.exe process
stops computer activity.

CREATE TABLE ou1.dok
(
doktyyp char(1),
dokumnr numeric(12) NOT NULL DEFAULT
nextval('"ou1".dok_dokumnr_seq'::text),
krdokumnr numeric(12),
... a lot of other fields
CONSTRAINT dok_pkey PRIMARY KEY (dokumnr)
)
WITHOUT OIDS;

any idea ?

Andrus.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl