Re: string_to_array has to be stable?

Hello

I am working on to_array, to_string functions and I am looking on
string_to_array function. I am surprised so this function is marked as
immutable

postgres=# select array_to_string(array[current_date],',');
array_to_string
-----------------
2010-07-20
(1 row)

postgres=# set datestyle to German ;
SET
postgres=# select array_to_string(array[current_date],',');
array_to_string
-----------------
20.07.2010
(1 row)

it is probably wrong

Regards

Pavel Stehule

On 20 July 2010 10:31, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello
>
> I am working on to_array, to_string functions and I am looking on
> string_to_array function. I am surprised so this function is marked as
> immutable
>
> postgres=# select array_to_string(array[current_date],',');
>  array_to_string
> -----------------
>  2010-07-20
> (1 row)
>
> postgres=# set datestyle to German ;
> SET
> postgres=# select array_to_string(array[current_date],',');
>  array_to_string
> -----------------
>  20.07.2010
> (1 row)
>
> it is probably wrong
>
> Regards
>
> Pavel Stehule
>

Yeah, that looks wrong.

Thom

On Tue, 2010-07-20 at 11:31 +0200, Pavel Stehule wrote:
> Hello
>
> I am working on to_array, to_string functions and I am looking on
> string_to_array function. I am surprised so this function is marked as
> immutable
>
> postgres=# select array_to_string(array[current_date],',');
> array_to_string
> -----------------
> 2010-07-20
> (1 row)
>
> postgres=# set datestyle to German ;
> SET
> postgres=# select array_to_string(array[current_date],',');
> array_to_string
> -----------------
> 20.07.2010
> (1 row)
>

What's wrong with that? "current_date" is the part that's changing, and
it's being passed as an argument to the function. If the argument
changes, an immutable function can return a different result.

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Tue, 2010-07-20 at 11:31 +0200, Pavel Stehule wrote:
>> I am working on to_array, to_string functions and I am looking on
>> string_to_array function. I am surprised so this function is marked as
>> immutable

> What's wrong with that? "current_date" is the part that's changing, and
> it's being passed as an argument to the function. If the argument
> changes, an immutable function can return a different result.

string_to_array() seems fine to me: it's a predictable transformation
from text to text. However, I think that there really is an issue with
array_to_string(), because that takes an anyarray and invokes the array
element's type output function. Type output functions are not
necessarily immutable, and if the input is of a type where that's not
true, then the array_to_string() transformation isn't immutable either.
An example is that date's output function produces different results
depending on datestyle.

I can't remember offhand whether there are any volatile type output
functions, but if there were we'd really need to mark array_to_string()
as volatile. That would be unpleasant for performance though. I'd
rather compromise on stable. Thoughts?

regards, tom lane

On Wed, 2010-07-28 at 20:25 -0400, Tom Lane wrote:
> string_to_array() seems fine to me: it's a predictable transformation
> from text to text. However, I think that there really is an issue with
> array_to_string(), because that takes an anyarray and invokes the array
> element's type output function.

Yes, I misread the problem because he used "current_date" rather than a
date literal.

> I can't remember offhand whether there are any volatile type output
> functions, but if there were we'd really need to mark array_to_string()
> as volatile. That would be unpleasant for performance though. I'd
> rather compromise on stable. Thoughts?

"Stable" seems reasonable to me.

A volatile type output function sounds like an edge case. Perhaps there
are even grounds to force a type output function to be stable, similar
to how we force the function for a functional index to be immutable.

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Wed, 2010-07-28 at 20:25 -0400, Tom Lane wrote:
>> I can't remember offhand whether there are any volatile type output
>> functions, but if there were we'd really need to mark array_to_string()
>> as volatile. That would be unpleasant for performance though. I'd
>> rather compromise on stable. Thoughts?

> "Stable" seems reasonable to me.

> A volatile type output function sounds like an edge case. Perhaps there
> are even grounds to force a type output function to be stable, similar
> to how we force the function for a functional index to be immutable.

I did a bit of research in the system catalogs, and found that the only
built-in type output function that is marked volatile is record_out().
I think this is probably from an excess of caution --- record_out has
the same issue that it's really as volatile as the underlying per-column
output functions. I notice in particular that anyarray_out is marked
stable, and of course it's got the same issue too.

I propose changing both array_to_string() and record_out() to be marked
stable, and that that be the default assumption for similar future cases
as well. This isn't something we can back-patch, but sneaking it into
9.0 at this point (without a catversion bump) seems reasonable to me.

I'm not in favor of trying to force output functions to be declared
non-volatile as Jeff suggests above. I think doing that would probably
break user type definitions far and wide --- for a comparative sample,
all of the user-defined types added in the standard regression tests
would break, because we never bothered to mark their output functions
as to volatility. If we did do it, it would retroactively justify
treating record_out and anyarray_out as stable, but I doubt it's worth
causing a flag day for user-defined types.

BTW, the situation on the input side is a bit different: record_in is
volatile because domain_in is, and I think we'd better leave that alone
since it's not too hard to believe that a domain might have volatile
CHECK expressions. If we had arrays of domains, anyarray_in would have
to be volatile too, but we don't and it isn't.

regards, tom lane

2010/7/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> On Wed, 2010-07-28 at 20:25 -0400, Tom Lane wrote:
>>> I can't remember offhand whether there are any volatile type output
>>> functions, but if there were we'd really need to mark array_to_string()
>>> as volatile.  That would be unpleasant for performance though.   I'd
>>> rather compromise on stable.  Thoughts?
>
>> "Stable" seems reasonable to me.
>
>> A volatile type output function sounds like an edge case. Perhaps there
>> are even grounds to force a type output function to be stable, similar
>> to how we force the function for a functional index to be immutable.
>
> I did a bit of research in the system catalogs, and found that the only
> built-in type output function that is marked volatile is record_out().
> I think this is probably from an excess of caution --- record_out has
> the same issue that it's really as volatile as the underlying per-column
> output functions.  I notice in particular that anyarray_out is marked
> stable, and of course it's got the same issue too.
>
> I propose changing both array_to_string() and record_out() to be marked
> stable, and that that be the default assumption for similar future cases
> as well.  This isn't something we can back-patch, but sneaking it into
> 9.0 at this point (without a catversion bump) seems reasonable to me.

+1

>
> I'm not in favor of trying to force output functions to be declared
> non-volatile as Jeff suggests above.  I think doing that would probably
> break user type definitions far and wide --- for a comparative sample,
> all of the user-defined types added in the standard regression tests
> would break, because we never bothered to mark their output functions
> as to volatility.  If we did do it, it would retroactively justify
> treating record_out and anyarray_out as stable, but I doubt it's worth
> causing a flag day for user-defined types.
>
> BTW, the situation on the input side is a bit different: record_in is
> volatile because domain_in is, and I think we'd better leave that alone
> since it's not too hard to believe that a domain might have volatile
> CHECK expressions.  If we had arrays of domains, anyarray_in would have
> to be volatile too, but we don't and it isn't.
>
>                        regards, tom lane
>

I wrote:
> BTW, the situation on the input side is a bit different: record_in is
> volatile because domain_in is, and I think we'd better leave that alone
> since it's not too hard to believe that a domain might have volatile
> CHECK expressions. If we had arrays of domains, anyarray_in would have
> to be volatile too, but we don't and it isn't.

Oh, wait: we have arrays of composites now, and a composite could
contain a domain. So that's wrong too; anyarray_in had better be marked
volatile. In general it seems that the coding rules need to be:

* if you depend on an arbitrary type output function, assume it's stable.

* if you depend on an arbitrary type input function, assume it's volatile.

* similarly for binary send/receive functions.

Or we could decide that volatile domain CHECK expressions are un-sensible
and just relabel all these input functions as stable, which would make
everything consistent. Thoughts?

regards, tom lane

On Thu, Jul 29, 2010 at 1:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> BTW, the situation on the input side is a bit different: record_in is
>> volatile because domain_in is, and I think we'd better leave that alone
>> since it's not too hard to believe that a domain might have volatile
>> CHECK expressions.  If we had arrays of domains, anyarray_in would have
>> to be volatile too, but we don't and it isn't.
>
> Oh, wait: we have arrays of composites now, and a composite could
> contain a domain.  So that's wrong too; anyarray_in had better be marked
> volatile.  In general it seems that the coding rules need to be:
>
> * if you depend on an arbitrary type output function, assume it's stable.
>
> * if you depend on an arbitrary type input function, assume it's volatile.
>
> * similarly for binary send/receive functions.
>
> Or we could decide that volatile domain CHECK expressions are un-sensible
> and just relabel all these input functions as stable, which would make
> everything consistent.  Thoughts?

Aren't volatile CHECK expressions pretty un-sensible in general?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Jul 29, 2010 at 1:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Or we could decide that volatile domain CHECK expressions are un-sensible
>> and just relabel all these input functions as stable, which would make
>> everything consistent. Thoughts?

> Aren't volatile CHECK expressions pretty un-sensible in general?

Yeah, probably so. I can't think of a use-case that seems like it would
justify the possible performance hit from having to assume all functions
performing datatype input calls are volatile.

regards, tom lane

On Thu, Jul 29, 2010 at 1:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Jul 29, 2010 at 1:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Or we could decide that volatile domain CHECK expressions are un-sensible
>>> and just relabel all these input functions as stable, which would make
>>> everything consistent.  Thoughts?
>
>> Aren't volatile CHECK expressions pretty un-sensible in general?
>
> Yeah, probably so.  I can't think of a use-case that seems like it would
> justify the possible performance hit from having to assume all functions
> performing datatype input calls are volatile.

That's my thought, too. Any non-immutable CHECK constraint is
basically playing with fire, to some degree. But a stable check
constraint is at least playing with it somewhat responsibly, whereas a
volatile check constraint strikes me as more like doing it while
bathing in turpentine.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company