The EDB Postgres Advanced Server (EPAS) has introduced a highly anticipated feature called EDB SPL Check in its 16th version. This extension is a boon for developers working with Stored Procedure Language (SPL) in the EDB Postgres Advanced Server. Here’s a quick dive into what EDB SPL Check offers and how it can transform your database management and development experience.
What is EDB SPL Check?
EDB SPL Check is a feature that helps detect errors in EDB stored procedures that might be overlooked by the standard CREATE PROCEDURE/FUNCTION command. The feature can be enabled using the following command: CREATE EXTENSION spl_check;
EDB SPL Check can operate in either active or passive mode. In active mode, it runs checks with API functions like spl_check_function, while in passive mode, functions are checked upon execution.
Why is SPL Check Important?
- Error Prevention: By catching syntax and semantic errors early, SPL Check helps prevent runtime errors that could disrupt database operations.
- Code Quality: It enforces best practices in coding, leading to cleaner, more maintainable, and efficient SPL code.
- Performance Optimization: Early detection of potential issues allows for optimizations that can improve the overall performance of stored procedures.
Let’s Check How It Works:
EDB SPL Check – An Amazing New Feature in EDB Postgres Advanced Server 16
Active Mode Active mode is the default behavior for EDB SPL Check. In this mode, you can manually start checks using API functions such as spl_check_function, spl_check_package, spl_check_objecttype, and spl_check_trigger.
Example:
Consider a scenario where we have an SPL function with a semantic error. The function products_est_tax() is created to show estimated taxes for items in the table products:
CREATE TABLE IF NOT EXISTS products(id INTEGER,
name VARCHAR(20),
price NUMERIC(10,0)
);
CREATE OR REPLACE FUNCTION public.products_est_tax()
RETURN void
AS
DECLARE
product_item RECORD;
BEGIN
FOR product_item IN SELECT * FROM products
LOOP
RAISE NOTICE '%', product_item.est_tax;
END LOOP;
END;
The function mistakenly tries to reference a non-existent column est_tax in the table products. Although the CREATE FUNCTION command completes successfully, the error is not immediately apparent because the table products is empty. To detect this error, you can run spl_check_function, as shown below:
edb=# SELECT * FROM spl_check_function('public.products_est_tax()');
spl_check_function
-------------------------------------------------------------------
error:42703:7:RAISE:record "product_item" has no field "est_taxt"
Context: SQL expression "product_item.est_tax"
(2 rows)
There is another function, spl_check_function_tb, which shows the above information in a row format as given below:
edb=# SELECT * FROM spl_check_function_tb('public.products_est_tax()');
-[ RECORD 1 ]---------------------------------------------
functionid | products_est_tax
lineno | 7
statement | RAISE
sqlstate | 42703
message | record "product_item" has no field "est_taxt"
detail |
hint |
level | error
position |
query |
context | SQL expression "product_item.est_tax"
This check will reveal the semantic error with the message “record ‘product_item’ has no field ‘est_tax’.”
Passive Mode
In passive mode, functions are automatically checked when they are executed, providing a more hands-off approach to error detection.
For setting passive mode, you can set the following parameters in your postgresql.conf file:
confCopy code
shared_preload_libraries = 'spl_check' # or set seesion_preload_libraries spl_check.mode = 'every_start'; spl_check.fatal_errors = no spl_check.show_nonperformance_warnings = false spl_check.show_performance_warnings = true
For more information on the parameters, please refer to the following link: Configuring Passive Mode
Below is an example of a session:
LOAD 'spl_check'; SET spl_check.mode = 'every_start'; SET spl_check.fatal_errors = no; SET spl_check.show_nonperformance_warnings = false; SET spl_check.show_performance_warnings = true; edb=# SELECT public.products_est_tax(); WARNING: record "product_item" has no field "est_tax" products_est_tax ------------------ (1 row)
Advanced Features
Setting Warning Levels The SPL Check function allows for setting various warning levels through its parameters, such as fatal_errors, other_warnings, and extra_warnings, which can show warnings for conditions like mismatched attribute numbers, variable overlaps, and unused variables.
Checking Triggers
To check triggers, use spl_check_function with the trigger name and the related table. For example:
CREATE TABLE IF NOT EXISTS products(id INTEGER,
name VARCHAR(20),
price NUMERIC(10,0)
);
CREATE OR REPLACE FUNCTION public.product_tax_trg()
RETURNS trigger
LANGUAGE edbspl
AS $function$
BEGIN
NEW.est_tax := NEW.price + 2.25 * NEW.price;
END;
$function$;
Running spl_check_function('public.product_tax_trg()', 'products') will check this trigger and return any errors found, as shown below:
edb=# SELECT spl_check_function('public.product_tax_trg()', 'products');
spl_check_function
--------------------------------------------------------------------------------
error:42703:3:assignment:record "new" has no field "est_tax"
Context: at assignment to field "est_tax" of variable "new" declared on line 0
(2 rows)
More Features
Validating Compound Triggers
For compound triggers, use spl_check_trigger() by providing the trigger name and optionally the relation name. This is useful for triggers where functions are created internally.
Validating Packages and Object Types
You can validate entire packages or object types using spl_check_package() and spl_check_objecttype() respectively. These tools allow for the validation of all functions/procedures within a package or all member functions of an object type in one go.
In-Comment Options EDB SPL Check also supports persistent settings written in comments. These settings are read from a function’s source code before checking and can be disabled by setting use_incomment_options to false.
Dependency List EDB SPL Check also supports listing the dependent objects of a procedure/function/trigger. Using this list, a user can validate and verify if an object exists or not and accordingly can ensure code is valid in a database. An example is given below:
SELECT * FROM spl_show_dependency_tb('public.products_est_tax()');
type | oid | schema | name | params
----------+-------+--------+----------+--------
RELATION | 16798 | public | products |
(1 row)
Performance Optimization EDB SPL Check also comes with a feature of knowing the execution time of each statement in a function. Knowing the execution time of each line in a procedure/function allows DBAs/Developers to focus on lines of code which are taking a significant amount of time and would allow them to write optimized code in a database.
Consider the following Fibonacci Series EDB SPL function:
CREATE OR REPLACE FUNCTION fibonacci_series()
RETURN SETOF INTEGER
IS
DECLARE
first NUMERIC := 0;
second NUMERIC := 1;
temp NUMERIC;
n NUMERIC := 5;
i NUMERIC;
BEGIN
DBMS_OUTPUT.PUT_LINE('Series:');
--print first two term first and second
RETURN NEXT first;
RETURN NEXT second;
-- loop i = 2 to n
FOR i IN 2..n
LOOP
temp := first + second;
first := second;
second := temp;
PERFORM pg_sleep(10);
--print terms of fibonacci series
RETURN NEXT temp;
END LOOP;
RETURN;
END;
Enabling EDB SPL Check Profiler
To enable the EDB SPL Check profiler, set spl_check.profiler to on and call the function spl_check_profiler(true) to activate it in a session as shown below:
SET spl_check.profiler TO on; SELECT spl_check_profiler(true); NOTICE: profiler is active spl_check_profiler -------------------- (1 row)
Now execute the function in the same session to profile it:
edb=# SELECT fibonacci_series();
fibonacci_series
------------------
0
1
1
2
3
5
(6 rows)
Now you can use the following SQL to capture and understand the execution time of the function:
edb=# SELECT lineno, avg_time, source FROM spl_profiler_function_tb('fibonacci_series()');
lineno | avg_time | source
--------+-----------+---------------------------------------------
1 | |
2 | | DECLARE
3 | | first NUMERIC := 0;
4 | | second NUMERIC := 1;
5 | | temp NUMERIC;
6 | | n NUMERIC := 5;
7 | | i NUMERIC;
8 | |
9 | 0.037 | BEGIN
10 | 0.097 | DBMS_OUTPUT.PUT_LINE('Series:');
11 | |
12 | | --print first two term first and second
13 | 0.016 | RETURN NEXT first;
14 | 0.001 | RETURN NEXT second;
15 | |
16 | | -- loop i = 2 to n
17 | 0.072 | FOR i IN 2..n
18 | | LOOP
19 | 0.062 | temp := first + second;
20 | 0.016 | first := second;
21 | 0.013 | second := temp;
22 | 10009.712 | PERFORM pg_sleep(10);
23 | | --print terms of fibonacci series
24 | 0.011 | RETURN NEXT temp;
25 | | END LOOP;
26 | 0 | RETURN;
27 | | END
(27 rows)
The above shows that pg_sleep in our function is taking more time, and we should optimize it.
Similar to the profile, there is a tracer capability in EDB SPL Check which shows the execution of each line in a function and helps in debugging and determining possible optimizations.
For more information on what developers/DBAs can achieve with EDB SPL Check, I recommend going through the following documentation: EDB SPL Check Documentation
Conclusion
The EDB SPL Check feature in EPAS 16 represents a significant step forward in ensuring the accuracy and reliability of SPL code in database systems. Its ability to catch subtle errors that might escape standard procedures, combined with the flexibility of setting warning levels and the convenience of checking entire packages or object types, makes it a valuable tool for database administrators and developers striving for error-free and efficient database operations.
Database Technologies & Cyber Security Platform 