Type existence check for `_cdb_has_usable_geom_record` isn't search_path aware

[nballenger]

I'm trying to get the test suite running for the SQL API repo, and one of the issues I've run into is that the function def for _CDB_Has_Usable_Geom in scripts-available/CDB_CartodbfyTable.sql fails in the test database, because the Postgres instance claims that the data type _cdb_has_usable_geom_record doesn't exist.

The test database is templated off the template_postgis database which has the cartodb-postgresql extension installed, so in fact the function and data type should be there, but because Postgres doesn't let you inherit search_path from the template database, they're not actually visible to the test run user.

Conceivably that wouldn't be a problem, since scripts-available/CDB_CartodbfyTable.sql just goes ahead and redefines the datatype if it isn't present, EXCEPT that it does so by running the following:

https://github.com/CartoDB/cartodb-postgresql/blob/74210c5b5cec34bbcba117b973d7d17ab3ad26a0/scripts-available/CDB_CartodbfyTable.sql#L637-L650

The SELECT 1 FROM pg_type... query on 639 succeeds because the data type does exist in pg_catalog.pg_type, but it isn't actually visible to the user / process because the search_path for the new database doesn't include the cartodb schema.

So two things:

1. If you add AND pg_catalog.pg_type_is_visible(oid) to that SELECT 1 query, it will return 0 if the datatype isn't in the search_path for the database (and so will create it).
2. Seems like maybe in your Depends On section up top of CDB_CartodbfyTable.sql, you may want to include CDB_SearchPath.sql?

[Algunenano]

This should have been fixed when we added the schema to all function calls and types inside the extension. In any case, I'll modify the condition to check just the extension schema and not the whole pg_type.