Big join breaks psql
От | Alessio Bragadini |
---|---|
Тема | Big join breaks psql |
Дата | |
Msg-id | 38B27283.12AEB679@albourne.com обсуждение исходный текст |
Ответы |
Re: [HACKERS] Big join breaks psql
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
The following (rather long) query run fine for some time when executed through Micro$oft Query via Psqlodbc. We never tried it under psql. Today it appeared broken (some trickery on the Excel worksheet seems to have fix that) and for the first time I tried to run it from the command line. I never managed, psql always breaks in the same way: cb=> \i /home/alessio/check.sql SELECT peergroup.id, convid.name, convid.bb_cb, convid.bb_eq, convdaily.date, convdaily.paritygross, convdaily.bidprice, convdaily.askprice, convdaily.stockprice, tradingsignal.job, tradingsignal.buysell, tradingsignal.delta, tradingsignal.premium FROM convdaily convdaily, convid convid, peergroup peergroup, tradingsignal tradingsignal WHERE convid.id = peergroup.id AND peergroup.id = tradingsignal.id AND convdaily.id = peergroup.id AND convdaily.date = tradingsignal.date AND ((tradingsignal.job=6) AND (tradingsignal.buysell='B') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=7) AND (tradingsignal.buysell='B') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=8) AND (tradingsignal.buysell='B') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=210) AND (tradingsignal.buysell='B') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=211) AND (tradingsignal.buysell='B') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=6) AND (tradingsignal.buysell='U') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=7) AND (tradingsignal.buysell='U') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=8) AND (tradingsignal.buysell='U') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=210) AND (tradingsignal.buysell='U') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=211) AND (tradingsignal.buysell='U') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=212) AND (tradingsignal.buysell='B') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000') OR (tradingsignal.job=212) AND (tradingsignal.buysell='U') AND (peergroup.pgid=45) AND (convdaily.date='21-02-2000')) ORDER BY peergroup.id, tradingsignal.job; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. Some info on the tables: cb=> select count(*) from convdaily; count ------- 2260691 (1 row) cb=> select count(*) from convid; count -----3666 (1 row) cb=> select count(*) from peergroup; count ----- 730 (1 row) cb=> select count(*) from tradingsignal;count ------ 221374 (1 row) Is it a problem on the backend or on psql? Is simply the query using a too big 4-table join? I am wondering, since postmaster.log states FATAL 1: Memory exhausted in AllocSetAlloc() We are running PostgreSQL 6.5.2 on alphaev6-dec-osf4.0f, compiled by cc. Any idea would be greatly appreciated. Thanks in advance. -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://www.sevenseas.org/~alessio Nicosia, Cyprus phone: +357-2-750652 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Jeroen van VianenДата:
Сообщение: Re: [PATCHES] Patch for more readable parse error messages