Обсуждение: Bug in 6.4 release
Hi I have the following problem using PostgreSQL 6.4 on RedHat Linux 5.1 on x86 using the following table thplus=> \d envelope Table = envelope +-------------------------+----------------------------------+-------+ | Field | Type | Length| +-------------------------+----------------------------------+-------+ | envelope_id | int4 not null default nextval ( | 4 | | order_type_id | int4 not null | 4 | | envelope_name | varchar() not null | 32 | | signed_string | text | var | | envelope_comment | text | var | | envelope_on_hold | int2 | 2 | | envelope_order_count | int4 | 4 | | envelope_total | int4 | 4 | | envelope_currency | text | var | | envelope_modify_time | datetime | 8 | | state_id | char() | 1 | +-------------------------+----------------------------------+-------+ thplus=> create index envelope_fk2 on envelope(state_id) I try to use the following query thplus=> explain thplus-> select count(*) from envelope where state_id='H' or state_id='E'; NOTICE: QUERY PLAN: Aggregate (cost=4.10 size=0 width=0) -> Index Scan using envelope_fk2 on envelope (cost=4.10 size=1 width=4) EXPLAIN when actually running it, I get the following: thplus=> select count(*) from envelope where state_id='H' or state_id='E'; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally beforeor while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. But the following query runs fine: thplu=> select count(*) from envelope where envelope_id=1 or envelope_id=3; count ----- 2 (1 row) as well as this thplus=> select count(*) from envelope where envelope_id=1 or state_id='E'; count ----- 12 (1 row) and this thplus=> select count(*) from envelope where state_id='H' thplus-> union thplus-> select count(*) from envelope where state_id='E'; count ----- 111140 (2 rows) So it seems that there is a problem with using indexes in ORs that are defined over text types the same crash happened also when using varchar(1) as the type of state_id BTW, it does not happen when the state_id is first field -------------- Hannu
> So it seems that there is a problem with using indexes in ORs that are > defined over text types > > the same crash happened also when using varchar(1) as the type of > state_id > > BTW, it does not happen when the state_id is first field Recreated. I am on it. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Hi
>
> I have the following problem using PostgreSQL 6.4 on RedHat Linux 5.1
> on x86
>
> using the following table
>
> thplus=> \d envelope
>
> Table = envelope
> +-------------------------+----------------------------------+-------+
> | Field | Type | Length|
> +-------------------------+----------------------------------+-------+
> | envelope_id | int4 not null default nextval ( | 4 |
> | order_type_id | int4 not null | 4 |
> | envelope_name | varchar() not null | 32 |
> | signed_string | text | var |
> | envelope_comment | text | var |
> | envelope_on_hold | int2 | 2 |
> | envelope_order_count | int4 | 4 |
> | envelope_total | int4 | 4 |
> | envelope_currency | text | var |
> | envelope_modify_time | datetime | 8 |
> | state_id | char() | 1 |
> +-------------------------+----------------------------------+-------+
>
> thplus=> create index envelope_fk2 on envelope(state_id)
>
> I try to use the following query
>
> thplus=>
> explain
> thplus-> select count(*) from envelope where state_id='H' or
> state_id='E';
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=4.10 size=0 width=0)
> -> Index Scan using envelope_fk2 on envelope (cost=4.10 size=1
> width=4)
>
> EXPLAIN
>
> when actually running it, I get the following:
>
> thplus=> select count(*) from envelope where state_id='H' or
> state_id='E';
> 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.
>
>
> But the following query runs fine:
>
> thplu=> select count(*) from envelope where envelope_id=1 or
> envelope_id=3;
> count
> -----
> 2
> (1 row)
>
> as well as this
>
> thplus=> select count(*) from envelope where envelope_id=1 or
> state_id='E';
> count
> -----
> 12
> (1 row)
>
> and this
>
> thplus=> select count(*) from envelope where state_id='H'
> thplus-> union
> thplus-> select count(*) from envelope where state_id='E';
> count
> -----
> 11
> 1140
> (2 rows)
>
>
> So it seems that there is a problem with using indexes in ORs that are
> defined over text types
>
> the same crash happened also when using varchar(1) as the type of
> state_id
>
> BTW, it does not happen when the state_id is first field
>
> --------------
> Hannu
>
>
I need help with this one. Attached is a patch that also fails, but it
looks closer than the original code. The problem appears to be that I
can't get a slot that matches the items of the Var node I am trying to
evaluate. If I used one that matches the heap tuple, that fails,
because if the index is on the second column of the tuple, the attnum is
1, while it is actually 2nd in the tuple slot.
Does anyone know the executor well enough to find me that slot that
matches the Var node? I can't figure it out.
---------------------------------------------------------------------------
*** ./backend/executor/nodeIndexscan.c.orig Fri Nov 20 11:38:27 1998
--- ./backend/executor/nodeIndexscan.c Fri Nov 20 13:25:46 1998
***************
*** 153,161 **** for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
prev_index++) {
- scanstate->cstate.cs_ExprContext->ecxt_scantuple = slot; if
(ExecQual(nth(prev_index,node->indxqual),
! scanstate->cstate.cs_ExprContext)) {
prev_matches= true; break;
--- 153,160 ---- for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
prev_index++) { if (ExecQual(nth(prev_index, node->indxqual),
! node->scan.scanstate->cstate.cs_ExprContext)) {
prev_matches = true; break;
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
> Hi
>
> I have the following problem using PostgreSQL 6.4 on RedHat Linux 5.1
> on x86
>
> using the following table
>
> thplus=> \d envelope
>
> Table = envelope
> +-------------------------+----------------------------------+-------+
> | Field | Type | Length|
> +-------------------------+----------------------------------+-------+
> | envelope_id | int4 not null default nextval ( | 4 |
> | order_type_id | int4 not null | 4 |
> | envelope_name | varchar() not null | 32 |
> | signed_string | text | var |
> | envelope_comment | text | var |
> | envelope_on_hold | int2 | 2 |
> | envelope_order_count | int4 | 4 |
> | envelope_total | int4 | 4 |
> | envelope_currency | text | var |
> | envelope_modify_time | datetime | 8 |
> | state_id | char() | 1 |
> +-------------------------+----------------------------------+-------+
>
> thplus=> create index envelope_fk2 on envelope(state_id)
>
> I try to use the following query
>
> thplus=>
> explain
> thplus-> select count(*) from envelope where state_id='H' or
> state_id='E';
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=4.10 size=0 width=0)
> -> Index Scan using envelope_fk2 on envelope (cost=4.10 size=1
> width=4)
>
> EXPLAIN
>
> when actually running it, I get the following:
>
> thplus=> select count(*) from envelope where state_id='H' or
> state_id='E';
> 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.
>
>
> But the following query runs fine:
>
> thplu=> select count(*) from envelope where envelope_id=1 or
> envelope_id=3;
> count
> -----
> 2
> (1 row)
>
> as well as this
>
> thplus=> select count(*) from envelope where envelope_id=1 or
> state_id='E';
> count
> -----
> 12
> (1 row)
>
> and this
>
> thplus=> select count(*) from envelope where state_id='H'
> thplus-> union
> thplus-> select count(*) from envelope where state_id='E';
> count
> -----
> 11
> 1140
> (2 rows)
>
>
> So it seems that there is a problem with using indexes in ORs that are
> defined over text types
>
> the same crash happened also when using varchar(1) as the type of
> state_id
>
> BTW, it does not happen when the state_id is first field
>
> --------------
> Hannu
>
>
I need help with this one. Attached is a patch that also fails, but it
looks closer than the original code. The problem appears to be that I
can't get a slot that matches the items of the Var node I am trying to
evaluate. If I used one that matches the heap tuple, that fails,
because if the index is on the second column of the tuple, the attnum is
1, while it is actually 2nd in the tuple slot.
Does anyone know the executor well enough to find me that slot that
matches the Var node? I can't figure it out.
---------------------------------------------------------------------------
*** ./backend/executor/nodeIndexscan.c.orig Fri Nov 20 11:38:27 1998
--- ./backend/executor/nodeIndexscan.c Fri Nov 20 13:25:46 1998
***************
*** 153,161 **** for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
prev_index++) {
- scanstate->cstate.cs_ExprContext->ecxt_scantuple = slot; if
(ExecQual(nth(prev_index,node->indxqual),
! scanstate->cstate.cs_ExprContext)) {
prev_matches= true; break;
--- 153,160 ---- for (prev_index = 0; prev_index < indexstate->iss_IndexPtr;
prev_index++) { if (ExecQual(nth(prev_index, node->indxqual),
! node->scan.scanstate->cstate.cs_ExprContext)) {
prev_matches = true; break;
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026