signal 11

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Tanya Mamedalin
Your email address : tmamedalin(at)registrypro(dot)pro

System Configuration
---------------------
Architecture (example: Intel Pentium) : XSER305 INTEL PENTIUM 4
Operating System (example: Linux 2.0.26 ELF) : RedHat 7.3

PostgreSQL version (example: PostgreSQL-7.4): PostgreSQL-7.4

Compiler used (example: gcc 2.95.2) : gcc version 2.96 20000731
(Red Hat Linux 7.3 2.96-110)

Please enter a FULL description of your problem:
------------------------------------------------

I was running the same Python code with Postgres 7.2.3 and psycopg 1.0.9
with no errors. As soon as I upgraded to Postgres 7.4-0.3 and pyscopg 1.1.6
I started getting errors when trying to execute a certain query. The
offending multi-part query is shown below. It seemed to specifically not
like the subquery portion. To circumvent the problem I changed the subquery
from ".pathname = (..subquery...)" to "e.pathname IN (...subquery...)"
suspecting that postgres was not happy with the one-to-one relation even
though the subquery was guaranteed to only return one record.

**---------------BEGIN ERROR----------------------------------------------
Traceback (most recent call last):
File "./auto_escalate.py", line 100, in ?
conn3 = c3.execute(third_query)
psycopg.ProgrammingError: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

select distinct o.outage_id, o.machine, o.service, o.level,
o.comments, o2.time, e.num
from outages o, outages o2, escalation e
where o.outage_id='31009'
and e.pathname=(select pathname from escalation_link el
where el.machine=o.machine and el.service=o.service)
and e.level=o.level and o2.outage_id=o.outage_id
and o2.status in ('ALERT','WARN','CRIT','DOWN','UNKNOWN')
order by level desc limit 1
There was an error connecting to the database
Traceback (most recent call last):
File "./ops2server.py", line 60, in dbopen
db=psycopg.connect(host=HOST,user=username, database=DB,
password=passwd)
OperationalError: FATAL: the database system is starting up
**-----------------END ERROR----------------------------------------------

From the postgres logs I was showing that every time the offending query was
attempted the process would receive a signal 11, quit and restart.

**-----------------BEGIN LOG----------------------------------------------
2004-01-23 10:44:00 LOG: database system is ready
2004-01-23 10:44:20 LOG: connection received: host=[local] port=
2004-01-23 10:44:20 LOG: connection authorized: user=postgres database=ops2
2004-01-23 10:44:45 LOG: server process (PID 18880) was terminated by
signal 11
2004-01-23 10:44:45 LOG: terminating any other active server processes
2004-01-23 10:44:45 LOG: all server processes terminated; reinitializing
2004-01-23 10:44:45 LOG: database system was interrupted at 2004-01-23
10:44:00 EST
2004-01-23 10:44:45 LOG: checkpoint record is at 0/D0337C
2004-01-23 10:44:45 LOG: redo record is at 0/D0337C; undo record is at 0/0;
shutdown TRUE
2004-01-23 10:44:45 LOG: next transaction ID: 1242; next OID: 98209
2004-01-23 10:44:45 LOG: database system was not properly shut down;
automatic recovery in progress
2004-01-23 10:44:45 LOG: connection received: host=[local] port=
2004-01-23 10:44:45 LOG: record with zero length at 0/D033BC
2004-01-23 10:44:45 LOG: redo is not required
2004-01-23 10:44:45 FATAL: the database system is starting up
2004-01-23 10:44:47 LOG: database system is ready
**-----------------END LOG----------------------------------------------

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Possible ways to reproduce the error: Run a multipart query similar to:

SELECT DISTINCT o.outage_id, o.machine, o.service, o.level, o.comments,
o2.time, e.num
FROM outages o, outages o2, escalation e
WHERE o.outage_id='31009'
AND e.pathname = (SELECT pathname from escalation_link el
WHERE el.machine=o.machine AND el.service=o.service)
AND e.level=o.level
AND o2.outage_id=o.outage_id
AND o2.status in ('ALERT','WARN','CRIT','DOWN','UNKNOWN')
ORDER BY LEVEL DESC LIMIT 1;

Thanks,
Tanya Mamedalin