Re: Dead Lock problem with 8.1.3

Alban Hertroys wrote:
>>> SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid =
>>> 34 AND s.sid NOT IN ( SELECT sid FROM stud_vera WHERE veraid = 2 );
>
> I'm pretty sure it's not a deadlock. It probably takes very long for
> some reason; maybe an explain of that query will give some insight. You
> probably lack some indices.

No. The system goes into an endless loop. The part ('SELECT sid FROM
stud_vera WHERE veraid = 2') seems to create a temporary table again and
again and again ....
The same clause needs around 5 seconds under Postgres 8.0.8. On 8.1.3 we
killed the process after 40 hours while using constantly 80% CPU power.
(Btw. Explain leads to the same problem, it just hangs up...)

We're currently trying to separate the problem into a simple setup.

> Alternatively, have you tried an EXCEPT query? I think this is what you
> try to query:
>
> SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid = 34
> EXCEPT
> SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid = 2

Thanks, I will try this out. To be honest, I didn't know the EXCEPT
query (and I think the programmer also doesn't *g*).

*greets*
Kai