bug ? updates and insert crash the backend - Mailing list pgsql-general

Hi

We're running Postgresql 7.3.2 on Linux boxes, two Debian systems and a
RH one, kernel 2.4.18 and 2.4.9. Recently, we've been experiencing the
following troubles, on every production box : updates and inserts appear
to crash the backend, then the backend restarts after 1-2 seconds.

Here's a psql example :

t2g_1=# explain verbose update tiers set num_siret='abcd' where
num_tiers='33';
message type 0x44 arrived from server while idle
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.

I start the backend using "./postmaster -i -D /var/pgsql/data -d2"
(sorry for huge copy & paste) :

DEBUG:  ./postmaster: PostmasterMain: initial environ dump:
DEBUG:  -----------------------------------------
DEBUG:          MANPATH=:/usr/local/pgsql/man
DEBUG:          SHELL=/bin/sh
DEBUG:          TERM=xterm
DEBUG:          SSH_CLIENT=192.168.0.3 1594 22
DEBUG:          SSH_TTY=/dev/pts/1
DEBUG:          USER=postgres
DEBUG:          REPL_DATABASE=reptest
DEBUG:          PGLIB=/usr/local/pgsql/lib
DEBUG:          PATH=/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games
DEBUG:          MAIL=/var/mail/root
DEBUG:          PWD=/usr/local/pgsql/bin
DEBUG:          SPREAD_NAME=4803@localhost
DEBUG:          SPREAD_GROUP=replicationGroup
DEBUG:          JAVA_HOME=/usr/local/j2sdk1.4.0_03
DEBUG:          LANG=C
DEBUG:          PS1=\h:\w\$
DEBUG:          HOME=/var/lib/postgres
DEBUG:          SHLVL=2
DEBUG:          LOGNAME=root
DEBUG:          SSH_CONNECTION=192.168.0.3 1594 192.168.0.200 22
DEBUG:

CLASSPATH=:.:/usr/local/jdk1.3.1_02/lib/tools.jar:.:/usr/local/jars/axis.jar:.:/usr/local/jars/commons-discovery.jar:.:/usr/local/jars/commons-logging.jar:.:/usr/local/jars/jaxrpc.jar:.:/usr/local/jars/saaj.jar:.:/usr/local/jars/log4j-1.2.4.jar:.:/usr/local/jars/xerces.jar:.:/home/olivier/trucking/postgresql.jar
DEBUG:          PGDATA=/var/pgsql/data
DEBUG:          _=./postmaster
DEBUG:          OLDPWD=/usr/local/pgsql
DEBUG:  -----------------------------------------
DEBUG:  FindExec: found "/usr/local/pgsql/bin/./postgres" using argv[0]
DEBUG:  invoking IpcMemoryCreate(size=1466368)
DEBUG:  FindExec: found "/usr/local/pgsql/bin/./postmaster" using argv[0]
LOG:  database system was shut down at 2003-03-05 15:59:25 GMT
LOG:  checkpoint record is at 0/5780CBC
LOG:  redo record is at 0/5780CBC; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 11880; next oid: 410192
LOG:  database system is ready
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)

When the query gets executed into psql monitor, here's the backend log :
- db connection, with "./psql -U dba t2g_1 "
DEBUG:  BackendStartup: forked pid=17126 socket=8
DEBUG:  ./postmaster child[17126]: starting with (
DEBUG:          postgres
DEBUG:          -d2
DEBUG:          -v131072
DEBUG:          -p
DEBUG:          t2g_1
DEBUG:  )
DEBUG:  InitPostgres
DEBUG:  StartTransactionCommand
LOG:  query: begin; select getdatabaseencoding(); commit
DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  ProcessQuery
DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  query: BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE
usename = 'dba'; COMMIT
DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  ProcessQuery
DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand


- query execution : "explain verbose update tiers set num_siret='abcd'
where num_tiers='33';"

DEBUG:  StartTransactionCommand
LOG:  query: update tiers set num_siret='abcd' where num_tiers='33';
DEBUG:  ProcessQuery
DEBUG:  child process (pid 17126) was terminated by signal 11
LOG:  server process (pid 17126) 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=1466368)
DEBUG:  BackendStartup: forked pid=17128 socket=8
LOG:  database system was interrupted at 2003-03-05 15:59:27 GMT
LOG:  checkpoint record is at 0/5780CBC
LOG:  redo record is at 0/5780CBC; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 11880; next oid: 410192
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  ReadRecord: record with zero length at 0/5780CFC
LOG:  redo is not required
FATAL:  The database system is starting up
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  child process (pid 17128) exited with exit code 0
LOG:  database system is ready
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)

Obviously something gets wrong, and I don't know where or why. I've
recompiled every instance of the 7.3.x branch and the error always
happens :( I had a look at google but didnt fnd much.
If someone wants to help me, I can provide the whole dump of the 't2g_1'
database I am currently using. The dump is ~500KB in size.

Thanks for any support

Laurent Perez