Introduction
If you're a PLSQL developer, it's highly improbable that you haven't leveraged DBMS_OUTPUT in your code. This essential package often proves indispensable for debugging extensive pieces of code. By inserting console messages at critical junctures within your code, you're able to debug and untangle complexities. However, it's worth noting that occasionally, an unintended consequence arises—numerous DBMS_OUTPUT console messages inadvertently find their way into the production codebase.
DBMS_OUTPUT lacks an inherent mechanism for establishing a configurable debug level that can be seamlessly deactivated within a production environment.
Now, envision a scenario where a codebase with hundreds of DBMS_OUTPUT instances, undergoing a transformation to PL/pgSQL through ORA2PG or similar tools. This process transforms each DBMS_OUTPUT occurrence into a RAISE NOTICE counterpart.
Curiously, there exists a common misunderstanding that RAISE NOTICE functions similarly to DBMS_OUTPUT. However, this notion is quite detached from the actual reality.
In this blog, we will see with examples what are the key differences between them and how to improve your code by using features of RAISE.
What are the key differences?
It is an inbuilt package that outputs messages to the console. It can be called from any PLSQL block, stored procedure, or function and is used mainly for debugging purposes.
It is very fast and consumes minimal resources because it buffers the output messages before displaying them. This buffering allows multiple messages to be collected and displayed together, reducing the frequency of context switches between the PL/SQL engine and the output display. This minimizes the overhead of displaying each message individually, leading to improved performance.
RAISE command is used for all types of logging and messaging in PL/pgSQL, including errors, warnings, and debugging. It has various levels which can differentiate between types of logging. Ora2PG and similar codes usually convert DBMS_OUTPUT to RAISE NOTICE.
It is not very efficient as DBMS_OUTPUT because RAISE NOTICE generates synchronous output. This means that before control returns to the caller, the message must be fully written to the log, causing a potential delay in the execution of the code. This synchronous behavior can impact performance, especially in scenarios where rapid or frequent messages are being generated. Also, displaying output messages involves context switching between the execution context and the logging mechanism. This context switching can introduce overhead and impact the overall execution speed of your code.
From the above explanation, it is evident that DBMS_OUTPUT is more efficient in displaying messages as compared to RAISE.
Test it out.
Let’s test it out by displaying a hundred thousand console messages in a loop.
Run the following block on the Oracle database.
DECLARE
counter INTEGER := 1;
BEGIN
WHILE counter <= 100000 LOOP
DBMS_OUTPUT.PUT_LINE('This is loop iteration number ' || counter);
counter := counter + 1;
END LOOP;
END;
Run the following block on PostgreSQL.
DO $$
DECLARE
counter INTEGER := 1;
BEGIN
WHILE counter <= 100000 LOOP
RAISE NOTICE 'This is loop iteration number %', counter;
counter := counter + 1;
END LOOP;
END $$;
Outcome:
- Oracle PLSQL took 121 milliseconds whereas Postgres took 51.43 seconds which is almost 500 times slower than Oracle.
- The size of both the databases are similar but having a larger sized database will have a miniscule effect on the test results.
- From this test, it is evident that buffering and less context switching made DBMS_OUTPUT much faster than RAISE.
What are some of the ways in which this can be improved?
RAISE is a powerful command that was not built for only displaying messages. It can evaluate and display messages based on the minimum level selected. Allowed levels are DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION. Therefore, it is essential that RAISE NOTICE is not considered just a like-to-like replacement for DBMS_OUTPUT.
Following are some points to consider when converting the code to PL/pgSQL.
- Get rid of unwanted messages – This can be a massive effort if the code base is large, but it is an extremely important step to make the new code more maintainable and efficient. It is also the easiest in terms of complexity.
- Categorize the messages – Categorizing the messages appropriately helps in taking advantage of minimal logging parameters and allowing debug/info messages to evaluate only on dev-test databases and not production.
- Set log_min_messages appropriately – The default level is WARNING in Azure Postgres Flex. This means that RAISE WARNING and higher will always be outputted.
Final Thoughts
Postgres allows you to select what logging level to use in which environment making it simpler to switch on/off debug messages.
Using RAISE judiciously sounds like a trivial idea but can improve performance and efficiency immensely. This is often overlooked because not everyone understands the performance implication of using too many console messages in the code on Postgres.
Feedback and suggestions
If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!
