Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
rafatower opened this issue Jan 21, 2020 · 5 comments · May be fixed by #394
Open

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

rafatower opened this issue Jan 21, 2020 · 5 comments · May be fixed by #394

Comments

@rafatower
Copy link
Contributor

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 pushed a commit that referenced this issue Jan 21, 2020
Fix for collation errors when importing catallog tables
bc5c6e2 
This fixes #393 by avoiding collation options. The affected function
is only used from diagnostics, and aside from `pg_extension` only
meant to import other catallog tables for internal
diagnostics (`pg_catalog` and `pg_settings`) so it shall be pretty
safe.
@rafatower rafatower linked a pull request Jan 21, 2020 that will close this issue
Open
@rafatower
Copy link
Contributor Author

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
Copy link
Contributor Author

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

@rafatower rafatower changed the title CDB_Federated_Server_Diagnostics may fail w/ collations are not supported by type name FT's may fail w/ collations are not supported by type name Jan 22, 2020
@rafatower
Copy link
Contributor Author

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
Copy link

oriolbx commented Jan 24, 2020 •
edited
Loading

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

@rafatower
Copy link
Contributor Author

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix for collation errors when importing catallog tables CartoDB/cartodb-postgresql
2 participants
@rafatower @oriolbx