Обсуждение: Very strange postgresql behaviour

Поиск
Список
Период
Сортировка

Very strange postgresql behaviour

От
Arnau
Дата:
Hi all,

   I have postgresql 7.4.2 running on debian and I have the oddest
postgresql behaviour I've ever seen.

I do the following queries:


espsm_asme=# select customer_app_config_id, customer_app_config_name
from customer_app_config where customer_app_config_id = 5929 or
customer_app_config_id = 11527 order by customer_app_config_id;


  customer_app_config_id | customer_app_config_name
------------------------+--------------------------
                    5929 | INFO
(1 row)


   I do the same query but changing the order of the or conditions:


espsm_asme=# select customer_app_config_id, customer_app_config_name
from customer_app_config where customer_app_config_id = 11527 or
customer_app_config_id = 5929 order by customer_app_config_id;


  customer_app_config_id | customer_app_config_name
------------------------+--------------------------
                   11527 | MOVIDOSERENA TONI 5523
(1 row)



   As you can see, the configuration 5929 and 11527 both exists, but
when I do the queries they don't appear.

   Here below you have the execution plans. Those queries use an index,
I have done reindex table customer_app_config but nothing has changed.

espsm_asme=# explain analyze select customer_app_config_id,
customer_app_config_name from customer_app_config where
customer_app_config_id = 11527 or customer_app_config_id = 5929 order by
customer_app_config_id;

                   QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=10.28..10.29 rows=2 width=28) (actual time=0.252..0.253
rows=1 loops=1)
    Sort Key: customer_app_config_id
    ->  Index Scan using pk_cag_customer_application_id,
pk_cag_customer_application_id on customer_app_config  (cost=0.00..10.27
rows=2 width=28) (actual time=0.168..0.232 rows=1 loops=1)
          Index Cond: ((customer_app_config_id = 11527::numeric) OR
(customer_app_config_id = 5929::numeric))
  Total runtime: 0.305 ms
(5 rows)

espsm_asme=# explain analyze select customer_app_config_id,
customer_app_config_name from customer_app_config where
customer_app_config_id = 5929 or customer_app_config_id = 11527 order by
customer_app_config_id;

                   QUERY PLAN


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=10.28..10.29 rows=2 width=28) (actual time=0.063..0.064
rows=1 loops=1)
    Sort Key: customer_app_config_id
    ->  Index Scan using pk_cag_customer_application_id,
pk_cag_customer_application_id on customer_app_config  (cost=0.00..10.27
rows=2 width=28) (actual time=0.034..0.053 rows=1 loops=1)
          Index Cond: ((customer_app_config_id = 5929::numeric) OR
(customer_app_config_id = 11527::numeric))
  Total runtime: 0.114 ms
(5 rows)

   The table definition is the following:

espsm_asme=# \d customer_app_config
                   Table "public.customer_app_config"
           Column          |         Type          |     Modifiers
--------------------------+-----------------------+--------------------
  customer_app_config_id   | numeric(10,0)         | not null
  customer_app_config_name | character varying(32) | not null
  keyword                  | character varying(43) |
  application_id           | numeric(10,0)         | not null
  customer_id              | numeric(10,0)         | not null
  customer_app_contents_id | numeric(10,0)         |
  number_access_id         | numeric(10,0)         |
  prefix                   | character varying(10) |
  separator                | numeric(1,0)          | default 0
  on_hold                  | numeric(1,0)          | not null default 0
  with_toss                | numeric(1,0)          | not null default 0
  number_id                | numeric(10,0)         |
  param_separator_id       | numeric(4,0)          | default 1
  memory_timeout           | integer               |
  with_memory              | numeric(1,0)          | default 0
  session_enabled          | numeric(1,0)          | default 0
  session_timeout          | integer               |
  number                   | character varying(15) |
Indexes:
     "pk_cag_customer_application_id" primary key, btree
(customer_app_config_id)
     "un_cag_kwordnumber" unique, btree (keyword, number_id)
     "idx_cappconfig_ccontentsid" btree (customer_app_contents_id)
     "idx_cappconfig_cusidappid" btree (customer_id, application_id)
     "idx_cappconfig_customerid" btree (customer_id)
     "idx_cappconfig_onhold" btree (on_hold)
     "idx_cappconfig_onholdkeyw" btree (on_hold, keyword)
Rules:

   A lot of rules that I don't paste as matter of length.


   Do you have any idea about how I can fix this?

--
Arnau

Re: Very strange postgresql behaviour

От
Andrew Edson
Дата:
Arnau,
I'm not sure about this for 7.4.2, but I'm running 8.1.3 and when I ran into a problem like that (having to select two distinct options) the solution that worked for me was to put the entire or statement within parentheses.  In your case, that would be this:
 
espsm_asme=# select customer_app_config_id, customer_app_config_name
from customer_app_config where (customer_app_config_id = 5929 or
customer_app_config_id = 11527) order by customer_app_config_id;
 
I hope that helps.


Arnau <arnaulist@andromeiberica.com> wrote:
Hi all,

I have postgresql 7.4.2 running on debian and I have the oddest
postgresql behaviour I've ever seen.

I do the following queries:


espsm_asme=# select customer_app_config_id, customer_app_config_name
from customer_app_config where customer_app_config_id = 5929 or
customer_app_config_id = 11527 order by customer_app_config_id;


customer_app_config_id | customer_app_config_name
------------------------+--------------------------
5929 | INFO
(1 row)


I do the same query but changing the order of the or conditions:


espsm_asme=# select customer_app_config_id, customer_app_config_name
from customer_app_config where customer_app_config_id = 11527 or
customer_app_config_id = 5929 order by customer_app_config_id;


customer_app_config_id | customer_app_config_name
------------------------+--------------------------
11527 | MOVIDOSERENA TONI 5523
(1 row)



As you can see, the configuration 5929 and 11527 both exists, but
when I do the queries they don't appear.

Here below you have the execution plans. Those queries use an index,
I have done reindex table customer_app_config but nothing has changed.

espsm_asme=# explain analyze select customer_app_config_id,
customer_app_config_name from customer_app_config where
customer_app_config_id = 11527 or customer_app_config_id = 5929 order by
customer_app_config_id;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.252..0.253
rows=1 loops=1)
Sort Key: customer_app_config_id
-> Index Scan using pk_cag_customer_application_id,
pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27
rows=2 width=28) (actual time=0.168..0.232 rows=1 loops=1)
Index Cond: ((customer_app_config_id = 11527::numeric) OR
(customer_app_config_id = 5929::numeric))
Total runtime: 0.305 ms
(5 rows)

espsm_asme=# explain analyze select customer_app_config_id,
customer_app_config_name from customer_app_config where
customer_app_config_id = 5929 or customer_app_config_id = 11527 order by
customer_app_config_id;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.063..0.064
rows=1 loops=1)
Sort Key: customer_app_config_id
-> Index Scan using pk_cag_customer_application_id,
pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27
rows=2 width=28) (actual time=0.034..0.053 rows=1 loops=1)
Index Cond: ((customer_app_config_id = 5929::numeric) OR
(customer_app_config_id = 11527::numeric))
Total runtime: 0.114 ms
(5 rows)

The table definition is the following:

espsm_asme=# \d customer_app_config
Table "public.customer_app_config"
Column | Type | Modifiers
--------------------------+-----------------------+--------------------
customer_app_config_id | numeric(10,0) | not null
customer_app_config_name | character varying(32) | not null
keyword | character varying(43) |
application_id | numeric(10,0) | not null
customer_id | numeric(10,0) | not null
customer_app_contents_id | numeric(10,0) |
number_access_id | numeric(10,0) |
prefix | character varying(10) |
separator | numeric(1,0) | default 0
on_hold | numeric(1,0) | not null default 0
with_toss | numeric(1,0) | not null default 0
number_id | numeric(10,0) |
param_separator_id | numeric(4,0) | default 1
memory_timeout | integer |
with_memory | numeric(1,0) | default 0
session_enabled | numeric(1,0) | default 0
session_timeout | integer |
number | character varying(15) |
Indexes:
"pk_cag_customer_application_id" primary key, btree
(customer_app_config_id)
"un_cag_kwordnumber" unique, btree (keyword, number_id)
"idx_cappconfig_ccontentsid" btree (customer_app_contents_id)
"idx_cappconfig_cusidappid" btree (customer_id, application_id)
"idx_cappconfig_customerid" btree (customer_id)
"idx_cappconfig_onhold" btree (on_hold)
"idx_cappconfig_onholdkeyw" btree (on_hold, keyword)
Rules:

A lot of rules that I don't paste as matter of length.


Do you have any idea about how I can fix this?

--
Arnau

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.

Re: Very strange postgresql behaviour

От
Arnau
Дата:
Hi Andrew,

> I'm not sure about this for 7.4.2, but I'm running 8.1.3 and when I ran
> into a problem like that (having to select two distinct options) the
> solution that worked for me was to put the entire or statement within
> parentheses.  In your case, that would be this:
>
> espsm_asme=# select customer_app_config_id, customer_app_config_name
> from customer_app_config where (customer_app_config_id = 5929 or
> customer_app_config_id = 11527) order by customer_app_config_id;

  The result is the same, in fact, the original query was
select customer_app_config_id, customer_app_config_name
from customer_app_config where customer_app_config_id in ( 5929, 11527)
  order by customer_app_config_id;

   Any other idea?

>
> I hope that helps.
>
>
> */Arnau <arnaulist@andromeiberica.com>/* wrote:
>
>     Hi all,
>
>     I have postgresql 7.4.2 running on debian and I have the oddest
>     postgresql behaviour I've ever seen.
>
>     I do the following queries:
>
>
>     espsm_asme=# select customer_app_config_id, customer_app_config_name
>     from customer_app_config where customer_app_config_id = 5929 or
>     customer_app_config_id = 11527 order by customer_app_config_id;
>
>
>     customer_app_config_id | customer_app_config_name
>     ------------------------+--------------------------
>     5929 | INFO
>     (1 row)
>
>
>     I do the same query but changing the order of the or conditions:
>
>
>     espsm_asme=# select customer_app_config_id, customer_app_config_name
>     from customer_app_config where customer_app_config_id = 11527 or
>     customer_app_config_id = 5929 order by customer_app_config_id;
>
>
>     customer_app_config_id | customer_app_config_name
>     ------------------------+--------------------------
>     11527 | MOVIDOSERENA TONI 5523
>     (1 row)
>
>
>
>     As you can see, the configuration 5929 and 11527 both exists, but
>     when I do the queries they don't appear.
>
>     Here below you have the execution plans. Those queries use an index,
>     I have done reindex table customer_app_config but nothing has changed.
>
>     espsm_asme=# explain analyze select customer_app_config_id,
>     customer_app_config_name from customer_app_config where
>     customer_app_config_id = 11527 or customer_app_config_id = 5929
>     order by
>     customer_app_config_id;
>
>     QUERY PLAN
>
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>     Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.252..0.253
>     rows=1 loops=1)
>     Sort Key: customer_app_config_id
>     -> Index Scan using pk_cag_customer_application_id,
>     pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27
>     rows=2 width=28) (actual time=0.168..0.232 rows=1 loops=1)
>     Index Cond: ((customer_app_config_id = 11527::numeric) OR
>     (customer_app_config_id = 5929::numeric))
>     Total runtime: 0.305 ms
>     (5 rows)
>
>     espsm_asme=# explain analyze select customer_app_config_id,
>     customer_app_config_name from customer_app_config where
>     customer_app_config_id = 5929 or customer_app_config_id = 11527
>     order by
>     customer_app_config_id;
>
>     QUERY PLAN
>
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>     Sort (cost=10.28..10.29 rows=2 width=28) (actual time=0.063..0.064
>     rows=1 loops=1)
>     Sort Key: customer_app_config_id
>     -> Index Scan using pk_cag_customer_application_id,
>     pk_cag_customer_application_id on customer_app_config (cost=0.00..10.27
>     rows=2 width=28) (actual time=0.034..0.053 rows=1 loops=1)
>     Index Cond: ((customer_app_config_id = 5929::numeric) OR
>     (customer_app_config_id = 11527::numeric))
>     Total runtime: 0.114 ms
>     (5 rows)
>
>     The table definition is the following:
>
>     espsm_asme=# \d customer_app_config
>     Table "public.customer_app_config"
>     Column | Type | Modifiers
>     --------------------------+-----------------------+--------------------
>     customer_app_config_id | numeric(10,0) | not null
>     customer_app_config_name | character varying(32) | not null
>     keyword | character varying(43) |
>     application_id | numeric(10,0) | not null
>     customer_id | numeric(10,0) | not null
>     customer_app_contents_id | numeric(10,0) |
>     number_access_id | numeric(10,0) |
>     prefix | character varying(10) |
>     separator | numeric(1,0) | default 0
>     on_hold | numeric(1,0) | not null default 0
>     with_toss | numeric(1,0) | not null default 0
>     number_id | numeric(10,0) |
>     param_separator_id | numeric(4,0) | default 1
>     memory_timeout | integer |
>     with_memory | numeric(1,0) | default 0
>     session_enabled | numeric(1,0) | default 0
>     session_timeout | integer |
>     number | character varying(15) |
>     Indexes:
>     "pk_cag_customer_application_id" primary key, btree
>     (customer_app_config_id)
>     "un_cag_kwordnumber" unique, btree (keyword, number_id)
>     "idx_cappconfig_ccontentsid" btree (customer_app_contents_id)
>     "idx_cappconfig_cusidappid" btree (customer_id, application_id)
>     "idx_cappconfig_customerid" btree (customer_id)
>     "idx_cappconfig_onhold" btree (on_hold)
>     "idx_cappconfig_onholdkeyw" btree (on_hold, keyword)
>     Rules:
>
>     A lot of rules that I don't paste as matter of length.
>
>
>     Do you have any idea about how I can fix this?
>
>     --
>     Arnau
>
>     ---------------------------(end of broadcast)---------------------------
>     TIP 4: Have you searched our list archives?
>
>     http://archives.postgresql.org
>
>
> ------------------------------------------------------------------------
> Looking for earth-friendly autos?
> Browse Top Cars by "Green Rating"
> <http://autos.yahoo.com/green_center/;_ylc=X3oDMTE4MGw4Z2hlBF9TAzk3MTA3MDc2BHNlYwNtYWlsdGFncwRzbGsDZ3JlZW5jZW50ZXI->
> at Yahoo! Autos' Green Center.


--
Arnau

Re: Very strange postgresql behaviour

От
Tom Lane
Дата:
Arnau <arnaulist@andromeiberica.com> writes:
>    I have postgresql 7.4.2 running on debian and I have the oddest
> postgresql behaviour I've ever seen.

Is this specific to these two rows?  If so it might be a case of this
bug, which was repaired in 7.4.13:

http://archives.postgresql.org/pgsql-general/2006-05/msg00756.php
http://archives.postgresql.org/pgsql-committers/2006-05/msg00174.php

2006-05-19 12:31  tgl

    * src/backend/executor/nodeIndexscan.c (REL7_4_STABLE): Fix nasty
    bug in nodeIndexscan.c's detection of duplicate tuples during a
    multiple (OR'ed) indexscan.  It was checking for duplicate
    tuple->t_data->t_ctid, when what it should be checking is
    tuple->t_self.    The trouble situation occurs when a live tuple has
    t_ctid not pointing to itself, which can happen if an attempted
    UPDATE was rolled back.  After a VACUUM, an unrelated tuple could
    be installed where the failed update tuple was, leading to one live
    tuple's t_ctid pointing to an unrelated tuple.    If one of these
    tuples is fetched by an earlier OR'ed indexscan and the other by a
    later indexscan, nodeIndexscan.c would incorrectly ignore the
    second tuple.  The bug exists in all 7.4.* and 8.0.* versions, but
    not in earlier or later branches because this code was only used in
    those releases.  Per trouble report from Rafael Martinez Guerrero.

REINDEX wouldn't fix this, although a table dump and reload would.

            regards, tom lane

PS: please don't spam multiple lists with the same question.

Re: Very strange postgresql behaviour

От
Arnau
Дата:
Tom Lane wrote:
> Arnau <arnaulist@andromeiberica.com> writes:
>>    I have postgresql 7.4.2 running on debian and I have the oddest
>> postgresql behaviour I've ever seen.
>
> Is this specific to these two rows?  If so it might be a case of this
> bug, which was repaired in 7.4.13:

   I don't know, we have discovered those two rows but I'm not sure if
there are more. Is there any way to check it?

>
> http://archives.postgresql.org/pgsql-general/2006-05/msg00756.php
> http://archives.postgresql.org/pgsql-committers/2006-05/msg00174.php
>
> 2006-05-19 12:31  tgl
>
>     * src/backend/executor/nodeIndexscan.c (REL7_4_STABLE): Fix nasty
>     bug in nodeIndexscan.c's detection of duplicate tuples during a
>     multiple (OR'ed) indexscan.  It was checking for duplicate
>     tuple->t_data->t_ctid, when what it should be checking is
>     tuple->t_self.    The trouble situation occurs when a live tuple has
>     t_ctid not pointing to itself, which can happen if an attempted
>     UPDATE was rolled back.  After a VACUUM, an unrelated tuple could
>     be installed where the failed update tuple was, leading to one live
>     tuple's t_ctid pointing to an unrelated tuple.    If one of these
>     tuples is fetched by an earlier OR'ed indexscan and the other by a
>     later indexscan, nodeIndexscan.c would incorrectly ignore the
>     second tuple.  The bug exists in all 7.4.* and 8.0.* versions, but
>     not in earlier or later branches because this code was only used in
>     those releases.  Per trouble report from Rafael Martinez Guerrero.
>
> REINDEX wouldn't fix this, although a table dump and reload would.
>
>             regards, tom lane
>
> PS: please don't spam multiple lists with the same question.

   My intention was not spam mulitple lists, the problem was that I was
not sure where to post this question.


--
Arnau

Re: Very strange postgresql behaviour

От
Tom Lane
Дата:
Arnau <arnaulist@andromeiberica.com> writes:
>    I don't know, we have discovered those two rows but I'm not sure if
> there are more. Is there any way to check it?

Update and see if the problem is gone ...

            regards, tom lane

Re: Very strange postgresql behaviour

От
Arnau
Дата:
Hi all,
> Arnau <arnaulist@andromeiberica.com> writes:
>>    I don't know, we have discovered those two rows but I'm not sure if
>> there are more. Is there any way to check it?
>
> Update and see if the problem is gone ...

   We have done:

  update customer_app_config set customer_app_config_id =
customer_app_config_id

   And the problem has gone.

Thank you very much!!!
--
Arnau