Thread: live metadata changes v8.3.4

On Tue, Sep 27, 2011 at 1:51 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

> How does one make a metadata change to a DB that's actively being used.
> Specifically, I want to drop a view, drop some columns from a table that's
> used in the view, recreate the view without those columns.

BEGIN TRANSACTION;

DROP VIEW someView ...;

ALTER TABLE DROP COLUMN someColumn1, DROP COLUMN someColumn2;

CREATE VIEW someView AS ...;

COMMIT; --Wait for pre-existing locks to complete and hope your client
app doesn't break.


--
Regards,
Richard Broersma Jr.

On Tue, Sep 27, 2011 at 01:51:42PM -0700, Gauthier, Dave wrote:
> Hi:
>
> How does one make a metadata change to a DB that's actively being
> used.  Specifically, I want to drop a view, drop some columns from a
> table that's used in the view, recreate the view without those
> columns.
>
> In the past, I've resorted to connecting as a super user, running
> "select procpid from pg_stat_activity..." then pg_ctl kill ABRT
> <procpid>.  This would create a window where I could get in and make
> the change.  But it also created some angry users whos processes got
> killed.

You have the choice between taking those users offline and not doing
the change.

> V8.3.4 on linux.

Upgrade to 8.3.16 immediately, if not sooner.  Oh, and start planning
the 9.1 migration, too.  December of 2012 is closer than you think. :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate