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

<div class="Section1"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; color: navy; font-family:
Arial;">Help! </span></font><br/><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; color: navy;
font-family:Arial;">I’m trying to use array_to_string on the pg_stats column histogram_bounds…</span></font><br /><font
color="navy"face="Arial" size="2"><span style="font-size: 10pt; color: navy; font-family: Arial;"><br /> test83=#
selectarray_to_string(histogram_bounds::anyarray, '-') from pg_stats where attname = ‘id' and tablename =
‘widgets’;</span></font><br/><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; color: navy;
font-family:Arial;">ERROR:  argument declared "anyarray" is not an array but type anyarray</span></font><br /><font
color="navy"face="Arial" size="2"><span style="font-size: 10pt; color: navy; font-family: Arial;"> </span></font><br
/><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10pt; color: navy; font-family:
Arial;"> </span></font><br/><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; color: navy;
font-family:Arial;">In 8.1, it worked fine…</span></font> <font color="navy" face="Arial" size="2"><span
style="font-size:10pt; color: navy; font-family: Arial;"> </span></font><br /><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 10pt; color: navy; font-family: Arial;">test81=# select
array_to_string(histogram_bounds::anyarray,'-') from pg_stats where attname = 'id' and tablename =
'widgets';</span></font><br/><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; color: navy;
font-family:Arial;">                                                   array_to_string</span></font><br /><font
color="navy"face="Arial" size="2"><span style="font-size: 10pt; color: navy; font-family:
Arial;">---------------------------------------------------------------------------------------------------------------------</span></font><br
/><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10pt; color: navy; font-family:
Arial;"> 140554732-572177261-753193757-830984044-942651560-1085142858-1287313768-1418796327-1623947492-1865024250-3199068162</span></font><br
/><fontface="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font><br /><font face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial;">Any help would be appreciated,</span></font> whether a known
fixto this, or any workaround you can think of.<br /><br /> Thanks!<br /><font face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;">Corey Horton</span></font><br /><font face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font></div> 

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:

> 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
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

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
Iexpect a fix?<br /><br /> Just trying to plan our testing/release schedule of rolling out to 8.3 around this problem.
<br/><br /> Thanks,<br /> Corey<br /><br /> Tom Lane wrote: <blockquote cite="mid:26729.1229278563@sss.pgh.pa.us"
type="cite"><prewrap="">I wrote: </pre><blockquote type="cite"><pre wrap="">While we could probably revert just enough
ofthe 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.   </pre></blockquote><pre wrap="">
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

 </pre></blockquote>

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.<br /><br /> Thanks!<br /> Corey<br /><br /> Tom Lane
wrote:<blockquote cite="mid:2304.1229284683@sss.pgh.pa.us" type="cite"><pre wrap="">Corey Horton <a
class="moz-txt-link-rfc2396E"href="mailto:chorton1@austin.rr.com"><chorton1@austin.rr.com></a> writes:
</pre><blockquotetype="cite"><pre wrap="">Is there any known workaround to get this the elements of the 
 
histogram_bounds anyarray in 8.3.5.   </pre></blockquote><pre wrap="">
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.
 </pre><blockquote type="cite"><pre wrap="">If not, when might I expect a fix?   </pre></blockquote><pre wrap="">
8.3.6, or apply
<a class="moz-txt-link-freetext"
href="http://archives.postgresql.org/pgsql-committers/2008-12/msg00109.php">http://archives.postgresql.org/pgsql-committers/2008-12/msg00109.php</a>
        regards, tom lane

 </pre></blockquote>

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. +