Thread: WEIRD! postmaster: segfault with sub select??!
Hi everybody,
I'm facing a strange problem with a relatively simple sub select
whereas everything else runs perfect on this machine (PG 8.4.2 @
Fedora 12, Core2 E4600, 4GB, 2 x 320GB).
# SELECT DISTINCT name FROM bbr_parts WHERE id IN (SELECT part_id FROM
bbr_v_office_to_parts WHERE office_id=39 AND office_type=9);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The sub select has no problems and returns 2 rows correctly:
# SELECT part_id FROM bbr_v_office_to_parts WHERE office_id=39 AND
office_type=9;
part_id
---------
412
394
Replacing the sub select with these hard-coded numbers works as well:
SELECT DISTINCT name FROM bbr_parts WHERE id IN (412, 394); -- okay
What I tried so far to circle the problem:
* REINDEXing all related tables didn't solve the problem (a tip from
Tom Lange to a similar problem I've found in another posting).
* Dropped RI Constraint.
* Decreased shared_buffer down to 1MB (the minimum postmaster starts).
* Dropped the complete database and re-created it. Then, the SELECT
worked about 10 times until the problem comes back!
When nothing else helps, I will downgrade from PostgreSQL 8.4.2 to
8.3.7. Thank you for your comments.
--Reto
On 3 Jan 2010, at 9:31, Reto wrote: > Hi everybody, > > I'm facing a strange problem with a relatively simple sub select > whereas everything else runs perfect on this machine (PG 8.4.2 @ > Fedora 12, Core2 E4600, 4GB, 2 x 320GB). > > # SELECT DISTINCT name FROM bbr_parts WHERE id IN (SELECT part_id FROM > bbr_v_office_to_parts WHERE office_id=39 AND office_type=9); > > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. Did you verify the problem is indeed a segmentation fault? There are other reasons backend processes terminate, for examplethe OS doing silly things. I'd check that your hardware is working properly and that you don't have things like memoryovercommit (I believe that's what Linux calls it) turned on. > When nothing else helps, I will downgrade from PostgreSQL 8.4.2 to > 8.3.7. Thank you for your comments. If that helps, then that's a strong pointer to a bug in Postgres or in a stored procedure in your application. There aretons of people running sub-queries on 8.4 though, so I strongly doubt Postgres is to blame here. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b40e6e69954031810539!
On Sun, Jan 3, 2010 at 8:31 AM, Reto <primzahl@gmail.com> wrote: > Hi everybody, > > I'm facing a strange problem with a relatively simple sub select > whereas everything else runs perfect on this machine (PG 8.4.2 @ > Fedora 12, Core2 E4600, 4GB, 2 x 320GB). > > # SELECT DISTINCT name FROM bbr_parts WHERE id IN (SELECT part_id FROM > bbr_v_office_to_parts WHERE office_id=39 AND office_type=9); > > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. Look at the postmaster logs for the error which caused the crash. There should be something there even if it seg faulted. Also, can you get a backtrace? Even with your current build if you attach to the backend that you're connected to and get a backtrace that could be useful. (run "select pg_backend_pid()" and then attach to that pid in gdb, type "c" and then after the crash run "backtrace"). The most useful would be to get a coredump from a debugging build but the two things above would be a good start. -- greg