Thread: View not allowing to drop column (Bug or Feature enhancement )

Hello

On 05/16/2016 08:49 AM, Shrikant Bhende wrote:
> Hi all,
>
> While working on the view I came across an unusual behaviour of the view,
> PostgreSQL do not allows to drop a column from the view, whereas same
> pattern of Create and Replace view works while adding a column.
>
> Please find below test for the same.
>
> *
> *
> *Version info *
> *===========*
> postgres=# select version();
>                                                   version
> ----------------------------------------------------------------------------------------------------------
>   PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
> 20080704 (Red Hat 4.1.2-55), 64-bit
> (1 row)
> \d+ orgdata
>                  Table "public.orgdata"
>   Column  |         Type          | Modifiers | Storage  | Stats target
> | Description
> ---------+-----------------------+-----------+----------+--------------+-------------
>   id      | integer        | not null  | plain    |              |
>   name    | character varying(20) | not null  | extended |              |
>   address | character varying(20) |           | extended |              |
>   age     | integer        | not null  | plain    |              |
>   salary  | numeric(10,0)        |           | main     |              |
> Indexes:
>      "orgdata_pkey" PRIMARY KEY, btree (id)
> Triggers:
>      example_trigger AFTER INSERT ON orgdata FOR EACH ROW EXECUTE
> PROCEDURE auditlogfunc()
>
> *Creating view *
> postgres=# create or replace view vi1  as
> select id , name from orgdata ;
> CREATE VIEW
>
> *Alter command do not have any option to drop column*
> postgres=# alter view vi1
> ALTER COLUMN  OWNER TO      RENAME TO     SET SCHEMA

Neither there is an option to add a column.

> *
> *
> *To add columns it will work.*
> *========================*
> postgres=# create or replace view vi1 as
> postgres-# select id, name, age from orgdata ;
> CREATE VIEW

What you are doing is actually a CREATE OR REPLACE VIEW and not a ALTER
VIEW. In this case it is allowed to add columns *at the end of the list*
(see below).

> *
> *
> *While trying to drop a column by replacing view definition from view it
> throws an error saying cannot drop column from view.*
> *=====================================================================*
> postgres=# create or replace view vi1 as select
> id , name from orgdata ;
> *ERROR:  cannot drop columns from view*

You need to drop the view before recreating it. Then it works. If you
changed the access to the view with grants or revokes, you also neet to
recreate them. They are dropped with the view.

> If its not a bug and a limitation kindly guide me towards any
> documentation where it is mentioned.

http://www.postgresql.org/docs/current/static/sql-createview.html

CREATE OR REPLACE VIEW is similar, but if a view of the same name
already exists, it is replaced. The new query must generate the same
columns that were generated by the existing view query (that is, the
same column names in the same order and with the same data types), but
it may add additional columns to the end of the list. The calculations
giving rise to the output columns may be completely different.

Regards,
Charles

>
> Thanks.
> --
> Shrikant Bhende
> +91-9975543712

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org

On Mon, May 16, 2016 at 8:49 AM, Shrikant Bhende
<shrikantbhende.net@gmail.com> wrote:
> While working on the view I came across an unusual behaviour of the view,
> PostgreSQL do not allows to drop a column from the view, whereas same
> pattern of Create and Replace view works while adding a column.

This is probably because you are using create or replace, which is
normally used to switch things in place and so it needs them to be
compatible with the old ones. A view with an extra column can be used
instead of the old one, but a view with less columns can not. I do not
see the 'not dropping' part as unusual, and the 'can add columns',
well, I see them as a little unusual on a create or replace but I see
the point in hallowing it, so just a little.

> Alter command do not have any option to drop column
> postgres=# alter view vi1
> ALTER COLUMN  OWNER TO      RENAME TO     SET SCHEMA

Well, it is a view, not a table. They are basically shorthands for
queries and places to attach triggers, so its normal they do not have
as much management options.

> If its not a bug and a limitation kindly guide me towards any documentation
> where it is mentioned.

Right at the top of create view? :

>>>>
Description

CREATE VIEW defines a view of a query. The view is not physically
materialized. Instead, the query is run every time the view is
referenced in a query.

CREATE OR REPLACE VIEW is similar, but if a view of the same name
already exists, it is replaced. The new query must generate the same
columns that were generated by the existing view query (that is, the
same column names in the same order and with the same data types), but
it may add additional columns to the end of the list. The calculations
giving rise to the output columns may be completely different.
<<<<<<

Francisco Olarte.

Hello

Please post your answers to the list.

>     You need to drop the view before recreating it. Then it works. If
>     you changed the access to the view with grants or revokes, you also
>     neet to recreate them. They are dropped with the view.
>
>
> Sorry to say but If we need to drop and replace then what is use of
> "Create OR Replace " syntax?

I am not sure about the concrete rationale behind it, but the
replacement of a view is useful when you make changes in the way how you
select data from different tables without changing the structure of the
record returned by the view. In our company views are used a.o. as
interfaces to applications. They may change during development, but
later on they become stable. If the design of the tables behind the view
changes you may need to change the body of the view leaving the
interface the same. In this way you can optimize performance in the
database without forcing application developers to make changes to their
code.

Besides, dropping and creating a view costs nothing in terms of performance.

>         If its not a bug and a limitation kindly guide me towards any
>         documentation where it is mentioned.
>
>
>     http://www.postgresql.org/docs/current/static/sql-createview.html
>
>     CREATE OR REPLACE VIEW is similar, but if a view of the same name
>     already exists, it is replaced. The new query must generate the same
>     columns that were generated by the existing view query (that is, the
>     same column names in the same order and with the same data types),
>     but it may add additional columns to the end of the list. The
>     calculations giving rise to the output columns may be completely
>     different.
>
>
> If this is the limitation. Is community is planning update this or add
> this feature soon?

Unfortunately I have no answer to that, but somebody else may.
Regards
Charles

>
>
> --
>
> Thanks and Regards,
> Sachin Kotwal

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Motorenstrasse 18
CH - 8005 Zürich

http://www.swisspug.org

Hi:

On Mon, May 16, 2016 at 9:46 AM, Sachin Kotwal <kotsachin@gmail.com> wrote:
>> You need to drop the view before recreating it. Then it works. If you
>> changed the access to the view with grants or revokes, you also neet to
>> recreate them. They are dropped with the view.
> Sorry to say but If we need to drop and replace then what is use of "Create
> OR Replace " syntax?

That is the use case. Create or replace <whatever> is for doing
backwards compatible changes without having to worry about what other
things is running concurrently with you. If you make non-backwards
compatible changes the system forces you to drop, so you can see
dependencies and who is using the things before dropping ( unless you
go trigger happy with cascade ).

>>> If its not a bug and a limitation kindly guide me towards any
>>> documentation where it is mentioned.
>> CREATE OR REPLACE VIEW is similar, but if a view of the same name already
>> exists, it is replaced. The new query must generate the same columns that
....
> If this is the limitation. Is community is planning update this or add this
> feature soon?

IMO you've got it backwards. The limitation IS the feature. In my
case, as an example, I can code every view ( and functions ) in a
script file using create or replace. And when I find a bug / want to
make a backwards compatible improvement I can just edit the script and
fire it again, and this feature insures I do not impact other code if
I inadvertently rename a column, or delete it.

If the feature were to be removed, and backwards-incompatible changes
were allowed, a lot of people will be unhappy.

Francisco Olarte.