Thread
-
Database in recovery mode
Michael Richards <michael@fastmail.ca> — 2000-08-06T16:38:04Z
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 -
Re: Database in recovery mode
Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-06T22:30:37Z
"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