Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit83ea6c5

Browse files
committed
Virtual generated columns
This adds a new variant of generated columns that are computed on read(like a view, unlike the existing stored generated columns, which arecomputed on write, like a materialized view).The syntax for the column definition is ... GENERATED ALWAYS AS (...) VIRTUALand VIRTUAL is also optional. VIRTUAL is the default rather thanSTORED to match various other SQL products. (The SQL standard makesno specification about this, but it also doesn't know about VIRTUAL orSTORED.) (Also, virtual views are the default, rather thanmaterialized views.)Virtual generated columns are stored in tuples as null values. (Avery early version of this patch had the ambition to not store them atall. But so much stuff breaks or gets confused if you have tupleswhere a column in the middle is completely missing. This is acompromise, and it still saves space over being forced to use storedgenerated columns. If we ever find a way to improve this, a bit ofpg_upgrade cleverness could allow for upgrades to a newer scheme.)The capabilities and restrictions of virtual generated columns aremostly the same as for stored generated columns. In some cases, thispatch keeps virtual generated columns more restricted than they mighttechnically need to be, to keep the two kinds consistent. Some ofthat could maybe be relaxed later after separate carefulconsiderations.Some functionality that is currently not supported, but could possiblybe added as incremental features, some easier than others:- index on or using a virtual column- hence also no unique constraints on virtual columns- extended statistics on virtual columns- foreign-key constraints on virtual columns- not-null constraints on virtual columns (check constraints are supported)- ALTER TABLE / DROP EXPRESSION- virtual column cannot have domain type- virtual columns are not supported in logical replicationThe tests in generated_virtual.sql have been copied over fromgenerated_stored.sql with the keyword replaced. This way we can makesure the behavior is mostly aligned, and the differences can bevisible. Some tests for currently not supported features arecurrently commented out.Reviewed-by: Jian He <jian.universality@gmail.com>Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>Tested-by: Shlok Kyal <shlok.kyal.oss@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org
1 parentcbc1279 commit83ea6c5

File tree

71 files changed

+3225
-209
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

71 files changed

+3225
-209
lines changed

‎contrib/pageinspect/expected/page.out

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -208,6 +208,43 @@ select tuple_data_split('test8'::regclass, t_data, t_infomask, t_infomask2, t_bi
208208
(1 row)
209209

210210
drop table test8;
211+
-- check storage of generated columns
212+
-- stored
213+
create table test9s (a int not null, b int generated always as (a * 2) stored);
214+
insert into test9s values (131584);
215+
select raw_flags, t_bits, t_data
216+
from heap_page_items(get_raw_page('test9s', 0)), lateral heap_tuple_infomask_flags(t_infomask, t_infomask2);
217+
raw_flags | t_bits | t_data
218+
---------------------+--------+--------------------
219+
{HEAP_XMAX_INVALID} | | \x0002020000040400
220+
(1 row)
221+
222+
select tuple_data_split('test9s'::regclass, t_data, t_infomask, t_infomask2, t_bits)
223+
from heap_page_items(get_raw_page('test9s', 0));
224+
tuple_data_split
225+
-------------------------------
226+
{"\\x00020200","\\x00040400"}
227+
(1 row)
228+
229+
drop table test9s;
230+
-- virtual
231+
create table test9v (a int not null, b int generated always as (a * 2) virtual);
232+
insert into test9v values (131584);
233+
select raw_flags, t_bits, t_data
234+
from heap_page_items(get_raw_page('test9v', 0)), lateral heap_tuple_infomask_flags(t_infomask, t_infomask2);
235+
raw_flags | t_bits | t_data
236+
----------------------------------+----------+------------
237+
{HEAP_HASNULL,HEAP_XMAX_INVALID} | 10000000 | \x00020200
238+
(1 row)
239+
240+
select tuple_data_split('test9v'::regclass, t_data, t_infomask, t_infomask2, t_bits)
241+
from heap_page_items(get_raw_page('test9v', 0));
242+
tuple_data_split
243+
----------------------
244+
{"\\x00020200",NULL}
245+
(1 row)
246+
247+
drop table test9v;
211248
-- Failure with incorrect page size
212249
-- Suppress the DETAIL message, to allow the tests to work across various
213250
-- page sizes.

‎contrib/pageinspect/sql/page.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -84,6 +84,25 @@ select tuple_data_split('test8'::regclass, t_data, t_infomask, t_infomask2, t_bi
8484
from heap_page_items(get_raw_page('test8',0));
8585
droptable test8;
8686

87+
-- check storage of generated columns
88+
-- stored
89+
createtabletest9s (aintnot null, bint generated alwaysas (a*2) stored);
90+
insert into test9svalues (131584);
91+
select raw_flags, t_bits, t_data
92+
from heap_page_items(get_raw_page('test9s',0)), lateral heap_tuple_infomask_flags(t_infomask, t_infomask2);
93+
select tuple_data_split('test9s'::regclass, t_data, t_infomask, t_infomask2, t_bits)
94+
from heap_page_items(get_raw_page('test9s',0));
95+
droptable test9s;
96+
97+
-- virtual
98+
createtabletest9v (aintnot null, bint generated alwaysas (a*2) virtual);
99+
insert into test9vvalues (131584);
100+
select raw_flags, t_bits, t_data
101+
from heap_page_items(get_raw_page('test9v',0)), lateral heap_tuple_infomask_flags(t_infomask, t_infomask2);
102+
select tuple_data_split('test9v'::regclass, t_data, t_infomask, t_infomask2, t_bits)
103+
from heap_page_items(get_raw_page('test9v',0));
104+
droptable test9v;
105+
87106
-- Failure with incorrect page size
88107
-- Suppress the DETAIL message, to allow the tests to work across various
89108
-- page sizes.

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 42 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -7395,94 +7395,97 @@ select * from rem1;
73957395
-- ===================================================================
73967396
create table gloc1 (
73977397
a int,
7398-
b int generated always as (a * 2) stored);
7398+
b int generated always as (a * 2) stored,
7399+
c int
7400+
);
73997401
alter table gloc1 set (autovacuum_enabled = 'false');
74007402
create foreign table grem1 (
74017403
a int,
7402-
b int generated always as (a * 2) stored)
7403-
server loopback options(table_name 'gloc1');
7404+
b int generated always as (a * 2) stored,
7405+
c int generated always as (a * 3) virtual
7406+
) server loopback options(table_name 'gloc1');
74047407
explain (verbose, costs off)
74057408
insert into grem1 (a) values (1), (2);
7406-
QUERY PLAN
7407-
-------------------------------------------------------------------
7409+
QUERY PLAN
7410+
-------------------------------------------------------------------------------
74087411
Insert on public.grem1
7409-
Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
7412+
Remote SQL: INSERT INTO public.gloc1(a, b, c) VALUES ($1, DEFAULT, DEFAULT)
74107413
Batch Size: 1
74117414
-> Values Scan on "*VALUES*"
7412-
Output: "*VALUES*".column1, NULL::integer
7415+
Output: "*VALUES*".column1, NULL::integer, NULL::integer
74137416
(5 rows)
74147417

74157418
insert into grem1 (a) values (1), (2);
74167419
explain (verbose, costs off)
74177420
update grem1 set a = 22 where a = 2;
7418-
QUERY PLAN
7419-
------------------------------------------------------------------------------------
7421+
QUERY PLAN
7422+
----------------------------------------------------------------------------------------
74207423
Update on public.grem1
7421-
Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT WHERE ctid = $1
7424+
Remote SQL: UPDATE public.gloc1 SET a = $2, b = DEFAULT, c = DEFAULT WHERE ctid = $1
74227425
-> Foreign Scan on public.grem1
74237426
Output: 22, ctid, grem1.*
7424-
Remote SQL: SELECT a, b, ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
7427+
Remote SQL: SELECT a, b,c,ctid FROM public.gloc1 WHERE ((a = 2)) FOR UPDATE
74257428
(5 rows)
74267429

74277430
update grem1 set a = 22 where a = 2;
74287431
select * from gloc1;
7429-
a | b
7430-
----+----
7431-
1 | 2
7432-
22 | 44
7432+
a | b| c
7433+
----+----+---
7434+
1 | 2 |
7435+
22 | 44 |
74337436
(2 rows)
74347437

74357438
select * from grem1;
7436-
a | b
7437-
----+----
7438-
1 | 2
7439-
22 | 44
7439+
a | b| c
7440+
----+----+----
7441+
1 | 2 | 3
7442+
22 | 44 | 66
74407443
(2 rows)
74417444

74427445
delete from grem1;
74437446
-- test copy from
74447447
copy grem1 from stdin;
74457448
select * from gloc1;
7446-
a | b
7447-
---+---
7448-
1 | 2
7449-
2 | 4
7449+
a | b| c
7450+
---+---+---
7451+
1 | 2 |
7452+
2 | 4 |
74507453
(2 rows)
74517454

74527455
select * from grem1;
7453-
a | b
7454-
---+---
7455-
1 | 2
7456-
2 | 4
7456+
a | b| c
7457+
---+---+---
7458+
1 | 2 | 3
7459+
2 | 4 | 6
74577460
(2 rows)
74587461

74597462
delete from grem1;
74607463
-- test batch insert
74617464
alter server loopback options (add batch_size '10');
74627465
explain (verbose, costs off)
74637466
insert into grem1 (a) values (1), (2);
7464-
QUERY PLAN
7465-
-------------------------------------------------------------------
7467+
QUERY PLAN
7468+
-------------------------------------------------------------------------------
74667469
Insert on public.grem1
7467-
Remote SQL: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT)
7470+
Remote SQL: INSERT INTO public.gloc1(a, b, c) VALUES ($1, DEFAULT, DEFAULT)
74687471
Batch Size: 10
74697472
-> Values Scan on "*VALUES*"
7470-
Output: "*VALUES*".column1, NULL::integer
7473+
Output: "*VALUES*".column1, NULL::integer, NULL::integer
74717474
(5 rows)
74727475

74737476
insert into grem1 (a) values (1), (2);
74747477
select * from gloc1;
7475-
a | b
7476-
---+---
7477-
1 | 2
7478-
2 | 4
7478+
a | b| c
7479+
---+---+---
7480+
1 | 2 |
7481+
2 | 4 |
74797482
(2 rows)
74807483

74817484
select * from grem1;
7482-
a | b
7483-
---+---
7484-
1 | 2
7485-
2 | 4
7485+
a | b| c
7486+
---+---+---
7487+
1 | 2 | 3
7488+
2 | 4 | 6
74867489
(2 rows)
74877490

74887491
delete from grem1;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1859,12 +1859,15 @@ select * from rem1;
18591859
-- ===================================================================
18601860
createtablegloc1 (
18611861
aint,
1862-
bint generated alwaysas (a*2) stored);
1862+
bint generated alwaysas (a*2) stored,
1863+
cint
1864+
);
18631865
altertable gloc1set (autovacuum_enabled='false');
18641866
create foreign table grem1 (
18651867
aint,
1866-
bint generated alwaysas (a*2) stored)
1867-
server loopback options(table_name'gloc1');
1868+
bint generated alwaysas (a*2) stored,
1869+
cint generated alwaysas (a*3) virtual
1870+
) server loopback options(table_name'gloc1');
18681871
explain (verbose, costs off)
18691872
insert into grem1 (a)values (1), (2);
18701873
insert into grem1 (a)values (1), (2);

‎doc/src/sgml/catalogs.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1307,8 +1307,10 @@
13071307
</para>
13081308
<para>
13091309
If a zero byte (<literal>''</literal>), then not a generated column.
1310-
Otherwise, <literal>s</literal> = stored. (Other values might be added
1311-
in the future.)
1310+
Otherwise, <literal>s</literal> = stored, <literal>v</literal> =
1311+
virtual. A stored generated column is physically stored like a normal
1312+
column. A virtual generated column is physically stored as a null
1313+
value, with the actual value being computed at run time.
13121314
</para></entry>
13131315
</row>
13141316

‎doc/src/sgml/ddl.sgml

Lines changed: 36 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -361,7 +361,6 @@ INSERT INTO people (id, name, address) VALUES (<emphasis>DEFAULT</emphasis>, 'C'
361361
storage and is computed when it is read. Thus, a virtual generated column
362362
is similar to a view and a stored generated column is similar to a
363363
materialized view (except that it is always updated automatically).
364-
<productname>PostgreSQL</productname> currently implements only stored generated columns.
365364
</para>
366365

367366
<para>
@@ -371,12 +370,12 @@ INSERT INTO people (id, name, address) VALUES (<emphasis>DEFAULT</emphasis>, 'C'
371370
CREATE TABLE people (
372371
...,
373372
height_cm numeric,
374-
height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm / 2.54) STORED</emphasis>
373+
height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm / 2.54)</emphasis>
375374
);
376375
</programlisting>
377-
The keyword <literal>STORED</literal> must be specified to choosethe
378-
stored kind of generated column. See <xref linkend="sql-createtable"/> for
379-
more details.
376+
A generated column is by default of the virtual kind. Usethe keywords
377+
<literal>VIRTUAL</literal> or <literal>STORED</literal> to make the choice
378+
explicit. See <xref linkend="sql-createtable"/> formore details.
380379
</para>
381380

382381
<para>
@@ -442,12 +441,18 @@ CREATE TABLE people (
442441
<listitem>
443442
<para>
444443
If a parent column is a generated column, its child column must also
445-
be a generated column; however, the child column can have a
446-
different generation expression. The generation expression that is
444+
be a generated column of the same kind (stored or virtual); however,
445+
the child column can have a different generation expression.
446+
</para>
447+
448+
<para>
449+
For stored generated columns, the generation expression that is
447450
actually applied during insert or update of a row is the one
448-
associated with the table that the row is physically in.
449-
(This is unlike the behavior for column defaults: for those, the
450-
default value associated with the table named in the query applies.)
451+
associated with the table that the row is physically in. (This is
452+
unlike the behavior for column defaults: for those, the default value
453+
associated with the table named in the query applies.) For virtual
454+
generated columns, the generation expression of the table named in the
455+
query applies when a table is read.
451456
</para>
452457
</listitem>
453458
<listitem>
@@ -502,6 +507,26 @@ CREATE TABLE people (
502507
particular role can read from a generated column but not from the
503508
underlying base columns.
504509
</para>
510+
511+
<para>
512+
For virtual generated columns, this is only fully secure if the
513+
generation expression uses only leakproof functions (see <xref
514+
linkend="sql-createfunction"/>), but this is not enforced by the system.
515+
</para>
516+
</listitem>
517+
<listitem>
518+
<para>
519+
Privileges of functions used in generation expressions are checked when
520+
the expression is actually executed, on write or read respectively, as
521+
if the generation expression had been called directly from the query
522+
using the generated column. The user of a generated column must have
523+
permissions to call all functions used by the generation expression.
524+
Functions in the generation expression are executed with the privileges
525+
of the user executing the query or the function owner, depending on
526+
whether the functions are defined as <literal>SECURITY INVOKER</literal>
527+
or <literal>SECURITY DEFINER</literal>.
528+
<!-- matches create_view.sgml -->
529+
</para>
505530
</listitem>
506531
<listitem>
507532
<para>
@@ -519,6 +544,7 @@ CREATE TABLE people (
519544
<link linkend="sql-createpublication-params-with-publish-generated-columns">
520545
<literal>publish_generated_columns</literal></link> or by including them
521546
in the column list of the <command>CREATE PUBLICATION</command> command.
547+
This is currently only supported for stored generated columns.
522548
See <xref linkend="logical-replication-gencols"/> for details.
523549
</para>
524550
</listitem>

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -102,7 +102,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
102102
NULL |
103103
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
104104
DEFAULT <replaceable>default_expr</replaceable> |
105-
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
105+
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> )[STORED | VIRTUAL ] |
106106
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
107107
UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
108108
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
@@ -264,8 +264,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
264264
<listitem>
265265
<para>
266266
This form replaces the expression of a generated column. Existing data
267-
inthecolumn is rewritten and all the future changes will apply the new
268-
generation expression.
267+
ina stored generatedcolumn is rewritten and all the future changes
268+
will apply the newgeneration expression.
269269
</para>
270270
</listitem>
271271
</varlistentry>
@@ -279,10 +279,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
279279
longer apply the generation expression.
280280
</para>
281281

282+
<para>
283+
This form is currently only supported for stored generated columns (not
284+
virtual ones).
285+
</para>
286+
282287
<para>
283288
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
284-
column is not astoredgenerated column, no error is thrown. In this
285-
case anotice is issued instead.
289+
column is not a generated column, no error is thrown. In this case a
290+
notice is issued instead.
286291
</para>
287292
</listitem>
288293
</varlistentry>

‎doc/src/sgml/ref/create_foreign_table.sgml

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
4747
NULL |
4848
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
4949
DEFAULT <replaceable>default_expr</replaceable> |
50-
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED }
50+
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> )[STORED | VIRTUAL ] }
5151
[ ENFORCED | NOT ENFORCED ]
5252

5353
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -283,7 +283,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
283283
</varlistentry>
284284

285285
<varlistentry>
286-
<term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED</literal><indexterm><primary>generated column</primary></indexterm></term>
286+
<term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> )[STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term>
287287
<listitem>
288288
<para>
289289
This clause creates the column as a <firstterm>generated
@@ -292,10 +292,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
292292
</para>
293293

294294
<para>
295-
The keyword <literal>STORED</literal> is required to signify that the
295+
When <literal>VIRTUAL</literal> is specified, the column will be
296+
computed when it is read. (The foreign-data wrapper will see it as a
297+
null value in new rows and may choose to store it as a null value or
298+
ignore it altogether.) When <literal>STORED</literal> is specified, the
296299
column will be computed on write. (The computed value will be presented
297300
to the foreign-data wrapper for storage and must be returned on
298-
reading.)
301+
reading.) <literal>VIRTUAL</literal> is the default.
299302
</para>
300303

301304
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp