Accelerating PL/pgSQL Code Conversion When Migrating to Postgres

Most of the adopters of PostgreSQL are folks moving from another database and code conversion or pl\pgsql development is a critical path. It is a session, we will walk through curated learning I had moving customers and large code bases to PostgreSQL with pl\pgsql.

Key Takeaways for the participant

Understand pl\pgsql best practise from a migration perspective.
Uncover hidden caveats with code conversion.
Tips to accelerate code conversion.

Migrating to Postgres on cloud? Need cost, performance, or code conversion help?
Let's Connect.

References -
https://www.citusdata.com/posette/speakers/deepak-mahto/
https://databaserookies.wordpress.com/

Transcript

1. Accelerating PL/pgSQL Code Conversion When Migrating to Postgres Deepak Mahto DataCloudGaze Consulting
2. © 2024 All Rights Reserved Agenda • PL/pgSQL Introductions - What and Why? • Challenges with PL/pgSQL • Code Conversion Planning - What and How? • Important PL/pgSQL Configuration Parameters • Understanding Accelerators for Conversion to PostgreSQL
3. © 2024 All Rights Reserved Typical Database Migration Efforts Distribution Migrate Data 18% Testing and Code Fixes. 24% Assess and Plan 12% Schema and Code Conversion 25% Integration test, Cutover and Post Production Support 21% *Assuming most of the business logic is within Database procedural code.
4. © 2024 All Rights Reserved What is PL/pgSQL PL/pgSQL is a loadable procedural language for the PostgreSQL database system.
5. © 2024 All Rights Reserved More supported Procedural Language. • PL/R - PostgreSQL support for R as a procedural language (PL) • PL/V8 - A procedural language in JavaScript powered by V8 • PL/Tcl - Tcl procedural language for PostgreSQL. • PL/Perl - The Perl procedural language for PostgreSQL. • PL/Rust - Procedural language in the Rust programming. • PL/Python - Untrusted procedural language for PostgreSQL. More..
6. © 2024 All Rights Reserved Why PL/pgSQL ? PL/pgSQL enhances performance by allowing code logic and SQL to run together on the server, reducing client/server communication overhead and improving execution efficiency. • Portable and easy to learn. • Standard language for PostgreSQL and relational databases • Group computations and queries on the server
7. © 2024 All Rights Reserved Structure of PL/pgSQL PL/pgSQL is a block-structured language. The complete text of functions needs to be in a block, i.e., enclosed within Dollar Quoting ($$)
8. © 2024 All Rights Reserved Challenges with PL/pgSQL - 1 The code within the functions is treated as a single string, which can make it difficult to manage, especially with nested functions or complex logic Enclosed in Quotes
9. © 2024 All Rights Reserved Challenges with PL/pgSQL - 2 postgres=# SELECT func_demo1(); ERROR: column "col1" does not exist LINE 1: SELECT 1 WHERE COL1 = 1 QUERY: SELECT 1 WHERE COL1 = 1 CONTEXT: PL/pgSQL function func_demo1() line 4 at SQL statement PL/pgSQL functions aren't syntax-checked until executed.
10. © 2024 All Rights Reserved Challenges with PL/pgSQL - 3 No dependency checks between procedural and functional code. postgres=# drop function add_numbers; DROP FUNCTION postgres=# select square_of_sum(2,2); ERROR: function add_numbers(numeric, numeric) does not exist
11. © 2024 All Rights Reserved Compiling ≠ Completion of Code Conversion Compilation is not the only definition of 'done' for conversion. Missing Function ERROR: division by zero Missing Table
12. © 2024 All Rights Reserved Exceptions at runtime. Most exceptions are reported at runtime and are limited to the sub-sections being executed. Input(1) - Function does not exist Input(-1) - Table does not exist Input(0) - division by zero
13. © 2024 All Rights Reserved Key Accelerator Code Conversion is incomplete without planning, code sanity, or functional test cases from Day 1.
14. © 2024 All Rights Reserved Code Conversion Planning - What? Planning code conversion helps uncover complexity and dependencies, streamlining the process and guiding where to start.
15. © 2024 All Rights Reserved Code Conversion Planning - How ? Dependency-based code conversion helps us scale the team, learn faster, and uncover issues early.
16. © 2024 All Rights Reserved Using Pl\pgSQL configuration Enhances code quality by checking assertions, warnings, and avoiding variable conflicts.
17. © 2024 All Rights Reserved plpgsql.extra_errors/warnings Set plpgsql.extra_warnings/plpgsql.extra_errors to 'all' in dev/test. Shadowed Variable too_many_rows strict_multi_assignment
18. © 2024 All Rights Reserved Empty String vs Null Consideration select func_null_empty_string(''); NOTICE: Processing not null select func_null_empty_string(null); NOTICE: Processing null/empty string In Postgres Empty string and null are not treated as same.
19. © 2024 All Rights Reserved Empty String vs Null Consideration - NULLIF select func_null_empty_string(''); NOTICE: Processing null/empty string select func_null_empty_string(null); NOTICE: Processing null/empty string Meeting Functionality is critical and handling null or empty string as per Source.
20. © 2024 All Rights Reserved Choosing the Right Procedural Argument Type Oracle's generic NUMBER type can cause confusion and issues in data type mapping for arguments.
21. © 2024 All Rights Reserved Data type mapping for Procedural arguments Postgres implicit conversion and data type mapping for numeric family. Function argument type INTEGER BIGINT FLOAT NUMERIC function func_int(int) Y N N N function func_double(float) Y Y Y Y function func_numeric(num eric) Y Y N Y
22. © 2024 All Rights Reserved Subtle art of Conversion Understanding the code's requirements is crucial at times.
23. © 2024 All Rights Reserved Subtle art of Conversion - PostgreSQL Native Code conversion is the subtle art of understanding the source engine and dissecting it with knowledge of the target engine.
24. © 2024 All Rights Reserved Atomic Function Atomic Functions build dependency across database object, currently limited to only sql language. postgres=# drop function add_numbers; ERROR: cannot drop function add_numbers(numeric,numeri c) because other objects depend on it
25. © 2024 All Rights Reserved Extensions Top Extension’s as your Code Conversion buddy. • orafce Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS. • plpgsql_check Linter tool for analyzing plpgsql source code. • pgTAP pgTAP is a unit testing framework for PostgreSQL written in PL/pgSQL • plprofiler create performance profiles of PL/pgSQL functions and stored procedures
26. © 2024 All Rights Reserved PL\pgSQL Performance - Identification Setting pg_stat_statements.track to 'all' captures nested queries within procedural statements(only in dev\test).
27. © 2024 All Rights Reserved Code Sanity/Functional Test Cases Ensure Code Integrity and Functionality : Run and validate functions after conversion to catch runtime errors missed during compilation.
28. © 2024 All Rights Reserved Conclusion • Source-Code Dependency-Based Conversion Planning • Leverage PL/pgSQL Configuration • Build Code Sanity or Functional Test Cases from Day 1 • Be Aware of Functional Argument Data Type Mismatch • Make Extensions Your Conversion Buddy • Only Compilation is Not the Definition of Done for Conversion
29. © 2024 All Rights Reserved Thank you! https://www.datacloudgaze.com/ https://www.linkedin.com/in/mahtodeepak/ https://x.com/mahtodeepak05 https://databaserookies.wordpress.com/