Обсуждение: BUG #15194: Strange results were displayed for select query.

The following bug has been logged on the website:

Bug reference:      15194
Logged by:          Kenneth Lee
Email address:      leejw@k4m.com
PostgreSQL version: 9.5.5
Operating system:   Linux Redhat 6.5 [2.6.32-431.el6.x86_64]
Description:

Dear PostgreSQL Community member,

I had a strange experience. During the select query strange results were
displayed.

The contents were as follows.
--------------------------------------------------------------------------------------------------
                                                   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.

However, after delete and insert the row, the results were normal.

Do you have any problems like this?

If not, what should I do?

I look forward to a good reply.

Thanks.

The following bug has been logged on the website:

Bug reference:      15194
Logged by:          Kenneth Lee
Email address:      leejw@k4m.com
PostgreSQL version: 9.5.5
Operating system:   Linux Redhat 6.5 [2.6.32-431.el6.x86_64]
Description:       

Do you have any problems like this?

If not, what should I do?

On 14 May 2018 at 14:02, PG Bug reporting form <noreply@postgresql.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