ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
| От | Jeff Davis |
|---|---|
| Тема | ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML |
| Дата | |
| Msg-id | bf4d04eecf4fe9830ce2674c3644b405ad978aa0.camel@j-davis.com обсуждение исходный текст |
| Ответы |
Re: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
|
| Список | pgsql-bugs |
This doesn't represent an actual use case, it's just a contrived test. The docs say: "Adding a column with a volatile DEFAULT (e.g., clock_timestamp()), a stored generated column, an identity column, or a column with a domain data type that has constraints will cause the entire table and its indexes to be rewritten. Adding a virtual generated column never requires a rewrite." https://www.postgresql.org/docs/current/sql-altertable.html (in Notes section). The following SQL seems to lose the updates during the ALTER: CREATE TABLE t(id INT); INSERT INTO t VALUES (1), (2); CREATE FUNCTION f() RETURNS INT VOLATILE AS $$ BEGIN UPDATE t SET id = id + 10; RETURN (SELECT MAX(id) FROM t); END $$ LANGUAGE plpgsql; ALTER TABLE t ADD COLUMN c INT DEFAULT f(); SELECT * FROM t; id | c ----+---- 1 | 12 2 | 22 (2 rows) It happens because the updates happen on the old heap while the function is being evaluated, and the old heap is thrown away. But uncontrolled DML happening during an ALTER seems hard to even define, so I'm not sure how to fix it, or if we even need to fix it. Thoughts? Regards, Jeff Davis
В списке pgsql-bugs по дате отправления: