topic Re: PostgreSQL Question in Data Management Questions

PostgreSQL Question

MarshallRobinson — Wed, 23 Jan 2019 21:08:53 GMT

Shot in the dark here. Does anyone know why I would be getting the following postgresql log error? I have an enterprise geodatabase loaded on postgresql on linux and I get this error even when doing a simple editing task for more than ten minutes or so. The feature class I create then get's corrupted and crashes arccatalog.

2019-01-23 13:34:02.425 MST [8901] LOG: execute sde_1548275642_3809_161: DECLARE sdecur_3809_161 BINARY CURSOR WITH HOLD FOR select V__68.shape , V__68.objectid from (SELECT /* ArcSDE NORMAL_FILTER */ b.objectid,b.shape FROM ugssde.sde.forfun b WHERE NOT EXISTS (SELECT * FROM ( SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM sde.d68, sde.sde_state_lineages l WHERE SDE_STATE_ID = 0 AND DELETED_AT = l.lineage_id AND l.lineage_name = $1 AND l.lineage_id <= $2 ) d WHERE b.objectid = d.SDE_DELETES_ROW_ID ) UNION ALL SELECT a.objectid,a.shape FROM sde.a68 a INNER JOIN sde.sde_state_lineages l ON a.SDE_STATE_ID = l.lineage_id WHERE NOT EXISTS (SELECT * FROM ( SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM sde.d68, sde.sde_state_lineages l WHERE DELETED_AT = l.lineage_id AND l.lineage_name = $3 AND l.lineage_id <= $4 ) d WHERE (a.objectid = d.SDE_DELETES_ROW_ID) AND (a.SDE_STATE_ID = d.SDE_STATE_ID) ) AND (l.lineage_name = $5 AND l.lineage_id <= $6) ) V__68 where ((V__68.shape && $7) = 't')
2019-01-23 13:34:02.425 MST [8901] DETAIL: parameters: $1 = '230', $2 = '255', $3 = '230', $4 = '255', $5 = '230', $6 = '255', $7 = '380000000500000008001000110F0000200000000000000092AAF9B6A4049DE28EAFB11482CCBF02000082CCBF02C2CCBF020000C2CCBF02'
2019-01-23 13:34:02.432 MST [30562] LOG: server process (PID 8901) was terminated by signal 11: Segmentation fault
2019-01-23 13:34:02.432 MST [30562] DETAIL: Failed process was running: DECLARE sdecur_3809_161 BINARY CURSOR WITH HOLD FOR select V__68.shape , V__68.objectid from (SELECT /* ArcSDE NORMAL_FILTER */ b.objectid,b.shape FROM ugssde.sde.forfun b WHERE NOT EXISTS (SELECT * FROM ( SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM sde.d68, sde.sde_state_lineages l WHERE SDE_STATE_ID = 0 AND DELETED_AT = l.lineage_id AND l.lineage_name = $1 AND l.lineage_id <= $2 ) d WHERE b.objectid = d.SDE_DELETES_ROW_ID ) UNION ALL SELECT a.objectid,a.shape FROM sde.a68 a INNER JOIN sde.sde_state_lineages l ON a.SDE_STATE_ID = l.lineage_id WHERE NOT EXISTS (SELECT * FROM ( SELECT SDE_DELETES_ROW_ID,SDE_STATE_ID FROM sde.d68, sde.sde_state_lineages l WHERE DELETED_AT = l.lineage_id AND l.lineage_name = $3 AND l.lineage_id <= $4 ) d WHERE (a.objectid = d.SDE_DELETES_ROW_ID) AND (a.SDE_STATE_ID = d.SDE_STATE_ID) ) AND (l.lineage_name = $5 AND l.lineage_id <= $6) ) V__68 where ((V__68.shape && $7) = 't')
2019-01-23 13:34:02.432 MST [30562] LOG: terminating any other active server processes
2019-01-23 13:34:02.433 MST [792] WARNING: terminating connection because of crash of another server process
2019-01-23 13:34:02.433 MST [792] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-01-23 13:34:02.433 MST [792] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2019-01-23 13:34:02.433 MST [22353] WARNING: terminating connection because of crash of another server process
2019-01-23 13:34:02.433 MST [22353] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-01-23 13:34:02.433 MST [22353] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2019-01-23 13:34:02.434 MST [790] WARNING: terminating connection because of crash of another server process
2019-01-23 13:34:02.434 MST [790] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-01-23 13:34:02.434 MST [790] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2019-01-23 13:34:02.442 MST [30562] LOG: all server processes terminated; reinitializing
2019-01-23 13:34:02.457 MST [12107] LOG: database system was interrupted; last known up at 2019-01-23 13:32:21 MST
2019-01-23 13:34:02.495 MST [12107] LOG: database system was not properly shut down; automatic recovery in progress
2019-01-23 13:34:02.496 MST [12107] LOG: redo starts at 0/BD9C2368
2019-01-23 13:34:02.497 MST [12107] LOG: invalid record length at 0/BD9E0E78: wanted 24, got 0
2019-01-23 13:34:02.497 MST [12107] LOG: redo done at 0/BD9E0D50
2019-01-23 13:34:02.497 MST [12107] LOG: last completed transaction was at log time 2019-01-23 13:33:51.462835-07
2019-01-23 13:34:02.508 MST [30562] LOG: database system is ready to accept connections

Re: PostgreSQL Question

EarlMedina — Thu, 24 Jan 2019 15:46:26 GMT

I think a bit more information might help - which version of PostgreSQL is this (i.e., what is the result of select version();)?

What are the specs of the machine? Is it just PostgreSQL installed on the machine? What is the Geodatabase version?

Re: PostgreSQL Question

MarshallRobinson — Thu, 24 Jan 2019 20:12:10 GMT

Thanks for looking into this, here is our environment:

PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

Re: PostgreSQL Question

EarlMedina — Thu, 24 Jan 2019 20:21:05 GMT

Sorry, can you answer the other questions as well?

What are the specs of the machine? Is it just PostgreSQL installed on the machine? What is the Geodatabase version?

Also, what is the version of the client you are editing with?

Re: PostgreSQL Question

MarshallRobinson — Thu, 24 Jan 2019 20:50:19 GMT

It is running on a linux server with 8gb RAM, 2 cores. It is not just postgresql installed. Python/Apache/PHP are also installed on the server. The geodatabase version is 10.6.1 (release: 106101 sdesvr_rel_low: 93001). I'm using ArcMap 10.6.1 to edit.

Re: PostgreSQL Question

MarshallRobinson — Thu, 24 Jan 2019 22:55:21 GMT

Here is another error that ArcMap 10.6.1 and ArcGIS Pro 2.3 says. It happens when I try to refresh the connection to the sde after the edit session stops working and all of the features disappear:

Underlying DBMS error [no connection to the server
::SQLSTATE=] [sdename.sde.GDB_Items]

Re: PostgreSQL Question

SilvioDa_Silva — Tue, 26 Mar 2019 10:51:21 GMT

Hi,

Did you find a fix to this? We are encountering the exact same issue, when running Identity_analysis from arcpy. PostgreSQL 10.3 and ArcGIS Deskotp 10.6.1

Thanks!

Re: PostgreSQL Question

AndreasWalter — Thu, 20 Feb 2020 13:03:12 GMT

Hello,

we encountered the same problem with our PostgreSQL 10.3 and ArcGIS Deskotp 10.6.1.
Is somebody figured something out, or found a workaround to this?

thanks in advance

Re: PostgreSQL Question

MartinKrál — Sat, 11 Dec 2021 13:44:52 GMT

Hello,

have you tried 10.7.1?
I do not know if we have just encounterd the same problem, but (with ArcGIS/geodatabase 10.6.1 on PostgreSQL 10 on RHEL 6.9) some feature classes (not all feature classes) could not be displayed by ArcMAP/ArcCatalog
It always ended with disconnect of all postgresql connections and same error message appeared in postgresql log.

In our case problem was in following spatial selection using sde.&& st_geometry operator

select * from sdedo.zus_technologie t
where ((t.shape && '400000000500000008001000E19304002800000000000000A9F0C09BCCFFFF0FB8F7DDB0AEFFFF0F8C86B9AA08000086D29FE10ACC86B9AA080000C6D29FE10A')='t')‍‍

but following simillar selection using just sde.st_geo_overlap function worked fine

select * from sdedo.zus_technologie t
where (sde.st_geo_overlap(t.shape, '400000000500000008001000E19304002800000000000000A9F0C09BCCFFFF0FB8F7DDB0AEFFFF0F8C86B9AA08000086D29FE10ACC86B9AA080000C6D29FE10A')='t')‍‍

difference between sde.&& operator and function sde.st_geo_overlap is in additional calls of RESTRIC/JOIN functions that are used to calculate selectivity for postgresql optimizer for && operator.

see operator definition:

CREATE OPERATOR sde.&& (
    PROCEDURE = sde.st_geo_overlap,
    LEFTARG = sde.st_geometry,
    RIGHTARG = sde.st_geometry,
    COMMUTATOR = OPERATOR(sde.&&),
    RESTRICT = sde.st_geom_gist_sel,
    JOIN = sde.st_geom_gist_joinsel
);‍‍‍‍‍‍‍‍

The one that seemed to be problematic in our case was function sde.st_geom_gist_sel.
It is a function that references st_geo_gist_sel in st_geometry.so.

So I tried to do following test (please do not take it as a recommended solution):

I put 10.7.1 st_geometry.so in postgresql/lib directory (changed name to st_geometry_1071.so there was already 10.6.1 st_geometry.so) and
changed function sde.st_geom_gist_sel to reference same function but in new 10.7.1 library.

CREATE OR REPLACE FUNCTION sde.st_geom_gist_sel(internal, oid, internal, integer) RETURNS double precision
    LANGUAGE c
    AS 'st_geometry_1071', 'st_geo_gist_sel';‍‍‍

and problem was gone.

So I would say that our problem was solved in st_geometry.so of 10.7.1 (Maybe your problem was solved there too)

Regards

Re: PostgreSQL Question

MartinKrál — Tue, 25 Feb 2020 15:35:55 GMT

here is probably the official way how to solve it

ArcGIS (Desktop, Engine, Server) 10.6.1 PostgreSQL Performance and Version 10 Support Patch

Esri Support ArcMap 10.6 (10.6.1)

see:

BUG-000115897 - Polygon and Line Feature Classes imported into PostgreSQL 10.3+ using the Feature Class to Feature Class tool fail to draw and throw "Failure to access the DBMS server" error.

Regards

Re: PostgreSQL Question

AndreasWalter — Wed, 26 Feb 2020 07:24:16 GMT

Hey,

thanks for the reply! Yes, we encountered exactly the same failure / problematic. I'll try your "no recommended solution" for a test run, and take this into count for my consideration for upgrading to 10.7.1

Regards

Re: PostgreSQL Question

AndreasWalter — Wed, 26 Feb 2020 07:28:48 GMT

Hey,

thanks you for your answer! As I can see, the BUG-000115897 was fixed in the 10.7.1, so I guess I have to updgrade ...

Regards

Re: PostgreSQL Question

MartinKrál — Wed, 26 Feb 2020 07:33:48 GMT

Hi,

you can check that 10.6.1 PostgreSQL Performance and Version 10 Support Patch first. That 10.6.1 patch solved our problem here too, so I think there is no need to upgrade.

Regards