Обсуждение: Generated column and string concatenation issue

Hi everyone,

Consider the following statement:

CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || 1)) STORED); --
unexpected: generation expression is not immutable

I would expect that this table can be created. However, I get the
following error message: "ERROR:  generation expression is not
immutable". The documentation [1] mentions several restrictions for
generated columns, but I did not see any restrictions on operators (or
is string concatenation considered to be a function?).

Adding an explicit cast to string on the non-string argument results
in no error being shown:

CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || 1::TEXT))
STORED); -- expected: no error

I'm using the following Postgres version: 12beta2 (Debian 12~beta2-1.pgdg90+1)

Best,
Manuel


[1] https://www.postgresql.org/docs/devel/ddl-generated-columns.html

Manuel Rigger <rigger.manuel@gmail.com> writes:
> Consider the following statement:

> CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || 1)) STORED); --
> unexpected: generation expression is not immutable

No, that's not a bug.  What you've got is "text || integer", which
requires an integer-to-text coercion, which isn't necessarily
immutable.  (Well, actually, integer-to-text is immutable.  But
the particular operator you're getting here is textanycat which
accepts anything on the RHS, so it has to be marked stable which
is our worst-case assumption for the stability of I/O conversions.
As an example, timestamp-to-text's results vary with the DateStyle
GUC so that one definitely isn't immutable.)

You could imagine different factorizations of this functionality
that might allow the specific RHS type to be taken into account,
but the actual details of how to make that happen aren't very
clear, and changing it might have other downsides.

Anyway the short answer is that you should have done

CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || '1')) STORED);

which would resolve as "text || text" which is immutable.
The explicit cast that you showed also dodges the problem
by not relying on textanycat.

            regards, tom lane

Thanks for the clarification! Do you think that's clear from the documentation?

Best,
Manuel

On Wed, Jul 10, 2019 at 4:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Manuel Rigger <rigger.manuel@gmail.com> writes:
> > Consider the following statement:
>
> > CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || 1)) STORED); --
> > unexpected: generation expression is not immutable
>
> No, that's not a bug.  What you've got is "text || integer", which
> requires an integer-to-text coercion, which isn't necessarily
> immutable.  (Well, actually, integer-to-text is immutable.  But
> the particular operator you're getting here is textanycat which
> accepts anything on the RHS, so it has to be marked stable which
> is our worst-case assumption for the stability of I/O conversions.
> As an example, timestamp-to-text's results vary with the DateStyle
> GUC so that one definitely isn't immutable.)
>
> You could imagine different factorizations of this functionality
> that might allow the specific RHS type to be taken into account,
> but the actual details of how to make that happen aren't very
> clear, and changing it might have other downsides.
>
> Anyway the short answer is that you should have done
>
> CREATE TABLE t0(c0 TEXT GENERATED ALWAYS AS (('abc' || '1')) STORED);
>
> which would resolve as "text || text" which is immutable.
> The explicit cast that you showed also dodges the problem
> by not relying on textanycat.
>
>                         regards, tom lane

Manuel Rigger <rigger.manuel@gmail.com> writes:
> Thanks for the clarification! Do you think that's clear from the documentation?

Our documentation doesn't specify which operators are immutable or stable
or volatile.  So, no it's not clear, but we'd be moving the goalposts
pretty far to expect it to be ...

            regards, tom lane

Okay, thanks again!

Best,
Manuel

On Wed, Jul 10, 2019 at 5:00 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Manuel Rigger <rigger.manuel@gmail.com> writes:
> > Thanks for the clarification! Do you think that's clear from the documentation?
>
> Our documentation doesn't specify which operators are immutable or stable
> or volatile.  So, no it's not clear, but we'd be moving the goalposts
> pretty far to expect it to be ...
>
>                         regards, tom lane