Error when inserting or updating a FDW table

[nextstopsun]

I've got a FDW to a DB2 spatial table. It has an OBJECTID primary key not null column, which is reported as FID by OGR. When trying to INSERT in this table I get a DB2 CLI error:

Assignment of a NULL value to a NOT NULL column is not allowed.

While the only non nullable column is OBJECTID, I suppose it's not sent in the INSERT statement. Is it possible to log real sent statements somehow? UPDATE statement doesn't work either.

[pramsey]

The SQL is generated at the OGR level, so it's not available to me in the FDW. You can set client_min_messages = debug4 to see everything that I do log, see if there's any clues.

[nextstopsun]

Can you please point to a line of code where INSERT statement goes to OGR?

[pramsey]

OGR is an abstraction layer, I don't generate SQL: here's where OGR is actually instructed to "create a new feature in the layer" https://github.com/pramsey/pgsql-ogr-fdw/blob/master/ogr_fdw.c#L2370

[nextstopsun]

@rouault Even, can this be somehow debugged on OGR side?

[pramsey]

Might be a good idea for me to bind the GDAL error reporting through to the pgsql logging system... only downside is it puts the database ops a bit at the mercy of gdal return codes. But it would make getting these messages as simple as turning up pgsql logging levels.

[nextstopsun]

Could be great! I'm feeling blind now, because I can't really see the statement with an error. But it has something to do with this OBJECTID - FID conversion done in OGR.

Right now all I can see in the log is this:

[2017-10-27 17:06:10] [00000] StartTransactionCommand
[2017-10-27 17:06:10] [00000] StartTransaction
[2017-10-27 17:06:10] [00000] name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 
[2017-10-27 17:06:10] [00000] bind <unnamed> to <unnamed>
[2017-10-27 17:06:10] [00000] ProcessQuery
[2017-10-27 17:06:10] [00000] ogrIsForeignRelUpdatable
[2017-10-27 17:06:10] [00000] ogrBeginForeignModify
[2017-10-27 17:06:10] [00000] ogrReadColumnData matched 1 FID, 1 GEOM, 13 FIELDS out of 15 PGSQL COLUMNS
[2017-10-27 17:06:10] [00000] ogrExecForeignInsert
[2017-10-27 17:06:10] [HV000] ERROR: failure writing OGR feature

[pramsey]

I've made a branch that (I think) hooks the gdal error handler into the pgsql logs, but I'm not sure it works: https://github.com/pramsey/pgsql-ogr-fdw/tree/gdal-errs

Also, for your case, the DB2 code doesn't log the SQL it creates anyways, so it won't give you any extra information even if it does work. PostgreSQL has a "log_statement" option in configuration; does DB2 have something similar?

[nextstopsun]

It does have a similar feature called event monitor. Unfortunately, I've got no control over DB2 side (I've contacted DBA to work it out). Meanwhile, isn't there really a way to get an SQL statement generated by OGR? I'm sure it's trying to INSERT FID column value instead of OBJECTID (the real column name). I could've changed the column name in DB2 to check this out, but it was generated by ArcSDE and it's a kind of a restriction to name a key column this way.

[pramsey]

The string "fid" is pretty well insulated on the FDW side of the wall. OGR has a baked-in idea of a FID and you can see it in the API very strongly: the developer doesn't specify it by name, they just set it using the FID setting and getting functions. The driver is then responsible for mapping that concept back into the data store behind. In databases, that means to a named column, for files it means something else, etc. So the fact that there is a "fid" column on the FDW side (I used that name as a magic string) doesn't mean it would be transmitted over to the OGR side. You could add some extra debug logging into ogrExecForeignInsert() and see that the fid exists and is non-null when OGR_L_CreateFeature is called. Once that function is called, it's all over on OGR's side at that point. Does ogr2ogr work for doing data imports into that table?

[nextstopsun]

@pramsey Thanks, good point with ogr2ogr.

Tried to test it, but

ogr2ogr -append -f "OGR_VRT" -nln db2_features -sql "SELECT 9999900::BIGINT AS OBJECTID, geom::GEOMETRY(MULTIPOLYGON, 900999) AS shape FROM pg_schema.pg_features WHERE mi_prinx = 1" /var/db2.vrt PG:"host=myhost user=myuser dbname=mydb password=mypass"

Gives me a crash

*** stack smashing detected ***: ogr2ogr terminated
======= Backtrace: =========
/lib64/libc.so.6(+0x6fb0c)[0x7f6cebbf0b0c]
/lib64/libc.so.6(__fortify_fail+0x37)[0x7f6cebc78a77]
/lib64/libc.so.6(__fortify_fail+0x0)[0x7f6cebc78a40]
/usr/lib64/libgdal.so(+0xa874d9)[0x7f6cecba64d9]
/usr/lib64/libgdal.so(_ZN11OGRDB2Layer16StartTransactionEv+0x17)[0x7f6cecbb34f7]
/usr/lib64/libgdal.so(_ZN15LayerTranslator9TranslateEP10OGRFeatureP15TargetLayerInfoxPxRxPFidPKcPvES8_P26GDALVectorTranslateOptions+0x100e)[0x7f6ceca56b0e]
/usr/lib64/libgdal.so(GDALVectorTranslate+0x1441)[0x7f6ceca5c6c1]
ogr2ogr[0x401c3c]
/lib64/libc.so.6(__libc_start_main+0xf0)[0x7f6cebba1640]
ogr2ogr[0x401f49]

Same command works ok with shapefile output, so no problems on postgres side. I know this isn't really related to this repo, but maybe @rouault can clear things up a bit here.

[rouault]

Looks like a bug in the DB2 driver. But as you use OGR_VRT as intermediate, would probably be better to directly try appending to a DB2 datasource itself to narrow things down. I've no DB2 setup available to help more. The issue you get with ogr2ogr is probably different than the one through ogr_fdw. The DB2 driver hasn't received much love after its initial contribution, so rough corners are to be expected

[nextstopsun]

I've put VRT out of equation and found something interesting: When using

ogr_fdw_info -s "DB2ODBC:database=MYDB;DSN=MYDB;Tables=MYSCHEMA.MYTABLE" -l MYSCHEMA.MYTABLE

I get table columns reported ok.

But when doing an ogrinfo call:

ogrinfo "DB2ODBC:database=mydb;DSN=MYDB;Tables=MYSCHEMA.MYTABLE" MYSCHEMA.MYTABLE

I get a similar error:

INFO: Open of `DB2ODBC:database=mydb;DSN=MYDB;Tables=MYSCHEMA.MYTABLE'
      using driver `DB2ODBC' successful.
*** stack smashing detected ***: ogrinfo terminated
======= Backtrace: =========
/lib64/libc.so.6(+0x6fb0c)[0x7f8e2f3ceb0c]
/lib64/libc.so.6(__fortify_fail+0x37)[0x7f8e2f456a77]
/lib64/libc.so.6(__fortify_fail+0x0)[0x7f8e2f456a40]
/usr/lib64/libgdal.so(+0xa874d9)[0x7f8e303844d9]
/usr/lib64/libgdal.so(_ZN16OGRDB2DataSource20CreateMetadataTablesEv+0x4f)[0x7f8e3038f91f]
/usr/lib64/libgdal.so(_ZN16OGRDB2DataSource17HasMetadataTablesEv+0xcd)[0x7f8e3038fc3d]
/usr/lib64/libgdal.so(_ZN16OGRDB2DataSource11GetMetadataEPKc+0xa3)[0x7f8e30390783]
ogrinfo[0x4031e1]
ogrinfo[0x403309]
ogrinfo[0x401e1a]
/lib64/libc.so.6(__libc_start_main+0xf0)[0x7f8e2f37f640]
ogrinfo[0x403039]

So maybe ogr utilities use org methods somehow different from ogr_fdw.