array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3

Corey Horton <chorton1@austin.rr.com> writes:

I'm trying to use array_to_string on the pg_stats column
histogram_bounds...
test83=# select array_to_string(histogram_bounds::anyarray, '-') from
pg_stats where attname = 'id' and tablename = 'widgets';
ERROR: argument declared "anyarray" is not an array but type anyarray
In 8.1, it worked fine...

Hmm. This seems to have been broken in this patch:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00173.php
which was in response to this complaint:
http://archives.postgresql.org/pgsql-bugs/2008-01/msg00029.php
and was attempting to prevent that same failure message in a different
context :-(. I guess we forgot that pg_statistic makes it possible that
the *actual* datatype passed to a function could be anyarray.

While we could probably revert just enough of the changes to
enforce_generic_type_consistency to allow this case again, I wonder
just how safe that'd really be. It would amount to expecting that
functions that take anyarray but don't take or return anyelement to
not only work on any array type, but to be always prepared for the
input element type to change on-the-fly (since that's exactly what
would happen when scanning pg_statistic). Quite a lot of the built-in
anyarray functions are prepared to do that, but I'm not sure they all
are.

Are we prepared to re-open what could be at least a risk of crashing
bugs, in order to support this type of usage?  I have to admit that
it's nice to be able to process the pg_statistic columns like this
--- I've done it myself.  And we'd not heard any reports of problems
with it before 8.3.

regards, tom lane

Huh, I didn't realize that ever worked in the past. I thought the way
to do what the op describes was to cast it to text[] or whatever
datatype you from out-of-band knowledge to expect.

--
Greg

On 13 Dec 2008, at 19:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Greg Stark <greg.stark@enterprisedb.com> writes:

Huh, I didn't realize that ever worked in the past. I thought the way
to do what the op describes was to cast it to text[] or whatever
datatype you from out-of-band knowledge to expect.

We don't seem to allow that either ...

regression=# select array_to_string(histogram_bounds::text[], '-') from pg_stats where attname = 'unique2' and tablename = 'tenk1';
ERROR: cannot cast type anyarray to text[]
LINE 1: select array_to_string(histogram_bounds::text[], '-') from ...
^

regards, tom lane

I wrote:

While we could probably revert just enough of the changes to
enforce_generic_type_consistency to allow this case again, I wonder
just how safe that'd really be. It would amount to expecting that
functions that take anyarray but don't take or return anyelement to
not only work on any array type, but to be always prepared for the
input element type to change on-the-fly (since that's exactly what
would happen when scanning pg_statistic). Quite a lot of the built-in
anyarray functions are prepared to do that, but I'm not sure they all
are.

I went and looked, and found that none of the thirty or so built-in
functions that accept ANYARRAY are coded to make unsafe assumptions
about the input array type remaining the same across calls. So at least
as of CVS HEAD, it seems safe to relax this back to the way it was
pre-8.3.

I'm still worried about the possibility of extension functions or future
core functions failing to follow this coding rule; but as long as people
are lazy and copy-and-paste from the existing models, it should be okay.

regards, tom lane

Is there any known workaround to get this the elements of the
histogram_bounds anyarray in 8.3.5. If not, when might I expect a fix?

Just trying to plan our testing/release schedule of rolling out to 8.3
around this problem.

Thanks,
Corey

Tom Lane wrote:

Corey Horton <chorton1@austin.rr.com> writes:

Is there any known workaround to get this the elements of the
histogram_bounds anyarray in 8.3.5.

It appears that you could explicitly cast to text and thence to text[]:
select array_to_string(histogram_bounds::text::text[], '-') from ...
but this might be too ugly for your taste.

If not, when might I expect a fix?

8.3.6, or apply
http://archives.postgresql.org/pgsql-committers/2008-12/msg00109.php

regards, tom lane

Fantastic - I'll just if/else the query based on db version.

Thanks!
Corey

Tom Lane wrote:

FYI, I tested your query in 8.3.X CVS and it worked so this fix will in
the next 8.3 minor release.

---------------------------------------------------------------------------

Corey Horton wrote:

Is there any known workaround to get this the elements of the
histogram_bounds anyarray in 8.3.5. If not, when might I expect a fix?

Just trying to plan our testing/release schedule of rolling out to 8.3
around this problem.

Thanks,
Corey

Tom Lane wrote:

I wrote:

While we could probably revert just enough of the changes to
enforce_generic_type_consistency to allow this case again, I wonder
just how safe that'd really be. It would amount to expecting that
functions that take anyarray but don't take or return anyelement to
not only work on any array type, but to be always prepared for the
input element type to change on-the-fly (since that's exactly what
would happen when scanning pg_statistic). Quite a lot of the built-in
anyarray functions are prepared to do that, but I'm not sure they all
are.

I went and looked, and found that none of the thirty or so built-in
functions that accept ANYARRAY are coded to make unsafe assumptions
about the input array type remaining the same across calls. So at least
as of CVS HEAD, it seems safe to relax this back to the way it was
pre-8.3.

I'm still worried about the possibility of extension functions or future
core functions failing to follow this coding rule; but as long as people
are lazy and copy-and-paste from the existing models, it should be okay.

regards, tom lane

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +