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
In-reply-to: <bf4d04eecf4fe9830ce2674c3644b405ad978aa0.camel@j-davis.com>
References: <bf4d04eecf4fe9830ce2674c3644b405ad978aa0.camel@j-davis.com>
Comments: In-reply-to Jeff Davis <pgsql@j-davis.com>
	message dated "Tue, 04 Nov 2025 10:14:08 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <650758.1762280552.1@sss.pgh.pa.us>
Date: Tue, 04 Nov 2025 13:22:32 -0500
Message-ID: <650759.1762280552@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/650759.1762280552%40sss.pgh.pa.us>
Precedence: bulk

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