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:


-- 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;


-- I have also noticied the following behaviour when using the SET
command with incorrect option names

SET anythingyoulike = 1,2

--will cause the error to be reported as ERROR: SET anythingyoulike
takes only one argument

SET anythingyoulike = 1
--will cause the error to be reported correctly ('anythingyoulike' is
not a valid option name)

6 Sep 2002 03:54:37 -0700
tim@ametco.co.uk (Tim Knowles)
7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
On Fri, 2002-09-06 at 06:54, Tim Knowles wrote:
> 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

This has to do with the way the JOIN currently functions.  At the moment
the JOIN nodes record an alias which has all columns listed, which is
appropriately picked up by the dependency code.

Tom is debating whether or not the alias on columns not used in the
where or clause or returned is strictly necessary.

Indeed, if you delete the dependencies, then drop the column the view
continues to function but I'm not sure thats always the case.

 
-- 
  Rod Taylor


09 Sep 2002 10:15:56 -0400
Rod Taylor <rbt@rbt.ca>
Re: 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM