1

Today we had to update the PostgreSQL version on our database servers. Before update the version was 9.3.4 and the new one is 9.3.6. Despite the small difference in the database version and the absolutely identical configs, the search results changed drastically. The number of results decreased many times for some search queries. The rows added today (after update) were found each time, while some older ones cannot be found by a given phrase.

We use tsvector to perform full text search.

Any ideas what might be causing this?

Example query:

SELECT * FROM content, to_tsquery('bulgarian_utf8', '{keyword}') q
WHERE (tsv_title @@ q OR tsv @@ q) ) AS c

migrated from serverfault.com Apr 5 '15 at 17:30

This question came from our site for system and network administrators.

2

Your problem is probably due to the difference from 9.3.4 to 9.3.5, see the manual for that, where it states:

E.2.1. Migration to Version 9.3.5

A dump/restore is not required for those running 9.3.X.

However, this release corrects a logic error in pg_upgrade, as well as an index corruption problem in some GiST indexes. See the first two changelog entries below to find out whether your installation has been affected and what steps you should take if so.

and below, the first 2 changelog entries:

  • In pg_upgrade, remove pg_multixact files left behind by initdb (Bruce Momjian)

    If you used a pre-9.3.5 version of pg_upgrade to upgrade a database cluster to 9.3, it might have left behind a file $PGDATA/pg_multixact/offsets/0000 that should not be there and will eventually cause problems in VACUUM. However, in common cases this file is actually valid and must not be removed. To determine whether your installation has this problem, run this query as superuser, in any database of the cluster:

    WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
      SELECT EXISTS (SELECT * FROM list WHERE file = '0000') AND
             NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
             NOT EXISTS (SELECT * FROM list WHERE file = 'FFFF') AND
             EXISTS (SELECT * FROM list WHERE file != '0000')
             AS file_0000_removal_required;
    

    If this query returns t, manually remove the file $PGDATA/pg_multixact/offsets/0000. Do nothing if the query returns f

  • Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns (Heikki Linnakangas)

    This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying columns should REINDEX those indexes after installing this update.

    ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy