find a way to emulate RETURNING

[mcr]

PG::InternalError:` ERROR:  RETURNING is not supported by this FDW : 
INSERT INTO "crm_accounts" ("date_entered", "date_modified", "id") VALUES ($1, $2, $3) RETURNING "id"
/sg1/web/beaumont/beaumont/shared/bundle/ruby/2.1.0/gems/activerecord-4.2.5/

I got this while running a rails migration against my test system which actually has our PG->Mysql FDW installed. Sigh. Of course, Mysql doesn't support RETURNING to get access to the sequence numbers, but rather has the per-connection SELECT LAST_INSERT_ID();. Could the FDW possibly emulate that? It would, I realize, be hard to do, since RETURNING in it's full generality can return multiple values, etc.

[ahsanhadi]

Yes you are right, mysql doesn't have support for returning clause. Not sure how we can emulate that with mysql_fdw. This is a feature request that we can look at once it is prioritised by our product management.

[KES777]

Maybe as workaround, may you passthrough 'SELECT LAST_INSERT_ID' queries from PostgreSQL to MySQL? Or do some special query that mysql_fdw will recognize and do 'SELECT LAST_INSERTPID' internally. For example the mysql_fdw may implement and supply UDF's with same name

[mcr]

I'm willing to hack the rails adapter to do something interesting as a work around. I'm not sure exactly what you have in mind at the rails side. It seems like we could add the SELECT LAST_INSERT_ID to the sql we send to mysql, which is what I will look into doing.

[drm]

I ran into this today, using PostgREST on top of a MySQL foreign table. I have created a small patch, that turns the error into a WARNING, just to see what would break. Curiously enough, this doesn't break as horribly as I had expected. I'm not too well versed in the internals of postgres (i.e.: not at all), to know how this exactly works, but it seems to me that values generated by MySQL aren't fed back in case of an INSERT, while the rows that were already scanned are returned in case of the DELETE and UPDATE as expected.

While I do get the point that the user should not be using RETURNING if it doesn't work as expected (or as documented in the manual), it doesn't really break, so I would want to make the case to turn the error into a warning, and document the caveats.

[drm]

I'm planning on working around the INSERT issues with a trigger managed in Postgres.

[mcr]

The ability to return the new primary key is used extensively by web frameworks in their postgresql drivers.

[drm]

Yeah that makes total sense, but there's an easy workaround by simply implementing a sequence in a trigger. With the error in place on the other hand, there is no possible workaround.

[bartlaarhoven]

+1 for this issue, but in our case, the foreign database (MySQL / MariaDB) is not under our own control. So implementing a trigger isn't that obvious or even possible to do. Note that MariaDB 10.5 and up does support RETURNING in the way PostgreSQL does. Maybe support can at least be built-in for underlying databases that support it?