Unable to update MS SQL Server tables with geometry

[Davis-Klavins]

Read through similar ticket, but can't get to work updating MS SQL Server 2016 tables that have geometry column. Without it, works fine both with FreeTDS driver on Ubuntu 24.04, PostgreSQL 16 and even ODBC Driver 17 for SQL Server on Centos 7, PostgreSQL 12.

On table created in MS SQL Server with

CREATE TABLE dbo.test (
  ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
  ,CODE VARCHAR(14) NOT NULL
  ,x DECIMAL(9, 3) NULL
  ,y DECIMAL(9, 3) NULL
  );

query

UPDATE schema_name.dbo_test
SET x = 1;

in PostgreSQL works fine.

But on table created in MS SQL Server with

CREATE TABLE dbo.test_geom (
  ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
  ,CODE VARCHAR(14) NOT NULL
  ,geom geometry NOT NULL
  ,x DECIMAL(9, 3) NULL
  ,y DECIMAL(9, 3) NULL
  );

query

UPDATE schema_name.dbo_test_geom
SET x = 1;

in PostgreSQL returns error:

ERROR:  foreign table "dbo_test_geom" does not allow updates 

SQL state: 55000

[pramsey]

What if you use FID as the name of your PK?

[Davis-Klavins]

Tested, unfortunately the same.

[pramsey]

Unless you can get a debugger on it and step through, this ticket will probably languish, as I do not have an environment to test against. It's possible if @robe2 can replicate your situation that she could debug it.

[pramsey]

(Actually there is one thing I can do which is verify that update works on a loop-back connection to postgres, but it probably does. What GDAL version do you have, that's another variable that might have changed over time.)

[Davis-Klavins]

On Ubuntu where OGR FDW was installed via apt (postgresql-16-ogr-fdw), SELECT ogr_fdw_version(); reports GDAL version 3.8.4. On Centos where it was compiled, respective gdal-config --version reports GDAL version 3.0.4.