Re: Very strange postgresql behaviour

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

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(at)andromeiberica(dot)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.

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(at)andromeiberica(dot)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

Arnau <arnaulist(at)andromeiberica(dot)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.

Tom Lane wrote:
> Arnau <arnaulist(at)andromeiberica(dot)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

Arnau <arnaulist(at)andromeiberica(dot)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

Hi all,
> Arnau <arnaulist(at)andromeiberica(dot)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