Re: ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression

On Fri, Oct 6, 2023 at 9:14 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> > Should we treat it the same fashion as ALTER COLUMN ... TYPE which
> > rewrites the column values? Of course that rewrites the whole table,
> > but logically they are comparable.
>
> I don't know. What are the semantics of that command with respect to
> triggers and logical decoding?

ALTER COLUMN ... TYPE doesn't fire triggers, and I don't think logical
decoding will do anything with it, either. As Amul also suggested, I
tend to think that this command should behave like that command
instead of inventing some new behavior. Sure, this is kind of like an
UPDATE, but it's also not actually an UPDATE: it's DDL. Consider this
example:

rhaas=# create table foo (a int, b text);
CREATE TABLE
rhaas=# create function nozero () returns trigger as $$begin if (new.b
= '0') then raise 'zero is bad'; end if; return new; end$$ language
plpgsql;
CREATE FUNCTION
rhaas=# create trigger fnz before insert or update or delete on foo
for each row execute function nozero();
CREATE TRIGGER
rhaas=# insert into foo values (1, '0');
ERROR: zero is bad
CONTEXT: PL/pgSQL function nozero() line 1 at RAISE
rhaas=# insert into foo values (1, '00');
INSERT 0 1
rhaas=# alter table foo alter column b set data type integer using b::integer;
ALTER TABLE
rhaas=# select * from foo;
a | b
---+---
1 | 0
(1 row)

rhaas=# insert into foo values (2, '0');
ERROR: type of parameter 14 (integer) does not match that when
preparing the plan (text)
CONTEXT: PL/pgSQL function nozero() line 1 at IF
rhaas=# \c
You are now connected to database "rhaas" as user "rhaas".
rhaas=# insert into foo values (2, '0');
ERROR: zero is bad
CONTEXT: PL/pgSQL function nozero() line 1 at RAISE
rhaas=# insert into foo values (2, '00');
ERROR: zero is bad
CONTEXT: PL/pgSQL function nozero() line 1 at RAISE

The trigger here is supposed to prevent me from inserting 0 into
column b, but I've ended up with one anyway, because when the column
was of type text, I could insert 00, and when I changed the column to
type integer, the value got smashed down to just 0, and the trigger
wasn't fired to prevent that. You could certainly argue with that
behavior, but I think it's pretty reasonable, and it seems like if
this command behaved that way too, that would also be pretty
reasonable. In fact, I'm inclined to think it would be preferable,
both for consistency and because it would be less work.

--
Robert Haas
EDB: http://www.enterprisedb.com