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