Обсуждение: A crashing query

Hello

Today I present myself before you with a vudu like question...

Here's the problem:

We're running several PostgreSQL databases in several different servers
(also with different PG versions :( ).

In one of our on-line systems we connect to the "local" database and a
"remote" database. The connection to the remote database was always ok,
without any kind of problems, but yesterday, out of the blue, one of the
query's (the most important one) started to crash the remote server.

The tables on the remote server are like this:


si=> \d lecciona
Table    = lecciona
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| classe                           | text not null default ( 'ects' ) |
var |
| curso                            | int4                             |
4 |
| disciplina                       | int4                             |
4 |
| ano_lectivo                      | int4                             |
4 |
| docente                          | text not null                    |
var |
+----------------------------------+----------------------------------+-------+
Index:    lecciona_mkey

There are several other tables descendand of this one. Some of them have
no extra fields, some of them do.

Yestrday a new class was added (this is the only difference from before,
and since then the following query no longer works:

 select distinct disciplina from lecciona* l where l.docente='$id' and
l.ano_lectivo<= $ano_lectivo and l.classe in ('lecciona','responsável')
and not exists (select 1 from lecciona* l1 where
l1.disciplina=l.disciplina and l1.docente <> l.docente and
l1.ano_lectivo <= $ano_lectivo and l1.ano_lectivo > l.ano_lectivo and
l.classe = l1.classe ) order by disciplina";
(this query comes from a PHP script so the $... are replaced on run-time
by actual values.

I've tryed to break this thing into peaces and found out that the
"offending" part is "l1.ano_lectivo > l.ano_lectivo"

BTW: to clarify this whole thing. The tables represent the way in wich
teacher (docente) teach some subjects (disciplina). Everytime there is a
change (a new teacher starts teaching that subject, or something
similar) there is a new record saying to what schhol year it refers
(ano_lectivo). If there is a record say in 1999 of a teacher teaching
some subject and there are no other records that means that if we look
at the data in 2010 it is still valid. There are different kinds of
assignment (responsible, efectively teaching, juri, etc). What the query
try's to do is given a teacher and school year (and other things) find
out wich are the subjects that he is teaching. The subselect serves to
discover if there is someone else teaching the subject after the record
that we found for thaht teacher (i think i'm starting to get confused
here :( ).

this system is a 6.4.2, it was vacuumed and it crashes, violently...
Does anyone have a clue of why?

Thanks.

P.S. : As always I'm available to give further explanations that could
help in solving this problem.

Thanks for you attention



--
        Mario Filipe
        mjnf@uevora.pt
        http://neptuno.sc.uevora.pt/~mjnf

Mario Jorge Nunes Filipe <mjnf@uevora.pt> wrote:
>We're running several PostgreSQL databases in several different servers
>(also with different PG versions :( ).
>
>In one of our on-line systems we connect to the "local" database and a
>"remote" database. The connection to the remote database was always ok,
>without any kind of problems, but yesterday, out of the blue, one of the
>query's (the most important one) started to crash the remote server.

Are you sure you're running a client that's the same PostgreSQL version as
the server?

>this system is a 6.4.2, it was vacuumed and it crashes, violently...
>Does anyone have a clue of why?

Even users running 6.5 are usually advised to upgrade to a 7.x release when
they run into crashes. 6.4 is old.

HTH,
Ray
--
Pinky, Are You Pondering What I'm Pondering?
I think so Brain, but "instant karma" always gets so lumpy.
    Pinky and the Brain in "Brainwashed Part 3: Wash Harder"

Mario Jorge Nunes Filipe <mjnf@uevora.pt> writes:
> Yestrday a new class was added (this is the only difference from before,
> and since then the following query no longer works:

>  select distinct disciplina from lecciona* l where l.docente='$id' and
> l.ano_lectivo<= $ano_lectivo and l.classe in ('lecciona','respons�vel')
> and not exists (select 1 from lecciona* l1 where
> l1.disciplina=l.disciplina and l1.docente <> l.docente and
> l1.ano_lectivo <= $ano_lectivo and l1.ano_lectivo > l.ano_lectivo and
> l.classe = l1.classe ) order by disciplina";

Subselects in inherited queries are a tricky area (in fact I just
yesterday fixed a bug in 7.1RC1 in that specific area).

> this system is a 6.4.2,

Try updating to something less ancient --- there are tons of bugs in
6.4.*'s subselect implementation.  I'm amazed not that this crashes,
but that it ever worked at all.  You really should be on 7.0.3, if
not experimenting with 7.1.

            regards, tom lane