topic Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public". in Geodatabase Questions

Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

AndrewKniowski — Wed, 30 Mar 2016 22:03:29 GMT

Is it possible for ArcMap (using 10.3.1) to use a postgresql database where the postgis extension is installed in a schema other than "public"? I prefer to install extensions in a separate schema especially when they are large like postgis. However, ArcGIS continues to throw errors when trying to import datasets from our geodatabase that otherwise works fine with this arrangement. I setup a test database with postgis extension installed in the public schema and was able to import the datasets without error.

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

UmakantJoglekar2 — Thu, 31 Mar 2016 07:09:39 GMT

I remember doing something similar to this a few years back and if I remember right it had worked for us. AS mentioned in the following link, You must use the PostGIS database template to create the PostgreSQL database you use for your geodatabase or enable PostGIS in the database.

http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-postgresql/data-types-postgresql.htm

And I believe this template creates two tables in PUBLIC schema named:

public.spatial_ref_sys

public.geometry_columns

IF you make sure these table exists in PUBLIC schema ArcMap SHOULD be able to handle PostGIS data stored in some other schema. So above two tables should be present in both PUBLIC and the other schema and should also be identical.

I have not tried this with 10.4 but I do remember doing this a few years ago with ArcGIS 10 or 10.X. It would be worth a try to see if it helps.

Thank you!

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

UmakantJoglekar2 — Thu, 31 Mar 2016 07:12:24 GMT

IF you make sure these tables exist in PUBLIC schema, ArcMap SHOULD be able to handle PostGIS data stored in some other schema. So, above two tables should be present in both PUBLIC and the other schema AND they should also be identical.

I have not tried this with 10.4 but I remember doing this a few years ago with ArcGIS 10 or 10.X (dont remember the version of PostgreSQL and PostGIS) with success. It would be worth a try to see if it helps.

Thank you!

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

AndrewKniowski — Thu, 31 Mar 2016 17:36:06 GMT

Thanks for your thoughts. I think you were on to something -Arc is most likely looking for information in one of those tables or a function and not finding it. Unfortunately, the information at the link you mentioned is not correct and probably hasn’t been updated in a long time. The “geometry_columns” table mentioned isn’t a table, and hasn’t been for a while in postgis I don’t believe –it’s a view and as such it is automatically updated.

I did try manually creating a duplicate spatial_ref_sys table and geometry_columns view in the public schema but that did not change anything. I also upgraded to ArcMap 10.4 and things actually go worse which is sad honestly. I was no longer even able to connect to a database that does not have postgis installed in the public schema. Prior to this I was able to connect and view the list of tables at least. Again, so long as postgis and its associated functions, tables, and views are installed in the public schema I am able to view and access all the data including spatial tables in different schemas. I also tried and had similar results with ArcPro 1.1. The error messages were better in ArcMap however. ArcPro failed blindly –it did nothing but returned no error.

I may just move the postgis extension to the public schema to avoid fighting the system for the time being. However, I do find this disappointing considering the great ease at which I can connect and access any and all of the database configurations in QGIS. I hope ESRI takes these issues seriously and continues to improve these features which are currently lagging far behind those of the open source options.

Thanks again for your help and if anyone finds a better workaround please share!

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

The_Right_Honorable_MatthewSyp — Tue, 22 Jun 2021 08:47:20 GMT

I, too, find this supremely disappointing. It will become more difficult since starting with version 2.3.0, PostGIS will not be able to relocate to a different schema from the one in which it was originally installed. I've created an "ArcGIS Idea" to allow proper support for use of any schema and not to hard-code "public". This is more of a defect than a "feature enhancement".

https://community.esri.com/ideas/12688

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

RomanChueh — Thu, 02 Nov 2017 19:11:45 GMT

I am wondering if you have receive any feed back from ESRI regarding to this issue, or have you found a workaround to connect to PostgreSQL database without public schema?

Thanks.

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

The_Right_Honorable_MatthewSyp — Wed, 14 Feb 2018 01:05:00 GMT

No word from ESRI. Has anyone tried this in Arc Pro? Maybe they have corrected it there.

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

ClaudioRuschel — Mon, 19 Feb 2018 13:01:57 GMT

PostGIS install a lot of functions in order to work properly. If these functions are not present in the "public" schema, it is needed to include the name of the schema at the function call. Maybe ArcGIS is not finding these functions.

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

The_Right_Honorable_MatthewSyp — Tue, 20 Feb 2018 16:23:39 GMT

ArcGIS is not finding PostGIS functions or types because it is referencing the public schema directly. The error message returns:

Underlying DBMS error[ERROR: schema “public” does not exist

LINE 1: SELECT public.postgis_lib_version()

^SQLSTATE=3F000]

This is easily reproduced by entering the same query directly into Postgres, and easily solved by either not specifying the schema (relying on the search path) or specifying the correct schema. Unfortunately, ArcGIS has no solution, easy or otherwise, that I have been able to find.

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

The_Right_Honorable_MatthewSyp — Tue, 24 Apr 2018 17:28:21 GMT

A support call was unable to resolve this issue. ArcGIS (Desktop or Pro) requires the "public" schema. The suggested workaround was simply to create the schema, but that does not work. It only changes the connection error message to:

Failed to connect to the specified DBMS instance.

A database connection exception has occurred.

Function or procedure does not exist.

The database log shows that it is still trying to run

SELECT public.postgis_lib_version()

So ArcGIS is requiring that PostGIS is installed in the "public" schema or it cannot connect. This is not a PostGIS requirement. (Schema presence does not solve the issue.).

This is a defect.

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

RexRobichaux2 — Tue, 24 Apr 2018 21:00:56 GMT

Hello Matthew,

If you had a support call that didn't result in a successful workaround for this issue, I'd suggest either reopening the last ticket, or request a new case to be created and see if this request is suitable in nature for an enhancement to be logged. Ensure to mention that you have already logged an ArcGIS Idea documenting this use case, and the reasoning behind it. Although this can't guarantee that this functionality (supported PostGIS storage outside of the Public schema)- it will at least better the odds of getting a final word on if this is possible or will be addressed in the future. I hope this is helpful!

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

The_Right_Honorable_MatthewSyp — Tue, 24 Apr 2018 21:13:05 GMT

Thanks. We're thinking along the same lines. I actually did just that, including links to this discussion and the idea, and they are looking further into submitting a bug or enhancement for it. I'll update if I hear anything.

Re: Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

MichaelDowney1 — Mon, 12 Sep 2022 19:30:50 GMT

In case you are still interested. Esri has supported PostGIS installed in another schema since 2.3/10.7. "ENH-000113705 Provide the ability for the PostGIS extension to be installed in any
schema and not be limited to the Public schema."

Esri still stores https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-postgresql/geodatabase-system-tables-postgresql.htm sde_spatial_references in the Public schema. This is to support select access to all users.