SEO

#1 (**permalink**) 04-24-2008, 06:12 PM

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#2 (**permalink**) 04-24-2008, 06:12 PM

"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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#3 (**permalink**) 04-24-2008, 06:12 PM

> > 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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

BUG #4125: Strange ALTER TABLE behavior