Re: simple update closes connection. why?

tom,

i have a few things for you. namely, i increased the debug settings for logging and i ran gdb by attaching to the running process.

history:

i had altered the table when it was okay with the following sequence

alter table calling add column uname character varying(20);
alter table calling alter column uname set not null;
said I couldn't do that because there were null values in it, so that's when i tried the
update calling set uname='joe';
and got errors.
maybe the attempt to set to not null with empty records was bad.

=======================================================================================================
pg_dump -d 'calling' > dump.sql

....................

pg_dump: DEBUG: StartTransactionCommand
pg_dump: DEBUG: ProcessUtility
pg_dump: DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: ProcessUtility
pg_dump: DEBUG: StartTransactionCommand
pg_dump: DEBUG: ProcessUtility
pg_dump: dumpClasses(): SQL command failed
pg_dump: Error message from server: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor
DEBUG: reaping dead processes
DEBUG: child process (pid 16468) was terminated by signal 11
LOG: server process (pid 16468) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing shared memory and semaphores
DEBUG: shmem_exit(0)
DEBUG: invoking IpcMemoryCreate(size=3620864)
-bash-2.05b$ LOG: database system was interrupted at 2004-02-01 01:16:07 CST
LOG: checkpoint record is at 0/344EA680
LOG: redo record is at 0/344EA680; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 8800413; next oid: 614903
LOG: database system was not properly shut down; automatic recovery in progress
LOG: ReadRecord: record with zero length at 0/344EA6C0
LOG: redo is not required
LOG: database system is ready
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)
DEBUG: reaping dead processes

==================================================================
postgres 17460 0.0 0.3 10952 3476 pts/4 S 01:52 0:00 postgres: joe calling [local] idle
postgres 17461 0.0 0.0 2620 704 pts/4 R 01:52 0:00 ps -axu
-bash-2.05b$ gdb attach 17460
GNU gdb Red Hat Linux (5.2.1-4)
Copyright 2002 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-redhat-linux"...attach: No such file or directory.

Attaching to process 17460
Reading symbols from /usr/local/pgsql/bin/postgres...done.
Reading symbols from /usr/lib/libz.so.1...done.
Loaded symbols for /usr/lib/libz.so.1
Reading symbols from /usr/lib/libreadline.so.4...done.
Loaded symbols for /usr/lib/libreadline.so.4
Reading symbols from /usr/lib/libncurses.so.5...done.
Loaded symbols for /usr/lib/libncurses.so.5
Reading symbols from /lib/libcrypt.so.1...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libresolv.so.2...done.
Loaded symbols for /lib/libresolv.so.2
Reading symbols from /lib/libnsl.so.1...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/i686/libm.so.6...done.
Loaded symbols for /lib/i686/libm.so.6
Reading symbols from /lib/i686/libc.so.6...done.
Loaded symbols for /lib/i686/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_files.so.2...done.
Loaded symbols for /lib/libnss_files.so.2
0x420dabc2 in recv () from /lib/i686/libc.so.6
(gdb) c
Continuing.
DEBUG: StartTransactionCommand
DEBUG: ProcessQuery

Program received signal SIGSEGV, Segmentation fault.
0x0806b286 in nocachegetattr ()
(gdb) where
#0 0x0806b286 in nocachegetattr ()
#1 0x080cef8b in ExecEvalVar ()
#2 0x080d01e0 in ExecEvalExpr ()
#3 0x080d04ea in ExecTargetList ()
#4 0x080d0797 in ExecProject ()
#5 0x080d0839 in ExecScan ()
#6 0x080d674b in ExecSeqScan ()
#7 0x080ce842 in ExecProcNode ()
#8 0x080d736e in ExecLimit ()
#9 0x080ce879 in ExecProcNode ()
#10 0x080cd7cb in ExecutePlan ()
#11 0x080cce92 in ExecutorRun ()
#12 0x0811a645 in ProcessQuery ()
#13 0x08118dc1 in pg_exec_query_string ()
#14 0x08119c21 in PostgresMain ()
#15 0x08101ac2 in DoBackend ()
#16 0x0810163a in BackendStartup ()
#17 0x08100781 in ServerLoop ()
#18 0x08100230 in PostmasterMain ()
#19 0x080defe4 in main ()
#20 0x420158d4 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)
RECOMPILE RECOMPILE RECOMPILE RECOMPILE RECOMPILE RECOMPILE RECOMPILE -g

(gdb) bt
#0 0x0806b854 in nocachegetattr (tuple=0x82f5b78, attnum=1, tupleDesc=0x406f23a0, isnull=0xbfffe9d3 "") at heaptuple.c:349
#1 0x080f79a3 in ExecEvalVar (variable=0x82f5080, econtext=0x82f5b08, isNull=0xbfffe9d3 "") at execQual.c:377
#2 0x080f972b in ExecEvalExpr (expression=0x82f5080, econtext=0x82f5b08, isNull=0xbfffe9d3 "", isDone=0xbfffe9f0)
at execQual.c:1682
#3 0x080f9c3d in ExecTargetList (targetlist=0x82f50a8, nodomains=1, targettype=0x82f5ee8, values=0x82f5fc8,
econtext=0x82f5b08, isDone=0xbfffec04) at execQual.c:2058
#4 0x080fa025 in ExecProject (projInfo=0x82f5fa0, isDone=0xbfffec04) at execQual.c:2282
#5 0x080fa140 in ExecScan (node=0x82f59f0, accessMtd=0x8102498 <SeqNext>) at execScan.c:133
#6 0x0810259c in ExecSeqScan (node=0x82f59f0) at nodeSeqscan.c:133
#7 0x080f6c28 in ExecProcNode (node=0x82f59f0, parent=0x0) at execProcnode.c:291
#8 0x080f56ac in ExecutePlan (estate=0x82f5e20, plan=0x82f59f0, operation=CMD_SELECT, numberTuples=0,
direction=ForwardScanDirection, destfunc=0x82f5fe0) at execMain.c:954
#9 0x080f4a87 in ExecutorRun (queryDesc=0x82f5df8, estate=0x82f5e20, direction=ForwardScanDirection, count=0)
at execMain.c:195
#10 0x0815ffc9 in ProcessQuery (parsetree=0x82f33e8, plan=0x82f59f0, dest=Remote, completionTag=0xbfffedd0 "")
at pquery.c:242
#11 0x0815e232 in pg_exec_query_string (query_string=0x82f3048, dest=Remote, parse_context=0x82ed1c8) at postgres.c:838
#12 0x0815f4da in PostgresMain (argc=4, argv=0xbffff070, username=0x82ac1a1 "joe") at postgres.c:2016
#13 0x0813e836 in DoBackend (port=0x82ac070) at postmaster.c:2293
#14 0x0813df3f in BackendStartup (port=0x82ac070) at postmaster.c:1915
#15 0x0813cd37 in ServerLoop () at postmaster.c:1000
#16 0x0813c810 in PostmasterMain (argc=1, argv=0x8293198) at postmaster.c:779
#17 0x0810e22f in main (argc=1, argv=0xbffff9d4) at main.c:210
#18 0x420158d4 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)

(gdb) info all-registers
eax 0x0 0
ecx 0x924 2340
edx 0xc 12
ebx 0x1a 26
esp 0xbfffe8d0 0xbfffe8d0
ebp 0xbfffe918 0xbfffe918
esi 0x40012020 1073815584
edi 0xbffff9d4 -1073743404
eip 0x806b854 0x806b854
eflags 0x10283 66179
cs 0x23 35
ss 0x2b 43
ds 0x2b 43
es 0x2b 43
fs 0x0 0
gs 0x0 0
st0 0 (raw 0x00000000000000000000)
st1 0 (raw 0x00000000000000000000)
st2 0 (raw 0x00000000000000000000)
st3 0 (raw 0x00000000000000000000)
st4 1 (raw 0x3fff8000000000000000)
st5 1 (raw 0x3fff8000000000000000)
st6 0 (raw 0x00000000000000000000)
st7 0 (raw 0x00000000000000000000)
fctrl 0x37f 895
fstat 0x4020 16416
ftag 0xffff 65535
fiseg 0x0 0
fioff 0x0 0
foseg 0x0 0
fooff 0x0 0
fop 0x0 0
xmm0 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
xmm1 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
xmm2 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
---Type <return> to continue, or q <return> to quit---
xmm3 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
xmm4 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
xmm5 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
xmm6 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
xmm7 {f = {0x0, 0x0, 0x0, 0x0}} {f = {0, 0, 0, 0}}
mxcsr 0x1f80 8064
orig_eax 0xffffffff -1
(gdb)

> joseph speigle <joe(dot)speigle(at)jklh(dot)us> writes:
> > this simple update closes the connection.
>
> My first guess is there's some data corruption in the table or one of
> its indexes. It would be useful to get a stack trace showing where
> in the backend the core dump occurs --- can you manage that?
>
> Other things to check: can you do a "select * from calling" without
> crashing? What shows up in the postmaster log when the crash occurs?
>
> If you just want to fix the database ASAP, you could try a REINDEX
> on the table --- if the problem is in the indexes that should get
> rid of it. But it would also destroy the evidence of exactly what
> went wrong.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
joe speigle