Horton

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

jian he <jian.universality@gmail.com>

View thread
From: jian he <jian.universality@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: Feike Steenbergen <feikesteenbergen@gmail.com>, PostgreSQL mailing lists <pgsql-hackers@postgresql.org>
Date: 2025-06-21T14:45:51Z
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 Sat, Jun 21, 2025 at 1:29 PM jian he <jian.universality@gmail.com> wrote:
>
> ( the following excerpted from create_type.sql)
>
> BEGIN;
> CREATE TYPE int42;
> -- Make dummy I/O routines using the existing internal support for int4, text
> CREATE FUNCTION int42_in(cstring)
>    RETURNS int42
>    AS 'int4in'
>    LANGUAGE internal STRICT IMMUTABLE;
> CREATE FUNCTION int42_out(int42)
>    RETURNS cstring
>    AS 'int4out'
>    LANGUAGE internal STRICT IMMUTABLE;
> CREATE TYPE int42 (
>    internallength = 4,
>    input = int42_in,
>    output = int42_out,
>    alignment = int4,
>    default = 42,
>    passedbyvalue
> );
> COMMIT;
>
>
> 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
);