[
https://issues.apache.org/jira/browse/IGNITE-21384?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17815352#comment-17815352
]
Maksim Myskov commented on IGNITE-21384:
----------------------------------------
[~amashenkov]
The same test (PostgreSQL), but using gen_random_uuid() function instead of
currnet_timestamp():
{noformat}
CREATE TABLE tbl(id INTEGER PRIMARY KEY);
INSERT INTO TBL VALUES(0);
INSERT INTO TBL VALUES(1);
INSERT INTO TBL VALUES(2);
INSERT INTO TBL VALUES(3);
ALTER TABLE tbl ADD COLUMN col1 UUID DEFAULT gen_random_uuid();
INSERT INTO TBL VALUES(4);
select * from tbl;{noformat}
The result:
{noformat}
0,71ec8ce9-85df-407a-9642-1bb77a002159
1,cf39c1ae-4ebf-47b1-b900-015acf40fc40
2,0b9d62ab-7d5f-4888-857e-44f204ae0465
3,8c3ae38f-1c36-4101-ad91-876f65bc1ede
4,9fb4fb97-c1d2-4fec-a3ec-9c368ffef6b8
{noformat}
If we go "evaluate once" approach, we will never achieve the above.
> Initialize all rows with new column's default value
> ---------------------------------------------------
>
> Key: IGNITE-21384
> URL: https://issues.apache.org/jira/browse/IGNITE-21384
> Project: Ignite
> Issue Type: New Feature
> Reporter: Maksim Myskov
> Priority: Major
> Labels: ignite-3
>
> In case of adding a new column with a default value specified (for example:
> ALTER TABLE ADD COLUMN col INT DEFAULT 10), all existing rows (that were
> added before "alter table") must be initialized with a new value.
> h3. Current behaviour
> The default value of a column is evaluated on read.
> h3. Motivation
> This is a pre-requisite for adding arbitrary expressions as default values.
> Let's imagine a simple case:
> {code:sql}
> ALTER TABLE ADD COLUMN modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
> {code}
> With the current behavior for all existing rows the value of "modified"
> column will be evaluated on every read.
> Many DBs use the proposed behavior:
> * PostgreSQL
> * CockroachDB
> * Yugabyte
> For example, from PostgreSQL
> [docs|https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-NOTES]:
> {noformat}
> Adding a column with a volatile DEFAULT or changing the type of an existing
> column will require the entire table and its indexes to be rewritten.
> {noformat}
> h3. Downsides
> Adding a new column with a default value will be a much more expensive
> operation and can take a significant time on large tables
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
