BUG #4125: Strange ALTER TABLE behavior

"Vladimir Kanazir" <vladimir@vlajko.com> writes:

If I execute this query:
alter table history add incoming_id bigint default 0;

the table will be locked until operation finishes (checked by executing the
select query on the same table via another connection).

Indeed, because it has to physically fill the value zero into every
table row.

But, if I do this:
alter table history add incoming_id bigint;
alter table history alter incoming_id set default 0;
update history set incoming_id=0;
the table will be locked only during alter table execution, which is very
short time.

But this exposes the state where incoming_id isn't zero to other
transactions.

regards, tom lane

But, if I do this:
alter table history add incoming_id bigint;
alter table history alter incoming_id set default 0;
update history set incoming_id=0;
the table will be locked only during alter table execution, which
is very short time.

But this exposes the state where incoming_id isn't zero to other
transactions.

Yes, I can see your point... But if there is a system running, this will
lock it up even current transactions doesn't care about the new field.
That is how I noticed this :)

But, I guess, you can't make everybody happy :)
Thanks
--
Regards
Vladimir Kanazir