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

[Keith Hayden <keith.c.hayden@xxxxxxxxxxxxxx>]

2009/5/22 Keith Hayden <keith.c.hayden@xxxxxxxxxxxxxx>

2009/5/22 Tom Lane <tgl@xxxxxxxxxxxxx>

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