Обсуждение: Database in recovery mode

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

"Michael Richards" <michael@fastmail.ca> writes:
> 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;

This sounds suspiciously like you've found another way to produce an
occurrence of the LIKE-input-is-smack-against-the-end-of-memory bug
that someone else reported a month ago.  The fix for this is in CVS
already (I mailed it under separate cover).

Now I'd have thought that problem was so unlikely as to never be seen in
practice --- so although this one particular crash might be explained
that way, you should probably keep alert for the possibility that there
are more problems biting you than just this one.

            regards, tom lane