2009/5/22 Keith Hayden <keith.c.hay...@googlemail.com>
> 2009/5/22 Tom Lane <t...@sss.pgh.pa.us>
>
> Keith Hayden <keith.c.hay...@googlemail.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.
