Re: BUG #15194: Strange results were displayed for select query.

On 14 May 2018 at 14:02, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> --------------------------------------------------------------------------------------------------
> Query
> --------------------------------------------------------------------------------------------------
> SELECT *
> FROM some_table
> WHERE fld_id = 'PRE_INF'
> AND state_id = 'ERR0010001'
> ;
> --------------------------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------------------------
> Result
> --------------------------------------------------------------------------------------------------
> postgres=# select * from some_table;
> fld_id | state_id | f_ver | seq | fld_dvsn_1 | fld_dvsn_2
> ---------+------------+-------+-----+------------+------------
> PRE_INF | ERR0010001 | | | |
> APT_INF | ERR0010002 | | | |
> (2 row)
> --------------------------------------------------------------------------------------------------
>
> The second line of the result should not appear. but strange result
> displayed for select query.

Can you explain why you think the 2nd row shouldn't appear? The
select in question has no WHERE clause, so I see nothing which would
have filtered out that row.

If you're saying that these are the results of the first query, then
that's quite a different story.

Does it give the same results if you first do:

SET enable_indexscan = off;

If so then its most likely down to a corrupt index. There was a bug
fixed that may cause this behaviour in 9.5.6. See
https://www.postgresql.org/docs/9.5/static/release-9-5-6.html

"Fix a race condition that could cause indexes built with CREATE INDEX
CONCURRENTLY to be corrupt (Pavan Deolasee, Tom Lane)

If CREATE INDEX CONCURRENTLY was used to build an index that depends
on a column not previously indexed, then rows updated by transactions
that ran concurrently with the CREATE INDEX command could have
received incorrect index entries. If you suspect this may have
happened, the most reliable solution is to rebuild affected indexes
after installing this update."

Was this index perhaps created with the CONCURRENTLY option?

I didn't study that particular bug in detail. I was previously under
the impression it could only miss tuples out the index, but the
release notes claim "could have received incorrect index entries", if
that's the case then it may explain what you're seeing.

If you find enable_indexscan = off returns the correct results, then
please follow the release notes in 9.5.6, and all other release notes
between 9.5.5 and 9.5.13, to which you should be upgrading to.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services