How to update stavaluesN columns in pg_statistics (type anyarry)

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

2009/5/22 Tom Lane <tgl@sss.pgh.pa.us>

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,

Keith.

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

2009/5/22 Tom Lane <tgl@sss.pgh.pa.us>

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,

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.

Keith Hayden <keith.c.hayden@googlemail.com> writes:

I tried the following:
update pg_type set typalign = 'd' where typname = 'int4'

Not int4, _int4 (that is, array of 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[].

Yeah, I remember hitting that too but I don't recall what I did about
it. I was in a hurry and only looking for a one-off solution, so I
might've just removed that error check from my working build :-(

regards, tom lane