I had a fun issue this morning, and now I'm wondering if I violated a
rule I didn't know about. Any insight would be much appreciated. All
operations involving this particular table may be assumed to have
functioned normally before today.
bash-2.05a$ cat PG_VERSION
7.3
Last night, we added 'index_t_payment_param' to this table. payment_id
is not a unique entry, hence the composite primary key, and I've since
been told that the additional index is likely redundant?
\d t_payment_param
Table "public.t_payment_param"
Column | Type | Modifiers
---------------+-----------------------------+-----------
payment_id | integer | not null
name | text | not null
value | text |
creation_date | timestamp(3) with time zone | not null
Indexes: pk_t_payment_param primary key btree (payment_id, name),
index_t_payment_param btree (payment_id)
In any case, a vacuum/analyze was run on the db after the index was
created. This morning I started seeing odd behavior with queries on this
table.
This query returned the correct result of _14_existing_rows_ for the
payment_id 148004 (each row has a different name):
select * from t_payment_param where payment_id in (select payment_id
from t_payment where order_id = 145786);
Run right afterwords in psql, this query returned only a SINGLE ROW:
select * from t_payment_param where payment_id in (148004);
Likewise for this one:
select name from t_payment_param where payment_id = 148004;
At this point, our application was failing due to various lookups on
this table failing to return rows that we could see with the sub-query
version up above.
I dropped the index, ran a vacuum/analyze, and all behavior returned to
normal. Perhaps rebuilding the index would have fixed the problem, but
at that point were just wanted to back out the changes and get things
running again.
Obviously we noted the composite index and wondered if I had violated a
rule. Are you allowed to index elements of a composite index or key? Is
such an operation totally redundant? Is it illegal?
Thank you,
--
// Peter