Re: Dead Lock problem with 8.1.3

Kai Hessing wrote:
> 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 ....

Ah, this is where part of the confusion stems from. That subquery just
results in a resultset being created (maybe not even that, depends a bit
on the query planner). That's not a temporary table.

I rather doubt that postgres would repeat that query, it's results won't
change between comparisons with rows from your main query (this depends
a bit on what type of transaction isolation you use).

> 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...)

EXPLAIN without ANALYZE locking up?!? Maybe some application is holding
a lock on a record in your result set. Did you try this query without
any other applications connecting to that database?

If you can't do that, you could dump that database and restore it in a
different one for testing cases like this.

Lastly, considering the odd behaviour and the huge differences between
minor versions of the database, you might have a corrupted index
somewhere. You can fix those with REINDEX.
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //