Search Postgresql Archives

Mismatched pg_class.reltuples between table and primary key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



We recently experienced a hard crash of a dev server due to lack of resources (we think, still investigating).
That resulted in an interesting scenario where one of the tables was returning 12 rows from "select * from tbl", but "select * from tbl order by <pk column>" was only returning 11.
Looking at pg_class.reltuples, it could be clearly seen that the table in question had a value of 12, but the primary key had only 11.

My system catalog ignorance is probably showing, but I assumed that the table and PK pg_class.reltuples values should always be the same?

The initial observations were made by a developer:
1). After an ANALYZE, PK reltuples remained at 11.
2). After REINDEX, PK reltuples was still 11.
3). After VACUUM FULL, PK reltuples was reset to 12.

The db was quiescent at this point (rows were not being added or removed).

I would chalk this up to a localized index corruption issue, but earlier today I observed the same thing on a different table which did not originally show a difference between the table and PK reltuples values.
This is following a VACUUM FREEZE, and an ANALYZE run, as well as at least two restarts of the db cluster since the initial failure.

Can anyone suggest whether this is an index corruption issue, or maybe a misunderstanding on my part about what the reltuples attribute means? Or perhaps it is something else?


PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-54), 64-bit
default_statistics_target = 200


Here is a query I hacked together to raise a WARNING if the reltuples value of a table was different than the associated primary key or unique key. I visually ignored any values that were above the default_statistics_target value, to factor out sample size issues.

DO
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT  n.nspname schemaname,
                c.relname as tablename,
                c.reltuples::NUMERIC as tbl_tuples,
                i.relname as idx_name,
                i.reltuples::NUMERIC as idx_tuples
        FROM pg_index x
        JOIN pg_class c ON c.oid = x.indrelid
        JOIN pg_class i ON i.oid = x.indexrelid
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'::"char"
        AND i.relkind = 'i'::"char"
        AND n.nspname NOT IN ('pg_catalog','information_schema')
        AND (x.indisunique IS TRUE OR x.indisprimary IS TRUE)
        ORDER BY 1,2,4
    LOOP
        IF ( rec.idx_name IS NOT NULL
             AND rec.tbl_tuples IS DISTINCT FROM rec.idx_tuples ) THEN
            RAISE WARNING 'Mismatched tuple counts - Table %, tuples: %, unique key: %, tuples: %',
                rec.schemaname||'.'||rec.tablename, rec.tbl_tuples, rec.idx_name, rec.idx_tuples;
        END IF;
    END LOOP;
END
$$;

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux