Re: Bug #759: 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM

Indeed. At the INNER JOIN it would appear that an alias is applied to
the columns of a given table.

{ RTE :alias { ALIAS :aliasname b :colnames <>} :eref { ALIAS :aliasname
b :colnames ( "col_b" "col_c" )} :rtekind 0 :relid 17194 :inh true
:inFromCl true :checkForRead true :checkForWrite false :checkAsUser 1})

The dependency mechanism sees col_c and marks it as a requirement of
this -- which it is. Removal of col_c will break this view.

The real trick is to make INNER JOINS less greedy in their requirements
based on the columns that are actually used. This could be a large
undertaking however.

On Fri, 2002-09-06 at 04:31, pgsql-bugs(at)postgresql(dot)org wrote:
> Tim Knowles (tim(at)ametco(dot)co(dot)uk) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
>
> Long Description
> Hi,
>
> Been playing with the 7.3beta1 version and I've noticed a small problem with dependency checking when dropping a column. If you have a view which uses JOIN's to join tables then dropping a column will fail on a dependency check, even though the column being dropped is not used at all in the view. If you join the tables in the WHERE clause the column can be dropped without problems.
>
> Please see below some example SQL to demonstrate:
>
> Sample Code
> -- wrap it all up in a transaction so we don't do anything permanent
>
> BEGIN;
>
> CREATE TABLE table1 (col_a text, col_b int);
> CREATE TABLE table2 (col_b int, col_c text);
>
> CREATE VIEW tester1 AS SELECT A.col_a,B.col_b FROM table1 A, table2 B WHERE (b.col_b=a.col_b);
>
> CREATE VIEW tester2 AS SELECT A.col_a,B.col_b FROM table2 B INNER JOIN table1 A ON (b.col_b=a.col_b);
>
> --Now try and drop column col_c from table2
> ALTER TABLE table2 DROP COLUMN col_c RESTRICT;
>
> --You should now get an error to say that col_c is a dependent object in view tester2
>
> ROLLBACK;
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>