Thread: 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
Tom Lane wrote: > 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. anyarray has been defined this way since 7.3 -- any concerns there? I see that back then pg_statistic used text[] instead of anyarray, so perhaps not. Joe
Joe Conway <mail@joeconway.com> writes:
> anyarray has been defined this way since 7.3 -- any concerns there?
I don't think so --- we weren't trying to use it as an actual column
datatype back then.
7.4 has a problem though :-( ... this is one of the "damn I wish we'd
caught that before release" ones, since it can't easily be fixed without
initdb. Reminds me that I need to get to work on making pg_upgrade
viable again.
regards, tom lane
> I don't think so --- we weren't trying to use it as an actual column > datatype back then. > > 7.4 has a problem though :-( ... this is one of the "damn I wish we'd > caught that before release" ones, since it can't easily be fixed without > initdb. Reminds me that I need to get to work on making pg_upgrade > viable again. Has anyone given any thought as to whether dumping and restoring pg_statistic is worthwhile? eg. some sort of ALTER TABLE..SET STATISTICS (1.0, 3.3, 'asdf',....) command? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Has anyone given any thought as to whether dumping and restoring
> pg_statistic is worthwhile?
Why? You can reconstruct it with a simple "ANALYZE" command. Dumping
and restoring would mean nailing down cross-version assumptions about
what it contains, which doesn't seem real forward-looking...
regards, tom lane
> Why? You can reconstruct it with a simple "ANALYZE" command. Dumping > and restoring would mean nailing down cross-version assumptions about > what it contains, which doesn't seem real forward-looking... I seem to recall that people like that kind of thing so that the dump is really the current state of the database. Also, I believe big db's like DB2 and Oracle do such a thing. I just recall it being discussed some time ago... Chris
Christopher Kings-Lynne wrote: > > Why? You can reconstruct it with a simple "ANALYZE" command. Dumping > > and restoring would mean nailing down cross-version assumptions about > > what it contains, which doesn't seem real forward-looking... > > I seem to recall that people like that kind of thing so that the dump is > really the current state of the database. > > Also, I believe big db's like DB2 and Oracle do such a thing. > > I just recall it being discussed some time ago... I have heard of dumping stats so you are sure your production db has the same stats as your test database, but with ANALYZE so fast, and our optimizer so good, I don't see a use case for us. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073