Re: modifying views - Mailing list pgsql-general

Thank you Mike, that should save me tons of time. I was dreading trying to
find all the dependencies, every time I want to make a change.

This way isn't perfect, but if it will do the job, that's what I need.

"Mike Rylander" <mrylander@gmail.com> wrote in message
news:b918cf3d05020303496208d4ea@mail.gmail.com...
> On Wed, 2 Feb 2005 11:16:56 +0200, Sim Zacks <sim@nospam.com> wrote:
> > I read the following thread from Nov 2002 on the impossibilities of
> > modifying a view and I was wondering if anything had changed in this
regard
> > since then?
> > http://archives.postgresql.org/pgsql-hackers/2002-11/msg00609.php
> >
> > Basically I want to remove a column from a table. The column is used in
a
> > view. The view, but not the column that I want to remove, is used in 24
> > other views. A number of those views are also used in other views...
> >
> > I can't remove the columns from the view without dropping over 100 other
> > views, removing the columns from this view and recreating them. I was
able
> > to remove the columns from the table by modifying the view so instead of
> > "table1.field1" it has "null::varchar as field1"
> > The problem is that the field is still in the view and I don't want it
there
> > (obviously).
> >
> > I read somewhere that a possible solution would be to do a pg_dump,
manually
> > change the text file and then do a pg_restore. Unfortunately, that means
> > taking the system offline, which I can't do.
>
> Actually, you shouldn't have to take the system down at all.
>
> 1) Do a 'pg_dump -s', which will give you just the schema of the DB.
>
> 2) Trim this file down to the create statements for the table and all
> the dependant views, add the "DROP VIEW baseview CASCADE" and "ALTER
> TABLE ... DROP COLUMN ..." statements to the top, and change the base
> view's definition.
>
> 2a) (this is the REALLY important part!) put "BEGIN;" at the top and
> DO NOT(!!!) put "COMMIT;" at the bottom, but DO put some test SELECTs
> that will touch the base view and the dependant views to make sure
> they are intact, and that the column is gone.
>
> 3) Use \i from within the psql console to run that SQL script.  The
> script will be run inside a transaction, and the changes wont be
> visible to anyone else until you type COMMIT;.
>
> If the output of the test SELECTs looks good just commit the change.
> If you are unsure, or something seems to have gone wrong just type
> "ROLLBACK;" and everything will be back the way is was before!
>
> The only downtime will be the few seconds it takes to alter the table,
> recreate the views, and inspect that everything is still OK.  If you
> want to test safely you could do a full load of 'pg_dump -s' (again,
> just the schema) into a temp database and test the script there.
>
>
> > I could use any help that you can give me.
> > Thanks
> > Sim
>
> Hope that counts as help!
>
> --
> Mike Rylander
> mrylander@gmail.com
> GPLS -- PINES Development
> Database Developer
> http://open-ils.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>