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@xxxxxxxxx> wrote in message news:b918cf3d05020303496208d4ea@xxxxxxxxxxxxxxxxx > On Wed, 2 Feb 2005 11:16:56 +0200, Sim Zacks <sim@xxxxxxxxxx> 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@xxxxxxxxx > 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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
