ListFeatureClasses will not return results on SDE Database

[AaronManuel2]

Hey all, trying to generate a list for a project and can't get any results from the DB using arcpy. We have two SDE databases - our edit DB and 'warehouse' DB, with jobs moving data from edit to warehouse nightly. We are running postgres 9.6.8.

My script, if I can even call it that, is as simple as possible.

env.workspace = r'<connection file path>'
fc_list = arcpy.ListFeatureClasses()
print(fc_list[:20]) #don't want to get the whole list here, just see that it returns something

Pointing to our warehouse db, the script will run forever and never return anything. I have used ListFeatureClasses tons of times and have had issues in the past if I didn't point to the right workspace or there was something strange with the data. But I'm not sure if I've seen it get stuck like this. The connection file is using our admin schema shouldn't be an issue there.

I also tried using the walk function to step through the data that way, but no luck it also just hangs and doesn't return anything.

Strangely, I have no issues getting a list from our edit db. This would seem to indicate something different about our warehouse db that would cause this behavior, but I can't think of what that difference is.

Anyone seen this or have any suggestions how I can troubleshoot further with arcpy? Thanks.

[Anonymous User]

I've had this happen too. Some things to check is if the path to the file is accessible by the user you are running this script under. One time, I had the old 'Database Connections\..' path in the string to the sde file and it would fail silently. Using an absolute path fixed it. Second thing I'd check is if the featureclasses are in datasets in the warehouse db. If they are, you'll have to go through each dataset to list the featureclasses. 

[AaronManuel2]

Hi Jeff, thanks for the ideas. I'm using absolute paths in the script. My connection file path is:

C:\Users\aaronmanuel\AppData\Roaming\ESRI\ArcGISPro\Favorites\Warehouse_coagis.sde

Since the appdata folder is hidden by default in Windows, I think, just to try I made a copy of the connection file and moved it to a folder just a few directories down from C: and that still did not return any results.

There are a few features in datasets but the vast majority of them are not.

[Anonymous User]

That's good testing.  AppData is just hidden from view but is a legit folder so it's visibility shouldn't be an issue. When you look at it in the debugger, is it returning a [] or a NoneType?

[AaronManuel2]

No, it seems to be getting stuck in the call stack, but I can't see where exactly.

I've had issues in the past getting an empty list returned but thats usually when I've messed something up in the path.

[Anonymous User]

when was the last time the db was compressed?

[AaronManuel2]

We have a job that is supposed to run regularly, but maybe something has happened. I'll check with the person that maintains the db, thanks for the idea.

Also for reference, If I run the script on our (versioned)Edit DB, it will return in about 20 seconds. 

[AaronManuel2]

After talking to the person who manages the DB, they are not sure compress will do anything since the warehouse DB, the one I'm working with, is not versioned.

We are thinking about running vacuum on the postgres side on some tables to see if that does anything.