query crashes backend - cvs

This query crashes the backend too.

template1=> select @1;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.

With 6.3.2 the same query produces:

test=> select @1;
ERROR: Can't find left op: @ for type 23
--
Tatsuo Ishii
t-ishii@sra.co.jp

select * from work_flats where (METRO_ID = 81 or METRO_ID = 82) and
DISTRICT_ID < 11;
crashes backend
pqReadData() -- backend closed the channel unexpectedly.
while
select * from work_flats where METRO_ID = 81 or METRO_ID = 82 and
DISTRICT_ID < 11;
works ok.

This happens with latest cvs, 6.3.2 has no problem.

postgres=> select * from t where (METRO_ID = 81 or METRO_ID = 82) and
postgres-> DISTRICT_ID < 11;
metro_id|district_id
--------+-----------
81| 1
82| 2
(2 rows)

More details please...

- Tom

Ok,
create table t0 ( a_id int4 NOT NULL, a varchar(10), a_t1_id int4);
create index a_id_t0 on t0 (a_id);
create index a_t1_id_t0 on t0 (a_t1_id);
COPY t0 FROM STDIN USING DELIMITERS '|';
1|at0|0
2|at0|0
\.

test=> \d t0

Table    = t0
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| a_id                             | int4 not null                    |     4 |
| a                                | varchar()                        |    10 |
| a_t1_id                          | int4                             |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  a_id_t0
          a_t1_id_t0
test=> select * from t0 where a_id = 1 or a_id = 2 and a_t1_id  < 1;
a_id|a  |a_t1_id
----+---+-------
   1|at0|      0
   2|at0|      0
(2 rows)

test=> select * from t0 where (a_id = 1 or a_id = 2) and a_t1_id < 1;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
This is Linux box, running postgres from cvs
Linux om 2.1.117 #2 Tue Aug 25 21:05:29 MSD 1998 i586
Kernel modules 2.1.121
Gnu C egcs-2.91.57
Binutils 2.9.1.0.7
Linux C Library 5.4.46
Dynamic Linker (ld.so) 1.9.9
Linux C++ Library 27.1.4
Procps 1.2.7
Mount 2.8a
Net-tools (1998-03-02)
Kbd
Sh-utils 1.16
This happens also on another Linux box:
Linux dv 2.0.34 #7 Wed Aug 26 14:05:30 MSD 1998 i586 unknown
Kernel modules 2.1.71
Gnu C 2.7.2.3
Binutils 2.8.1.0.23
Linux C Library 5.4.44
Dynamic Linker (ld.so) 1.9.7
Linux C++ Library 27.2.8
Procps 1.2.6
Procinfo 12
Mount 2.7l
Net-tools 1.432.
Kbd 0.94
Sh-utils 1.16

Again, 6.3.2 works fine.
Also, cvs has a problem with vacuum analyze

test=> vacuum analyze t0;
ERROR: cannot write block -1 of [] blind
test=> \q
11:53[om]:~/bin>psql test
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: test

test=> \d t0
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.

I run postmaster as 'postmaster -i -S -D/usr/local/pgsql/data/ -o -Fe'
(note, no -B 1024 now, because number of buffers > 128 causes
vacuum analyze crash also, I've already posted several day s ago)

Regards,
Oleg

On Wed, 16 Sep 1998, Thomas G. Lockhart wrote:

Date: Wed, 16 Sep 1998 05:14:13 +0000
From: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] query crashes backend - cvs

select * from work_flats where (METRO_ID = 81 or METRO_ID = 82) and
DISTRICT_ID < 11;
crashes backend
pqReadData() -- backend closed the channel unexpectedly.
while
select * from work_flats where METRO_ID = 81 or METRO_ID = 82 and
DISTRICT_ID < 11;
works ok.

This happens with latest cvs, 6.3.2 has no problem.

postgres=> select * from t where (METRO_ID = 81 or METRO_ID = 82) and
postgres-> DISTRICT_ID < 11;
metro_id|district_id
--------+-----------
81| 1
82| 2
(2 rows)

More details please...

- Tom

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

interesting that
select * from work_flats where (METRO_ID = 81 or METRO_ID = 82) and DISTRICT_ID < 11;
crashes backend
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
while
select * from work_flats where METRO_ID = 81 or METRO_ID = 82 and DISTRICT_ID < 11;
works ok.

This happens with latest cvs, 6.3.2 has no problem.

I can't reproduce this. Can you send a reproducable example with data,
or tell me what columns have indexes.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

interesting that
select * from work_flats where (METRO_ID = 81 or METRO_ID = 82) and DISTRICT_ID < 11;
crashes backend
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.
while
select * from work_flats where METRO_ID = 81 or METRO_ID = 82 and DISTRICT_ID < 11;
works ok.

This happens with latest cvs, 6.3.2 has no problem.

Never mind. I have a reproducable example now.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |

Bruce,

after sending you email I made an experiment to verify if locale
settings effect to failures of some regression tests.
I did a clean recompilation of latest cvs with --enable-locale option.
First time I run regression test strictly follow instructions in
INSTALL document. Almost all tests run ok except geometry and random.
Second time I start postmster as I usually run on production machine -
i.e. I specified LC_CTYPE and LC_COLLATE env. variables ( koi8-r).
Again geometry fails but random ok (strange) and
select_implicit .. failed
select_having .. failed
select_views .. failed

So, certainly something either in locale code or in my system locale is broken.
But as far as I remember there were no problem with 6.3.2 and there is
nothing locale specific ( I mean national characters) in regression test.
So, most probable there is a bug in a code.
Differences are only in the order of rows returned from query,
for example:
dv:~/cvs/pgsql/src/test/regress/results$ diff select_having.out ../expected/select_having.out
33d32
< bbbb | 5
34a34

bbbb | 5

I run all these tests on regular Linux box, nothing bleeding edge -
Linux dv 2.0.34 #7 Wed Aug 26 14:05:30 MSD 1998 i586 unknown
Kernel modules found
Gnu C 2.7.2.3
Binutils 2.8.1.0.23
Linux C Library 5.4.44
Dynamic Linker (ld.so) 1.9.7
Linux C++ Library 27.2.8
Procps 1.2.6
Procinfo 12
Mount 2.7l
Net-tools 1.432.
Kbd 0.94
Sh-utils 1.16

It would be great if other linux users could verify my results.

Best regards,

Oleg

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Ok. I've already sent perl-script generated test table.
Here are sql commands:

drop table t0;
create table t0 ( a_id int4 NOT NULL, a varchar(10), a_t1_id int4, a_t2_id int4);
create index a_id_t0 on t0 (a_id);
create index a_t1_id_t0 on t0 (a_t1_id);
create index a_t2_id_t0 on t0 (a_t2_id);
COPY t0 FROM STDIN USING DELIMITERS '|';
1|at0|1|1
2|at0|2|2
\.

5:35[dv]:~>psql test
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: test

test=> \d t0

Table    = t0
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| a_id                             | int4 not null                    |     4 |
| a                                | varchar()                        |    10 |
| a_t1_id                          | int4                             |     4 |
| a_t2_id                          | int4                             |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  a_id_t0
a_t1_id_t0
a_t2_id_t0
test=> explain select * from t0 where a_id=1 or a_id=2 and a_t1_id > 2;
NOTICE:  QUERY PLAN:

Index Scan using a_id_t0 on t0 (cost=0.00 size=0 width=24)

EXPLAIN
test=> explain select * from t0 where (a_id=1 or a_id=2) and a_t1_id > 2;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while processing the request.
We have lost the connection to the backend, so further processing is impossible. Terminating.

Notice, that
test=> explain select * from t0 where (a_id=1 and a_id=2) and a_t1_id > 2;
NOTICE: QUERY PLAN:

Index Scan using a_id_t0 on t0 (cost=0.00 size=0 width=24)

EXPLAIN

works also fine.
Another problem with 'vacuum analyze':
works if I run postmaster -i -S -D/usr/local/pgsql/data/ -o '-Fe'
vacuum analyze works as expected:
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: regression

regression=> vacuum analyze;
VACUUM
regression=>

but when I start postmaster with -B 1024 it fails

dv:~$ psql regression
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: regression

regression=> vacuum analyze;
NOTICE: AbortTransaction and not in in-progress state
NOTICE: AbortTransaction and not in in-progress state
regression=>

These problem I experience on several Linux boxes with different
compilers, kernels but with 6.4. In all cases 6.3.2 works ok !

Also It seems that some locale code is broken:
select_implicit and select_having tests produce different order of rows
in results, for example:
dv:~/cvs/pgsql/src/test/regress/results$ diff select_having.out ../expected/select_having.out
33d32
< bbbb | 5
34a34

bbbb | 5

There are also couple of strange things happens with 6.4 on my home machine
which running bleeding edge egcs 1.1b and linux 2.1.122 but I'm
not ready to give you some results.

Best regards,

Oleg

I am working on the first bug you list now. Keep in mind, both cases
are using new 6.4 features, indexing of OR's and HAVING.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
http://www.op.net/~candle              |  (610) 353-9879(w)
  +  If your life is a hard drive,     |  (610) 853-3000(h)
  +  Christ can be your backup.        |