Postgresql9.6 type cache invalidation issue - different behave of psql and pg regress
Hi
I searching a reason why result of plpgsql_check's regress tests are
different when its executed from regress tests or interactive.
This is simple test
drop table testtable;
create table testtable(a int, b int);
create or replace function test()
returns int as $$
declare r testtable;
begin
select * into r from testtable;
return r.a;
end;
$$ language plpgsql;
alter table testtable drop column b;
select * from plpgsql_check_function('test()');
this test should to return 0 rows, and it is working when I run it as test.
But when I execute it in psql I got
┌───────────────────────────────────────────────────────────────────────┐
│ plpgsql_check_function │
╞═══════════════════════════════════════════════════════════════════════╡
│ warning:00000:4:SQL statement:too few attributes for target variables │
│ Detail: There are more target variables than output columns in query. │
│ Hint: Check target variables in SELECT INTO statement. │
└───────────────────────────────────────────────────────────────────────┘
(3 rows)
In interactive mode, the build_row_from_class has unrefreshed metadata. But
why this behave I see only in psql and not in my regress tests?
Regards
Pavel
Pavel Stehule <pavel.stehule@gmail.com> writes:
In interactive mode, the build_row_from_class has unrefreshed metadata. But
why this behave I see only in psql and not in my regress tests?
The short answer is that no plpgsql version before commit 4b93f5799
will have nice behavior for cases where you change a referenced composite
type between calls. Why that's translating to the particular behavior
you're seeing isn't clear, considering you showed only one case in
detail; but I imagine it's because a parse of the plpgsql function
happens before the ALTER TABLE in one case and not the other.
Perhaps you have different settings of check_function_bodies,
for instance.
regards, tom lane
2018-04-20 15:44 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
In interactive mode, the build_row_from_class has unrefreshed metadata.
But
why this behave I see only in psql and not in my regress tests?
The short answer is that no plpgsql version before commit 4b93f5799
will have nice behavior for cases where you change a referenced composite
type between calls. Why that's translating to the particular behavior
you're seeing isn't clear, considering you showed only one case in
detail; but I imagine it's because a parse of the plpgsql function
happens before the ALTER TABLE in one case and not the other.
Perhaps you have different settings of check_function_bodies,
for instance.
good catch - I had check_function_bodies disabled.
Thank you for reply. Now I can believe to my regress tests again :)
Regards
Nice weekend
Pavel
Show quoted text
regards, tom lane