registered UserDefinedType gets discarded on new session

[strk]

I'm registering a 'geometry' datatype in sqlalchemy.dialects.* for the sake of being able to transfer geometries from MySQL to PostgreSQL. It works pretty nicely, but as soon as I disconnect and re-connect the mapping is lost, and PostgreSQL gets the empty string as the value of the MySQL geometry field (rather than calling func.AsText as requested).

How am I supposed to make the registration persistent across sessions ?

[rdunklau]

How are you registering the new types for now ?

I think the easiest would be to add a new option to the sqlalchemy_fdw called types_map, which would accept a dict (or a json object) defining the mappings you would like to add.

[strk]

I'm defining a class and attching to sqlalchemy.dialects..base.ischema_names[]:

https://github.com/strk/cartodb-postgresql/blob/8af7db8460845b70e0f71bce41b90245d2539224/scripts-available/CDB_ForeignTables.sql#L43-L86

I guess also the class definition would need to be re-issued on session refresh. What about an hook that's called on FDW load, for max flexibility ?

[rdunklau]

From an external function, that's clever ! :) Unfortunately, due to the design of Multicorn it can't work: the Python interpreter is instantiated once for each session.

What you should probably do:

• extend the SQLAlchemyFDW class
• override the init method to perform the type registration.

Nonetheless, I think a generic option to add types mapping would be great.

[strk]

Extending the SQLALchemyFDW class would still need to act outside of the database. I don't understand why the callback cannot work. Could the name of a function to call be specified in the OPTION of the foreign server ?

[rdunklau]

I don't understand your concern regarding actions "outside of the database". If you can install Multicorn, you should be able to install your own Multicorn-FDW too.

Adding a hook to be called by Multicorn would probably be a bit more complicated than it seems, too: of the top of my head, what happens to parameters ?

Really, if you want to mingle with the internals of a FDW, extend it and be done with it.

On the other hand, the specific "add custom types mapping" feature is general enough to warrant an inclusion in the FDW.

[strk]

Ok, got it. I guess my own multicorn-based FDW could also have support for remote sql queries, right ? (#70)

[rdunklau]

Yes it could.