Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id E317EB5DBD6
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 17 Jun 2011 06:59:51 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 46435-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 17 Jun 2011 09:59:45 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-wy0-f196.google.com (mail-wy0-f196.google.com
	[74.125.82.196])
	by mail.postgresql.org (Postfix) with ESMTP id 67C89B5D944
	for <pgsql-admin@postgresql.org>; Fri, 17 Jun 2011 06:59:45 -0300 (ADT)
Received: by wyf28 with SMTP id 28so366381wyf.7
	for <pgsql-admin@postgresql.org>; Fri, 17 Jun 2011 02:59:44 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:date:message-id:subject:from:to
	:content-type; bh=ShO5jzDflAmbcseTY51eCk7AEEtU5n1IWv+N6CGrCHM=;
	b=WvWHPQ4Wup+5mqZzh/B4ZVQWF+KXTkew4y+l6uDQTv1KVUl8d1vCKH9ysq0xFKa5iF
	0djUsoS0ww0mbbxVNwE4IKkwaYzPex/ANbxnmqRz2Rnfy1R0GSBvlyJjU0VeOn5vF/tw
	Yzyx29uxWEunlS8dQN6atqsnk2P1J0bd/6iKI=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=dZ8YpJOydJgnv6IV2Y0cV6GrQY7ktEJrsd4G4UPXbjdSJLng/Y179Y3aiB2La3oQPE
	4rHbF1zHwcXwZw2ORWPNFgrH7LL702Z4I99uMK7kSBjDf+xGqSzrrb87gudebfsGj2ll
	qWxoP9v5tm72+BBgcIAQUmwbUKZ6RomWh6+nk=
MIME-Version: 1.0
Received: by 10.216.221.200 with SMTP id r50mr1975452wep.102.1308304784789;
	Fri, 17 Jun 2011 02:59:44 -0700 (PDT)
Received: by 10.216.3.135 with HTTP; Fri, 17 Jun 2011 02:59:44 -0700 (PDT)
Date: Fri, 17 Jun 2011 11:59:44 +0200
Message-ID: <BANLkTikh0czdScJ2mKR7XPYZkregdinfOA@mail.gmail.com>
Subject: Re: alter table without an ACCESS EXCLUSIVE lock
From: Andreas Berger <4postgres@gmail.com>
To: tgl@sss.pgh.pa.us, pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary=0016364d22a7f4906804a5e5712a
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.887 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001,
	RFC_ABUSE_POST=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201106/104
X-Sequence-Number: 37730

--0016364d22a7f4906804a5e5712a
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks for your answer!
In this spacial case your suggestion is a solution, but I am searching
for an opportunity to make every change (e.g. alter table) without
effecting availability of the database and the modified table when using
wal shipping (maybe something like concurrently when creating an index).
When using skytools I can keep the downtime minimal by doing a master
slave change after modifying the slave db. But the question now is, is
it possible to use wal shipping without having downtimes when modifying
tables?

regards,
Andi


On 2011-06-16 20:21, Tom Lane wrote:

> Andreas Berger <4postgres@gmail.com> <4postgres@gmail.com> writes:

>> i'm searching for a solution for changing the type of a column, e.g. fro=
m>> varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock.

> > That specific case (increasing the max length of a varchar column) coul=
d> be handled by hacking the pg_attribute.atttypmod entry for the column,> =
since no change in the actual data is required.> > However, I have no idea =
whether your replication environment would cope> with such a change --- exp=
erimenting on a non-production setup would be> highly recommendable.> > 			=
regards, tom lane

--0016364d22a7f4906804a5e5712a
Content-Type: text/html; charset=ISO-8859-1

<pre>Thanks for your answer!
In this spacial case your suggestion is a solution, but I am searching
for an opportunity to make every change (e.g. alter table) without
effecting availability of the database and the modified table when using
wal shipping (maybe something like concurrently when creating an index).
When using skytools I can keep the downtime minimal by doing a master
slave change after modifying the slave db. But the question now is, is
it possible to use wal shipping without having downtimes when modifying
tables?

regards,
Andi


On 2011-06-16 20:21, Tom Lane wrote:
</pre><blockquote type="cite" style="color: rgb(0, 0, 0);"><pre><span class="moz-txt-citetags">&gt; </span>Andreas Berger <a class="moz-txt-link-rfc2396E" href="mailto:4postgres@gmail.com">&lt;4postgres@gmail.com&gt;</a> writes:
</pre><blockquote type="cite" style="color: rgb(0, 0, 0);"><pre><span class="moz-txt-citetags">&gt;&gt; </span>i&#39;m searching for a solution for changing the type of a column, e.g. from
<span class="moz-txt-citetags">&gt;&gt; </span>varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock.
</pre></blockquote><pre><span class="moz-txt-citetags">&gt; </span>
<span class="moz-txt-citetags">&gt; </span>That specific case (increasing the max length of a varchar column) could
<span class="moz-txt-citetags">&gt; </span>be handled by hacking the pg_attribute.atttypmod entry for the column,
<span class="moz-txt-citetags">&gt; </span>since no change in the actual data is required.
<span class="moz-txt-citetags">&gt; </span>
<span class="moz-txt-citetags">&gt; </span>However, I have no idea whether your replication environment would cope
<span class="moz-txt-citetags">&gt; </span>with such a change --- experimenting on a non-production setup would be
<span class="moz-txt-citetags">&gt; </span>highly recommendable.
<span class="moz-txt-citetags">&gt; </span>
<span class="moz-txt-citetags">&gt; </span>			regards, tom lane
</pre></blockquote>

--0016364d22a7f4906804a5e5712a--