Support EXECUTE/CREATE...WITH RECOMPILE for stored procedures

[robverschoor]

Description

T-SQL supports EXECUTE WITH RECOMPILE and CREATE PROCEDURE ... WITH RECOMPILE. For EXECUTE WITH RECOMPILE, an ad-hoc plan is generated for that particular execution. For CREATE PROCEDURE WITH RECOMPILE, an ad-hoc plan is generated for every execution of the procedure, also when the EXECUTE statement does not specify WITH RECOMPILE. In both cases, the ad-hoc generated plan is discarded immediately afterwards.

In Babelfish, forcing ad-hoc plan generation is implemented by setting the GUC "plan_cache_mode" to "force_custom_plan" for the particular procedure execution. For CREATE PROCEDURE WITH RECOMPILE, and additional status bit is used in the babelfish_function_ext.flag_values catalog field. Currently, recompilation will not apply to non-parametrized queries since PG will not generate a custom plan for such queries.

Engine PR: https://github.com/babelfish-for-postgresql/postgresql_modified_for_babelfish/pull/360

Signed-off-by: Rob Verschoor [email protected]

Issues Resolved

BABEL-346 Support WITH RECOMPILE for Transact-SQL stored procedures

Test Scenarios Covered

• Use case based - Yes

• Boundary conditions - N/A

• Arbitrary inputs - N/A

• Negative test cases - N/A

• Minor version upgrade tests - N/A

• Major version upgrade tests - N/A

• Performance tests - N/A

• Tooling impact - N/A

• Client tests - N/A

Check List

• [x] Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is under the terms of the PostgreSQL license, and grant any person obtaining a copy of the contribution permission to relicense all or a portion of my contribution to the PostgreSQL License solely to contribute all or a portion of my contribution to the PostgreSQL open source project.

For more information on following Developer Certificate of Origin and signing off your commits, please check here.