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