Keith Hayden <keith.c.hayden@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,
