Migrating Function based indexes from Oracle to PostgreSQL

In this post, I will share  issue faced while working with function based index in PostgreSQL . I was performing database migration from Oracle to PostgreSQL and used AWS Schema Conversion Tool(SCT) for converting the database schema.AWS Schema conversion tool takes care of automatically converting schema from one database engine to other. You can get more information about it on AWS documentation or  AWS Database Blogs articles

AWS SCT converted a function based index but while executing ddl in PostgreSQL, it failed to execute  with error

 “ERROR: functions in index expression must be marked IMMUTABLE”

-- Oracle Index DDL
 
CREATE UNIQUE INDEX "APP"."STUDENT_IDX1" ON "APP"."STUDENT" (CASE "IS_VALID" WHEN 1 THEN TO_CHAR("STUDENT_ID")||'-'||TO_CHAR("COURSE_ID") ELSE NULL END ) TABLESPACE "USERS" ; 
 
-- PostgreSQL DDL (Recommended by SCT)
 
CREATE UNIQUE INDEX student_idx1 ON student
USING BTREE ((CASE is_valid
    WHEN 1 THEN CONCAT_WS('', student_id::TEXT, '-', course_id::TEXT)
    ELSE NULL
END) ASC);

As per PostgreSQL docs, function could be of 3 types

• Volatile – It can return different results on successive calls with the same arguments
• Stable – It is guaranteed to return the same results given the same arguments for all rows within a single statement
• Immutable – It is guaranteed to return the same results given the same arguments forever

Let’s understand this with example. In below example, we are using current_timestamp function and output would change based on client timezone setting

postgres=> set timezone to 'US/Pacific';
SET
postgres=> select current_timestamp;
              now
-------------------------------
 2018-01-17 20:22:32.023775-08
(1 row)
postgres=> set timezone to 'UTC';
SET
postgres=> select current_timestamp;
              now
-------------------------------
 2018-01-18 04:22:46.227855+00
(1 row)

Current_timestamp is tagged as STABLE, since their values do not change within a transaction but it will change in next transaction even though input is same.

But Immutable function won’t change result. e.g Below two sql will give same result

select * from emp where empid=4 

OR

select * from emp where empid = 2+2

In PostgreSQL, function/expression used to create function based index needs to be immutable i.e function is guaranteed to return same results on giving same arguments to avoid data corruption.

We can identify whether function is immutable/stable by quering pg_proc view . Value of provalite field will indicate the type

SELECT proname, provolatile, proargtypes, proargtypes[0]::regtype AS argtype, prosrc FROM pg_proc WHERE proname like 'concat%';
proname  | provolatile | proargtypes | argtype |     prosrc
-----------+-------------+-------------+---------+----------------
concat    | s           | 2276        | "any"   | text_concat
concat_ws | s           | 25 2276     | text    | text_concat_ws
(2 rows)

Definition from docs

provolatile tells whether the function’s result depends only on its input arguments, or is affected by outside factors. It is i for “immutable” functions, which always deliver the same result for the same inputs. It is s for “stable” functions, whose results (for fixed inputs) do not change within a scan. It is v for “volatile” functions, whose results might change at any time. (Use v also for functions with side-effects, so that calls to them cannot get optimized away.)

We can see that concat and concat_ws are listed as stable function and their value can depend on client setting. E.g setting parameters like extra_float_digits will impact the output of concat function

postgres=> select concat(1.0000000000003::float8,56);
      concat
-----------------
1.000000000000356
(1 row)
postgres=> set extra_float_digits TO 3;
SET
postgres=> select concat(1.0000000000003::float8,56);
     concat
-----------------------
1.0000000000002999856
(1 row)

We were able to workaround by creating new function with immutable type which accepts input as text

CREATE OR REPLACE FUNCTION immutable_concat ( text, text, text, text ) RETURNS text AS 'SELECT $1 || $2 || $3 || $4;' LANGUAGE SQL IMMUTABLE;

New index statement

CREATE UNIQUE INDEX student_idx1
ON student
USING BTREE ((CASE is_valid
    WHEN 1 THEN immutable_concat('', student_id::TEXT, '-', course_id::TEXT)
    ELSE NULL
END) ASC);

Tags: awsPostgreSQLrds