Google Cloud has unveiled the intricacies of its Database Migration Service, specifically focusing on the conversion of SQL Server stored procedures with multiple result sets into PostgreSQL code. This transformation addresses a significant hurdle often encountered during large-scale database migration projects.
Automated Decision Process
The service employs an automated decision-making process to ascertain whether a SQL Server procedure should be translated into a PostgreSQL stored procedure or a function. This determination hinges on the number of result sets produced by the original routine and the presence of a scalar return value.
In Google Cloud’s framework, procedures yielding a single result set or solely a scalar return value are converted into PostgreSQL stored procedures. Conversely, routines that return multiple result sets or combine result sets with a scalar return value are transformed into functions that return a SETOF refcursor.
This distinction highlights a fundamental difference between SQL Server and PostgreSQL. SQL Server can stream multiple tabular results from a single execution path, while PostgreSQL manages multiple datasets through explicit cursor management. Consequently, organizations migrating extensive databases may need to adjust not only the syntax but also the execution behavior of their routines. Manual rewriting becomes impractical, especially when dealing with hundreds of stored procedures featuring nested calls and conditional logic.
Healthcare Reporting Example
To illustrate this process, Google Cloud presented a healthcare reporting scenario. In this case, a master procedure retrieves patient details, invokes one child procedure for lab results, another for doctor visits, and may return up to four result sets along with a status integer indicating whether the patient was located.
In the PostgreSQL translation, a simpler child procedure that returns one result set is converted into a standard procedure with an INOUT refcursor parameter. More intricate routines are transformed into functions that sequentially open cursors and return each one individually.
A notable aspect of this design is the handling of scalar return values. Instead of maintaining the status outside the result flow, the translated PostgreSQL function opens a dedicated cursor named return_value at the conclusion of execution, placing the scalar integer within it. This adjustment alters how applications and testing teams interact with outputs from migrated routines. Rather than directly reading rows from a single execution response, the calling layer now receives cursor references, necessitating the fetching of each dataset separately and in the correct order.
Testing Changes
Testing these translated objects in PostgreSQL must occur within an explicit transaction block. Given that PostgreSQL cursors are linked to the transaction lifecycle, both execution and data retrieval must transpire between BEGIN and COMMIT.
In the healthcare example, the user first executes the translated function to generate cursor references, subsequently fetching data from each anonymous portal sequentially, and finally retrieving the return_value cursor. This process encompasses patient demographics, lab result sets, doctor visits, and the scalar status code.
Internal Analysis for Classification
Google Cloud also detailed the internal analysis employed to classify stored procedures prior to translation. The service initiates by scanning a procedure body for direct result sets, seeking explicit SELECT statements that yield tabular output. However, the presence of conditional logic and loops complicates this analysis. If a procedure contains conditional SELECT or EXEC statements, the number of result sets may vary across executions, leading the routine to be categorized as having a dynamic result count rather than a fixed one.
Nested procedure calls introduce another layer of complexity. To resolve these dependencies, the service constructs a directed graph of calls between procedures, propagating result-set counts back through the hierarchy using a depth-first search approach. This graph-based methodology aims to ascertain whether a top-level procedure has no result sets, one result set, or multiple or dynamic result sets after considering child routines. It also accommodates direct and indirect recursion within procedure call chains.
Migration Context
The significance of these details cannot be overstated, as database migrations frequently falter due to edge cases concealed within legacy application logic. Stored procedures that return multiple datasets are prevalent in older SQL Server systems, particularly when a single call was designed to minimize round trips between the application and the database.
As these routines transition to PostgreSQL, teams may discover that successful code conversion represents only a fraction of the overall task. They must also revise test harnesses, application data access layers, and operational scripts to accommodate explicit cursor fetching and transaction-bound result handling.
Google Cloud’s description of the Database Migration Service indicates that the product is crafted to alleviate this burden by automating structural rewrites while preserving existing conditional logic. The trade-off, however, is that downstream application components must adapt to the new response pattern established by PostgreSQL’s cursor management.
According to Google Cloud, the service classifies each SQL Server procedure into one of three categories: no result sets, a single result set, or multiple or dynamic result sets.
