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@postgresql.org wrote:
> Tim Knowles (tim@ametco.co.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,
eventhough the column being dropped is not used at all in the view. If you join the tables in the WHERE clause the
columncan 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@postgresql.org so that your
> message can get through to the mailing list cleanly
>