pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
3+ messages / 2 participants
[nested] [flat]
* ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
@ 2025-11-04 18:14 Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jeff Davis @ 2025-11-04 18:14 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
@ 2025-11-04 18:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-11-04 18:22 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-bugs
Jeff Davis <pgsql@j-davis.com> writes:
> 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)
Hmm ... ideally we'd throw an error for that. CheckTableNotInUse()
intends to prevent some similar cases, but it misses this one because
ALTER TABLE is the outermost command and there's no check performed
at the inner UPDATE. Not sure how hard we need to work at preventing
people from shooting themselves in the foot, though.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
@ 2025-11-04 19:20 Jeff Davis <pgsql@j-davis.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jeff Davis @ 2025-11-04 19:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs
On Tue, 2025-11-04 at 13:22 -0500, Tom Lane wrote:
> Hmm ... ideally we'd throw an error for that. CheckTableNotInUse()
> intends to prevent some similar cases, but it misses this one because
> ALTER TABLE is the outermost command and there's no check performed
> at the inner UPDATE. Not sure how hard we need to work at preventing
> people from shooting themselves in the foot, though.
I poked around at a few ideas and they seemed to get invasive. Perhaps
the cure is worse than the disease, unless someone has an idea.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-11-04 19:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-04 18:14 ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML Jeff Davis <pgsql@j-davis.com>
2025-11-04 18:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-11-04 19:20 ` Jeff Davis <pgsql@j-davis.com>
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