Microsoft SQL Azure foreign table does not allow updates

[Jnb2387]

I am having the same issues as #171 and I am wondering if updating or deleting be any different for Microsoft SQL Azure 12? I am able to connect and read the tables just fine, but I am getting 'Foreign table does not allow updates' for updates and then "cannot find 'fid' column in table" for delete. I do have an integer primary key in the table and is read as the fid. I set the client to debug and do not see any errors

UPDATE dforce_mssql_fdw_test.fed_lands_test
SET "type"='National Wilderness Area'
WHERE fid=7223
bind to
ogrAddForeignUpdateTargets
GDAL config option 'CPL_DEBUG' set to 'ON'
GDAL None [0] MSSQLSpatial: Use COPY/BCP: 0
GDAL None [0] ODBC: SQLDriverConnect(DRIVER={SQL Server};server=server,1433;database=dbname;UID=user;PWD=password;)
GDAL None [0] GDAL: GDALOpen(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0) succeeds as MSSQLSpatial.
GDAL None [0] OGR_MSSQLSpatial: Using column ogr_fid as FID for table fed_lands_test.
ogrReadColumnData matched 1 FID, 1 GEOM, 7 FIELDS out of 9 PGSQL COLUMNS
ogrOperatorIsSupported got operator '='
OGR SQL: ("ogr_fid" = 7223)
GDAL None [0] ODBC: SQLDisconnect()
GDAL None [0] GDAL: GDALClose(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0)
ogrIsForeignRelUpdatable
GDAL config option 'CPL_DEBUG' set to 'ON'
GDAL None [0] MSSQLSpatial: Use COPY/BCP: 0
GDAL None [0] ODBC: SQLDriverConnect(DRIVER={SQL Server};server=server,1433;database=dbname;UID=user;PWD=password;)
GDAL None [0] GDAL: GDALOpen(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0) succeeds as MSSQLSpatial.
GDAL None [0] ODBC: SQLDisconnect()
GDAL None [0] GDAL: GDALClose(MSSQL:server=server,1433;database=dbname;UID=user;PWD=password;Tables=dbo.fed_lands_test, this=0000000001F299B0)

I am able to insert new rows without error.

I am using Postgres 14 Postgis 3.2.1 OGR_FDW 1.1 and GDAL 3.4.2

MSSQL Table
CREATE TABLE [dbo].[fed_lands_test](
[ogr_fid] [int] IDENTITY(1,1) NOT NULL,
[sp_geography] [geography] NULL,
[name] nvarchar NULL,
[type] nvarchar NULL,
[type_abbrev] nvarchar NULL,
[website] nvarchar NULL,
[fed_land_id] [int] NULL,
[layer_id] [int] NULL,
[area_sq_mi] [float] NULL,
CONSTRAINT [PK_fed_lands_test] PRIMARY KEY CLUSTERED
(
[ogr_fid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Postgres Foreign table
CREATE FOREIGN TABLE dforce_mssql_fdw.fed_lands_test (
fid int8 NULL,
sp_geography public.geometry(geometry, 4326) NULL,
"name" varchar(250) NULL,
"type" varchar(50) NULL,
type_abbrev varchar(10) NULL,
website varchar(250) NULL,
fed_land_id int4 NULL,
layer_id int4 NULL,
area_sq_mi float8 NULL
)
SERVER dforce_mssql_test
OPTIONS (layer 'fed_lands_test', updateable 'true');

[pramsey]

Possible that the latest fixes also fix your problem, as they were a result of Pg14 changes to update/delete support.