Re: Dead Lock problem with 8.1.3

From: Kai Hessing <kai(dot)hessing(at)hobsons(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Dead Lock problem with 8.1.3
Date: 2006-09-27 16:42:38
Message-ID: 4nvnvuFcad75U1@individual.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kai Hessing 2006-09-27 16:46:42 Re: Dead Lock problem with 8.1.3
Previous Message Tony Caduto 2006-09-27 16:41:23 Re: Sockets and posgtres