Re: [GENERAL] select statement against pg_stats returns inconsistent data

Shelby Cain <alyandon@yahoo.com> writes:
> The select statements return different data for
> most_commons_vals depending on whether n_distinct is
> included in the select clause or not.
> I only seem to get the behavior below against int8
> columns - but I haven't interated through every
> conceivable data type either.

Hoo, I'm surprised no one noticed this during 7.4 development/testing.
The problem applies for any datatype that requires double alignment,
which includes int8, float8, and timestamp as well as most of the
geometric types.  pg_statistic is declared as using type "anyarray",
and this type really needs to be marked as requiring double alignment
so that arrays of double-aligned datatypes will come out correctly.

The correct source fix is a one-line change in pg_type.h, but this will
not propagate into existing databases without an initdb.  It looks like
what you'd need to do to fix an existing database is

-- clear out broken data in pg_statistic
DELETE FROM pg_statistic;
-- this should update 1 row:
UPDATE pg_type SET typalign = 'd' WHERE oid = 2277;
-- this should update 6 rows:
UPDATE pg_attribute SET attalign = 'd' WHERE atttypid = 2277;
-- might be a good idea to start a fresh backend at this point
-- repopulate pg_statistic
ANALYZE;

Ugh :-(

            regards, tom lane