Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results

corruptionpostgresqlpostgresql-9.3

I am running postgresql 9.3.4 on a Centos 6.5 server as a back end for a web app which I am currently load testing.

Presently, if I visit the state table in pgAdmin, right click on "count", I get a value of 7535871

If I run select count(*) from state in the sql window, I get the value of 35871

The state_id column is the primary key and incremented for each row, running select state_id from state order by state_id DESC limit 1 gives the value 7537276

If I run select max(state_id) from state, I get the result 537276

Can anyone explain where all these discrepancies lie, or have I just got a very corrupt database? The load testing was hammering the database, so I wouldn't be massively surprised if there was corruption, however it is a worrying prospect so I need to first ascertain if there is another explanation.

Many thanks

EDIT

A Friend has suggested that on insert ignores/replace into type queries, the PK might update despite no new rows. You would assume the pgAdmin would account for this, and the function that adds rows to this table performs a simple insert into, so I'm not convinced. However, I would be interested in seeing if there are any gaps in the primary key field. Suggestions as to best do this are welcome, although I realise that is effectively a different question.

Best Answer

Thanks to Hannah Vernon for pointing out the obvious truncation going on, which I'm quite annoyed with myself for not noticing.

Having re run all the queries above in psql, they return the full, non-truncated numbers. Re-running them again in the pgAdmin sql window, I get truncated values. What is most strange is that there is different precision in the truncation and it is reliably so. Who am I to guess at the logic, I'm sure it's a bug and I will look to see if there is a bug tracker for the software.

Many thanks to all for your comments.

EDIT

Here is a comment about column truncation in the FAQ, however it doesn't go into any detail. I would assume this is for long text columns rather than integers over 5 digits, and I can't see where the option it refers to are, but I thought it prudent to include it anyway:

Query tool columns truncated

Some columns are truncated when running a query in the query tool.

You can increase the query option "max. chars per column" for this. Please note that there's another limit for this imposed by the underlying windows control, which apparently doesn't allow more than 511 characters. In pgAdmin V1.1 and up, we provide the function "execute to file", which has no column restrictions. [AP]

http://www.pgadmin.org/pgadmin3/faq/#ColTrunc

Related Solutions

PostgreSQL: Corrupt primary key, inconsistent table

There are various ways this can happen in Oracle - I'm not sure about postgres, but I think I would call this an "integrity violation" rather than "corruption"

Perhaps you can do one of the things suggested here, ie set enable_indexscan = off or

begin;
drop index rank_details_pkey;
select user_id, count(*) from rank_details group by user_id having count(*) > 1;
rollback;

But "there are likely some locking issues with this, so be careful with it in production"

The idea is to force the query to scan the table rather than just the index (which does not have the duplicates). You may also, and more simply, be able to acheive the same by:

select user_id, f(<some other column>), count(*)
from rank_details
group by user_id, f(<some other column>)
having count(*) > 1

where f() returns a constant, which may trick the planner into a table scan.

Postgresql – Search results mismatch after postgresql update

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.

...

Best Answer

Related Solutions

PostgreSQL: Corrupt primary key, inconsistent table

Postgresql – Search results mismatch after postgresql update

Related Question