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

Question

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.

• Mitch Kent: $ psql --version psql (PostgreSQL) 9.3.4
• Hannah Vernon: Surely it cannot be completely coincidental that the numbers in pgAdmin vs the numbers from the SQL Window match so closely? 7535871 vs 35871 looks to me like the number 35871 has the two left-most digits truncated.
• Mitch Kent: Hi Hannah - yes I can't believe I didn't notice this yesterday, however it has been noticed this morning. It is still strange, but not nearly as worrying if this is the case, and the numbers do increment as it's 'partner' does.

Answer 1

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