Skip site navigation (1) Skip section navigation (2)

Database in recovery mode

From: "Michael Richards" <michael(at)fastmail(dot)ca>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Database in recovery mode
Date: 2000-08-06 16:38:04
Message-ID: 398D946C.000085.12224@frodo.searchcanada.ca (view raw or flat)
Thread:
Lists: pgsql-bugs
Okay. I've narrowed it down just a little more.

If I start up postgres and run the query that caused it to crash 
before, it will crash again iff it is the first query I run.

Even the slightest change in that query seems to stop it from 
crashing. The original query was and should have returned:
equipment=> SELECT 
year,manufacturer,model,stocknumber,quantity,realprice,province,countr
y,id,pricecurrency FROM ad_trucks AS AD WHERE active='t' AND cat1=9 
AND cat2=4576 AND UPPER(manufacturer) LIKE '%KENWORTH%' AND year 
BETWEEN 1997 AND 2000;
 year | manufacturer |     model     | stocknumber | quantity | 
realprice | province | country |  id   | pricecurrency 
------+--------------+---------------+-------------+----------+-------
----+----------+---------+-------+---------------
 1998 | Kenworth     | T2000         |             |        1 
|           | Manitoba | Canada  | 31990 | CAD
 1999 | Kenworth     | W900B         |             |        1 
|           | Manitoba | Canada  | 31991 | CAD
 1998 | Kenworth     | W-900 Damaged |             |        1 
|           | Missouri | USA     | 16535 | USD
 1998 | Kenworth     | W900L         | 755934      |        1 
|           | Indiana  | USA     | 16496 | USD
 1997 | Kenworth     | W900L         | 742184      |        1 
|           | Indiana  | USA     | 23936 | USD
 1997 | Kenworth     | T600          |             |        1 
|           | Manitoba | Canada  | 31989 | CAD
 1997 | Kenworth     | T600          | #050520-97  |        1 |  
47500.00 | Quebec   | Canada  | 32675 | USD
 1999 | Kenworth     | T600          | 99130       |        1 |  
79500.00 | Ontario  | Canada  | 32829 | CAD
 1998 | Kenworth     | W900L         | 758000      |        1 
|           | Indiana  | USA     | 22370 | USD
 1997 | Kenworth     | W900L         | #170420     |        3 
|           | Quebec   | Canada  | 32215 | USD
 1997 | Kenworth     | T800          | #01502-15   |        5 |  
34500.00 | Quebec   | Canada  | 24482 | USD
 1997 | Kenworth     | W900L         | 2-00-54/56  |        2 |  
51950.00 | Ontario  | Canada  | 32620 | USD
 1997 | Kenworth     | W900L         | fmdc        |        1 
|           | Ontario  | Canada  | 31932 | CAD
 2000 | Kenworth     | T800          |             |        1 
|           | Texas    | USA     | 33111 | USD
(14 rows)

If I replace the column list with a *, it runs fine. Any number of 
times I run it within that session of pgsql the original query runs 
fine.

Here is the query with a fresh postgres:
/usr/local/pgsql/bin/psql -U equipment
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

equipment=> SELECT 
year,manufacturer,model,stocknumber,quantity,realprice,province,countr
y,id,pricecurrency FROM ad_trucks AS AD WHERE active='t' AND cat1=9 
AND cat2=4576 AND UPPER(manufacturer) LIKE '%KENWORTH%' AND year 
BETWEEN 1997 AND 2000;
pqReadData() -- backend closed the channel unexpectedly.
 This probably means the backend terminated abnormally
 before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Poof! It explodes.

Running a:
Select * from ad_trucks; before running the query causes the query to 
succeed. 

Having found that... I don't know where to go from here...

-Michael
_________________________________________________________________
     http://fastmail.ca/ - Fast Free Web Email for Canadians

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2000-08-06 22:30:37
Subject: Re: Database in recovery mode
Previous:From: Michael RichardsDate: 2000-08-06 16:19:19
Subject: Database in recovery mode

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group