Configuring EDB SPL Check

To run EDB SPL Check, use the CREATE EXTENSION command:

CREATE EXTENSION spl_check;

You can run EDB SPL Check in active mode or passive mode. In active mode, you can run checks with API functions like spl_check_function. In passive mode, functions are checked when executed.

Active mode

In active mode, start checks by running API functions like spl_check_function. Active mode is the default behavior for EDB SPL Check. However, you can change this mode with the spl_check.mode setting. See Configuring passive mode for more information.

You can also use the functions spl_check_package, spl_check_objecttype, and spl_check_trigger to validate your code. See Using EDB SPL Check for more information.

Example

The validator checks the SQL statements inside SPL functions for semantic errors:

CREATE TABLE t1(a int, b int);

CREATE TABLE

CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE edbspl
AS $function$
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM t1
LOOP
  RAISE NOTICE '%', r.c; 
END LOOP;
END;
$function$;

CREATE FUNCTION

RAISE NOTICE '%', r.c; indicates that there's a bug, which is that table t1 is missing a c column. However, the CREATE FUNCTION command doesn't identify there's a bug because table t1 is empty:

SELECT f1();

 f1 
────   

(1 row)

You can view the bug and other semantic errors by running spl_check_function:

SELECT * FROM spl_check_function_tb('f1()');

─[ RECORD 1 ]───────────────────────────
functionid │ f1
lineno     │ 6
statement  │ RAISE
sqlstate   │ 42703
message    │ record "r" has no field "c"
detail     │ [null]
hint       │ [null]
level      │ error
position   │ 0
query      │ [null]

spl_check_function() has three possible output formats, which are text, json, and xml:

SELECT * FROM spl_check_function('f1()', fatal_errors := false);

                         spl_check_function                         
------------------------------------------------------------------------
 error:42703:4:SQL statement:column "c" of relation "t1" does not exist
 Query: update t1 set c = 30
 --                   ^
 error:42P01:7:RAISE:missing FROM-clause entry for table "r"
 Query: SELECT r.c
 --            ^
 error:42601:7:RAISE:too few parameters specified for RAISE
(7 rows)

SELECT * FROM spl_check_function('fx()', format:='xml');

                 spl_check_function                     
────────────────────────────────────────────────────────────────
 <Function oid="16400">                                        ↵
   <Issue>                                                     ↵
     <Level>error</level>                                      ↵
     <Sqlstate>42P01</Sqlstate>                                ↵
     <Message>relation "foo111" does not exist</Message>       ↵
     <Stmt lineno="3">RETURN</Stmt>                            ↵
     <Query position="23">SELECT (select a from foo111)</Query>↵
   </Issue>                                                    ↵
  </Function>
(1 row)

Setting the level of warnings

You can use the function's parameters to set the level of warnings.

Required arguments

funcoid oid

The function name or function signature, as functions require a function specification. An oid, a name, or a signature can specify any function in PostgreSQL. Once you know the oid or a function's complete signature, you can use a regprocedure like 'fx()'::regprocedure or 16799::regprocedure. A possible alternative is to use only a name when the function's name is unique, like 'fx'. If the name isn't unique or doesn't exist, the function raises an error.

Optional arguments

relid DEFAULT 0

The oid of the relation assigned to the trigger function. You need to check the trigger function because you're sending the table that the trigger operates on.

fatal_errors boolean DEFAULT true

Stop on the first error. This argument prevents massive error reports.

other_warnings boolean DEFAULT true

Show warnings for conditions, for example:

• Different attribute numbers are on the left and right side of assignment
• The variable overlaps the function's parameter
• Unused variables
• Unwanted casting

extra_warnings boolean DEFAULT true

Show warnings for conditions such as a missing RETURN, shadowed variables, dead code, never read (unused) function parameter, unmodified variables, and modified auto variables.

performance_warnings boolean DEFAULT false

Show performance-related warnings for conditions such as the declared type with type modifier, casting, and implicit casts in the WHERE clause (which can be the reason why an index isn't used).

security_warnings boolean DEFAULT false

Show security-related checks like SQL-injection vulnerability detection.

compatibility_warnings boolean DEFAULT false

Show compatibility-related checks like the obsolete explicit setting internal cursor names in refcursor or cursor variables.

anyelementtype regtype DEFAULT 'int'

Actual type to use when testing the anyelementtype.

anyenumtype regtype DEFAULT '-'

Actual type to use when testing the anyenumtype.

anyrangetype regtype DEFAULT 'int4range'

Actual type to use when testing the anyrangetype.

anycompatibletype DEFAULT 'int'

Actual type to use when testing the anycompatibletype.

anycompatiblerangetype DEFAULT 'int4range'

Actual type to use when testing the anycompatiblerangetype.

without_warnings DEFAULT false

Disable all warnings by ignoring xxxx_warning parameters, which is a quick override.

all_warnings DEFAULT false

Enable all warnings by ignoring other xxxx_warning parameters, which is a quick positive.

newtable DEFAULT NULL, oldtable DEFAULT NULL

The names of NEW or OLD transition tables. When transition tables are used in trigger functions, these parameters are required.

use_incomment_options DEFAULT true

When set to true, activates in-comment options.

incomment_options_usage_warning DEFAULT false

When set to true, raises a warning when in-comment options are used.

Compatibility warnings

PostgreSQL cursor and refcursor variables are enhanced string variables that hold unique names for their respective portal. Before PostgreSQL version 16, the portal had the same name as the cursor variable. In PostgreSQL versions 16 and later, the portal has a unique name.

With this change, the refursor variable takes the value from another refcursor variable or from a cursor variable when the cursor is opened. For example:

-- obsolete pattern
DECLARE
  cur CURSOR FOR SELECT 1;
  rcur refcursor;
BEGIN
  rcur := 'cur';
  OPEN cur;
  ...

-- new pattern
DECLARE
  cur CURSOR FOR SELECT 1;
  rcur refcursor;
BEGIN
  OPEN cur;
  rcur := cur;
  ...

When the compatibility_warnings flag is active, EDB SPL Check tries to identify incorrect assigning to a refcursor variable or returning of a refcursor variable:

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS refcursor
AS $$
DECLARE
   c cursor FOR SELECT 1;
   r refcursor;
BEGIN
  OPEN c;
  r := 'c';
  RETURN r;
END;
$$ LANGUAGE edbspl;

SELECT * FROM spl_check_function('foo', extra_warnings =>false, compatibility_warnings => true);

                              spl_check_function                                   
-----------------------------------------------------------------------------------
 compatibility:00000:6:assignment:obsolete setting of refcursor or cursor variable 
 Detail: Internal name of cursor should not be specified by users.                 
 Context: at assignment to variable "r" declared on line 3                         

(3 rows)

Passive mode

In passive mode, EDB SPL Check can check your functions upon execution. Load the EDB SPL Check module with postgres.conf.

Configuring passive mode

These are the EDB SPL Check settings:

spl_check.mode = [ disabled | by_function | fresh_start | every_start ]
spl_check.fatal_errors = [ yes | no ]

spl_check.show_nonperformance_warnings = false
spl_check.show_performance_warnings = false

By default, spl_check.mode is set to by_function, which means that checks are done only in active mode by using spl_check_function. fresh_start means cold start, so the function is called first.

To enable passive mode:

LOAD 'edb-spl'; -- 1.1 and higher doesn't need it
LOAD 'spl_check';
SET spl_check.mode = 'every_start';  -- This scans all code before it is executed

SELECT fx(10); -- run functions - function is checked before runtime starts it