Content-Type: multipart/alternative;
 boundary="------------MG5vWnU07v1y1ru5g8WKHsNy"
Message-ID: <b5f96b24-ccac-47fd-9e20-14681b894f36@gmail.com>
Date: Fri, 16 May 2025 07:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Virtual generated columns
To: Richard Guo <guofenglinux@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>,
 Dean Rasheed <dean.a.rasheed@gmail.com>,
 jian he <jian.universality@gmail.com>, Zhang Mingli <zmlpostgres@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org>
 <CAMbWs4-h49s0ehfSq4CnztRLSwOF79ZQUkSPzGCQLNroAG7joA@mail.gmail.com>
 <CACJufxHVsCoxhqeDV973Hny3EtgCGztDJj=KKvErexnH=2k6yQ@mail.gmail.com>
 <12d5cce9-917f-4a99-86d3-715bb3b363bb@Spark>
 <CAMbWs4_xiGiB5Oufdn62cJ=MoiS9cJWLzq=jR_7xnTO8x_oPqg@mail.gmail.com>
 <CACJufxEWnz__Bf-KhVaiisHZd-5JcRRY_xttCKLs_m2xaUJmQA@mail.gmail.com>
 <d23c8a44-9124-4eaa-af90-e5482b3b4680@eisentraut.org>
 <CAEZATCUpzF0XZGVpuJ-O4WLnDWBmddSc+yMWvg6zsz_ekegfPg@mail.gmail.com>
 <CAMbWs4-JmGEBDucPw8iJ3H8hg-7TkbOqE0WTVzhJ5iyzQyP=-A@mail.gmail.com>
 <CAMbWs4_LRNGAxbQbLOrj+VMy80dUR5v2JQ9fVcre10C4KVYUDg@mail.gmail.com>
 <CAEZATCWhr=FM4X5kCPvVs-g2XEk+ceLsNtBK_zZMkqFn9vUjsw@mail.gmail.com>
 <CAEZATCWsKqCtZ=ud26-gGV3zHt-hjS4OKG43GCBhSaYUWyfKiw@mail.gmail.com>
 <CAMbWs4_Rg-F=jQ7-WfBztnVZG2i5kAUwoQv0O5GbGPzxpjDeTQ@mail.gmail.com>
 <CAEZATCV+msUomqYUc-M70epBn7WppLqiw1z=4u4yf6w4vUECiQ@mail.gmail.com>
 <CAMbWs4_DVtFfonq7eL6Ocor4apV9dBb6wOhJ5iUZPEddKc-_vg@mail.gmail.com>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: 
 <CAMbWs4_DVtFfonq7eL6Ocor4apV9dBb6wOhJ5iUZPEddKc-_vg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/b5f96b24-ccac-47fd-9e20-14681b894f36%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------MG5vWnU07v1y1ru5g8WKHsNy
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello Richard,

22.02.2025 16:55, Richard Guo wrote:
> create table t (a int, b int);
> create table vt (a int, b int generated always as (a * 2));
>
> insert into t values (1, 1);
> insert into vt values (1);
>
> # select 1 from t t1 where exists
>     (select 1 from vt where exists
>      (select t1.a from t t2 where vt.b = 2));
> ERROR:  unexpected virtual generated column reference

I've discovered yet another way to trigger that error:
create table vt (a int, b int generated always as (a * 2), c int);
insert into vt values(1);
alter table vt alter column c type bigint using b + c;

ERROR:  XX000: unexpected virtual generated column reference
LOCATION:  CheckVarSlotCompatibility, execExprInterp.c:2410

Shouldn't this be expected/supported?

Best regards,
Alexander Lakhin
Neon (https://neon.tech)
--------------MG5vWnU07v1y1ru5g8WKHsNy
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Hello Richard,<br>
      <br>
      22.02.2025 16:55, Richard Guo wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAMbWs4_DVtFfonq7eL6Ocor4apV9dBb6wOhJ5iUZPEddKc-_vg@mail.gmail.com">
      <pre wrap="" class="moz-quote-pre">create table t (a int, b int);
create table vt (a int, b int generated always as (a * 2));

insert into t values (1, 1);
insert into vt values (1);

# select 1 from t t1 where exists
   (select 1 from vt where exists
    (select t1.a from t t2 where vt.b = 2));
ERROR:  unexpected virtual generated column reference
</pre>
    </blockquote>
    <br>
    I've discovered yet another way to trigger that error:<br>
    create table vt (a int, b int generated always as (a * 2), c int);<br>
    insert into vt values(1);<br>
    alter table vt alter column c type bigint using b + c;<br>
    <br>
    ERROR:  XX000: unexpected virtual generated column reference<br>
    LOCATION:  CheckVarSlotCompatibility, execExprInterp.c:2410<br>
    <br>
    Shouldn't this be expected/supported?<br>
    <br>
    Best regards,<br>
    Alexander Lakhin <br>
    Neon (<a class="moz-txt-link-freetext" href="https://neon.tech">https://neon.tech</a>)
  </body>
</html>

--------------MG5vWnU07v1y1ru5g8WKHsNy--