Re: How to update stavaluesN columns in pg_statistics (type anyarry)

2009/5/22 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Keith Hayden <keith(dot)c(dot)hayden(at)googlemail(dot)com> writes:
> > I need to spoof statistics, and so need to update the stavaluesN columns
> in
> > pg_statistics, which are of type anyarray. Can this be done using an
> UPDATE
> > statement ? I have tried using array[...] and '{...}' syntax with no
> luck.
> > Any other ideas as to how to achieve this ?
>
> I've done it successfully but it's not something you should consider for
> any sort of production purpose. IIRC the non-obvious part is that the
> columns are declared anyarray which has alignment 'd', but if you want
> to stick in an array of, say, integer then that only has alignment 'i'.
> I think what I did to make it work was to temporarily change the
> typalign entry in _int4's pg_type row to 'd' ... this was in a test
> database so I wasn't afraid of how badly it might break ;-)
>
> Something that might actually be sustainable is to take advantage
> of the planner statistics hook function that is new in 8.4. That is,
> make a hook function that shoves in the values you want at the time
> of use, rather than modifying pg_statistic directly.
>
> regards, tom lane

Thanks Tom, I will give the typalign change a go and see if that works. 8.4
is not an option at the moment. I am looking for a way to create a bunch of
tables and indices with meaningful/realistic stats and tuple counts, without
actually loading data into these relations, and then get meaningful query
plans out. So any other hints or tips you have will be gratefully received,

Keith.