ALTER TABLE without long waiting is possibly ?

From: Condor <condor(at)stz-bg(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ALTER TABLE without long waiting is possibly ?
Date: 2016-10-11 08:46:14
Message-ID: 3607ea2a36d900daa4095a418bb14bf3@stz-bg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hello,

today I need to alter one of our biggest tables to add two new columns
with default value 0.
Server version: PostgreSQL 9.5.4 on x86_64-slackware-linux-gnu, compiled
by x86_64-slackware-linux-gcc (GCC) 5.3.0, 64-bit

when I do: ALTER TABLE stocks ADD COLUMN promo INTEGER DEFAULT 0;

long waiting is coming, so I try to find a way how to avoid that
waiting. I know isn't possibly to alter table without lock it,
but Im wondering do will be more fast if I do:

ALTER TABLE stocks ADD COLUMN promo INTEGER;
UPDATE TABLE stocks SET promo = 0;
ALTER TABLE stocks ALTER COLUMN promo SET DEFAULT 0;

Unfortunately I can't test on product servers, so Im looking for some
advice or some one to point me the right direction how I can alter table
today without clients to notice their query is locked and need to wait.

Regards,
Hristo S.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Geoff Winkless 2016-10-11 09:59:33 Re: Transactional-DDL DROP/CREATE TABLE
Previous Message John R Pierce 2016-10-11 08:18:00 Re: ANN: Upscene releases Database Workbench 5.2.4