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]https://www.postgresql.org/docs/devel/ddl-generated-columns.html 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
Show quoted text
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 immutableNo, 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
Show quoted text
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