On Fri, Oct 6, 2023 at 9:14 AM Peter Eisentraut <pe...@eisentraut.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