Re: [HACKERS] query crashes backend - cvs - Mailing list pgsql-hackers

> 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.        |