Support MySQL SET type with Postgres TEXT[]

[duncand]

Given a MySQL table like this:

create table the_table (
the_field set('x', 'y', 'z')
);

When trying to import its schema like this:

import foreign schema the_foreign_schema
from server the_server
into the_schema
options (
import_enum_as_text 'true'
);

The entire table is skipped citing "skipping import for relation".

This seems to be the only 1 MySQL type that mysql_fdw treats this way; the mysql_fdw.c at line 2370 says:

"PostgreSQL does not have an equivalent data type to map with SET, so skip the table definitions for the ones having SET type column."

However, Postgres has the native TEXT[] type which should be fully capable of directly mapping with a MySQL SET, and mysql_fdw can use it same as it maps MySQL ENUM to TEXT now, at least when enabled via import_enum_as_text.

This is a request to formally support MySQL SET mapped with Postgres TEXT[] so that imports of SET can succeed and be useful.

For consistency, this could be gated behind an option like import_set_as_text_array or such, or you could overload import_enum_as_text to also have that behaviour, since the 2 features are closely related.

Without this change, the current behaviour of mysql_fdw causes a real problem for me and others as it means I can not fully access an existing MySQL schema from Postgres and it would be onerous to change the MySQL schema to use something other than SET as it is a legacy application still used in production.

I recognize that technically SET is an unordered type while TEXT[] is ordered, but for all practical purposes that shouldn't be a problem to map them. The TEXT[] can be semantically treated as containing an unordered set anyway. But to ensure correct behaviour the mapping could also sort the values lexicographically to guarantee that 2 equal SET map to 2 equal TEXT[] deterministically. And going the other way is ordered to unordered.

Making this change would address a low hanging fruit for obvious missing functionality that in theory should be very little implementation effort to someone expert in maintaining it.

Thank you in advance for your consideration.

[surajkharage19]

Thank you @duncand for your request.

Earlier, we tried to map MYSQL set type to Postgres TEXT type and that idea got rejected because certain operations may not yield desired/consistent results due to the way MySQL stores the SET type like FIND_IN_SET function, bit mask comparision etc. I think that same applies to TEXT[] type as well, however, I would like to know what kind of queries are expected at your end to retrieve and modification of SET data using mysql_fdw.

[duncand]

Thank you @surajkharage19 for responding.

The main context I have is:

1. Legacy version of application using MySQL, rewritten version of application using Postgres, during an intermediate period both versions of the app runs at once effectively like a single application.

2. Rewritten application needs to access the ENTIRE legacy schema, partly for thorough migration purposes that leaves nothing out, and partly for active integration between the concurrent versions running.

3. The mysql_fdw would be used in a read-only manner the vast majority of the time, like 99%, and it would be used to write on rare occasions that are foreseen, and none of the immediately foreseen writes would be to the SET-typed columns, those just need to be read.

4. It is okay for the SET-typed columns to be exposed in a sub-optimal manner, such as in terms of a comma-delimited TEXT like MySQL sends it over the wire anyway, with extra work by user code on the Postgres code to interpret it.

The bottom line is that mysql_fdw needs to be able to import and expose an entire MySQL schema at least for reading purposes in a lossless manner, to handle the primary use case of reading from the MySQL. It is not reasonable to simply skip over entire tables because they have a field of that type. It is better to bring over the table anyway and just expose the SET column some-how, so it is at least possible for user SQL on the Postgres side to see the data.

There is not necessarily any requirement to modify SET-typed data, but even then for the general user-base, this ought to be supported in some manner, even if it means exposure as a comma-separated TEXT, and users have to be careful to format the values properly to not get an error from MySQL.

Note that since writing this ticket I also discovered a possible workaround for my own case, which is to ALTER TABLE the original table and modify the SET field into a VARCHAR, this simple act seemed to preserve all the data, and seemed to behave the same to users since over-the-wire MySQL sent it as comma-delimited text anyway, and also FIND_IN_SET() is actually a TEXT function anyway; when I made that change on the MySQL end, mysql_fdw was able to import and expose the table properly. We don't want to have to do this, but it seemed better to deal with its associated risks and effort than to not be able to import the tables to Postgres at all.

So if mysql_fdw isn't updated, that workaround can be done.

However, it would still be best if mysql_fdw could at least do something with SET type fields that is better than it does now, if not for me than for all the other users.

Note that I'm not stuck on a TEXT[] or TEXT representation, I just suggested the former as it seemed the most logically direct analogy, and avoids the problem of distinguishing TEXT that are TEXT from TEXT that are SET.

Thank you.