Обсуждение: BUG #4125: Strange ALTER TABLE behavior
The following bug has been logged online: Bug reference: 4125 Logged by: Vladimir Kanazir Email address: vladimir@vlajko.com PostgreSQL version: 8.2.6 Operating system: Fedora 7 Description: Strange ALTER TABLE behavior Details: 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). 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. I think that alter table with default values should work the same way.
"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