Opened 3 years ago

Closed 2 years ago

#852 closed task (fixed)

Eliminate PL/pgSQL functions from petascope

Reported by: dmisev Owned by: bthapaliya
Priority: critical Milestone: 9.0.x
Component: petascope Version: development
Keywords: Cc: pbaumann, mdumitru
Complexity: Medium

Description

PL/pgSQL functions are used in petascope to compute some queries. This saves some number of queries, but ties petascope to postgres, so they should be eliminated:

  1. identify which PL/pgSQL functions in source:applications/petascope/src/main/db/petascope/update8 (in particular in utilities.sql and macros.sql) are used in the SQL queries executed by petascope. All SQL queries are executed in DbMetadataSource, so look here to identify which (custom) functions they use.
  2. translate those functions to Java, so that the function call in the SQL query in DbMetadataSource is instead replaced with the result from the Java method.

Change History (8)

comment:1 Changed 2 years ago by pcampalani

Hi there,
those functions were developed in order to maintain integrity of the data at coverage level, ie. ensure you do not insert data that does not make sense.
Make sure Petascope reproduces those checks: I'm here in case something is not clear about them!

comment:2 Changed 2 years ago by dmisev

  • Cc changed from pbaumann,mdumitru to pbaumann, mdumitru

I'm getting an exception on coverages imported with rasimport, after commit changeset:b8dfedd6d3464f3194a304398b675e869b3072ca

 DEBUG [20:46:34] DbMetadataSource@2731: SQL query:  SELECT ps_uom.code,ps_quantity.label,ps_quantity.description,ps_quantity.definition_uri,ps_quantity.nil_ids,ps_nil_value.id,ps_nil_value.value AS nil_values , ps_nil_value.reason AS nil_reasons FROM ps_nil_value , ps_quantity INNER JOIN ps_uom ON (ps_uom.id=ps_quantity.uom_id)  WHERE ps_quantity.id=11 GROUP BY ps_uom.code,ps_quantity.label,ps_quantity.description,ps_quantity.definition_uri,ps_quantity.nil_ids,ps_nil_value.id , nil_values , nil_reasons
 ERROR [20:46:34] DbMetadataSource@1424: Failed reading the coverage metadata
InvalidServiceConfiguration: No SWE quantities stored in the database.
	at petascope.core.DbMetadataSource.readSWEQuantity(DbMetadataSource.java:2735)
	at petascope.core.DbMetadataSource.read(DbMetadataSource.java:1060)
	at petascope.wcs2.handlers.GetCapabilitiesHandler.handle(GetCapabilitiesHandler.java:312)
	at petascope.wcs2.handlers.GetCapabilitiesHandler.handle(GetCapabilitiesHandler.java:68)
	at petascope.wcs2.extensions.AbstractProtocolExtension.handle(AbstractProtocolExtension.java:70)
	at petascope.PetascopeInterface.handleWcs2Request(PetascopeInterface.java:646)
	at petascope.PetascopeInterface.handleWcsRequest(PetascopeInterface.java:576)
	at petascope.PetascopeInterface.doGet(PetascopeInterface.java:356)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:558)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:237)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:281)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)

comment:3 Changed 2 years ago by bthapaliya

That is weird. It is not finding the metadata. Will look at the mentioned changeset to see if something there can cause it.

comment:4 Changed 2 years ago by mdumitru

Probably needs a left join here, instead of the inner join, as it would require a nill value to always be present, which is not the case.
http://rasdaman.org/browser/applications/petascope/src/main/java/petascope/core/DbMetadataSource.java#L2709

comment:5 Changed 2 years ago by mdumitru

  • Priority changed from major to critical

Actually, it's the line above: http://rasdaman.org/browser/applications/petascope/src/main/java/petascope/core/DbMetadataSource.java#L2708

 FROM " + TABLE_NIL_VALUE + " , " + TABLE_QUANTITY will need something in the table NIL_VALUE each time the query is executed.

Replacing it with TABLE_NIL_VALUE LEFT JOIN TABLE_QUANTITY, might do the trick, but I don't have enough time to check it today, and I'll be at OGC next days. If any of you could submit a patch, that would be great, right now, nothing works due to this issue.
Raising the level to Critical as Petascope is currently unusable.

comment:6 Changed 2 years ago by dmisev

Ok I'll check it now.

comment:7 Changed 2 years ago by dmisev

Patch submitted

comment:8 Changed 2 years ago by dmisev

  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.