Thread: [OT] 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
In response to Arnau <arnaulist@andromeiberica.com>: > > 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. [snip] Just a guess, but perhaps your index is damaged. Have you tried REINDEXing? -- Bill Moran Collaborative Fusion Inc.
Arnau wrote: > Hi all, > > I have postgresql 7.4.2 running on debian and I have the oddest > postgresql behaviour I've ever seen. You should upgrade. The latest 7.2 release is 7.4.15 > I do the following queries: > ... At first glance this looks like a bug in PostgreSQL, but.. > Rules: > > A lot of rules that I don't paste as matter of length. Is there any SELECT rules by chance that might explain this? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Hi Bill,
> In response to Arnau <arnaulist@andromeiberica.com>:
>> 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.
>
> [snip]
>
> Just a guess, but perhaps your index is damaged. Have you tried
> REINDEXing?
>
Yes, I have tried with:
reindex table customer_app_config
reindex index pk_cag_customer_application_id
but nothing changed. I also tried to drop the index:
espsm_asme=# begin; drop index pk_cag_customer_application_id;
BEGIN
ERROR: cannot drop index pk_cag_customer_application_id because
constraint pk_cag_customer_application_id on table customer_app_config
requires it
HINT: You may drop constraint pk_cag_customer_application_id on table
customer_app_config instead.
espsm_asme=# rollback;
ROLLBACK
But I can't remove the constraint as it's the primary key and there
are foreign keys over it
--
Arnau
Arnau wrote: > Hi Bill, > >> In response to Arnau <arnaulist@andromeiberica.com>: >> >>> 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; >> Just wild guessing: is there any chance that there may be some form of "implicit" limit modifier for the select statements on this table? Does the behaviour change if you add "limit 2" at the end of the query? Does it change if you use customer_app_config_id in (5929, 11527) instead? Another wild guess: if the data is somewhat corrupt, maybe a vacuum analyze would detect it? Or perhaps try pg_dumping, to see if pg_dump at some point complains about mssing or corrupt data? HTH, Carlos --