search

robins / tds_fdw

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

While UPDATE support is WIP a better message would be nice #6

Open robins opened 2 years ago

robins commented 2 years ago

Issue: Ideally it'd be nice to give a clear error message during UPDATE (like INSERT).

Detail:

Create a foreign table + INSERT fails with a clear message (expected).

tdsdb=> CREATE FOREIGN TABLE warehouse
tdsdb->   (
tdsdb(>     id int,
tdsdb(>     item text
tdsdb(>   )
tdsdb->   SERVER tdsdb
tdsdb->   OPTIONS (table 'warehouse');
CREATE FOREIGN TABLE
tdsdb=> table warehouse ;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
 id | item
----+-------
  1 | UPS
  2 | TV
  3 | Table
(3 rows)

tdsdb=> INSERT INTO warehouse values (1, 'UPS');
ERROR:  cannot insert into foreign table "warehouse"

But UPDATE fails with an odd error message:

tdsdb=> update warehouse set item = NULL where id =2;
ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

When verbose logging is enabled, we can see that it actualy tried to execute it (partly). 

tdsdb=> set client_min_messages = debug5;
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
tdsdb=> update warehouse set item = NULL where id =2;
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
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
DEBUG:  tds_fdw: Getting query
DEBUG:  tds_fdw: deparsing an expression
DEBUG:  tds_fdw: deparsing an operator expression
DEBUG:  tds_fdw: deparsing an expression
DEBUG:  tds_fdw: deparsing a var
DEBUG:  tds_fdw: deparsing an expression
DEBUG:  tds_fdw: deparsing a constant
DEBUG:  tds_fdw: deparsing an expression
DEBUG:  tds_fdw: deparsing an operator expression
DEBUG:  tds_fdw: deparsing an expression
DEBUG:  tds_fdw: deparsing a var
DEBUG:  tds_fdw: deparsing an expression
DEBUG:  tds_fdw: deparsing a constant
DEBUG:  tds_fdw: Value of query is SELECT [id], [item] FROM warehouse WHERE (([id] = 2)) AND (([id] = 2)) FOR UPDATE
DEBUG:  tds_fdw: Initiating DB-Library
DEBUG:  tds_fdw: Getting login structure
DEBUG:  tds_fdw: Setting login user to msssql2017
DEBUG:  tds_fdw: Setting login password to msssql2017
DEBUG:  tds_fdw: Setting login database to tdsdb
DEBUG:  tds_fdw: Connection string is msssql2017.cc0vi2lxnnjg.ap-southeast-2.rds.amazonaws.com
DEBUG:  tds_fdw: Connecting to server
DEBUG:  tds_fdw: Connected successfully
DEBUG:  tds_fdw: Setting database command to SELECT [id], [item] FROM warehouse WHERE (([id] = 2)) AND (([id] = 2)) FOR UPDATE
DEBUG:  tds_fdw: Executing the query
ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

Also note the wierd the id=2 twice ^^^ (which smells odd) and probably a bug somewhere.

A similar INSERT (with verbose logging) gives an expected error message even before trying (expected).

tdsdb=> insert into warehouse values (1, 'item');
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
ERROR:  cannot insert into foreign table "warehouse"