The following bug has been logged online:

Bug reference: 2463
Logged by: Satya Prakash Tripathi
Email address: sptripathi78 (AT) yahoo (DOT) com
PostgreSQL version: 8.1.3
Operating system: Linux
Description: postgres server crashes on "select count(*) from table;"
Details:

postgres server crashes on running "select count(*) from table;" from psql
client

steps to reproduce :

1) create a test table:

CREATE TABLE test_tbl(
a integer,
b integer,
c integer,
d integer,
e integer,
f integer,
g integer,
h integer,
i integer);





2)
run a script to do inserts in this table in a loop.
example script:

-----------------------------------------------------
#set -x
#!/bin/bash
FILE=/tmp/test.log.$$
x=0
y=0
PSQL=`which psql`
while [ 1 ];
do
if [ $x -eq 1000 ]; then
` echo "delete from test_tbl;" | $PSQL -q test_db`
x=0
fi

#echo "loop: $x"
` echo "insert into test_tbl values(1,1,1,1,1,1,1,1,1);" | $PSQL -q
test_db`
x=`expr $x + 1`
y=`expr $y + 1`
echo " no of inserts(y)=$y" > $FILE
done
-----------------------------------------------------




3)

now on psql client, run select command as following:

test_db=# select count(*) from test_tbl;
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
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: Succeeded.



On this, postgres server crashes and terminates psql client connection.

I have seen postgres crashing without running this command too.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"Satya Prakash Tripathi" <sptripathi78 (AT) yahoo (DOT) com> writes:
I don't think so ;-). Perhaps you have a corrupted copy of the postgres
executables?

It's worth noting that this is NOT what you'd see if the select count(*)
itself were failing. Perhaps you have something going on in the
background that is causing a crash of a different query? Look in your
postmaster log to find out what's actually failing, because the above is
merely blowback.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings