Обсуждение: do functions cache views?

are there circumstances under which a pl/pgsql function will cache the
contents of a view?

i can do this sequence of actions just fine:

create table foo (
    id int2 primary key
);

create view foo_view as
select *
from foo;

create function get_new_foo() returns int2 as '
declare
    v_max_foo int2;
begin
    select into v_max_foo max( id )
    from foo;
    return v_max_foo;
end;

then, if i run get_new_foo() while modifying the values in foo, the
function seems to get the correct values.

unfortunately, i have a function/view pair that looks roughly like this:

create view significant_records_view as
select *
from significant_records
where
/*
  * certain status conditions hold.
  * i don't think i need to include this logic since the joins here are
  * only to other standard tables. no other functions or views.
  */
;

create function get_next_significant_date( int4 ) returns date as '
declare
    v_id alias for $1;
    v_significant_date date;
begin
    select into v_significant_date
    max( other_date )
    from more_significant_records msr, significant_records_view srv
    where msr.significant_records_id = srv.id
    and srv.significant_id = v_id;
    if not found then
        select into v_significant_date min( significant_date )
        from significant_records_view srv
        where srv.significant_id = v_id;
    end if;
    return v_significant_date;
end;
' language 'plpgsql';

now, this function works for existing records in the table
significant_records. but as i add records, i end up having to drop and
recreate the function in order for it to record the existence of the new
records.

when i notice this happening, i attempt to run the two select statements
of the function. the first one, as i expect, returns null. the second
one, as i expect, returns a date. but i'm suspecting that the function
may be caching the null for the second function and ends up returning that.

if this is the case, why does the foo example at top work correctly? if
this is not the case, why does my function not work?

i would be happy to provide further details if this is not sufficient
for a reasonable response.

thanks.

-tfo

"Thomas F. O'Connell" <tfo@monsterlabs.com> writes:
> now, this function works for existing records in the table
> significant_records. but as i add records, i end up having to drop and
> recreate the function in order for it to record the existence of the new
> records.

This is hard to believe.

I am not sure that "if not found" means anything after a "select max()"
query.  The select will always return exactly one row --- even if it's
just a NULL --- so I'd expect the "if not found" never to succeed.
Perhaps you want to be testing whether v_significant_date is NULL or
not, instead.

Another issue, since you omitted the details of the view and of what
version you are running, is whether the view involves GROUP BY and/or
aggregates.  An aggregate over a grouped view won't work properly in
versions before 7.1.

If it's not those issues then we'll need more details --- preferably
a self-contained example.

            regards, tom lane

> This is hard to believe.

i don't want to believe it. trust me.


> I am not sure that "if not found" means anything after a "select max()"
> query.  The select will always return exactly one row --- even if it's
> just a NULL --- so I'd expect the "if not found" never to succeed.
> Perhaps you want to be testing whether v_significant_date is NULL or
> not, instead.

i wasn't sure if this would work. i actually changed it to this after
trying just a straight null check. i'll change it back to that, and see
if i still experience the same problem. that may have been back when i
was still running 7.0.3 or an early 7.1 beta.

speaking of which, how is "found" functionality supposed to work?


> Another issue, since you omitted the details of the view and of what
> version you are running, is whether the view involves GROUP BY and/or
> aggregates.  An aggregate over a grouped view won't work properly in
> versions before 7.1.

this shouldn't be an issue since i'm running 7.1.


> If it's not those issues then we'll need more details --- preferably
> a self-contained example.

i'll try removing the "if not found" code and see what happens.

thanks.

-tfo

>> If it's not those issues then we'll need more details --- preferably
>> a self-contained example.
>
>
> ok. here are some more details:
>
> the only other thing that might cause a problem with the view that i can
> see is that there is a part of the where clause that references
> CURRENT_DATE.
>
> as in:
>
> create view significant_records_view as
> select *
> from significant_records sr
> where sr.significant_date <= CURRENT_DATE
> -- rest of where clause
> ;
>
> now, this function is used in a date-sensitive context. could the
> function in my original example, which references this view, be caching
> the view as of the last CURRENT_DATE on which the function was brought
> into being?

in which case, maybe the more appropriate question is:

do views cache functions?

-tfo

>> now, this function works for existing records in the table
>> significant_records. but as i add records, i end up having to drop and
>> recreate the function in order for it to record the existence of the new
>> records.
>
> This is hard to believe.

indeed, but it still seems to be happening.


> I am not sure that "if not found" means anything after a "select max()"
> query.  The select will always return exactly one row --- even if it's
> just a NULL --- so I'd expect the "if not found" never to succeed.
> Perhaps you want to be testing whether v_significant_date is NULL or
> not, instead.

ok. i made this an explicit check for NULL just in case.


> Another issue, since you omitted the details of the view and of what
> version you are running, is whether the view involves GROUP BY and/or
> aggregates.  An aggregate over a grouped view won't work properly in
> versions before 7.1.

this is on a system running 7.1b4. and there are no GROUP BYs in the
view. the only aggregate is in a subquery.


> If it's not those issues then we'll need more details --- preferably
> a self-contained example.

ok. here are some more details:

the only other thing that might cause a problem with the view that i can
see is that there is a part of the where clause that references
CURRENT_DATE.

as in:

create view significant_records_view as
select *
from significant_records sr
where sr.significant_date <= CURRENT_DATE
-- rest of where clause
;

now, this function is used in a date-sensitive context. could the
function in my original example, which references this view, be caching
the view as of the last CURRENT_DATE on which the function was brought
into being?

-tfo

"Thomas F. O'Connell" <tfo@monsterlabs.com> writes:
>> If it's not those issues then we'll need more details --- preferably
>> a self-contained example.

> ok. here are some more details:

You still have not given an example that would allow someone else to
reproduce the behavior you think is broken.  Please see
http://www.postgresql.org/devel-corner/docs/postgres/bug-reporting.html
concerning what constitutes a useful bug report.

            regards, tom lane