Custom defined data types disrupt FDW tables

[JosefMachytkaNetApp]

Issue report

(I checked issues #325 and #323 but my issue looks like something different. Maybe this is even feature be design but I decided to record it anyway.)

I have encounter a problem with FDW tables when Sybase source tables use custom defined data types and I try to recreate them in PostgreSQL using domains.

Sybase types are for example
TypCounter number(10,0)
TypPassword univarchar(60)

TDS_FDW casts all these cases as simple TEXT, which of course in many cases does not fit.

Therefore I tried to recreate them as they are as PostgreSQL domains because that was the original request for the migration.
So these data types have exactly the same name and also corresponding underlying data types on both Sybase and PostgreSQL:
number -> number, univarchar -> character varying

I know that number(10,0) is strange on PostgreSQL and should be rather integer/ bigint but original request was to try to use user defined data types.

But if I alter types of FDW columns, these columns are marked as deleted and disappear from FDW table. Here is how it looks like in pg_attributes for the FDW table:

postgres=# select attrelid, attname, atttypid, attlen, attnum, attisdropped, attfdwoptions from pg_attribute a where attrelid = 19152 order by attnum;
 attrelid |            attname            | atttypid | attlen | attnum | attisdropped |          attfdwoptions
----------+-------------------------------+----------+--------+--------+--------------+----------------------------------
    19152 | tableoid                      |       26 |      4 |     -6 | f            |
    19152 | cmax                          |       29 |      4 |     -5 | f            |
    19152 | xmax                          |       28 |      4 |     -4 | f            |
    19152 | cmin                          |       29 |      4 |     -3 | f            |
    19152 | xmin                          |       28 |      4 |     -2 | f            |
    19152 | ctid                          |       27 |      6 |     -1 | f            |
    19152 | ........pg.dropped.1........  |        0 |     -1 |      1 | t            | {column_name=access_id}
    19152 | ........pg.dropped.2........  |        0 |     -1 |      2 | t            | {column_name=username}
    19152 | ........pg.dropped.3........  |        0 |     -1 |      3 | t            | {column_name=password}
    19152 | maxstunden                    |       23 |      4 |      4 | f            | {column_name=maxstunden}
    19152 | verbrauchtezeit               |       23 |      4 |      5 | f            | {column_name=verbrauchtezeit}
    19152 | ........pg.dropped.6........  |        0 |      1 |      6 | t            | {column_name=multiprovider}
    19152 | ........pg.dropped.7........  |        0 |      1 |      7 | t            | {column_name=multiplelogin}

And this is how it should look like:

       Column       |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 access_id          | "TypID"                     |           | not null |         | plain    |             |              |
 username           | "TypUsername"               |           |          |         | plain    |             |              |
 password           | "TypLongPassword"           |           |          |         | plain    |             |              |
 maxstunden         | integer                     |           |          |         | plain    |             |              |
 verbrauchtezeit    | integer                     |           |          |         | plain    |             |              |
 multiprovider      | "TypBool"                   |           |          |         | plain    |             |              |
 multiplelogin      | "TypBool"                   |           |          |         | plain    |             |              |

At the end we decided to abandon user defined data types and rather make custom substitution. But in some other use cases this could be actually a must have. So I rather report it.

Thank you very much.

Operating system

NAME="Ubuntu"
VERSION="20.04.6 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.6 LTS"
VERSION_ID="20.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal

Version of tds_fdw

2.0.3 with patch for float(0) data types from master branch

  Name   | Version | Schema |                                    Description                                    
---------+---------+--------+-----------------------------------------------------------------------------------
 tds_fdw | 2.0.3   | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

Version of PostgreSQL

PostgreSQL 15.4 (Ubuntu 15.4-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

Version of FreeTDS

root@cf3996ed5ded:/# dpkg -l|grep freetds
ii  freetds-bin                    1.1.6-1.1                         amd64        FreeTDS command-line utilities
ii  freetds-common                 1.1.6-1.1                         all          configuration files for FreeTDS SQL client libraries
ii  freetds-dev                    1.1.6-1.1                         amd64        MS SQL and Sybase client library (static libs and headers)

Logs

I did not find anything in logs related to this problem

Sentences, data structures, data

see above

[GeoffMontee]

Thanks for the report!

For this to work, we would need to implement some kind of mapping for custom types in tdsIterateForeignScan, similar to what is done for DATETIME:

• tds_fdw/src/tds_fdw.c

  Line 1710 in 2323efe

  else if (srctype == SYBDATETIME && attr_oid == TIMESTAMPOID)

• tds_fdw/src/tds_fdw.c

  Line 1836 in 2323efe

  #if (PG_VERSION_NUM >= 90400)