search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
357 stars 99 forks source link

after 6 time run same plsql function mssql query is wrong, REPLACE value with NULL #279

Open Linreg-gmx-net opened 3 years ago

Linreg-gmx-net commented 3 years ago

Issue report

i have many plpgsql function with many CTE sections. when you run the SAME function 1-5 time it is okay and results are correct (if wrtiting materialized in CTE's section) after 6 time results get wrong. Please show down to Log Files.

After 5 time postgres use an prepared query cache plan and this lead to an not correct MSSQL query.

Run 1-5 time: SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG WHERE (([kidnr] = 77345))

After 6th time: SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG WHERE (([kidnr] = (CAST((SELECT CAST(null as integer)) as integer))))

Operating system

OpenSuse 15.2

Version of tds_fdw

2.0.2 Name | Version | Schema | Beschreibung --------------+---------+------------+----------------------------------------------------------------------------------- file_fdw | 1.0 | public | foreign-data wrapper for flat file access hstore | 1.3 | public | data type for storing sets of (key, value) pairs plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers tablefunc | 1.0 | public | functions that manipulate whole tables, including crosstab tds_fdw | 2.0.2 | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server) uuid-ossp | 1.0 | public | generate universally unique identifiers (UUIDs)

ESC[33mcommit 012350f68cea6159d1667b30d939afed05399e4aESC[m HEADESC[33m (ESC[mESC[1;36mHEAD -> ESC[mESC[1;32mmasterESC[mESC[33m, ESC[mESC[1;31morigin/masterESC[mESC[33m, ESC[mESC[1;31morigin/HEADESC[mESC[33m) > Author: RAFAELDEV2016 rafael.dev2016@gmail.com Date: Fri Nov 27 21:44:34 2020 +0100

Version of PostgreSQL

PostgreSQL 10.16 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit

Version of FreeTDS

freetds-config-1.1.36-lp152.1.1.x86_64 freetds-tools-1.1.36-lp152.1.1.x86_64 freetds-devel-1.1.36-lp152.1.1.x86_64

Logs

SELECT * from sp_m04_6_korrektur_harz4(5,77345,2113,2114);

Run 1-5 time: HINWEIS: ----> starting tdsGetForeignRelSize DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is an op or distinct expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a list expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a var expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a constant expression DEBUG: tds_fdw: the constant seems to be a supported type DEBUG: tds_fdw: Using remote estimate DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is an op or distinct expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a list expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a var expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a constant expression DEBUG: tds_fdw: the constant seems to be a supported type HINWEIS: ----> starting tdsBuildForeignQuery DEBUG: tds_fdw: Getting query DEBUG: tds_fdw: AAA deparseSelectSql DEBUG: tds_fdw: deparseColumnRef kidnr DEBUG: tds_fdw: deparseColumnRef eidnr DEBUG: tds_fdw: deparseColumnRef gruppe DEBUG: tds_fdw: deparseColumnRef datum DEBUG: tds_fdw: deparseColumnRef ende DEBUG: tds_fdw: BBB appendWhereClause SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG DEBUG: tds_fdw: deparsing an expression DEBUG: tds_fdw: deparsing an operator expression DEBUG: tds_fdw: deparsing an expression DEBUG: tds_fdw: deparsing T_Var 106 DEBUG: tds_fdw: deparsing a var DEBUG: tds_fdw: deparseColumnRef kidnr DEBUG: tds_fdw: deparsing an expression DEBUG: tds_fdw: deparsing T_Const 107 DEBUG: tds_fdw: deparsing a constant DEBUG: tds_fdw: DDD appendWhereClause SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG WHERE (([kidnr] = 77345)) DEBUG: tds_fdw: Value of query is SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG WHERE (([kidnr] = 77345)) HINWEIS: ----> finishing tdsBuildForeignQuery

After 6th time: HINWEIS: ----> starting tdsGetForeignRelSize DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is an op or distinct expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a list expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a var expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a param expression DEBUG: tds_fdw: Using remote estimate DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is an op or distinct expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a list expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a var expression DEBUG: tds_fdw: checking if an expression is safe to execute remotely DEBUG: tds_fdw: it is a param expression HINWEIS: ----> starting tdsBuildForeignQuery DEBUG: tds_fdw: Getting query DEBUG: tds_fdw: AAA deparseSelectSql DEBUG: tds_fdw: deparseColumnRef kidnr DEBUG: tds_fdw: deparseColumnRef eidnr DEBUG: tds_fdw: deparseColumnRef gruppe DEBUG: tds_fdw: deparseColumnRef datum DEBUG: tds_fdw: deparseColumnRef ende DEBUG: tds_fdw: BBB appendWhereClause SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG DEBUG: tds_fdw: deparsing an expression DEBUG: tds_fdw: deparsing an operator expression DEBUG: tds_fdw: deparsing an expression DEBUG: tds_fdw: deparsing T_Var 106 DEBUG: tds_fdw: deparsing a var DEBUG: tds_fdw: deparseColumnRef kidnr DEBUG: tds_fdw: deparsing an expression DEBUG: tds_fdw: deparsing T_Param 108 DEBUG: tds_fdw: deparsing a param DEBUG: tds_fdw: DDD appendWhereClause SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG WHERE (([kidnr] = (CAST((SELECT CAST(null as integer)) as integer)))) DEBUG: tds_fdw: Value of query is SELECT [kidnr], [eidnr], [gruppe], [datum], [ende] FROM KUNDEN_EINRICHTUNG WHERE (([kidnr] = (CAST((SELECT CAST(null as integer)) as integer)))) HINWEIS: ----> finishing tdsBuildForeignQuery

Sentences, data structures, data

Linreg-gmx-net commented 3 years ago

I think it could be that issue #251 is in this context too

lobettini commented 2 months ago

I have the exact same problem without CTE: 2024-04-26 14:01:43.008 UTC [44650] DEBUG: tds_fdw: Value of query is SELECT [CaseKey], [VariableKey] FROM [MYSCHEMA].[mytable] WHERE (([CaseKey] = 1190)) 2024-04-26 14:01:43.078 UTC [44650] DEBUG: tds_fdw: Value of query is SELECT [CaseKey], [VariableKey] FROM [MYSCHEMA].[mytable] WHERE (([CaseKey] = 1190)) 2024-04-26 14:01:43.415 UTC [44650] DEBUG: tds_fdw: Value of query is SELECT [CaseKey], [VariableKey] FROM [MYSCHEMA].[mytable] WHERE ([CaseKey] = 1193)) 2024-04-26 14:01:43.774 UTC [44650] DEBUG: tds_fdw: Value of query is SELECT [CaseKey], [VariableKey] FROM [MYSCHEMA].[mytable] WHERE (([CaseKey] = 1194)) 2024-04-26 14:01:44.130 UTC [44650] DEBUG: tds_fdw: Value of query is SELECT [CaseKey], [VariableKey] FROM [MYSCHEMA].[mytable] WHERE (([CaseKey] = 1196)) 2024-04-26 14:01:44.529 UTC [44650] DEBUG: tds_fdw: Value of query is SELECT [CaseKey], [VariableKey] FROM [MYSCHEMA].[mytable] WHERE (([CaseKey] = (CAST((SELECT CAST(null as bigint)) as bigint))))

tkmeyer commented 1 month ago

After testing various variants, it has become evident that there is an issue related to data type mapping for function parameters. Specifically, I encountered this problem while working with a PostgreSQL function that interacts with a Microsoft SQL Server (MSSQL) database in the scenario below:

  1. Function Description:

    • I have a PostgreSQL function that includes a WHERE clause.
    • This WHERE clause compares a datetime column (from the MSSQL side) with a parameter of the PostgreSQL function.
    • The parameter in question is of type date.

  2. Data Type Mismatch:

    • The datetime column on the MSSQL side maps to timestamp without timezone on the PostgreSQL side.
    • When querying the function without casting the parameter to ::timestamp in the WHERE clause, the result set becomes empty after the 6th execution.
    • However, if I explicitly cast the parameter to ::timestamp, everything works as expected.

  3. Alternative Approach:

    • I attempted to change the function parameter's type to timestamp, assuming that I wouldn't need to cast it anymore.
    • Surprisingly, instead of an empty result set, I encountered an error message during the 6th execution.

I'd be very happy if these facts could motivate someone to either solve the issue or at least point me in the right direction so I can solve it myself.

Prepare the Table on the Mssql Side

-- tds_fdw_test_mssql.sql

/*
use master
go
drop database fdwtestdb;
go
drop login fdwtestlogin;
go
*/

create database fdwtestdb;
go
use fdwtestdb;
go
CREATE LOGIN [fdwtestlogin] WITH PASSWORD=N'fdwtestloginpass', DEFAULT_DATABASE=[fdwtestdb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [fdwtestlogin] FOR LOGIN [fdwtestlogin]
GO
create table t1 (a datetime);
insert into t1(a) values('2024-01-01');
insert into t1(a) values('2024-01-02');
select * from t1;
grant select on t1 to [fdwtestlogin];
go

Prepare the Table on the Postgres Side

Connect to your postgres database and:

-- tds_fdw_test_postgres.sql

select current_schema(), current_user;

-- drop extension tds_fdw cascade;
CREATE EXTENSION tds_fdw;

CREATE SERVER fdwtestserver
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername 'localhost', port '1433', database 'fdwtestdb');

CREATE FOREIGN TABLE t1 (
    a timestamp NULL
)
SERVER fdwtestserver
OPTIONS (table_name 't1');

CREATE USER MAPPING FOR current_user SERVER fdwtestserver
    OPTIONS (username 'fdwtestlogin', password 'fdwtestloginpass');

Execute the Tests

select t.* from t1 t where t.a>=date '2024-01-01'; -- executes ok many times

-- DROP FUNCTION test_d_ts(date);
CREATE FUNCTION test_d_ts(d date)
    RETURNS TABLE(a timestamp without time zone)
    LANGUAGE plpgsql
AS $function$ begin
    return query 
        select t1.a from t1 where t1.a>=d;
end;
$function$

select t.* from test_d_ts(date '2024-01-01') t; -- executes ok 5 times, then returns empty result set

-- DROP FUNCTION test_d_ts_cast(d date);
CREATE FUNCTION test_d_ts_cast(d date)
    RETURNS TABLE(a timestamp without time zone)
    LANGUAGE plpgsql
AS $function$ begin
    return query 
        select t1.a from t1 where t1.a>=d::timestamp; 
end;
$function$

select t.* from test_d_ts_cast(date '2024-01-01') t; -- executes ok many times

-- DROP FUNCTION test_ts_ts(d timestamp);
CREATE FUNCTION test_ts_ts(d timestamp)
    RETURNS TABLE(a timestamp without time zone)
    LANGUAGE plpgsql
AS $function$ begin
    return query 
        select t1.a from t1 where t1.a>=d; 
end;
$function$

select t.* from test_ts_ts((date '2024-01-01')::timestamp) t; -- executes ok 5 times then errors out
/*
SQL-Fehler [HV00L]: FEHLER: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
  Wobei: SQL-Anweisung »select t1.a from t1 where t1.a=d«
PL/pgSQL-Funktion test_ts_ts(timestamp without time zone) Zeile 2 bei RETURN QUERY
*/

Versions:

dpkg -l | grep "mssql"
ii  mssql-server                      16.0.4120.1-1                       amd64        Microsoft SQL Server Relational Database Engine
ii  mssql-tools18                     18.2.1.1-1

dpkg -l | grep "postgresql"
ii  postgresql-15                     15.6-0+deb12u1                      amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-15-tds-fdw             2.0.3-1+b1                          amd64        PostgreSQL foreign data wrapper for TDS databases
ii  postgresql-client-15              15.6-0+deb12u1                      amd64        front-end programs for PostgreSQL 15
ii  postgresql-client-common          248                                 all          manager for multiple PostgreSQL client versions
ii  postgresql-common                 248                                 all          PostgreSQL database-cluster manager
ii  postgresql-server-dev-15          15.6-0+deb12u1                      amd64        development files for PostgreSQL 15 server-side programming
ii  postgresql-server-dev-all:amd64   248                                 amd64        extension build tool for multiple PostgreSQL versions

dpkg -l | grep "tds"
ii  freetds-common                    1.3.17+ds-2                         all          configuration files for FreeTDS SQL client libraries
ii  postgresql-15-tds-fdw             2.0.3-1+b1                          amd64        PostgreSQL foreign data wrapper for TDS databases
lobettini commented 1 month ago

Thank you @tkmeyer for your help. Your workaround seems to work but bring an other problem on my side. Indeed casting the paramter prevent tds_fdw to stop to work at the 6th execution. But at the 6th execution, tds_fdw push 2 times queries (first one without WHERE conditions and the second one with WHERE conditions)

My Test function

CREATE OR REPLACE FUNCTION mySchema.test(test int)
    RETURNS int
    LANGUAGE plpgsql
AS $function$
DECLARE
    count_result int;
BEGIN
    SELECT count(*)
    INTO count_result
    FROM ft_schema.ft_table fcd
    WHERE fcd."CaseKey"::bigint = test::bigint;

    RETURN count_result;
END;
$function$;;

My test

select mySchema.test(1010); --executes ok 5 times , at 6 does 2 queries at 7 go back to normal

My log general

--1th execution 0,005s
tds_fdw: Query executed correctly
tds_fdw: Getting results

--2th execution 0,005s
tds_fdw: Query executed correctly
tds_fdw: Getting results

--3th execution 0,005s
tds_fdw: Query executed correctly
tds_fdw: Getting results

--4th execution 0,005s
tds_fdw: Query executed correctly
tds_fdw: Getting results

--5th execution 0,005s
tds_fdw: Query executed correctly
tds_fdw: Getting results

--6th execution 1s
tds_fdw: Query executed correctly
tds_fdw: Getting results
tds_fdw: Query executed correctly
tds_fdw: Getting results

--7th execution 0,005s
tds_fdw: Query executed correctly
tds_fdw: Getting results

--8th execution 0,005s
tds_fdw: Query executed correctly
tds_fdw: Getting results

My log debug (push down queries)

--1th execution to 5th execution
SELECT NULL FROM [Datamart01].[FT_TABLE] WHERE (([CaseKey] = 1010))

-- 6th execution (2 query and in my case the first one cost a lot)
SELECT [CaseKey] FROM [Datamart01].[FT_TABLE]
SELECT NULL FROM [Datamart01].[FT_TABLE] WHERE (([CaseKey] = 1010))

--7th execution (go back to normal)
SELECT NULL FROM [Datamart01].[FT_TABLE] WHERE (([CaseKey] = 1010))

Are you seeing the same thing in your tests?

lobettini commented 1 month ago

I found a work around for my problem of 2 queries push down From this comment: https://stackoverflow.com/a/48276980, it looks likes:

For the first 5 executions, it will be planned using the actual parameter values (“custom plan”), and if the estimated execution time is not significantly shorter than that of a plan that ignores the actual parameter values (“generic plan”), the generic plan will be used from the sixth execution on.

Knowing this then I tried to force a custom plan with SET plan_cache_mode = force_custom_plan;

And it worked.

tkmeyer commented 1 month ago

These are great news, @lobettini :-) Thank you for sharing this workaround with us. And sorry, that I didn't respond to your previous comment until now. I had eye surgery and therefore cannot work at the monitor at the moment. I will verify your results and join our efforts to find a solution as soon as possible.

tkmeyer commented 1 month ago

Hi @GeoffMontee, perhaps you could give us a hint, where to start? We'd really like to contribute.