Re: 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(at)magproductions(dot)nl
W: http://www.magproductions.nl