From: Jeff Davis <pgsql@j-davis.com>
To: pgsql-bugs@postgresql.org
Subject: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
Date: Tue, 04 Nov 2025 10:14:08 -0800
Message-ID: <bf4d04eecf4fe9830ce2674c3644b405ad978aa0.camel@j-davis.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: pgsql@j-davis.com
Subject: Re: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
In-Reply-To: <bf4d04eecf4fe9830ce2674c3644b405ad978aa0.camel@j-davis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox