Thread: modifying views

modifying views

From
"Sim Zacks"
Date:
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.

Even adding a column to a view isn't allowed. So if I want a new field in my
table to be accessible in the same manner that the view would be accessible
in, I would have to go through the same process of dropping 100 views,
making my change and then recreating 100 views. Talk about inefficient.

Are there any realistic methods for changing the number of columns in a
view? Is it possible to manually modify the backend? I looked at the
pg_views view SQL and it seemed to work completely internally.
the definition is called by this function "pg_get_viewdef(c.oid) AS
definition"
and the pg_get_viewdef(oid) function  just calls 'pg_get_viewdef' with a
language Internal (as shown below)

CREATE OR REPLACE FUNCTION pg_get_viewdef(oid)
  RETURNS text AS
'pg_get_viewdef'
  LANGUAGE 'internal' STABLE STRICT;
GRANT EXECUTE ON FUNCTION pg_get_viewdef(oid) TO public;
COMMENT ON FUNCTION pg_get_viewdef(oid) IS 'select statement of a view';

I could use any help that you can give me.
Thanks
Sim



Re: modifying views

From
Mike Rylander
Date:
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

Re: modifying views

From
"Sim Zacks"
Date:
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
>