Removing exception blocks

In the last article titled 'UDF Issue 1', I have shown you why it is important to reduce the number of function calls. In this note I wiill show you how much impact an exception block in PL/pgSQL has on query performance and I will also give you an idea of how to eliminate exception blocks.

I had drafted this note when I had worked on a project where I had to port Oracle functions to PostgreSQL. I was prompted to complete this note recently when I noticed a PL/pgSQL function which, I think, contained unnecessary exception blocks.

​Let's assume that we have to port the following Oracle function:

​CREATE OR REPLACE FUNCTION F_SIDO_NM (

     p_empno IN EMPLOYEE.EMPNO%TYPE )

RETURN VARCHAR2

IS

   v_sido_nm EMPLOYEE.SIDO_NM%TYPE;

BEGIN

   SELECT SIDO_NM

     INTO v_sido_nm

     FROM EMPLOYEE

   WHERE EMPNO = p_empno;

   RETURN v_sido_nm;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

       v_sido_nm := 'Not Assigned';

   RETURN v_sido_nm;

END;

Here is the code to create the table EMPLOYEE.

​CREATE TABLE employee (

empno numeric(5,0) NOT NULL,

ename             character varying(10),

job                 character varying(9),

mgr                numeric(5,0),

hiredate               timestamp(0),

sal                 numeric(7,2),

comm             numeric(7,2),

deptno             numeric(2,0),

sido_nm      character varying(100)

);

insert into employee

select i, chr(65+mod(i,26))||i::text||'NM'

,case when mod(i,10000)=0 then 'PRESIDENT'

when mod(i,1000) = 0 then 'MANAGER'

when mod(i,3)=0 then 'SALESMAN'

when mod(i,3)=1 then 'ANALYST'

when mod(i,3)=2 then 'CLERK'

end as job

,case when mod(i,10000)= 0 then null when mod(i,1000)= 1 then 10000 when i >= 9000 then 1000

   else ceiling((i+1000)/1000)*1000 end as mgr

, current_date - i , trunc(random() * 10000) as sal

, trunc(random() * 10000) as com

, mod(i,12)+1 as deptno

, case when mod(i,3) = 0 then 'Jeonbuk' when mod(i,3) = 1 then 'Kangwon'

  else 'Chungnam' end as sido_nm

from generate_series(1,10000) a(i);

ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY (empno);

​

The Oracle function in question accepts an employee number as an input variable and returns the sido name. When no rows are retrieved, that is to say, there are no employees, we get the message 'Not Assigned'.

​Let's go through the Oracle function and see the differences compared to PL/pgSQL.

- The Return clause in the function header (not the function body) becomes RETURNS in PL/pgSQL.

- The datatype of the returned value, VARCHAR2, which is after the Return keyword has to be changed to VARCHAR. In this note, we'll use VARCHAR, but TEXT is often a better choice if you do not need specific string length limits.

- the IS keword becomes AS, and we need to add a LANGUAGE clause because PL/pgSQL is not the only possible function language.

- An assignment of a value to a variable is done by the colon sign combined with an equality sign(:=).

- In PostgreSQL, the function body is considered to be a string literal, so you need to use quote marks(") or dollar quotes($$) around it. This substitutes for the terminating slash(/) in the Oracle function.

​

Here is how this function would look when ported to PostgreSQL:

​

CREATE OR REPLACE FUNCTION F_SIDO_NM_PL(

          p_empno IN NUMERIC)

RETURNS VARCHAR

LANGUAGE PLPGSQL STABLE PARALLEL UNSAFE

AS

$$

DECLARE

   v_sido_nm EMPLOYEE.SIDO_NM%TYPE;

BEGIN

   SELECT SIDO_NM

     INTO STRICT v_sido_nm

     FROM EMPLOYEE

    WHERE EMPNO = p_empno;

   RETURN v_sido_nm;

EXCEPTION

   WHEN NO_DATA_FOUND THEN

        v_sido_nm := 'Not Assigned';

   RETURN v_sido_nm;

END;

$$

There are a few points to note here:

Firstly, the STABLE keyword was specified instead of VOLATILE(default value). When you are certain that the function does not change any data in the database you'better declare the function STABLE. Secondly, the function was marked PARALLEL UNSAFE because there is an exception block in the function body. Thirdly, the STRICT option in the INTO clause was specified to check whether the query returns exactly one row. If the query does not retrieve one orw, a run-time error will be reported, either NO_DATA_FOUND(no rows) or TOO_MANY_ROWS(more than one row). Finally there still exists an exception block to catch the NO_DATA_FOUND error.

​

You may think that the code conversion was successful and we did a good job in porting the Oracle function to PostgreSQL. But there are always peripheral considerations that might cause problems.

- The ported function was declared paralled unsafe, which prevents queries with this function from being processed in parallel. (This may not be a serious problem because the Oracle function itself was not enabled for parallel executions.)

- There exists an exception block which degrades performance.

​

Regarding this specific Oracle function, if the SIDO_NM column does not allow null values, we can use an sql function when porting to PostgreSQL. If the SIDO_NM column has a null value, we can't port the Oracle function to a PostgreSQL sql function because we can't have the sql function to return the same result.

In the project I had worked on, there were some lazy data modeling. In an ERD there was a column marked nullable but when I checked the vaules on the column I couldn't find any null values. When this happens, all developers think that the column is nullable, and they start using unneccesary COALESCE on the column and then some.

​

Here is how the sql function will look if the SISO_NM column has a NOT NULL constraint:

​

CREATE OR REPLACE FUNCTION F_SIDO_NM(

p_empno IN NUMERIC)

RETURNS VARCHAR

LANGUAGE SQL STABLE PARALLEL SAFE

AS

$$

SELECT COALESCE(max(sido_nm),'Not Assigned')

FROM EMPLOYEE

WHERE EMPNO = p_empno;

$$

​

If there are no null values in the SIDO_NM column, the above function can replace the PL/pgSQL function. And sql functions are more performant than PL/pgSQL functions.

​

Regarding exception blocks in PostgreSQL, the exception block of PostgreSQL is not identical with that of Oracle. When an error occurs in a block, PostgreSQL will abort the execution of the block and also the surrounding transaction. To recover from the error, the EXCEPTION clause is used. However, the exception block of PostgreSQL incurs subtransactions. In Oracle, the exception block is just another branch of the begin .... end block.

How can we remove an exception block in the above PL/pgSQL function?

Instead of using STRICT in the INTO clause and an exception block, we can use the special FOUND variable to determine whether a row was returned. The FOUND variable is set to TRUE if more than one row is returned.

​

Here is the rewritten code of the PL/pgSQL function.

Note that we could safely declare the function PARALLEL SAFE because there is no exeption blocks in the rewritten code.

​

CREATE OR REPLACE FUNCTION F_SIDO_NM_NE(

        p_empno IN NUMERIC)

RETURNS VARCHAR

LANGUAGE PLPGSQL STABLE PARALLEL SAFE

AS

$$

DECLARE

   v_sido_nm EMPLOYEE.SIDO_NM%TYPE;

BEGIN

   SELECT SIDO_NM

     INTO v_sido_nm

     FROM EMPLOYEE

    WHERE EMPNO = p_empno;

   IF NOT FOUND THEN

   RETURN ‘Not Assigned’;

   END IF;

   RETURN v_sido_nm;

END;

$$

​

This technique of using the special FOUND variable to remove an exception block is not always possible. If the Oracle function had an exception handler code to catch TOO_MANY_ROWS in the exception block, we had to use the STRICT option and an exception block in the PL/pgSQL function.

Now let's check the performance of PostgreSQL functions we have created so far.

​

I have run an PL/pgSQL function with an exception block.

​SELECT F_SIDO_NM_PL(EMPNO)

FROM EMPLOYEE;

​

Here is the output of the EXPLAIN command.

​Seq Scan on employee (actual time=0.055..56.311 rows=10000 loops=1)

Buffers: shared hit=30110

Planning Time: 0.036 ms

Execution Time: 56.951 ms

​

I have run an PL/pgSQL function without an exception block.

​SELECT F_SIDO_NM_NE(EMPNO)

FROM EMPLOYEE;

​

Seq Scan on employee (actual time=0.040..44.250 rows=10000 loops=1)

Buffers: shared hit=30110

Planning Time: 0.036 ms

Execution Time: 44.752 ms

​

Note that even though the numbers of block IO are identical, the elapsed time dropped when we called a PL/pgSQL function without an exception block.

The EMPLOYEE table is small in size, so PostgreSQL did not apply parallel processing. If the EMPLOYEE table had been big enough for prallel processing to kick in, the elapsed time gap would have been bigger.

​

I have run an sqlL function.

​SELECT F_SIDO_NM(EMPNO)

FROM EMPLOYEE;

​

Seq Scan on employee (actual time=0.108..59.657 rows=10000 loops=1)

Buffers: shared hit=30110

Planning Time: 0.098 ms

Execution Time: 60.181 ms

​

Unlike what I had expected, the sql function did not outperform PL/pgSQL functions. Repeated execution of COALESCE and MAX is the culprit of poor performance.

This article didn't go into why an exception block is a cause of performance degradation and why you must not label a funcion as PARALLEL SAFE if there in an exeption block in PL/pgSQL. I will explain it in detail in the next article.

​Conclusion

When you run into an exception block in a PL/pgSQL function, try to find a way to remove it. Sometimes you can increase the chances of removing an exception block when you look into the business requirements carefully.

Added on the next day of this publication

In the middle of this article I stated:

"However, the exception block of PostgreSQL incurs subtransactions."

The above statement is only true when there is an INSERT or UPDATE or DELETE operation in the main block. Since there are no DML queries in this sample, the above statement is not correct.