Error when publishing layers in bulk from data store - Invalid column data type

[Strahanjen]

We are running ArcGIS Enterprise 10.9.1 and are connecting to a database server running Postgres13.6/Postgis 3.2. I'm able to add the data store to Portal using the .sde connection file, but when I try to create the layers in bulk, I get this message in Portal, "An unknown error occurred. The operation may not have completed successfully."

I looked in the the ArcGIS Server logs for more information and see the following error which mentions an invalid column data type. How might I track down which column/table is causing the error?  Any other suggestions for troubleshooting this and getting our layers to load? 

"Error executing tool. Publish Datasets In Data Store Job ID: j528bdb98ba9a4b74a71cf99b21f4396e : ERROR 999999: Something unexpected caused the tool to fail. Contact Esri Technical Support (http://esriurl.com/support) to Report a Bug, and refer to the error help for potential solutions or workarounds. Invalid column data type Failed to execute (Publish Datasets In Data Store)."

[ChrisBerryman]

@Strahanjen did you ever find a solution to this?  We are seeing the same issue. 

[OlivieroVitale]

There is a BUG-000170143 submitted on August 21, 2024.
It appears that the issue is non longer reproducible as of ArcGIS Enterprise 11.4

[George_Thompson]

I ran into something similar and it was related to some specific PostGIS extensions installed on the database.

Run the following SQL on the postgresql db:

SELECT name, default_version,installed_version
FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%' or name LIKE 'poi%';

Do you have the any of the following installed:
postgis_sfcgal
fuzzystrmatch
postgis_tiger_geocoder
pointcloud_postgis
pointcloud

If so, do you need them for PostGIS functions that you are using?

- I have not found that ArcGIS needs them for any PostGIS storage items. My testing has NOT been exhaustive.

TAKE A VERIFIED BACKUP before running the below SQL

If not, you can run the following on that specific database:

DROP EXTENSION postgis_tiger_geocoder;
DROP EXTENSION fuzzystrmatch;
DROP EXTENSION postgis_sfcgal;
DROP EXTENSION pointcloud_postgis;
DROP EXTENSION pointcloud;​

 
Then retest.

If you need to install them again, here is the SQL:

CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION pointcloud_postgis;
CREATE EXTENSION pointcloud;