Thursday, December 6, 2018

New release of Orafce extension

I released new mostly release of Orafce. Today it is massive package of emulation often used Oracle's API and the emulation is on maximum what is possible.

Now Orafce has good and very nice documentation written by Horikawa Tomohiro (big thanks for his work).

There are not too much news for people who use Oracle 3.6:
  • possibility to better emulate || operator for varchar2 and nvarchar2 types
  • few bugfixes
  • only PostgreSQL 9.4 and newer are supported
  • support for PostgreSQL 11, current master branch (future PostgreSQL 12) is supported too

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:

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.

Tuesday, November 27, 2018

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}"

Sunday, November 25, 2018

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.

Saturday, November 17, 2018

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.

Thursday, October 11, 2018

New functionality of plpgsql_extension

I am working on new release of plpgsql_check extension - https://github.com/okbob/plpgsql_check.

Interesting new function is possibility to return list of used relation and functions. With these information is easy to generate dependency graph:

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
  perform upper(((plus(a) + 200) * 100)::text) from xx;
end;
$function$
postgres=# select * from plpgsql_show_dependency_tb('fx()');
┌──────────┬───────┬────────┬──────┬───────────┐
│   type   │  oid  │ schema │ name │  params   │
╞══════════╪═══════╪════════╪══════╪═══════════╡
│ FUNCTION │ 18310 │ public │ plus │ (integer) │
│ RELATION │ 24576 │ public │ xx   │           │
└──────────┴───────┴────────┴──────┴───────────┘
(2 rows)