Thread: BUG #2458: Postgresql crash

BUG #2458: Postgresql crash

From
"Cstdenis"
Date:
The following bug has been logged online:

Bug reference:      2458
Logged by:          Cstdenis
Email address:      cstdenis@fanart-central.net
PostgreSQL version: 8.1.3
Operating system:   FreeBSD 6.1-RELEASE
Description:        Postgresql crash
Details:

I am running a very busy postgresql based database server. This server had
been running for a few days with a load average in the 20's during most of
the day but postgres seemed to still be returning results quickly enough.
Then somebody reported a "the database system is in recovery mode" error and
on investivation I see a crash.

May 26 17:25:56 ai postgres[41657]: [73388-1] ERROR:  value too long for
type character varying(50)
May 26 19:26:38 ai kernel: pid 41463 (postgres), uid 70: exited on signal 11
(core dumped)
May 26 17:26:40 ai postgres[41712]: [73388-1] WARNING:  terminating
connection because of crash of another server process
May 26 17:26:40 ai postgres[41712]: [73388-2] DETAIL:  The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server
May 26 17:26:40 ai postgres[41712]: [73388-3]  process exited abnormally and
possibly corrupted shared memory.



Here is some more info

ai# gdb postgres postgres.core
GNU gdb 6.1.1 [FreeBSD]
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you
are
welcome to change it and/or distribute copies of it under certain
conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i386-marcel-freebsd"...(no debugging symbols
found)...
Core was generated by `postgres'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /usr/local/lib/libintl.so.6...(no debugging symbols
found)...done.
Loaded symbols for /usr/local/lib/libintl.so.6
Reading symbols from /usr/lib/libssl.so.4...(no debugging symbols
found)...done.
Loaded symbols for /usr/lib/libssl.so.4
Reading symbols from /lib/libcrypto.so.4...(no debugging symbols
found)...done.
Loaded symbols for /lib/libcrypto.so.4
Reading symbols from /lib/libz.so.3...(no debugging symbols found)...done.
Loaded symbols for /lib/libz.so.3
Reading symbols from /lib/libreadline.so.6...(no debugging symbols
found)...done.
Loaded symbols for /lib/libreadline.so.6
Reading symbols from /lib/libcrypt.so.3...(no debugging symbols
found)...done.
Loaded symbols for /lib/libcrypt.so.3
Reading symbols from /lib/libm.so.4...(no debugging symbols found)...done.
Loaded symbols for /lib/libm.so.4
Reading symbols from /lib/libutil.so.5...(no debugging symbols
found)...done.
Loaded symbols for /lib/libutil.so.5
Reading symbols from /lib/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib/libc.so.6
Reading symbols from /usr/local/lib/libiconv.so.3...(no debugging symbols
found)...done.
Loaded symbols for /usr/local/lib/libiconv.so.3
Reading symbols from /lib/libncurses.so.6...(no debugging symbols
found)...done.
Loaded symbols for /lib/libncurses.so.6
Reading symbols from /usr/local/lib/postgresql/plpgsql.so...(no debugging
symbols found)...done.
Loaded symbols for /usr/local/lib/postgresql/plpgsql.so
Reading symbols from /usr/local/lib/postgresql/slony1_funcs.so...(no
debugging symbols found)...done.
Loaded symbols for /usr/local/lib/postgresql/slony1_funcs.so
Reading symbols from /usr/local/lib/postgresql/xxid.so...(no debugging
symbols found)...done.
Loaded symbols for /usr/local/lib/postgresql/xxid.so
Reading symbols from /usr/local/lib/postgresql/tsearch2.so...(no debugging
symbols found)...done.
Loaded symbols for /usr/local/lib/postgresql/tsearch2.so
Reading symbols from /libexec/ld-elf.so.1...(no debugging symbols
found)...done.
Loaded symbols for /libexec/ld-elf.so.1
#0  0x35dfeff0 in plpgsql_xact_cb () from
/usr/local/lib/postgresql/plpgsql.so
(gdb) bt
#0  0x35dfeff0 in plpgsql_xact_cb () from
/usr/local/lib/postgresql/plpgsql.so
#1  0x080ae891 in ReleaseCurrentSubTransaction ()
#2  0x080af075 in CommitTransactionCommand ()
#3  0x08213902 in pg_parse_query ()
#4  0x08215bb8 in PostgresMain ()
#5  0x081d8740 in ClosePostmasterPorts ()
#6  0x081da14b in PostmasterMain ()
#7  0x0818c1ed in main ()
(gdb) q

ai# postmaster --version
postmaster (PostgreSQL) 8.1.3

ai# uname -a
FreeBSD ai.ctgameinfo.com 6.1-RELEASE FreeBSD 6.1-RELEASE #0: Sun May 21
13:59:57 CDT 2006     cstdenis@ai.ctgameinfo.com:/usr/obj/usr/src/sys/AI
i386



postgresql.conf changed lines from default

listen_addresses = '*'
max_connections = 450
shared_buffers = 25600
work_mem = 10240
maintenance_work_mem = 102400
max_fsm_pages = 800000
max_fsm_relations = 1000
vacuum_cost_delay = 200
vacuum_cost_limit = 50
fsync = on
commit_delay = 60000
commit_siblings = 2
checkpoint_segments = 8
checkpoint_warning = 30
geqo_threshold = 14
join_collapse_limit = 11
log_destination = 'syslog'
silent_mode = on
stats_start_collector = on
stats_command_string = on
stats_row_level = on
stats_reset_on_server_start = off
autovacuum = on
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'

server is a dual xeon with hyperthreading disabled and a software mirror of
2 ultra 320 scsi HDs and 2gb of ram. Server has plenty of free disk space
and does not appear to have been swapping at the time.

Partial top output a while after the fact

last pid: 44232;  load averages: 22.86, 13.99, 10.69
                                                         up 4+22:57:51
17:51:17
223 processes: 39 running, 180 sleeping, 4 zombie
CPU states: 43.8% user,  0.0% nice,  4.9% system,  1.3% interrupt, 50.0%
idle
Mem: 752M Active, 916M Inact, 277M Wired, 55M Cache, 112M Buf, 2996K Free
Swap: 4096M Total, 92K Used, 4096M Free

  PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
44160 pgsql       1 -16    0   241M   134M wdrain 0   0:03  7.78% postgres
44165 pgsql       1 -16    0   241M   107M wdrain 0   0:02  7.65% postgres


postgresql is compiled via FreeBSD ports. This server is a clean install of
OS and all software from about a week ago.


Can't think of anything more to add, but can try to give any more desired
information on request.

Re: BUG #2458: Postgresql crash

From
Tom Lane
Date:
"Cstdenis" <cstdenis@fanart-central.net> writes:
> [ SIGSEGV in plpgsql_xact_cb ]

I think this is probably an instance of the bug patched here:
http://archives.postgresql.org/pgsql-committers/2006-03/msg00022.php

If you're certain that your applications never redefine a plpgsql
function that might be in active use, then we might need to look
harder.  Otherwise, please update to 8.1.4 and see if the problem
recurs ...

            regards, tom lane

Re: BUG #2458: Postgresql crash

From
"Cstdenis"
Date:
My application does not use any transactions (other than the implicit per
statement ones that must exist), but I think I did edit some functions in
pgadmin around that time so it may be that bug or something similar/related.

I didn't bother to upgrade to 8.1.4 because I thought it was just a security
fix that didn't apply to me. I'll upgrade tonight during off hours.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Cstdenis" <cstdenis@fanart-central.net>
Cc: <pgsql-bugs@postgresql.org>
Sent: Friday, May 26, 2006 8:05 PM
Subject: Re: [BUGS] BUG #2458: Postgresql crash


> "Cstdenis" <cstdenis@fanart-central.net> writes:
> > [ SIGSEGV in plpgsql_xact_cb ]
>
> I think this is probably an instance of the bug patched here:
> http://archives.postgresql.org/pgsql-committers/2006-03/msg00022.php
>
> If you're certain that your applications never redefine a plpgsql
> function that might be in active use, then we might need to look
> harder.  Otherwise, please update to 8.1.4 and see if the problem
> recurs ...
>
> regards, tom lane
>