Wednesday, November 28, 2018

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:

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:

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.

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.