Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgresql9.6 type cache invalidation issue - different behave ofpsql and pg regress

Postgresql9.6 type cache invalidation issue - different behave ofpsql and pg regress

From
Pavel Stehule
Date:
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




Re: Postgresql9.6 type cache invalidation issue - different behave of psql and pg regress

From
Tom Lane
Date:
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

Re: Postgresql9.6 type cache invalidation issue - different behave ofpsql and pg regress

From
Pavel Stehule
Date:


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


                        regards, tom lane