[Bug]: Incorrect schema name rewriting in RI trigger queries

[staticlibs]

What happened?

With the following DB:

create database test1
use test1
create schema s1
create table s1.tab1 (col1 int primary key)
insert into s1.tab1 values(42)
create schema s2
create table s2.tab2 (col2 int, constraint c1 foreign key (col2) references s1.tab1 (col1))

When the insert like this is run to a table with a foreign key:

insert into s2.tab2 values(42)

When insert is finalized, the internal trigger runs the system function RI_FKey_check_ins to check the referential integrity of the foreign key. This function runs the SQL query:

SELECT 1 FROM ONLY "test1_s1"."tab1" x WHERE "col1" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x

When this query is run, in cases when its plan is not valid (for example, when revalidation hook returns false), it calls pg_analyze_and_rewrite_fixedparams. After that, because this query runs with SQL_DIALECT_TSQL, it eventually gets into rewrite_rangevar and gets the multi-db schema name rewritten from test1_s1 to test1_test1_s1 that causes the query to fail with the following:

2024-10-27 07:56:06.267 GMT [19788] LOG:  Unmapped error found. Code: 16908420, Message: relation "test1_test1_s1.tab1" does not exist, File: parse_relation.c, Line: 1456, Context: babelfishpg_tsql
2024-10-27 07:56:06.267 GMT [19788] CONTEXT:  PL/tsql function inline_code_block line 1 at SQL statement
	TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Processing SQL Batch Request
2024-10-27 07:56:06.267 GMT [19788] LOG:  Unmapped error found. Code: 16908420, Message: relation "test1_test1_s1.tab1" does not exist, File: parse_relation.c, Line: 1456, Context: babelfishpg_tsql
2024-10-27 07:56:06.267 GMT [19788] CONTEXT:  TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Processing SQL Batch Request
2024-10-27 07:56:06.268 GMT [19788] ERROR:  relation "test1_test1_s1.tab1" does not exist at character 20
2024-10-27 07:56:06.268 GMT [19788] QUERY:  SELECT 1 FROM ONLY "test1_s1"."tab1" x WHERE "col1" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
2024-10-27 07:56:06.268 GMT [19788] CONTEXT:  SQL statement "insert into s2.tab2 values(42)"
	PL/tsql function inline_code_block line 1 at SQL statement
	TDS Protocol: Message Type: SQL BATCH, Phase: TDS_REQUEST_PHASE_PROCESS. Processing SQL Batch Request
2024-10-27 07:56:06.268 GMT [19788] LOG:  Unmapped error found. Code: 16908420, Message: relation "test1_test1_s1.tab1" does not exist, File: parse_relation.c, Line: 1456, Context: TDS

This was happening to a user intermittently, when from time to time during the application run the ordinary UPDATE query was failing this way. The user was able to collect the following backtrace:

	at postgres!addRangeTableEntry (parse_relation.c:1471:110) [0x40298ecf]
	at postgres!transformFromClauseItem (parse_clause.c:1080:25) [0x402842ce]
	at postgres!transformFromClause (parse_clause.c:140:0) [0x40281746]
	at postgres!transformSelectStmt (analyze.c:1411:0) [0x404f7c32]
	at postgres!transformStmt (analyze.c:404:5) [0x404f4758]
	at postgres!parse_analyze_fixedparams (analyze.c:156:12) [0x404f3b92]
	at postgres!pg_analyze_and_rewrite_fixedparams (postgres.c:662:0) [0x4034d560]
	at postgres!RevalidateCachedQuery (plancache.c:717:0) [0x40483bde]
	at postgres!GetCachedPlan (plancache.c:1178:0) [0x404829c8]
	at postgres!_SPI_execute_plan (spi.c:2594:0) [0x401c7ee7]
	at postgres!SPI_execute_snapshot (spi.c:813:0) [0x401c51ce]
	at postgres!ri_PerformCheck (ri_triggers.c:2444:0) [0x40428625]
	at postgres!RI_FKey_check (ri_triggers.c:443:0) [0x40426ee7]
	at postgres!ExecCallTriggerFunc (trigger.c:2574:15) [0x40165d51]
	at postgres!AfterTriggerExecute (trigger.c:4844:0) [0x40165227]
	at postgres!afterTriggerInvokeEvents (trigger.c:5162:0) [0x401670df]
	at postgres!AfterTriggerEndQuery (trigger.c:5558:27) [0x401638c5]
	at postgres!standard_ExecutorFinish (execMain.c:442:0) [0x40188b49]
	at pg_stat_statements!pgss_ExecutorFinish (pg_stat_statements.c:1033:0) [0x017e2481]
	at postgres!_SPI_execute_plan (spi.c:2718:271) [0x401c828e]
	at postgres!SPI_execute_plan_with_paramlist (spi.c:767:0) [0x401c4d28]
	at babelfishpg_tsql!exec_stmt_execsql (pl_exec.c:4861:28) [0xeea0b755]
	at babelfishpg_tsql!dispatch_stmt (iterative_exec.c:847:0) [0xeea02bb4]
	at babelfishpg_tsql!dispatch_stmt_handle_error (iterative_exec.c:1214:11) [0xeea0386f]
	at babelfishpg_tsql!exec_stmt_iterative (iterative_exec.c:1527:23) [0xeea0d136]
	at babelfishpg_tsql!pltsql_exec_function (pl_exec.c:673:0) [0xeea126f0]
	at babelfishpg_tsql!pltsql_inline_handler (pl_handler.c:4565:0) [0xee9f7e56]
	at babelfishpg_tds!SPExecuteSQL (tdsrpc.c:536:0) [0x98b3d19d]
	at babelfishpg_tds!ProcessTDSRequest (tdsprotocol.c:388:0) [0x98b2fb1d]
	at babelfishpg_tds!TdsSocketBackend (tdsprotocol.c:644:0) [0x98b300fb]
	at babelfishpg_tds!pe_process_command (tds_srv.c:429:5) [0x98b43f52]
	at postgres!PostgresMain (postgres.c:4624:0) [0x4034eefc]
	at postgres!SubPostmasterMain (postmaster.c:5185:0) [0x402c0dac]
	at postgres!main (main.c:194:10) [0x401e1f97]
	at postgres!__scrt_common_main_seh (exe_common.inl:288:34) [0x404fb8f4]
	at KERNEL32!BaseThreadInitThunk (unknown_file:0:0) [0xc080257d]
	at ntdll!RtlUserThreadStart (unknown_file:0:0) [0xc1aeaf08]

Based on it I was only able to reproduce this by forcing the revalidation hook failure with the code change:

diff --git a/contrib/babelfishpg_tsql/src/plan_inval.c b/contrib/babelfishpg_tsql/src/plan_inval.c
index c8002dcf..ecf3a74f 100644
--- a/contrib/babelfishpg_tsql/src/plan_inval.c
+++ b/contrib/babelfishpg_tsql/src/plan_inval.c
@@ -82,7 +82,7 @@ pltsql_check_guc_plan(CachedPlanSource *plansource)
                        return false;
        }
 
-       return valid;
+       return false;
 }
 

I assume the problem is caused by SQL_DIALECT_TSQL used for the RI query, perhaps it would be appropriate to flip the dialect for the RI query and then flip it back after that.

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Fedora

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct.

[tanscorpio7]

This issue is being tracked under BABEL-5270