Thread: BUG #7786: select from view is computing columns not selected

The following bug has been logged on the website:

Bug reference:      7786
Logged by:          Douglas Toltzman
Email address:      doug@oakstreetsoftware.com
PostgreSQL version: 9.2.2
Operating system:   Linux CentOS
Description:        =


in the interest of simplicity, I've got a view that calls a stored procedure
to compute a value ...

create view testv as select tt.field1, tt.intfield, compute_val(tt.field1)
AS compute1 from tt;

If compute_val() selects sum(intfield) from the testv view, compute_val() is
called again when selecting records from testv (even though compute1 was not
selected), resulting in infinite recursion (stack overflow).

In retrospect, I realize this may have been a bad thing to do, but it worked
in version 8.1.x. I recently upgraded to 9.2.2 and I had to create a 2nd
view that didn't include the computed values, so I could use that view in
the stored procedures.

It seems like a performance problem to compute all columns in a view when
selecting only specific columns. I've already fixed my stack overflow, so
I'll let you decide if this is a bug or a feature.

If you're interested in reproducing this and you don't understand my
description, I'd be happy to provide a test case.

doug@oakstreetsoftware.com writes:
> in the interest of simplicity, I've got a view that calls a stored procedure
> to compute a value ...

> create view testv as select tt.field1, tt.intfield, compute_val(tt.field1)
> AS compute1 from tt;

> If compute_val() selects sum(intfield) from the testv view, compute_val() is
> called again when selecting records from testv (even though compute1 was not
> selected), resulting in infinite recursion (stack overflow).

> In retrospect, I realize this may have been a bad thing to do, but it worked
> in version 8.1.x.

I suspect that your function is declared VOLATILE, possibly by default.
PG versions later than 8.1 refrain from flattening views that contain
volatile functions in their SELECT list, on the grounds that such a
transformation might remove side-effects that the user expects to have
happen, or even cause the side-effects to happen more often than expected.

If you can declare the function STABLE then the view should continue to
work as before.

            regards, tom lane