Re: alter table without an ACCESS EXCLUSIVE lock

From: Andreas Pötzlberger <andreas(dot)poetzlberger(at)payon(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: alter table without an ACCESS EXCLUSIVE lock
Date: 2011-06-17 08:50:00
Message-ID: 4DFB1538.5060707@payon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)gmail(dot)com> writes:
>> i'm searching for a solution for changing the type of a column, e.g. from
>> varchar(128) to varchar(512), without an ACCESS EXCLUSIVE lock.
>
> That specific case (increasing the max length of a varchar column) could
> 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 --- experimenting on a non-production setup would be
> highly recommendable.
>
> regards, tom lane

--
Andreas Pötzlberger

PAY.ON | www.payon.com
Payment Technologies for Global Payment Solutions

Jakob-Haringer-Str.1 | 5020 Salzburg | Austria
mail: andreas(dot)poetzlberger(at)payon(dot)com
phone DE: +49 89 45230 406
fax DE: +49 89 45230 407
phone AT: +43 662 890008 15
fax AT: +43 662 890008 99

PAY.ON AG
Court: HR Munich | Docket-#: HRB 173756 | VAT-ID: DE 234431573
Executive Board: Markus Rinderer (Head), Nikolaus von Taysen
Head of Supervisory Board: Alan Goslar

PAY.ON GmbH Austria
Court: LG Salzburg | Docket-#: FN 315081 f | VAT-ID: ATU64439405
Managing Director: Christian Bamberger

This email and any attachments are issued by PAY.ON. It is
confidential and intended for the exclusive use of the addressee only.
You should not disclose its contents to any other person. If you are not
the addressee (or responsible for delivery of the message to the
addressee), please notify the originator immediately by return message
and destroy the original message.
This message and any attachments have been scanned for viruses prior
leaving PAY.ON; however, PAY.ON does not guarantee the security of this
message and will not be responsible for any damages arising as a result
of any virus being passed on or arising from any alteration of this
message by a third party. PAY.ON may monitor emails sent to and from
PAY.ON.

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Andreas Berger 2011-06-17 09:59:44 Re: alter table without an ACCESS EXCLUSIVE lock
Previous Message Martin Münstermann 2011-06-17 08:40:57 archive_command vs. cp -i