From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
Date: Tue, 04 Nov 2025 13:22:32 -0500
Message-ID: <650759.1762280552@sss.pgh.pa.us> (raw)
In-Reply-To: <bf4d04eecf4fe9830ce2674c3644b405ad978aa0.camel@j-davis.com>
References: <bf4d04eecf4fe9830ce2674c3644b405ad978aa0.camel@j-davis.com>
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
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: tgl@sss.pgh.pa.us, pgsql@j-davis.com
Subject: Re: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML
In-Reply-To: <650759.1762280552@sss.pgh.pa.us>
* 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