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

2009/5/22 Keith Hayden <keith(dot)c(dot)hayden(at)googlemail(dot)com>

> 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.
>

I tried the following:

update pg_type set typalign = 'd' where typname = 'int4'

then

update pg_statistic set stavalues1 = array[1,10,20,30,40,50,60,70,80,90,100]
where starelid = 24751

this failed with:

ERROR: table row type and query-specified row type do not match
DETAIL: Table has type anyarray at ordinal position 18, but query expects
integer[].

Any idea how you got this to work once you updated pg_type.typalign to 'd'
for int4 ?

Thanks,
Keith.