Article about PostgreSQL 11

My new article is in Czech language, but Google translator can help.

plpgsql_check can identify variables with wrong type used in predicates and breaks index usage

Simple example:

create table bigtable(id bigint, ...)

...
declare _id numeric;
begin
  _id := ...
  FOR r IN SELECT * FROM bigtable WHERE id = _id
  LOOP
     ...

In this case, PostgreSQL newer use index due different type of query parameter (type of parameter is defined by type of PLpgSQL variable) and table attribute. This time this error is more usual due migration from Oracle. Id in tables are declared as bigint, int, but variables in functions are often declared as numeric.

PLpgSQL can identify some symptom of this issue - implicit cast inside predicate - and can throw performance warning. See commit.

Example:

create table bigtable(id bigint, v varchar);
create or replace function test()
returns void as $$
declare
  r record;
  _id numeric;
begin
  select * into r from bigtable where id = _id;
  for r in select * from bigtable where _id = id
  loop
  end loop;
  if (exists(select * from bigtable where id = _id)) then
  end if;
end;
$$ language plpgsql;

select * from plpgsql_check_function('test()', performance_warnings => true);
                                                    plpgsql_check_function
-------------------------------------------------------------------------------------------------------------------------------
 performance:42804:6:SQL statement:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause
 Query: select *        from bigtable where id = _id
 --                                             ^
 Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute
 Hint: Check a variable type - int versus numeric
 performance:42804:7:FOR over SELECT rows:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause
 Query: select * from bigtable where _id = id
 --                                 ^
 Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute
 Hint: Check a variable type - int versus numeric
 performance:42804:10:IF:implicit cast of attribute caused by different PLpgSQL variable type in WHERE clause
 Query: SELECT (exists(select * from bigtable where id = _id))
 --                                                     ^
 Detail: An index of some attribute cannot be used, when variable, used in predicate, has not right type like a attribute
 Hint: Check a variable type - int versus numeric
 warning:00000:3:DECLARE:never read variable "r"
(16 rows)