PostgreSQL Question

2436
12
01-23-2019 01:08 PM
MarshallRobinson
New Contributor III

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

0 Kudos
12 Replies
EarlMedina
Esri Regular Contributor

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?

0 Kudos
MarshallRobinson
New Contributor III

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

0 Kudos
EarlMedina
Esri Regular Contributor

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?

0 Kudos
MarshallRobinson
New Contributor III

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.

0 Kudos
MarshallRobinson
New Contributor III

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]

0 Kudos
SilvioDa_Silva
New Contributor II

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!

0 Kudos
AndreasWalter
New Contributor

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

0 Kudos
MartinKrál
Esri Contributor

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

AndreasWalter
New Contributor

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

0 Kudos