Thread: Bug #759: 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
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
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
>
Rod Taylor <rbt@zort.ca> writes:
> Indeed. At the INNER JOIN it would appear that an alias is applied to
> the columns of a given table.
> ...
> The real trick is to make INNER JOINS less greedy in their requirements
> based on the columns that are actually used.
What surprised me about this report was not that the JOIN syntax exposed
a dependency on column c, but that the non-JOIN syntax didn't. There
shouldn't be any semantic difference AFAIR, so it seems to me that at
least one of these behaviors needs to be fixed.
I am not sure that it's practical to remove the dependency as Tim is
hoping for...
regards, tom lane
On Fri, 2002-09-06 at 09:17, Tom Lane wrote: > Rod Taylor <rbt@zort.ca> writes: > > Indeed. At the INNER JOIN it would appear that an alias is applied to > > the columns of a given table. > > ... > > The real trick is to make INNER JOINS less greedy in their requirements > > based on the columns that are actually used. > > What surprised me about this report was not that the JOIN syntax exposed > a dependency on column c, but that the non-JOIN syntax didn't. There > shouldn't be any semantic difference AFAIR, so it seems to me that at > least one of these behaviors needs to be fixed. > > I am not sure that it's practical to remove the dependency as Tim is > hoping for... I wondered about that too, but by that time figured I was in way over my head. The big difference is that the INNER JOIN code needs to drop one of the colb's coming up with a virtual relation cola, colb, colc; where the other doesn't have such a renaming scheme. Or, thats how it appears to function to the user. I've not dug into the internals.
Rod Taylor <rbt@zort.ca> writes:
> The big difference is that the INNER JOIN code needs to drop one of the
> colb's coming up with a virtual relation cola, colb, colc; where the
> other doesn't have such a renaming scheme.
On reflection I think it might be possible to make this work the way
Tim wants: I think it would be okay for the dependency-extracter code
to ignore the Var lists that are attached to join RTEs. Needs more
thought though, and I don't plan to think about it before Monday;
I'm taking the weekend off ...
regards, tom lane
On Fri, 2002-09-06 at 19:30, Tom Lane wrote: > Rod Taylor <rbt@zort.ca> writes: > > The big difference is that the INNER JOIN code needs to drop one of the > > colb's coming up with a virtual relation cola, colb, colc; where the > > other doesn't have such a renaming scheme. > > On reflection I think it might be possible to make this work the way > Tim wants: I think it would be okay for the dependency-extracter code > to ignore the Var lists that are attached to join RTEs. Needs more > thought though, and I don't plan to think about it before Monday; > I'm taking the weekend off ... Your right. The view continues to function perfectly fine with the column dropped (did a delete from pg_depend to allow it).
Hi Guys, I haven't got access to my test machine which is at home so I can't try it but will the previous practice of creating a temp table (minus the column you want to delete), dropping the orignal and renaming the temp table work in 7.3 or will the dependency check catch this when you try and drop the orignal table too? If this is the case I'd imagine the route to take would be to manually dump all the dependent views, drop those views, drop the column and then recreate the views. Best Regards, Tim Knowles On Fri, 2002-09-06 at 09:17, Tom Lane wrote: > Rod Taylor <rbt@zort.ca> writes: > > Indeed. At the INNER JOIN it would appear that an alias is applied to > > the columns of a given table. > > ... > > The real trick is to make INNER JOINS less greedy in their requirements > > based on the columns that are actually used. > > What surprised me about this report was not that the JOIN syntax exposed > a dependency on column c, but that the non-JOIN syntax didn't. There > shouldn't be any semantic difference AFAIR, so it seems to me that at > least one of these behaviors needs to be fixed. > > I am not sure that it's practical to remove the dependency as Tim is > hoping for... I wondered about that too, but by that time figured I was in way over my head. The big difference is that the INNER JOIN code needs to drop one of the colb's coming up with a virtual relation cola, colb, colc; where the other doesn't have such a renaming scheme. Or, thats how it appears to function to the user. I've not dug into the internals.
Is this a TODO item or something for beta2? --------------------------------------------------------------------------- Rod Taylor wrote: > On Fri, 2002-09-06 at 19:30, Tom Lane wrote: > > Rod Taylor <rbt@zort.ca> writes: > > > The big difference is that the INNER JOIN code needs to drop one of the > > > colb's coming up with a virtual relation cola, colb, colc; where the > > > other doesn't have such a renaming scheme. > > > > On reflection I think it might be possible to make this work the way > > Tim wants: I think it would be okay for the dependency-extracter code > > to ignore the Var lists that are attached to join RTEs. Needs more > > thought though, and I don't plan to think about it before Monday; > > I'm taking the weekend off ... > > Your right. The view continues to function perfectly fine with the > column dropped (did a delete from pg_depend to allow it). > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this a TODO item or something for beta2?
I have patches on my laptop to fix this, and will commit once I've
tested 'em a little more.
regards, tom lane
pgsql-bugs@postgresql.org writes:
> 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.
FYI, I've fixed this in CVS tip. Thanks for the report!
regards, tom lane