Re: simple update closes connection. why? - Mailing list pgsql-novice

tom,

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

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.speigle@jklh.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