Horton

Re: pg18: Virtual generated columns are not (yet) safe when superuser selects from them

Peter Eisentraut <peter@eisentraut.org>

View thread
From: Peter Eisentraut <peter@eisentraut.org>
To: jian he <jian.universality@gmail.com>
Cc: Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-06-23T13:13:40Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Restrict virtual columns to use built-in functions and types

  2. Fix virtual generated column type checking for ALTER TABLE

  3. Expand virtual generated columns in the planner

Attachments

On 21.06.25 16:45, jian he wrote:
>> CREATE TABLE gtest1 (a int42 GENERATED ALWAYS AS ('1') VIRTUAL);
>> CREATE TABLE gtest2 (a int42 GENERATED ALWAYS AS ('1'::int42) VIRTUAL);
>> ERROR:  generation expression uses user-defined type
>> LINE 1: CREATE TABLE gtest2 (a int42 GENERATED ALWAYS AS ('1'::int42...
>>                                                            ^
>> DETAIL:  Virtual generated columns that make use of user-defined types
>> are not yet supported.
>>
>> Do we need error out for the first case?
>>
> 
> I think these two cases both should error out.
> 
> If generated column expressions do not allow user-defined types or functions, it
> makes sense to also disallow virtual generated columns from using user-defined
> types.
> Attached patch change CheckAttributeType to do the job.
> related tests also added.
> 
> Note: Support for composite types in virtual generated columns is
> currently partial.
> for example:
> 
> CREATE TYPE double_int as (a int, b int);
> --ok
> CREATE TABLE gtest4 (
>      a int,
>      b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) VIRTUAL
> );
> --not ok.
> CREATE TABLE gtest4 (
>    a int,
>    b double_int GENERATED ALWAYS AS ((a * 2, a * 3)::double_int) VIRTUAL
> );

Your CheckAttributeType() change is conditional on TYPTYPE_BASE, but if 
you remove that and check it for all types, then you get the right error 
in both cases.

I have attached a patch that is similar to yours but with that change. 
I've also written the test cases a bit differently, but it also covers 
everything now.

(The two patches should be squashed.  I'm just keeping them separate to 
show what is changed.)