CMDBuild Forum

Errors when Updating Certain Cards or Creating Relations

Old Versions
#1

I've created a building object.  I cannot update it after I create it, that seems to be a problem with a lot of items throughout.

 

After creating a building I created a complex, I went back to the building to add it to the complex:

 

Call: services/json/management/modcard/updatecard
--------------------------------------------------
Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE "Building" SET "ZIP" = ?, "Group" = ?, "Description" = ?, "Address" = ?, "GlazedArea" = ?, "Name" = ?, "Complex" = ?, "TotalGrossArea" = ?, "CleanableArea" = ?, "TotalHeatedVolume" = ?, "IsInUnit" = ?, "Notes" = ?, "Floors" = ?, "Criticality" = ?, "Availability" = ?, "Condition" = ?, "IsInRoom" = ?, "LastCheckDate" = ?, "City" = ?, "Code" = ?, "ClimaticZone" = ?, "MainUse" = ?, "IsInFloor" = ?, "Units" = ?, "TotalNetArea" = ?, "Rooms" = ?, "IsInBuilding" = ?, "CoveredArea" = ?, "IsInComplex" = ?, "Country" = ?, "TotalVolume" = ?, "User" = ? WHERE "Id" = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "Detail_Building_Position" does not exist
  Where: PL/pgSQL function _cm_trigger_restrict() line 5 during statement block local variable initialization
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:866)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:937)
	at org.cmdbuild.dao.driver.postgres.EntryUpdateCommand.execute(EntryUpdateCommand.java:35)
	at org.cmdbuild.dao.driver.postgres.PostgresDriver.update(PostgresDriver.java:171)
	at org.cmdbuild.dao.entry.DBEntry.updateOnly(DBEntry.java:148)
	at org.cmdbuild.dao.entry.DBCard.save(DBCard.java:79)
	at org.cmdbuild.dao.entry.DBCard.save(DBCard.java:9)
	at org.cmdbuild.dao.entry.ForwardingCardDefinition.save(ForwardingCardDefinition.java:59)
	at org.cmdbuild.services.errors.management.CustomExceptionHandlerDataView$1.save(CustomExceptionHandlerDataView.java:55)
	at org.cmdbuild.dao.entry.ForwardingCardDefinition.save(ForwardingCardDefinition.java:59)
	at org.cmdbuild.services.event.ObservableDataView$ObservableExistingCardDefinition.save(ObservableDataView.java:59)
	at org.cmdbuild.data.store.dao.DataViewStore.update(DataViewStore.java:244)
	at org.cmdbuild.logic.data.access.DefaultDataAccessLogic.updateCard0(DefaultDataAccessLogic.java:752)
	at org.cmdbuild.logic.data.access.DefaultDataAccessLogic.updateCard(DefaultDataAccessLogic.java:731)
	at org.cmdbuild.servlets.json.management.ModCard.updateCard(ModCard.java:559)
	at sun.reflect.GeneratedMethodAccessor799.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:104)
	at org.cmdbuild.servlets.JSONDispatcher.doPost(JSONDispatcher.java:64)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:188)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:48)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:52)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.LocalizationFilter.doFilter(LocalizationFilter.java:127)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "Detail_Building_Position" does not exist
  Where: PL/pgSQL function _cm_trigger_restrict() line 5 during statement block local variable initialization
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:622)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:472)
	at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:429)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:873)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:866)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)
	... 68 more






I then went to the relationship tab and tried to add it there and got this error:




Call: services/json/management/modcard/createrelations
-------------------------------------------------------
Error: org.cmdbuild.exception.ORMException: ORM_ERROR_RELATION_CREATE
	at org.cmdbuild.exception.ORMException$ORMExceptionType.createException(ORMException.java:63)
	at org.cmdbuild.logic.data.access.DefaultDataAccessLogic.saveRelation(DefaultDataAccessLogic.java:1128)
	at org.cmdbuild.logic.data.access.DefaultDataAccessLogic.createRelations(DefaultDataAccessLogic.java:1078)
	at org.cmdbuild.servlets.json.management.ModCard.createRelations(ModCard.java:807)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:104)
	at org.cmdbuild.servlets.JSONDispatcher.doPost(JSONDispatcher.java:64)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:188)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:48)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:52)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.cmdbuild.filters.LocalizationFilter.doFilter(LocalizationFilter.java:127)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)

        


        
          

        

           
           
           
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #2
          
        

        

          
It

would appear that you have not performed the operations described in

the CMDBuild Technical Manual on page 8 regarding Postgis.
CMDBuild Team

        


        

        

           
           
           
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #3
          
        

        

          
Previously Tecnoteca wrote:


It
 would appear that you have not performed the operations described in 
the CMDBuild Technical Manual on page 8 regarding Postgis.
CMDBuild Team


 


 


Hello, thank you for the reply!


 


Could you kindly point me to what step you think I have missed or not followed on Page 8?  


 


I had originally used openmaint-1.1-demo.backup file and used the set of instructions for an existing database.  


 


$ psql ... cmdbuild
cmdbuild=# CREATE SCHEMA gis;
cmdbuild=# SET SEARCH_PATH TO gis, public;
cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql
cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)
cmdbuild=# ALTER DATABASE ${DB_NAME} SET search_path="$user", public, gis;
cmdbuild=# DROP TABLE gis.geometry_columns;
cmdbuild=# DROP TABLE gis.spatial_ref_sys; 


 


At that point I could not activate GIS options at all in OpenMaint, so I dropped all the tables in that Schema and used the top set of instructions for a new database.  


 


$ psql ... cmdbuild
cmdbuild=# CREATE SCHEMA gis;
cmdbuild=# SET SEARCH_PATH TO gis, public;
cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql
cmdbuild=# \i ${POSTGIS_DIR}/spatial_ref_sys.sql
cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)
cmdbuild=# ALTER DATABASE your_database_name_here SET
search_path="$user", public, gis;


 


Since that portion doesn't seem to be working I went ahead and performed the last set of steps of the imported database again on my current database; 


 




mdbuild=# DROP TABLE gis.geometry_columns; cmdbuild=# DROP TABLE gis.spatial_ref_sys; 




 


 


I did not have gis.geometry_columns  (I have a view named that, but not a table.  It did drop gis.spatial_ref_sys.  


 


I am still getting the error when I attempt to create a relation, but can also still successfully turn GIS off and on in setup without any errors.

        


        

        

           
           
           
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #4
          
        

        

          
I created a new DB for testing and re-ran through restoring from the openmaint-1.1-demo.backup db and the openmaint-1.1-empty-gis.backup db.  I am getting some odd errors in the actual unpacking the DB, even though it is finishing and appears to be running a functional DB otherwise.


 


I am running Ubuntu 16 and have installed both Postgres and PostGIS from apt.


 


 


 


Here are a few samples:


 




pg_restore: [archiver (db)] Error while PROCESSING TOC:


pg_restore: [archiver (db)] Error from TOC entry 889; 1255 3733787 FUNCTION box2d_in(cstring) postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory


    Command was: CREATE FUNCTION box2d_in(cstring) RETURNS box2d


    LANGUAGE c IMMUTABLE STRICT


    AS '$libdir/postgis-2.1', 'BOX2D_in';


 


 


 


pg_restore: [archiver (db)] could not execute query: ERROR:  function gis.box2d_in(cstring) does not exist


    Command was: ALTER FUNCTION gis.box2d_in(cstring) OWNER TO postgres;




 


 


 


 


--------------------------------------------


 


 


 




pg_restore: creating TYPE "gis.box3d"


pg_restore: [archiver (db)] Error from TOC entry 2332; 1247 3733782 TYPE box3d postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  function box3d_in(cstring) does not exist


    Command was: CREATE TYPE box3d (


    INTERNALLENGTH = 52,


    INPUT = box3d_in,


    OUTPUT = box3d_out,


    ALIGNMENT = double,


    STORA...


pg_restore: creating SHELL TYPE "gis.geography"


pg_restore: creating FUNCTION "gis.geography_analyze(internal)"


pg_restore: [archiver (db)] Error from TOC entry 1307; 1255 3734314 FUNCTION geography_analyze(internal) postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory


    Command was: CREATE FUNCTION geography_analyze(internal) RETURNS boolean


    LANGUAGE c STRICT


    AS '$libdir/postgis-2.1', 'gserialized...


pg_restore: [archiver (db)] could not execute query: ERROR:  function gis.geography_analyze(internal) does not exist


    Command was: ALTER FUNCTION gis.geography_analyze(internal) OWNER TO postgres;


 


 


pg_restore: creating FUNCTION "gis.geography_in(cstring, oid, integer)"


pg_restore: [archiver (db)] Error from TOC entry 1303; 1255 3734310 FUNCTION geography_in(cstring, oid, integer) postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory


    Command was: CREATE FUNCTION geography_in(cstring, oid, integer) RETURNS geography


    LANGUAGE c IMMUTABLE STRICT


    AS '$libdir/postgi...


pg_restore: [archiver (db)] could not execute query: ERROR:  function gis.geography_in(cstring, oid, integer) does not exist


    Command was: ALTER FUNCTION gis.geography_in(cstring, oid, integer) OWNER TO postgres;




 


 


 


 


------------------------------------


and the root of the other problem


 


------------------------------------


 




pg_restore: processing data for table "gis.Detail_Building_Position"


pg_restore: [archiver (db)] Error from TOC entry 13751; 0 3735070 TABLE DATA Detail_Building_Position postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Building_Position" does not exist


    Command was: COPY "Detail_Building_Position" ("Id", "IdClass", "User", "BeginDate", "Master", "Geometry") FROM stdin;


 


pg_restore: processing data for table "gis.Detail_GenericFurnishingElement_Position"


pg_restore: [archiver (db)] Error from TOC entry 13752; 0 3735078 TABLE DATA Detail_GenericFurnishingElement_Position postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_GenericFurnishingElement_Position" does not exist


    Command was: COPY "Detail_GenericFurnishingElement_Position" ("Id", "IdClass", "User", "BeginDate", "Master", "Geometry") FROM stdin;


 


pg_restore: processing data for table "gis.Detail_Room_Perimeter"


pg_restore: [archiver (db)] Error from TOC entry 13753; 0 3735086 TABLE DATA Detail_Room_Perimeter postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Room_Perimeter" does not exist


    Command was: COPY "Detail_Room_Perimeter" ("Id", "IdClass", "User", "BeginDate", "Master", "Geometry") FROM stdin;


 






pg_restore: creating CONSTRAINT "gis.Detail_Building_Position_pkey"


pg_restore: [archiver (db)] Error from TOC entry 7765; 2606 3742046 CONSTRAINT Detail_Building_Position_pkey postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Building_Position" does not exist


    Command was: ALTER TABLE ONLY "Detail_Building_Position"


    ADD CONSTRAINT "Detail_Building_Position_pkey" PRIMARY KEY ("Id");


 


 


 


pg_restore: creating CONSTRAINT "gis.Detail_GenericFurnishingElement_Position_pkey"


pg_restore: [archiver (db)] Error from TOC entry 7768; 2606 3742054 CONSTRAINT Detail_GenericFurnishingElement_Position_pkey postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_GenericFurnishingElement_Position" does not exist


    Command was: ALTER TABLE ONLY "Detail_GenericFurnishingElement_Position"


    ADD CONSTRAINT "Detail_GenericFurnishingElement_Position_pke...


pg_restore: creating CONSTRAINT "gis.Detail_Room_Perimeter_pkey"


pg_restore: [archiver (db)] Error from TOC entry 7771; 2606 3742047 CONSTRAINT Detail_Room_Perimeter_pkey postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Room_Perimeter" does not exist


    Command was: ALTER TABLE ONLY "Detail_Room_Perimeter"


    ADD CONSTRAINT "Detail_Room_Perimeter_pkey" PRIMARY KEY ("Id");


 


 




pg_restore: creating INDEX "gis.idx_detailbuildingposition_begindate"


pg_restore: [archiver (db)] Error from TOC entry 7766; 1259 3742060 INDEX idx_detailbuildingposition_begindate postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Building_Position" does not exist


    Command was: CREATE INDEX idx_detailbuildingposition_begindate ON "Detail_Building_Position" USING btree ("BeginDate");


 


 


 


pg_restore: creating INDEX "gis.idx_detailgenericfurnishingelementposition_begindate"


pg_restore: [archiver (db)] Error from TOC entry 7769; 1259 3742061 INDEX idx_detailgenericfurnishingelementposition_begindate postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_GenericFurnishingElement_Position" does not exist


    Command was: CREATE INDEX idx_detailgenericfurnishingelementposition_begindate ON "Detail_GenericFurnishingElement_Position" USING btree ...


pg_restore: creating INDEX "gis.idx_detailroomperimeter_begindate"


pg_restore: [archiver (db)] Error from TOC entry 7772; 1259 3742059 INDEX idx_detailroomperimeter_begindate postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Room_Perimeter" does not exist


    Command was: CREATE INDEX idx_detailroomperimeter_begindate ON "Detail_Room_Perimeter" USING btree ("BeginDate");


 






 


 


 


 


 


 


 


 


 


 

Previously Sam wrote:

Previously Tecnoteca wrote:

It
 would appear that you have not performed the operations described in 
the CMDBuild Technical Manual on page 8 regarding Postgis.
CMDBuild Team


 


 


Hello, thank you for the reply!


 


Could you kindly point me to what step you think I have missed or not followed on Page 8?  


 


I had originally used openmaint-1.1-demo.backup file and used the set of instructions for an existing database.  


 


$ psql ... cmdbuild
cmdbuild=# CREATE SCHEMA gis;
cmdbuild=# SET SEARCH_PATH TO gis, public;
cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql
cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)
cmdbuild=# ALTER DATABASE ${DB_NAME} SET search_path="$user", public, gis;
cmdbuild=# DROP TABLE gis.geometry_columns;
cmdbuild=# DROP TABLE gis.spatial_ref_sys; 


 


At that point I could not activate GIS options at all in OpenMaint, so I dropped all the tables in that Schema and used the top set of instructions for a new database.  


 


$ psql ... cmdbuild
cmdbuild=# CREATE SCHEMA gis;
cmdbuild=# SET SEARCH_PATH TO gis, public;
cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql
cmdbuild=# \i ${POSTGIS_DIR}/spatial_ref_sys.sql
cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)
cmdbuild=# ALTER DATABASE your_database_name_here SET
search_path="$user", public, gis;


 


Since that portion doesn't seem to be working I went ahead and performed the last set of steps of the imported database again on my current database; 


 




mdbuild=# DROP TABLE gis.geometry_columns; cmdbuild=# DROP TABLE gis.spatial_ref_sys; 




 


 


I did not have gis.geometry_columns  (I have a view named that, but not a table.  It did drop gis.spatial_ref_sys.  


 


I am still getting the error when I attempt to create a relation, but can also still successfully turn GIS off and on in setup without any errors.




 

        


        

        

           
           
           
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #5
          
        

        

          
Ok,


 


So it looks like your dump file was written using an old convention, as PostGIS is supposed to be portable between PostGIS updates now due to the PostgreSQL-PostGIS link?  I was sure I had done some research to double-check when I was originally getting ready to do this if I could just used 2.2 when something called for 2.0 (BTW, you manual does call for 2.0, but it looks like your dump files wouldn't be compatible with 2.0 either?  I'm not a database guy by any stretch of the imagination outside of tinkering and troubleshooting, and I'm entirely new to Postgres)


 


But those error messages on install plus this article helped me figure out what was going on http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html



 


All the demo databases have this hard call


 




    LANGUAGE c IMMUTABLE STRICT


    AS '$libdir/postgis-2.1', 'BOX2D_in';




 


 I guess I'll have to just purge my database and postgis packages and start fresh but....seriously... I have to be honest, while the software looks very good I'm evaluating along two lines here, A) if the software is user friendly enough for our users to use and B) if the software is easy enough to install/manage from a server admin viewpoint....and C) if we like the software enough to pay the subscription/maintenance fee for the mobile app.


 


So far I've dumped 3.5 days into getting an install of this with all the bells and whistles (Addons/connectors) up and running and troubleshooting weird little issues like this.  This along has sucked up a HUGE amount of that time.  I wouldn't have noticed had I not tried to reinstall the dump file to an empty db, and again to log the output.  


 


And frankly, if this dump is hard coded for 2.1 and the documentation calls for 2.0 and internet support forums at light perusal seemed to thing that PostGIS 2.0, 2.1 and 2.2 would not be a problem for running software on (obviously what I referenced did not expand on the whole using a previous or prepackaged database) then.... I'm just very frustrated with this at the moment is all.


 


 


When I get back to this tomorrow should I use Postgres 9.3 and PostGIS 2.0 as the manually explicitly says?  Or do I need really need Postgres 9.3 and PostGIS 2.1 to be 'functional'.... or will Postgres 9.5 and PostGIS 2.1 suffice???


 


 


 

Previously Sam wrote:



I created a new DB for testing and re-ran through restoring from the openmaint-1.1-demo.backup db and the openmaint-1.1-empty-gis.backup db.  I am getting some odd errors in the actual unpacking the DB, even though it is finishing and appears to be running a functional DB otherwise.


 


I am running Ubuntu 16 and have installed both Postgres and PostGIS from apt.


 


 


 


Here are a few samples:


 




pg_restore: [archiver (db)] Error while PROCESSING TOC:


pg_restore: [archiver (db)] Error from TOC entry 889; 1255 3733787 FUNCTION box2d_in(cstring) postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory


    Command was: CREATE FUNCTION box2d_in(cstring) RETURNS box2d


    LANGUAGE c IMMUTABLE STRICT


    AS '$libdir/postgis-2.1', 'BOX2D_in';


 


 


 


pg_restore: [archiver (db)] could not execute query: ERROR:  function gis.box2d_in(cstring) does not exist


    Command was: ALTER FUNCTION gis.box2d_in(cstring) OWNER TO postgres;




 


 


 


 


--------------------------------------------


 


 


 




pg_restore: creating TYPE "gis.box3d"


pg_restore: [archiver (db)] Error from TOC entry 2332; 1247 3733782 TYPE box3d postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  function box3d_in(cstring) does not exist


    Command was: CREATE TYPE box3d (


    INTERNALLENGTH = 52,


    INPUT = box3d_in,


    OUTPUT = box3d_out,


    ALIGNMENT = double,


    STORA...


pg_restore: creating SHELL TYPE "gis.geography"


pg_restore: creating FUNCTION "gis.geography_analyze(internal)"


pg_restore: [archiver (db)] Error from TOC entry 1307; 1255 3734314 FUNCTION geography_analyze(internal) postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory


    Command was: CREATE FUNCTION geography_analyze(internal) RETURNS boolean


    LANGUAGE c STRICT


    AS '$libdir/postgis-2.1', 'gserialized...


pg_restore: [archiver (db)] could not execute query: ERROR:  function gis.geography_analyze(internal) does not exist


    Command was: ALTER FUNCTION gis.geography_analyze(internal) OWNER TO postgres;


 


 


pg_restore: creating FUNCTION "gis.geography_in(cstring, oid, integer)"


pg_restore: [archiver (db)] Error from TOC entry 1303; 1255 3734310 FUNCTION geography_in(cstring, oid, integer) postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/postgis-2.1": No such file or directory


    Command was: CREATE FUNCTION geography_in(cstring, oid, integer) RETURNS geography


    LANGUAGE c IMMUTABLE STRICT


    AS '$libdir/postgi...


pg_restore: [archiver (db)] could not execute query: ERROR:  function gis.geography_in(cstring, oid, integer) does not exist


    Command was: ALTER FUNCTION gis.geography_in(cstring, oid, integer) OWNER TO postgres;




 


 


 


 


------------------------------------


and the root of the other problem


 


------------------------------------


 




pg_restore: processing data for table "gis.Detail_Building_Position"


pg_restore: [archiver (db)] Error from TOC entry 13751; 0 3735070 TABLE DATA Detail_Building_Position postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Building_Position" does not exist


    Command was: COPY "Detail_Building_Position" ("Id", "IdClass", "User", "BeginDate", "Master", "Geometry") FROM stdin;


 


pg_restore: processing data for table "gis.Detail_GenericFurnishingElement_Position"


pg_restore: [archiver (db)] Error from TOC entry 13752; 0 3735078 TABLE DATA Detail_GenericFurnishingElement_Position postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_GenericFurnishingElement_Position" does not exist


    Command was: COPY "Detail_GenericFurnishingElement_Position" ("Id", "IdClass", "User", "BeginDate", "Master", "Geometry") FROM stdin;


 


pg_restore: processing data for table "gis.Detail_Room_Perimeter"


pg_restore: [archiver (db)] Error from TOC entry 13753; 0 3735086 TABLE DATA Detail_Room_Perimeter postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Room_Perimeter" does not exist


    Command was: COPY "Detail_Room_Perimeter" ("Id", "IdClass", "User", "BeginDate", "Master", "Geometry") FROM stdin;


 






pg_restore: creating CONSTRAINT "gis.Detail_Building_Position_pkey"


pg_restore: [archiver (db)] Error from TOC entry 7765; 2606 3742046 CONSTRAINT Detail_Building_Position_pkey postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Building_Position" does not exist


    Command was: ALTER TABLE ONLY "Detail_Building_Position"


    ADD CONSTRAINT "Detail_Building_Position_pkey" PRIMARY KEY ("Id");


 


 


 


pg_restore: creating CONSTRAINT "gis.Detail_GenericFurnishingElement_Position_pkey"


pg_restore: [archiver (db)] Error from TOC entry 7768; 2606 3742054 CONSTRAINT Detail_GenericFurnishingElement_Position_pkey postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_GenericFurnishingElement_Position" does not exist


    Command was: ALTER TABLE ONLY "Detail_GenericFurnishingElement_Position"


    ADD CONSTRAINT "Detail_GenericFurnishingElement_Position_pke...


pg_restore: creating CONSTRAINT "gis.Detail_Room_Perimeter_pkey"


pg_restore: [archiver (db)] Error from TOC entry 7771; 2606 3742047 CONSTRAINT Detail_Room_Perimeter_pkey postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Room_Perimeter" does not exist


    Command was: ALTER TABLE ONLY "Detail_Room_Perimeter"


    ADD CONSTRAINT "Detail_Room_Perimeter_pkey" PRIMARY KEY ("Id");


 


 




pg_restore: creating INDEX "gis.idx_detailbuildingposition_begindate"


pg_restore: [archiver (db)] Error from TOC entry 7766; 1259 3742060 INDEX idx_detailbuildingposition_begindate postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Building_Position" does not exist


    Command was: CREATE INDEX idx_detailbuildingposition_begindate ON "Detail_Building_Position" USING btree ("BeginDate");


 


 


 


pg_restore: creating INDEX "gis.idx_detailgenericfurnishingelementposition_begindate"


pg_restore: [archiver (db)] Error from TOC entry 7769; 1259 3742061 INDEX idx_detailgenericfurnishingelementposition_begindate postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_GenericFurnishingElement_Position" does not exist


    Command was: CREATE INDEX idx_detailgenericfurnishingelementposition_begindate ON "Detail_GenericFurnishingElement_Position" USING btree ...


pg_restore: creating INDEX "gis.idx_detailroomperimeter_begindate"


pg_restore: [archiver (db)] Error from TOC entry 7772; 1259 3742059 INDEX idx_detailroomperimeter_begindate postgres


pg_restore: [archiver (db)] could not execute query: ERROR:  relation "Detail_Room_Perimeter" does not exist


    Command was: CREATE INDEX idx_detailroomperimeter_begindate ON "Detail_Room_Perimeter" USING btree ("BeginDate");


 






 


 


 


 


 


 


 


 


 


 

Previously Sam wrote:

Previously Tecnoteca wrote:

It
 would appear that you have not performed the operations described in 
the CMDBuild Technical Manual on page 8 regarding Postgis.
CMDBuild Team


 


 


Hello, thank you for the reply!


 


Could you kindly point me to what step you think I have missed or not followed on Page 8?  


 


I had originally used openmaint-1.1-demo.backup file and used the set of instructions for an existing database.  


 


$ psql ... cmdbuild
cmdbuild=# CREATE SCHEMA gis;
cmdbuild=# SET SEARCH_PATH TO gis, public;
cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql
cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)
cmdbuild=# ALTER DATABASE ${DB_NAME} SET search_path="$user", public, gis;
cmdbuild=# DROP TABLE gis.geometry_columns;
cmdbuild=# DROP TABLE gis.spatial_ref_sys; 


 


At that point I could not activate GIS options at all in OpenMaint, so I dropped all the tables in that Schema and used the top set of instructions for a new database.  


 


$ psql ... cmdbuild
cmdbuild=# CREATE SCHEMA gis;
cmdbuild=# SET SEARCH_PATH TO gis, public;
cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql
cmdbuild=# \i ${POSTGIS_DIR}/spatial_ref_sys.sql
cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)
cmdbuild=# ALTER DATABASE your_database_name_here SET
search_path="$user", public, gis;


 


Since that portion doesn't seem to be working I went ahead and performed the last set of steps of the imported database again on my current database; 


 




mdbuild=# DROP TABLE gis.geometry_columns; cmdbuild=# DROP TABLE gis.spatial_ref_sys; 




 


 


I did not have gis.geometry_columns  (I have a view named that, but not a table.  It did drop gis.spatial_ref_sys.  


 


I am still getting the error when I attempt to create a relation, but can also still successfully turn GIS off and on in setup without any errors.




 




 

        


        

        

           
           
           
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #6
          
        

        

          
If

after you create the empty CMDBuild database, and before performing the

restore, the following commands are executed (either shell or

SQL-like):


 


cmdbuild=# CREATE SCHEMA gis;


cmdbuild=# SET SEARCH_PATH TO gis, public;


cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql


cmdbuild=# \i ${POSTGIS_DIR}/spatial_ref_sys.sql


cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)


cmdbuild=# ALTER DATABASE your_database_name_here SET search_path="$user", public, gis;


 

normally everything works properly.
It's all documented in the CMDBuild 2.4 Technical Manual page 8.

CMDBuild Team

        


        

        

           
           
           
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #7
          
        

        

          
Hello,


 


Not sure how I managed to deviate from this the first three times around, or what I may have skipped or overlooked - as I was following the manual and the steps described below - but a fourth completely new database seemed to address my problem, so thank you!


 

Previously Tecnoteca wrote:


If
 after you create the empty CMDBuild database, and before performing the
 restore, the following commands are executed (either shell or 
SQL-like):


 


cmdbuild=# CREATE SCHEMA gis;


cmdbuild=# SET SEARCH_PATH TO gis, public;


cmdbuild=# \i ${POSTGIS_DIR}/postgis.sql


cmdbuild=# \i ${POSTGIS_DIR}/spatial_ref_sys.sql


cmdbuild=# \i ${POSTGIS_DIR}/legacy.sql (if you use PostGIS 2.0)


cmdbuild=# ALTER DATABASE your_database_name_here SET search_path="$user", public, gis;


 

normally everything works properly.
It's all documented in the CMDBuild 2.4 Technical Manual page 8.

CMDBuild Team