How to insert a new row when using (__doc json) without failing on first column of MongoDB's foreign table must be "_id"?

[marmor]

Hi,

mongo_fdw advises to use "(__doc json)" where the exact structure of the collection's documents is unknown and/or dynamic.
And it's even working great for SELECT, DELETE and even UPDATE.
But then, when trying to INSERT a new row, the following error message is thrown:
ERROR: first column of MongoDB's foreign table must be "_id"
Of course, it's impossible to insert _id before __doc, because the "column relation does not exist".
Defining the columns as (_id name, __doc json) doesn't help either, because _id should be the first column of __doc, inside __doc
(otherwise, if there are more than one column, the command will fail because there are more values than columns).

Do I miss anything?
Or is it really impossible to INSERT rows into a (__doc json) foreign table?

Thanks!

[vaibhavdalvi93]

Hi @marmor , the functionality "Full Document Retrieval" i.e. use of _doc json is to retrieve the document from MongoDB when collection fields are unknown. AFAIK, this functionality is designed only for reading the data from MongoDB.

And it's even working great for SELECT, DELETE and even UPDATE.

Are you sure about DELETE and UPDATE works fine? because I don't see it working for me.

edb=# delete from test_json;
ERROR:  first column of MongoDB's foreign table must be "_id"

But then, when trying to INSERT a new row, the following error message is thrown:
ERROR: first column of MongoDB's foreign table must be "_id"

To insert the row, first of all need to know the number of fields collection has and second is type of fields to insert the valid data. So, If remote collection is unknown and locally defined only with single column(i.e __id) then NOT possible to INSERT the data and it's expected behaviour.

I think, the full document retrieval functionality can't be used to INSERT/UPDATE/DELETE.

We can think of any other solution for this but currently this can't be done. Considering the current design of mongo_fdw, this seems bit difficult to work.

Thanks & Regards,
Vaibhav

[marmor]

Thank you very much!

I want to address each of the two issues separately, and start with the less important:

You are correct, I was mistaken when writing about DELETE/UPDATE, and it happened because I didn't want to bother the busy members here without leaving no stone unturned, including many variations and combinations that I tried, different versions of PG and mongo_fdw and MongoDB, I looked at the various forks of mongo_fdw, and even at the source code and inserted some dirty patches just to understand more.
During these tries, there were special cases that I succeeded to delete and update but not insert, but not in the normal case that I asked about, so I probably confused between the cases.

Now back to the main issue:

The dynamic structure of MongoDB, and the requirement for _id, are both "by design" principles of MongoDB, so in my humble opinion, PG support for MongoDB cannot be complete without a writable __doc (especially now, with the recent versions of PostgreSQL and the great support for JSON).

I'm not familiar with mongo_fdw source (more than inserting those dirty patches), and I'm busy too, but I can try to add support for that (minimal and inefficient, but it's better than what we have now...).

I don't want to do it in a fork that will remain separate forever, so even if some of you don't think it's a nice thing to have, I'll be grateful if you agree at least to add it as a feature that is disabled by default and can be enabled by an "OPTIONS" option.

After applying this patch, it will be possible to enable IMPORT FOREIGN SCHEMA (currently I have a C program that I wrote that gets a MongoDB database name, checks what collections are included in it, and generates a psql script that import each of them as a __doc table. I believe that I'm not the only one with such dirty workaround).

These patches (writable __doc and maybe even the IMPORT FOREIGN SCHEMA) are not going to happen tomorrow (I'm quite busy...), but I believe that they are not only useful, but also bring mongo_fdw on a par with the FDWs of the other DBs, so I'm looking for your support in my effort (moral support, of course. And maybe even taking a part in the developent).

Thanks again, and also thanks for mongo_fdw, which is (despite what I wrote...) very useful and important FDW.