signal 11 - Mailing list pgsql-bugs

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


Your name               : Tanya Mamedalin
Your email address      : tmamedalin@registrypro.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