Kind of "bug-report"

Hi!

I say kind of, as I am not sure about it, or whether there is a newer
version that does not show up the bug. Here's the description:

When I use the following request (either on psql or using JDBC), the
backend crashes, making the other backends fail spectacularly.

The beast:

select S.last_stat, hdb_comfort as "Confort Haut Dbit" , U.url as item
from url_stats S, urls U where S.idzone = 9999 and S.idurl=U.idurl and

S.idurl in (543888938, -776493094) and last_stat between '2001-09-24
16:15:00.704' and '2001-10-25 00:00:00.0' union select
trunc_3hour(last_stat) as last_stat, avg(hdb_comfort) as "Confort Haut
Dbit", idcontact::varchar(512) as item from url_stats S,urls U, reports
R where S.idzone = 9999 and S.last_stat between '2001-09-24
16:15:00.704' and '2001-10-25 00:00:00.0' and S.idurl=u.idurl and
r.idurl=u.idurl and (status=1 or status=5) and (idcontact in
(-431758079)) group by idcontact, trunc_3hour(last_stat) order by
last_stat;

(sorry about that ;-)

I have three (interesting for the example) tables:

Table url_stats ( hdb_comfort int, last_stat timestamp, idurl int,
idzone int, [...] )
Table urls ( idurl int, url varchar(512), status int [...] )
Table reports ( idurl int, idcontact int, [...] )

There are indices, called:
ident_url
url_by_id
both on table urls (idurl)
url_by_status
on table urls (status)

For table url_stats, they are quite straightforward:
Indices: stat_by_idurl,
stat_by_idurl_idzone_laststat,
stat_by_idurl_last_stat

Function timestamp trunc_3hour (timestamp) returns the year, month, day
fields intact, minutes and seconds to zero, and hour /3 *3 (so as I only
get 00:00:00, 03:00:00, 06:00:00, 09:00:00, ... 21:00:00).

Well, now you have all the elemensts.

An explain select ... shows:
Unique (cost=41329.35..41329.56 rows=3 width=32)
-> Sort (cost=41329.35..41329.35 rows=28 width=32)
-> Append (cost=0.00..41328.66 rows=28 width=32)
-> Nested Loop (cost=0.00..41222.22 rows=28 width=32)
-> Seq Scan on urls u (cost=0.00..68.31
rows=1431 width=16)
-> Index Scan using stat_by_idurl_idzone_laststat
on url_stats s (cost=0.00..28.75 rows=1 width=16)
-> Aggregate (cost=106.44..106.44 rows=0 width=28)
-> Group (cost=106.44..106.44 rows=1 width=28)
-> Sort (cost=106.44..106.44 rows=1
width=28)
-> Nested Loop (cost=0.00..106.43
rows=1 width=28)
-> Nested Loop
(cost=0.00..52.11 rows=2 width=12)
-> Index Scan using
url_by_contact on reports r (cost=0.00..13.26 rows=19 width=8)
-> Index Scan using
url_by_id on urls u (cost=0.00..2.02 rows=1 width=4)
-> Index Scan using
stat_by_idurl_idzone_laststat on url_stats s (cost=0.00..28.71 rows=6
width=16)

Would the verbose query plan useful? I can send it to you if needed.

About the version:
$ psql --version
psql (PostgreSQL) 7.0.3
contains readline, history, multibyte support

I firmly believe that it's a RedHat compiled version.

I do not wish to upgrade, if it is not absolutely required, as I have
about 2Gb data and availability is a main concern.

More information:

If I execute (from psql) the two parts of the union separately, none
crashes. If I do that into tables temp1 and temp2, which were not
previously created and I issue "select * from temp1 union select * from
temp2;" it does not crash either.

The other clients tell me that the backend wishes them to reconnect, as
another backend died and shared memory could be corrupted. The crashing
one just says pgReadData() -- the backend closed the connection
unexpectedly, or something close to this.

If you have some clues, or some other way of writing the request without
dramatically turning performance to unacceptable limits, anything will
be welcome.

The url_stats table contains 1500000+ tuples (I do not dare select
count(*) from url_stats ;-), urls contains 1000+ and reports contains
about 5000 (not sure, but >1000 and <100000).

If you believe that upgrading could lead us to a notable performance
increase, we may study the situation.

Thank you for reading my e-mail.

Thank you very, very much for answering it.

Yours,

Antonio Fiol
W3ping