Skip site navigation (1) Skip section navigation (2)

Re: select statement against pg_stats returns inconsistent data

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Shelby Cain <alyandon(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: select statement against pg_stats returns inconsistent data
Date: 2004-02-24 23:28:36
Message-ID: 28810.1077665316@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
Shelby Cain <alyandon(at)yahoo(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Dann CorbitDate: 2004-02-24 23:33:52
Subject: Re: Is indexing broken for bigint columns?
Previous:From: Mike MascariDate: 2004-02-24 23:26:36
Subject: Re: Is indexing broken for bigint columns?

pgsql-general by date

Next:From: Scott GoodwinDate: 2004-02-25 00:03:35
Subject: Inheritance and column references problem
Previous:From: Vivek KheraDate: 2004-02-24 22:21:51
Subject: Re: DBD::Pg 1.32 ready for testing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group