FT's may fail w/ collations are not supported by type name

[rafatower]

During testing of FT from @josemazo:

cartodb_user_6b304b59-2c86-4d7b-a8a2-dc98a5a35d48_db=> select cdb_federated_server_diagnostics('gc_vm');
ERROR:  collations are not supported by type name
LINE 3:   extname name OPTIONS (column_name 'extname') COLLATE pg_ca...
                                                       ^
QUERY:  CREATE FOREIGN TABLE pg_extension (
  oid oid OPTIONS (column_name 'oid') NOT NULL,
  extname name OPTIONS (column_name 'extname') COLLATE pg_catalog."C" NOT NULL,
  extowner oid OPTIONS (column_name 'extowner') NOT NULL,
  extnamespace oid OPTIONS (column_name 'extnamespace') NOT NULL,
  extrelocatable boolean OPTIONS (column_name 'extrelocatable') NOT NULL,
  extversion text OPTIONS (column_name 'extversion') COLLATE pg_catalog."C" NOT NULL,
  extconfig oid[] OPTIONS (column_name 'extconfig'),
  extcondition text[] OPTIONS (column_name 'extcondition') COLLATE pg_catalog."C"
) SERVER cdb_fs_gc_vm
OPTIONS (schema_name 'pg_catalog', table_name 'pg_extension');
CONTEXT:  importing foreign table "pg_extension"
SQL statement "IMPORT FOREIGN SCHEMA pg_catalog LIMIT TO (pg_extension) FROM SERVER cdb_fs_gc_vm INTO cdb_fs_schema_fe3201d828f95848e5f2e05555af026b"
PL/pgSQL function __cdb_fs_import_if_not_exists(name,name,name) line 10 at EXECUTE
SQL statement "SELECT cartodb.__CDB_FS_Import_If_Not_Exists(server_internal, remote_schema, remote_table)"
PL/pgSQL function __cdb_fs_foreign_postgis_version_pg(name) line 8 at PERFORM
PL/pgSQL function __cdb_fs_server_diagnostics_pg(name) line 7 during statement block local variable initialization
PL/pgSQL function cdb_federated_server_diagnostics(text) line 8 at RETURN

this is due to a collation incompatibility. It is a rather complex topic that you can read about here: https://www.postgresql.org/docs/current/collation.html

[rafatower]

Possible fix here: #394

Tested in dbd-team:

cartodb_user_6b304b59-2c86-4d7b-a8a2-dc98a5a35d48_db=> select cdb_federated_server_diagnostics('gc_vm');

                                                                                                                                                              cdb_federated_server_diagnostics

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"server_options": {"host": "****", "port": "5432", "dbname": "postgres", "updatable": "false", "extensions": "postgis", "fetch_size": "1000", "use_remote_estimate": "true"}, "server_version": "12.0", "postgis_version": null, "server_latency_ms": {"avg": 1.598, "max": 3.791, "min": 1.013, "stdev": 1.021, "n_errors": 0, "n_samples": 10}}

[rafatower]

This is more generic and is gonna affect other parts of the code: https://rollbar.com/carto/CartoDB/items/40833/

[rafatower]

The new error as seen from the DB:

ERROR:  collations are not supported by type name at character 105
QUERY:  CREATE FOREIGN TABLE geography_columns (
  f_table_catalog name OPTIONS (column_name 'f_table_catalog') COLLATE pg_catalog."C",
  f_table_schema name OPTIONS (column_name 'f_table_schema') COLLATE pg_catalog."C",
  f_table_name name OPTIONS (column_name 'f_table_name') COLLATE pg_catalog."C",
  f_geography_column name OPTIONS (column_name 'f_geography_column') COLLATE pg_catalog."C",
  coord_dimension integer OPTIONS (column_name 'coord_dimension'),
  srid integer OPTIONS (column_name 'srid'),
  type text OPTIONS (column_name 'type') COLLATE pg_catalog."default"
) SERVER cdb_fs_gc_vm
OPTIONS (schema_name 'public', table_name 'geography_columns');

CONTEXT:  importing foreign table "geography_columns"
SQL statement "IMPORT FOREIGN SCHEMA public LIMIT TO (geometry_columns, geography_columns) FROM SERVER cdb_fs_gc_vm INTO cdb_fs_schema_3980e59c245f2448696c8d6c459645a5"
PL/pgSQL function __cdb_fs_list_foreign_geometry_columns_pg(name,name,name) line 17 at EXECUTE
PL/pgSQL function __cdb_fs_list_foreign_columns_pg(name,name) line 28 at RETURN QUERY
PL/pgSQL function cdb_federated_server_list_remote_tables(text,text) line 8 at RETURN QUERY

STATEMENT:  SELECT * FROM (SELECT registered, local_qualified_name as qualified_name, remote_table as remote_table_name, 'public' as remote_schema_name, id_column_name, geom_column_name, webmercator_column_name, columns FROM cartodb.CDB_Federated_Server_List_Remote_Tables(server => 'gc_vm', remote_schema => 'public')) AS remote_tables ORDER BY remote_table_name asc LIMIT 20 OFFSET 0

The affected function is CDB_Federated_Server_List_Remote_Tables and the troublesome tables ( in this case geography_columns) are from postgis in the schema public, so this is going to affect more than I expected.

[oriolbx]

@rafatower Is this for RT? It was added to the Response Team project.

[rafatower]

Is this for RT? It was added to the Response Team project.

No, sorry, my mistake. At least not yet. Will remove from there.