plpgsql_check can detect bad default volatility flag

Common performance problem of plpgsql function when these functions are used from some more complex queries is using default VOLATILE flag. There are not possible to do more aggressive optimization of this function call. plpgsql_check can detect this issue now:

CREATE OR REPLACE FUNCTION public.flag_test1(integer)
 RETURNS integer
 LANGUAGE plpgsql
 STABLE
AS $function$
begin
  return $1 + 10;
end;
$function$;

CREATE OR REPLACE FUNCTION public.flag_test2(integer)
 RETURNS integer
 LANGUAGE plpgsql
 VOLATILE
AS $function$
begin
  return (select * from fufu where a = $1 limit 1);
end;
$function$;

postgres=# select * from plpgsql_check_function('flag_test1(int)', performance_warnings => true);
┌────────────────────────────────────────────────────────────────────┐
│                       plpgsql_check_function                       │
╞════════════════════════════════════════════════════════════════════╡
│ performance:00000:routine is marked as STABLE, should be IMMUTABLE │
└────────────────────────────────────────────────────────────────────┘
(1 row)

postgres=# select * from plpgsql_check_function('flag_test2(int)', performance_warnings => true);
┌──────────────────────────────────────────────────────────────────────┐
│                        plpgsql_check_function                        │
╞══════════════════════════════════════════════════════════════════════╡
│ performance:00000:routine is marked as VOLATILE, should be STABLE    │
└──────────────────────────────────────────────────────────────────────┘
(1 row)

Orafce - simple thing that can help

I merged small patch to master branch of Orafce. This shows a wide PostgreSQL possibilities and can decrease a work necessary for migration from Oracle to Postgres.

One small/big differences between Oracle and any other databases is meaning of empty string. There are lot of situation, when Oracle use empty string as NULL, and NULL as empty string. I don't know any other database, that does it.

Orafce has native type (not domain type) varchar2 and nvarchar2. Then it is possible to define own operators. I implemented || concat operator as null safe for these types. So now it is possible to write:

postgres=# select null || 'xxx'::varchar2 || null;
┌──────────┐
│ ?column? │
╞══════════╡
│ xxx      │
└──────────┘
(1 row)

When you port some application from Oracle to Postgres, then is good to disallow empty strings in Postgres. One possible solution is using generic C trigger function replace_empty_string(). This trigger function can check any text type field in stored rows and can replace empty strings by NULLs. Sure, you should to fix any check like colname = '' or colname <> '' in your application, and you should to use just only colname IS [NOT] NULL. Then the code will be same on Oracle and PostgreSQL too, and you can use automatic translation by ora2pg.

Orafce || (n)varchar2 operator automatically convert empty string to NULL.

PostgreSQL 12 - psql - csv output

After some years and long discussion, a psql console has great feature - csv output (implemented by Daniel Vérité).

Usage is very simple, just use --csv option.

[pavel@nemesis postgresql.master]$ psql --csv -c "select * from pg_namespace limit 10" postgres
oid,nspname,nspowner,nspacl
99,pg_toast,10,
10295,pg_temp_1,10,
10296,pg_toast_temp_1,10,
11,pg_catalog,10,"{postgres=UC/postgres,=U/postgres}"
2200,public,10,"{postgres=UC/postgres,=UC/postgres}"
11575,information_schema,10,"{postgres=UC/postgres,=U/postgres}"

plpgsql_check can be used as profiler

Today I integrated profiling functionality into plpgsql_check. When you enable profiling, then you don't need configure more.

postgres=# select lineno, avg_time, source from plpgsql_profiler_function_tb('fx(int)');
┌────────┬──────────┬───────────────────────────────────────────────────────────────────┐
│ lineno │ avg_time │                              source                               │
╞════════╪══════════╪═══════════════════════════════════════════════════════════════════╡
│      1 │          │                                                                   │
│      2 │          │ declare result int = 0;                                           │
│      3 │    0.075 │ begin                                                             │
│      4 │    0.202 │   for i in 1..$1 loop                                             │
│      5 │    0.005 │     select result + i into result; select result + i into result; │
│      6 │          │   end loop;                                                       │
│      7 │        0 │   return result;                                                  │
│      8 │          │ end;                                                              │
└────────┴──────────┴───────────────────────────────────────────────────────────────────┘
(9 rows)

In this case, the function profile is stored in session memory, and when session is closed, the profile is lost.

There is possibility to load plpgsql_check by shared_preload_libraries config option. In this case, the profile is stored in shared memory and it is "pseudo" persistent. It is cleaned, when profile reset is required or when PostgreSQL is restarted.

There is another good PLpgSQL profiler. I designed integrated plpgsql_check profiler because I would to collect different data from running time, and I would to use this profiler for calculating test coverage. More, this profiler can be used without any special PostgreSQL configuration, what can be useful for some cases, when there are not a possibility to restart a server.

new update pspg

I released new update of pspg https://github.com/okbob/pspg/releases/tag/1.6.3. It is bugfix release + new HiColor theme.