Re: ALTER TABLE ... ADD COLUMN ... DEFAULT with volatile function loses DML - Mailing list 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