search

babelfish-for-postgresql / babelfish_extensions

Babelfish for PostgreSQL provides the capability for PostgreSQL to work with applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query and procedural language, so you don’t have to switch database drivers or rewrite all of your application queries.
https://babelfishpg.org/
Apache License 2.0
274 stars 93 forks source link

Fix error when OUTPUT INTO table variable statement fires trigger #2639

Closed tanscorpio7 closed 2 months ago

tanscorpio7 commented 4 months ago

Description

Fix error portal snapshots (1) did not account for all active snapshots (2) when OUTPUT INTO table variable statement fires triggers.

pltsql statements that could fire trigger must always run inside a transaction block. If a txn block is not active, we create one. We exclude some types of statements from this, one of them being DML on table variables since they can never fire a trigger. We detect this using the variable mod_stmt_tablevar.

Now an edge case here is OUTPUT into table variable stmt. Which we internally transform into two DML nodes. TOP INSERT NODE (signifies the output into clause) Another DELETE / UPDATE / INSERT node inside the top insert node -> with clause (specifies the first part of the output into clause )

As a fix we simply skip transaction commands inside pltsql UDFs and not rely on mod_stmt_tablevar on anymore.

Also blocked certain cases of OUTPUT clause which should not be allowed inside pltsql UDFs.

  1. OUTPUT clause flushes results to client (INTO clause is missing)
  2. OUTPUT INTO clause has a non local object as its target. (UDFs only allow DML on local objects)

Issues Resolved

[BABEL-4859]

Sign Off

Signed-off-by: Tanzeel Khan tzlkhan@amazon.com

Check List

By submitting this pull request, I confirm that my contribution is under the terms of the Apache 2.0 and PostgreSQL licenses, 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.

coveralls commented 4 months ago

Pull Request Test Coverage Report for Build 9345257297

Details

Changes Missing Coverage Covered Lines Changed/Added Lines %
contrib/babelfishpg_tsql/src/prepare.c 34 38 89.47%
<!-- Total: 40 44 90.91% -->
Files with Coverage Reduction New Missed Lines %
contrib/babelfishpg_tds/src/backend/tds/tdscomm.c 2 74.29%
<!-- Total: 2 -->
Totals Coverage Status
Change from base Build 9313151345: 0.01%
Covered Lines: 41931
Relevant Lines: 57510
💛 - Coveralls
coveralls commented 2 months ago

Pull Request Test Coverage Report for Build 10055375937

Warning: This coverage report may be inaccurate.

This pull request's base commit is no longer the HEAD commit of its target branch. This means it includes changes from outside the original pull request, including, potentially, unrelated coverage changes.

Details

Files with Coverage Reduction New Missed Lines %
contrib/babelfishpg_tsql/src/pltsql_bulkcopy.c 23 88.33%
contrib/babelfishpg_tsql/src/hooks.c 165 79.69%
<!-- Total: 188 -->
Totals Coverage Status
Change from base Build 9956055141: 0.01%
Covered Lines: 42905
Relevant Lines: 58597
💛 - Coveralls