Tag: generated

Waiting for PostgreSQL 18 – Virtual generated columns

On 7th of February 2025, Peter Eisentraut committed patch:

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 are
computed on write, like a materialized view).
 
The syntax for the column definition is
 
    ... GENERATED ALWAYS AS (...) VIRTUAL
 
and VIRTUAL is also optional.  VIRTUAL is the default rather than
STORED to match various other SQL products.  (The SQL standard makes
no specification about this, but it also doesn't know about VIRTUAL or
STORED.)  (Also, virtual views are the default, rather than
materialized views.)
 
Virtual generated columns are stored in tuples as null values.  (A
very early version of this patch had the ambition to not store them at
all.  But so much stuff breaks or gets confused if you have tuples
where a column in the middle is completely missing.  This is a
compromise, and it still saves space over being forced to use stored
generated columns.  If we ever find a way to improve this, a bit of
pg_upgrade cleverness could allow for upgrades to a newer scheme.)
 
The capabilities and restrictions of virtual generated columns are
mostly the same as for stored generated columns.  In some cases, this
patch keeps virtual generated columns more restricted than they might
technically need to be, to keep the two kinds consistent.  Some of
that could maybe be relaxed later after separate careful
considerations.
 
Some functionality that is currently not supported, but could possibly
be 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 replication
 
The tests in generated_virtual.sql have been copied over from
generated_stored.sql with the keyword replaced.  This way we can make
sure the behavior is mostly aligned, and the differences can be
visible.  Some tests for currently not supported features are
currently 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

Continue reading Waiting for PostgreSQL 18 – Virtual generated columns

Waiting for PostgreSQL 17 – Support identity columns in partitioned tables

On 16th of January 2024, Peter Eisentraut committed patch:

Support identity columns in partitioned tables
 
Previously, identity columns were disallowed on partitioned tables.
(The reason was mainly that no one had gotten around to working
through all the details to make it work.)  This makes it work now.
 
Some details on the behavior:
 
* A newly created partition inherits identity property
 
  The partitions of a partitioned table are integral part of the
  partitioned table.  A partition inherits identity columns from the
  partitioned table.  An identity column of a partition shares the
  identity space with the corresponding column of the partitioned
  table.  In other words, the same identity column across all
  partitions of a partitioned table share the same identity space.
  This is effected by sharing the same underlying sequence.
 
  When INSERTing directly into a partition, the sequence associated
  with the topmost partitioned table is used to calculate the value of
  the corresponding identity column.
 
  In regular inheritance, identity columns and their properties in a
  child table are independent of those in its parent tables.  A child
  table does not inherit identity columns or their properties
  automatically from the parent.  (This is unchanged.)
 
* Attached partition inherits identity column
 
  A table being attached as a partition inherits the identity property
  from the partitioned table.  This should be fine since we expect
  that the partition table's column has the same type as the
  partitioned table's corresponding column.  If the table being
  attached is a partitioned table, the identity properties are
  propagated down its partition hierarchy.
 
  An identity column in the partitioned table is also marked as NOT
  NULL.  The corresponding column in the partition needs to be marked
  as NOT NULL for the attach to succeed.
 
* Drop identity property when detaching partition
 
  A partition's identity column shares the identity space
  (i.e. underlying sequence) as the corresponding column of the
  partitioned table.  If a partition is detached it can longer share
  the identity space as before.  Hence the identity columns of the
  partition being detached loose their identity property.
 
  When identity of a column of a regular table is dropped it retains
  the NOT NULL constraint that came with the identity property.
  Similarly the columns of the partition being detached retain the NOT
  NULL constraints that came with identity property, even though the
  identity property itself is lost.
 
  The sequence associated with the identity property is linked to the
  partitioned table (and not the partition being detached).  That
  sequence is not dropped as part of detach operation.
 
* Partitions with their own identity columns are not allowed.
 
* The usual ALTER operations (add identity column, add identity
  property to existing column, alter properties of an indentity
  column, drop identity property) are supported for partitioned
  tables.  Changing a column only in a partitioned table or a
  partition is not allowed; the change needs to be applied to the
  whole partition hierarchy.
 
Author: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/CAExHW5uOykuTC+C6R1yDSp=o8Q83jr8xJdZxgPkxfZ1Ue5RRGg@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Support identity columns in partitioned tables

Waiting for PostgreSQL 17 – ALTER TABLE command to change generation expression

On 4th of January 2024, Peter Eisentraut committed patch:

ALTER TABLE command to change generation expression
 
This adds a new ALTER TABLE subcommand ALTER COLUMN ... SET EXPRESSION
that changes the generation expression of a generated column.
 
The syntax is not standard but was adapted from other SQL
implementations.
 
This command causes a table rewrite, using the usual ALTER TABLE
mechanisms.  The implementation is similar to and makes use of some of
the infrastructure of the SET DATA TYPE subcommand (for example,
rebuilding constraints and indexes afterwards).  The new command
requires a new pass in AlterTablePass, and the ADD COLUMN pass had to
be moved earlier so that combinations of ADD COLUMN and SET EXPRESSION
can work.
 
Author: Amul Sul <sulamul@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b94yyJeGA-5M951_Lr+KfZokOp-2kXicpmEhi5FXhBeTog@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – ALTER TABLE command to change generation expression

Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

Title: Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

On 14th of January 2020, Peter Eisentraut committed patch:

ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION 
 
Add an ALTER TABLE subcommand for dropping the generated property from
a column, per SQL standard.
 
Discussion: https://www.postgresql.org/message-id/flat/-946e-0453-d841-%402ndquadrant.com

Continue reading Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

Waiting for PostgreSQL 12 – Generated columns

On 30th of March 2019, Peter Eisentraut committed patch:

Generated columns
 
This is an SQL-standard feature that allows creating columns that are
computed from expressions rather than assigned, similar to a view or
materialized view but on a column basis.
 
This implements one kind of generated column: stored (computed on
write).  Another kind, virtual (computed on read), is planned for the
future, and some room is left for it.
 
Discussion: https://www.postgresql.org/message-id/flat/-4019-bdb1-699e-@2ndquadrant.com

Continue reading Waiting for PostgreSQL 12 – Generated columns

Waiting for PostgreSQL 10 – Identity columns

On 6th of April 2017, Peter Eisentraut committed patch:

Identity columns
 
This is the SQL standard-conforming variant of PostgreSQL's serial
columns.  It fixes a few usability issues that serial columns have:
 
- CREATE TABLE / LIKE copies default but refers to same sequence
- cannot add/drop serialness with ALTER TABLE
- dropping default does not drop sequence
- need to grant separate privileges to sequence
- other slight weirdnesses because serial is some kind of special macro

Continue reading Waiting for PostgreSQL 10 – Identity columns