Virtuoso queries with OPTION return different results in isql and web

Virtuoso Universal Server Web Application Services
1

When using Virtuoso OpenSource v7.2.7 to load UniProt RDF, the results for the following query are different between isql and WEB. the results for isql appear to be the same as in previous versions, how can I handle this to get similar results via WEB?

DEFINE sql:select-option "order"
DEFINE input:inference "go-trans"
PREFIX obo: <http://www.geneontology.org/formats/oboInOwl#>
SELECT ?target ?link ?step ?linkName ?linkId
WHERE {
  {
    SELECT DISTINCT ?target ?link ?step
    WHERE {
      {
        SELECT ?target ?parent
        WHERE {
          ?target obo:id "GO:0016123"^^xsd:string .
          VALUES (?parent) {(<http://purl.obolibrary.org/obo/GO_0008150>)}
        }
      }
      ?target rdfs:subClassOf ?parent
      OPTION (TRANSITIVE,
              T_DISTINCT,
              T_EXISTS,
              T_DIRECTION 2,
              T_IN(?target),
              T_OUT(?parent),
              T_MIN(0),
              T_STEP(?target) AS ?link,
              T_STEP('step_no') AS ?step ) .
    }
  }
  ?link rdfs:label ?linkName .
  ?link obo:id ?linkId .
  FILTER (?link != <http://purl.obolibrary.org/obo/GO_0008150>)
}
ORDER BY DESC(?step)
2

What is the version and gitid of the Virtuoso open source instance, which can be obtained by running the query:

SELECT	
  ( bif:sys_stat('st_dbms_name')          AS ?name )
  ( bif:sys_stat('st_dbms_ver')           AS ?version )
  ( bif:sys_stat('st_build_thread_model') AS ?thread )
  ( bif:sys_stat('st_build_opsys_id')     AS ?opsys )
  ( bif:sys_stat('st_build_date')         AS ?date )
  (bif:sys_stat ('git_head') AS ?git_head)

WHERE
  { }

What Uniprot dataset do you have loaded in your instance ?

Does the query return the expected results when run against the Uniprot Public SPARQL Endpoint ?

I presume your Virtuoso Uniprot instance was setup by yourself, loading the Uniprot datasets with the Virtuoso RDF Bulk Loader ?

3

Thank you for your prompt response.We are pleased to respond to your inquiry.

| name                       |    version | thread    | opsys | date        | git_head |
|----------------------------+------------+-----------+-------+-------------+----------|
| OpenLink Virtuoso   Server | 07.20.3234 | -pthreads | Linux | Jun  1 2022 | 8312faa  |

https://ftp.uniprot.org/pub/databases/uniprot/current_release/rdf/

No, I modified the query as follows because I got a lexical error, but
it is also a lexical error.

PREFIX obo: <http://www.geneontology.org/formats/oboInOwl#>
SELECT ?target ?link ?step ?linkName ?linkId
WHERE {
{
 SELECT DISTINCT ?target ?link ?step
 WHERE {
   {
     SELECT ?target ?parent
     WHERE {
       ?target obo:id "GO:0016123" .
       VALUES (?parent) {(<http://purl.obolibrary.org/obo/GO_0008150>)}
     }
   }
   ?target rdfs:subClassOf ?parent
   OPTION (TRANSITIVE,
           T_DISTINCT,
           T_EXISTS,
           T_DIRECTION 2,
           T_IN(?target),
           T_OUT(?parent),
           T_MIN(0),
           T_STEP(?target) AS ?link,
           T_STEP('step_no') AS ?step ) .
 }
}
?link rdfs:label ?linkName .
?link obo:id ?linkId .
FILTER (?link != <http://purl.obolibrary.org/obo/GO_0008150>)
}
ORDER BY DESC(?step)

→ Malformed query. Lexical error at line 15, column 13. Encountered: " " (32), after : “OPTION”

Yes, I set up the VOS as follows

git clone https://github.com/openlink/virtuoso-opensource.git
autogen.sh
configure
make
make install

And buld loaded as follows

$ISQL $PORT $USERNAME $PASSWORD exec="grant select  on \"DB.DBA.SPARQL_SINV_2\"   to \"SPARQL\";"
$ISQL $PORT $USERNAME $PASSWORD exec="grant execute on \"DB.DBA.SPARQL_SINV_IMP\" to \"SPARQL\";"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.VT_BATCH_UPDATE ('DB.DBA.RDF_OBJ', 'ON', NULL);"

$ISQL $PORT $USERNAME $PASSWORD exec="log_enable(2, 1); ld_dir_all('$DATA_DIR/uniprot/rdf', 'citation*.rdf.xz', 'http://foo/uniprot');"
$ISQL $PORT $USERNAME $PASSWORD exec="log_enable(2, 1); ld_dir_all('$DATA_DIR/uniprot/rdf', 'database*.rdf.xz', 'http://foo/uniprot');"
:
$ISQL $PORT $USERNAME $PASSWORD exec="log_enable(2, 1); ld_dir_all('$DATA_DIR/uniprot/rdf', 'tissue*.rdf.xz', 'http://foo/uniprot');"
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
:
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
wait

$ISQL $PORT $USERNAME $PASSWORD exec="log_enable(2, 1); ld_dir_all('$DATA_DIR/uniprot/rdf', 'uniref_*.rdf.xz', 'http://foo/uniprot');"
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
:
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
wait

$ISQL $PORT $USERNAME $PASSWORD exec="log_enable(2, 1); ld_dir_all('$DATA_DIR/uniprot/rdf', 'uniprotkb_*.rdf.xz', 'http://foo/uniprot');"
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
:
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
wait

$ISQL $PORT $USERNAME $PASSWORD exec="checkpoint;"
$ISQL $PORT $USERNAME $PASSWORD exec="commit work;"
$ISQL $PORT $USERNAME $PASSWORD exec="checkpoint;"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.RDF_OBJ_FT_RULE_ADD (null, null, 'All');"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.VT_INC_INDEX_DB_DBA_RDF_OBJ ();"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.XML_SET_NS_DECL ('dcterms', 'http://purl.org/dc/terms/',       2);"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.XML_SET_NS_DECL ('obo',     'http://purl.obolibrary.org/obo/', 2);"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.XML_SET_NS_DECL ('qudt',    'http://qudt.org/schema/qudt/',    2);"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.XML_SET_NS_DECL ('prov',    'http://www.w3.org/ns/prov#',      2);"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.XML_SET_NS_DECL ('pav',     'http://purl.org/pav/',            2);"
$ISQL $PORT $USERNAME $PASSWORD exec="SPARQL INSERT INTO GRAPH <http://foo/ontology/go> { <http://www.w3.org/2000/01/rdf-schema#subClassOf> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://www.w3.org/2002/07/owl#TransitiveProperty> . };"
$ISQL $PORT $USERNAME $PASSWORD exec="rdfs_rule_set('go-trans', 'http://foo/ontology/go');"
$ISQL $PORT $USERNAME $PASSWORD exec="SPARQL INSERT INTO GRAPH <http://foo/chebi> { <http://www.w3.org/2000/01/rdf-schema#subClassOf> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://www.w3.org/2002/07/owl#TransitiveProperty> . };"
$ISQL $PORT $USERNAME $PASSWORD exec="rdfs_rule_set('chebi-trans', 'http://foo/chebi');"

$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.VT_BATCH_UPDATE ('DB.DBA.RDF_OBJ', 'ON', NULL);"
$ISQL $PORT $USERNAME $PASSWORD exec="log_enable(2, 1); ld_dir_all('$DATA_DIR/uniprot/rdf', 'uniparc_*.rdf.xz', 'http://integbio.jp/rdf/mirror/uniprot');"
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
:
$ISQL $PORT $USERNAME $PASSWORD exec="rdf_loader_run();" &
wait

$ISQL $PORT $USERNAME $PASSWORD exec="checkpoint;"
$ISQL $PORT $USERNAME $PASSWORD exec="commit work;"
$ISQL $PORT $USERNAME $PASSWORD exec="checkpoint;"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.RDF_OBJ_FT_RULE_ADD (null, null, 'All');"
$ISQL $PORT $USERNAME $PASSWORD exec="DB.DBA.VT_INC_INDEX_DB_DBA_RDF_OBJ ();"

Thank you in advance for your cooperation.

4

The dataset you are using is from Aug 2022 based on link provided , whereas the dataset in use on the Uniprot public SPARQL endpoint is indicated to be from Mar 2022, is that the dataset you were using previously that ran the query successfully via both interfaces ?

Was the same Virtuoso binary and virtuoso.ini configuration file used with the previous dataset that was working, and can you provide a copy of the virtuoso.ini file for review ?

What is the triple count ie select count(*) where {?s ?p ?o} when run against isql and the SPARQL endpoint for the new dataset loaded, as they should be the same ?

Is your SPARQL endpoint accessible publicly or only from within your network/organisation ?

The Virtuoso default SPARQL endpoint ie http://<cname>/sparql runs as the internal SPARQL user. If the query is run via the Virtuoso default authenticated SPARQL endpoint ie http://<cname>/sparql-auth, which prompts for authentication and logging in as the dba user , as we used with isql, does the query return the expected results ?

5

Regarding the previous results that worked fine, the data was from the 2021-06-16 version, and similar results were obtained with the two interfaces isql and www.

No, there are changes in virtuoso.ini.

The difference is as follows

--- old
+++ new
@@ -32 +32 @@
-MaxCheckpointRemap             = 2000000
+MaxCheckpointRemap             = 63161283
@@ -40 +40 @@
-MaxCheckpointRemap             = 2000000
+MaxCheckpointRemap             = 63161283
@@ -58 +58 @@
-MaxClientConnections           = 10
+MaxClientConnections           = 128
@@ -68 +68 @@
-ThreadThreshold                        = 10
+ThreadThreshold                        = 128
@@ -76 +76 @@
-MaxMemPoolSize                  = 200000000
+MaxMemPoolSize                  = 134217728
@@ -79,2 +79,2 @@
-IndexTreeMaps                   = 64
-MaxQueryMem                    = 10G ; memory allocated to query processor
+IndexTreeMaps                   = 256
+MaxQueryMem                    = 2G  ; memory allocated to query processor
@@ -82 +82 @@
-MaxVectorSize                  = 4000000 ; query vector size threshold.
+MaxVectorSize                  = 1000000 ; query vector size threshold.
@@ -84,2 +84,2 @@
-ThreadsPerQuery                = 4
-AsyncQueueMaxThreads           = 10
+ThreadsPerQuery                = 128
+AsyncQueueMaxThreads           = 128
@@ -116,3 +116,7 @@
-NumberOfBuffers          = 22100000
-MaxDirtyBuffers          = 16900000
-HashJoinSpace = 64G
+NumberOfBuffers          = 252645135
+MaxDirtyBuffers          = 189483851
+ServerThreads = 512
+MaxCheckpointRemap = 63161283
+HashJoinSpace = 1G
+CheckpointSyncMode = 2
+FDsPerFile = 1
@@ -119,0 +124 @@
+TransactionAfterImageLimit = 1000000000

virtuoso.ini is as follows

[Database]
DatabaseFile                    = /mnt/db/virtuoso.db
ErrorLogFile                    = /mnt/db/virtuoso.log
LockFile                        = /mnt/db/virtuoso.lck
TransactionFile                 = /mnt/db/virtuoso.trx
xa_persistent_file              = /mnt/db/virtuoso.pxa
ErrorLogLevel                   = 7
FileExtend                      = 200
MaxCheckpointRemap              = 63161283
Striping                        = 0
TempStorage                     = TempDatabase

[TempDatabase]
DatabaseFile                    = /mnt/db/virtuoso-temp.db
TransactionFile                 = /mnt/db/virtuoso-temp.trx
MaxCheckpointRemap              = 63161283
Striping                        = 0

[Parameters]
ServerPort                      = 1111
LiteMode                        = 0
DisableUnixSocket               = 1
DisableTcpSocket                = 0
MaxClientConnections            = 128
CheckpointInterval              = 60
O_DIRECT                        = 0
CaseMode                        = 2
MaxStaticCursorRows             = 5000
CheckpointAuditTrail            = 0
AllowOSCalls                    = 0
SchedulerInterval               = 10
DirsAllowed                     = .,
/opt/virtuoso-opensource/share/virtuoso/vad, /usr/share/proj, /mnt/rdf
ThreadCleanupInterval           = 0
ThreadThreshold                 = 128
ResourcesCleanupInterval        = 0
FreeTextBatchSize               = 100000
SingleCPU                       = 0
VADInstallDir                   = /opt/virtuoso-opensource/share/virtuoso/vad/
PrefixResultNames               = 0
RdfFreeTextRulesSize            = 100
IndexTreeMaps                   = 256
MaxMemPoolSize                  = 134217728
PrefixResultNames               = 0
MacSpotlight                    = 0
IndexTreeMaps                   = 256
MaxQueryMem                     = 2G  ; memory allocated to query processor
VectorSize                      = 1000          ; initial parallel
query vector (array of query operations) size
MaxVectorSize                   = 1000000 ; query vector size threshold.
AdjustVectorSize                = 1
ThreadsPerQuery                 = 128
AsyncQueueMaxThreads            = 128
NumberOfBuffers                 = 252645135
MaxDirtyBuffers                 = 189483851
ServerThreads                   = 512
MaxCheckpointRemap              = 63161283
HashJoinSpace                   = 1G
CheckpointSyncMode              = 2
FDsPerFile                      = 1
UnremapQuota                    = 0
TransactionAfterImageLimit      = 1000000000

[HTTPServer]
ServerPort                      = 8890
ServerRoot                      = /opt/virtuoso-opensource/var/lib/virtuoso/vsp
MaxClientConnections            = 10
DavRoot                         = DAV
EnabledDavVSP                   = 0
HTTPProxyEnabled                = 0
TempASPXDir                     = 0
DefaultMailServer               = localhost:25
ServerThreads                   = 10
MaxKeepAlives                   = 10
KeepAliveTimeout                = 10
MaxCachedProxyConnections       = 10
ProxyConnectionCacheTimeout     = 15
HTTPThreadSize                  = 280000
HttpPrintWarningsInOutput       = 0
Charset                         = UTF-8
MaintenancePage                 = atomic.html
EnabledGzipContent              = 1

[AutoRepair]
BadParentLinks                  = 0

[Client]
SQL_PREFETCH_ROWS               = 10000
SQL_PREFETCH_BYTES              = 1600000
SQL_QUERY_TIMEOUT               = 0
SQL_TXN_TIMEOUT                 = 0

[VDB]
ArrayOptimization               = 0
NumArrayParameters              = 10
VDBDisconnectTimeout            = 1000
KeepConnectionOnFixedThread     = 0

[Replication]
ServerName                      = db-INSPIRO
ServerEnable                    = 1
QueueMax                        = 50000

[Striping]
Segment1                        = 100M, db-seg1-1.db, db-seg1-2.db
Segment2                        = 100M, db-seg2-1.db

[Zero Config]
ServerName                      = virtuoso (INSPIRO)

[Mono]

[URIQA]
DynamicLocal                    = 0
DefaultHost                     = localhost:8890

[SPARQL]
ResultSetMaxRows                = 1000000
MaxQueryCostEstimationTime      = 0   ; in seconds
MaxQueryExecutionTime           = 600 ; in seconds
DefaultQuery                    = select distinct ?Concept where {[] a
?Concept} LIMIT 100
DeferInferenceRulesInit         = 0  ; controls inference rules loading
MaxSortedTopRows                = 200000

[Plugins]
LoadPath                        = /opt/virtuoso-opensource/lib/virtuoso/hosting
Load1                           = plain, wikiv
Load2                           = plain, mediawiki
Load3                           = plain, creolewiki

When run in isql it returned 110,343,156,196, but when run in http it did not return a single line of results.
We have confirmed that a simple query returns results.

SELECT * { ?s ?p ?o . } LIMIT 1

SPARQL endopoint is not accessible from the outside.

I tried accessing via http:///sparql-auth, and the results for both the query and all triples in this case were the same as in the http:///sparql case.

Thank you for your time.

6

I was not able to get any results with http when I cut out the subquery (using the first query in the following two), but I was able to get results by replacing OPTION with property path, which I will report for your information.

# OPTION
DEFINE sql:select-option "order"
DEFINE input:inference "go-trans"
PREFIX obo: <http://www.geneontology.org/formats/oboInOwl#>
    SELECT DISTINCT ?target ?link ?step
    WHERE {
      {
        SELECT ?target ?parent
        WHERE {
          ?target obo:id "GO:0016123" .
          VALUES (?parent) {(<http://purl.obolibrary.org/obo/GO_0008150>)}
        }
      }
      ?target rdfs:subClassOf ?parent
      OPTION (TRANSITIVE,
              T_DISTINCT,
              T_EXISTS,
              T_DIRECTION 2,
              T_IN(?target),
              T_OUT(?parent),
              T_MIN(0),
              T_STEP(?target) AS ?link,
              T_STEP('step_no') AS ?step ) .
    }

# property path (i.e. rdfs:subClassOf*)
DEFINE sql:select-option "order"
PREFIX obo: <http://www.geneontology.org/formats/oboInOwl#>
    SELECT DISTINCT ?target
    WHERE {
      {
        SELECT ?target ?parent
        WHERE {
          ?target obo:id "GO:0016123" .
          VALUES (?parent) {(<http://purl.obolibrary.org/obo/GO_0008150>)}
        }
      }
      ?target rdfs:subClassOf* ?parent .
    }
7

How long did “isql” command line tool take to count the indicated 110,343,156,196 triples in the database ?

In the INI file you have MaxQueryExecutionTime = 600 set meaning a 10min timeout period with the Virtuoso Anytime query feature enabled as a result.

Can you disable the Anytime to eliminate the possibility of partial results being returned by setting ExecutionTimeout = 0 and MaxQueryExecutionTime = 0 and removing the MaxQueryCostEstimationTime param which is not needed, in the [SPARQL] section of the INI file ie

[SPARQL]
ResultSetMaxRows                = 1000000
ExecutionTimeout                = 0   ; in seconds
MaxQueryExecutionTime           = 0 ; in seconds
DefaultQuery                    = select distinct ?Concept where {[] a
?Concept} LIMIT 100
DeferInferenceRulesInit         = 0  ; controls inference rules loading
MaxSortedTopRows                = 200000

restart Virtuoso and try running your queries again via the SPARQL endpoint …

8

In isql it took 334468 msec. It took 2.06 seconds for http.

I changed virtuoso.ini as you suggested, restarted, and re-ran the triple count and query query again, but there was no change from before the virtuoso.ini change.

You can access this endpoint, so please check it out.
https://sip-db.dbcls.jp/6b3d0d0e05ba7f769928a2a7f20ea70e/sparql/

Sorry for the delay in answering.

9

In isql it took 334468 msec. It took 2.06 seconds for http.

When I run the select count(*) where {?s ?p ?o} query to count the triples via the /sparql endpoint I get a gateway timeout , where you indicate it takes 2.06secs ?

Gateway Time-out

The gateway did not receive a timely response from the upstream server or application.

You can access this endpoint, so please check it out.
https://sip-db.dbcls.jp/6b3d0d0e05ba7f769928a2a7f20ea70e/sparql/

When I run your original query with OPTION against the /sparql endpoint it returns no results, is this expected behaviour as I was expecting it to return something or is the fact it resturns nothing the difference between running the query against isql which returns something ?

10

We have corrected the settings. However, sorry, due to the configuration of the upper network, this endpoint cannot have a timeout setting longer than 5 minutes when running externally.

When executed in isql, it returns 20 results as follows.

target                                                                            link                                                                              step                                                                              linkName                                                                          linkId
LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR
_______________________________________________________________________________

http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123
http://purl.obolibrary.org/obo/GO_0016123                                         http://purl.obolibrary.org/obo/GO_0016123                                         0                                                                                 xanthophyll biosynthetic process                                                  GO:0016123

20 Rows. -- 2216 msec.

But, I will not get this result if you run it over http. I am aware that I used to get the same results as with isql.

My apologies. I would like to correct part of the query from “GO:0016123”^^xsd:string to “GO:0016123” as follows

DEFINE sql:select-option "order"
DEFINE input:inference "go-trans"
PREFIX obo: <http://www.geneontology.org/formats/oboInOwl#>
SELECT ?target ?link ?step ?linkName ?linkId
WHERE {
  {
    SELECT DISTINCT ?target ?link ?step
    WHERE {
      {
        SELECT ?target ?parent
        WHERE {
          ?target obo:id "GO:0016123" .
          VALUES (?parent) {(<http://purl.obolibrary.org/obo/GO_0008150>)}
        }
      }
      ?target rdfs:subClassOf ?parent
      OPTION (TRANSITIVE,
              T_DISTINCT,
              T_EXISTS,
              T_DIRECTION 2,
              T_IN(?target),
              T_OUT(?parent),
              T_MIN(0),
              T_STEP(?target) AS ?link,
              T_STEP('step_no') AS ?step ) .
    }
  }
  ?link rdfs:label ?linkName .
  ?link obo:id ?linkId .
  FILTER (?link != <http://purl.obolibrary.org/obo/GO_0008150>)
}
ORDER BY DESC(?step)

And, when I run this query with isql, I get 20 results in about 2 seconds, but when I run it with http, I get the following error:

Virtuoso 42000 Error TN...: Exceeded 1000000000 bytes in transitive
temp memory.  use t_distinct, t_max or more T_MAX_memory options to
limit the search or increase the pool

Sorry, but due to the configuration of the upper network, this endpoint will not show any error messages when run from the outside.

I followed the error message and set T_MAX_memory as follows, but it gave me a syntax error.

OPTION (TRANSITIVE,
        T_DISTINCT,
        T_EXISTS,
        T_DIRECTION 2,
        T_IN(?target),
        T_OUT(?parent),
        T_MIN(0),
        T_STEP(?target) AS ?link,
        T_STEP('step_no') AS ?step,
        T_MAX_memory 1000000000 ) .

→ 

Virtuoso 37000 Error SP030: SPARQL compiler, line 28: syntax error
at 'T_MAX_memory' before '1000000000'

OPTION (TRANSITIVE,
        T_DISTINCT,
        T_EXISTS,
        T_DIRECTION 2,
        T_IN(?target),
        T_OUT(?parent),
        T_MIN(0),
        T_STEP(?target) AS ?link,
        T_STEP('step_no') AS ?step,
        T_MAX_memory(1000000000) ) .

→ 

Virtuoso 37000 Error SP030: SPARQL compiler, line 28: syntax error
at 'T_MAX_memory' before '('

Finally, I tried updating MaxMemPoolSize in virtuoso.ini, but the first error (= Virtuoso 42000 Error) was reproduced and the phenomenon did not change.

-MaxMemPoolSize                  = 134217728
+MaxMemPoolSize                  = 10000000000

Sorry for the delay in replying, I didn’t notice the notification.

11

If the query returns results against isql presumably connecting as the dba user, but does not return results against the ~/sparql endpoint which runs as the special internal SPARQL user , what happens if you connect to the available ~/sparq-auth endpoint of your installation, which required authentication enabling you to connect as the dba user (as with isql) and run the query to see if it then returns results as isql does ?

Should results be returned via ~/sparql-auth and not ~/sparql then please select the Generate SPARQL compilation report (instead of executing the query) checkbox option to enable Generation of Query Compilation Reports against both endpoints for comparison.

Also, you indicate that at one point the query was returning results when running against the ~/sparql endpoint, in which case when did it stop returning results and did anything change on the server at the time ie configuration , new data load etc ?

The param tn_max_memory can be set in the [Flags] section of the INI file or with the __dbf_set()
function to control the transitive memory size.

12

I tried to run a query against ~/sparql-auth through the dba user for authentication, but the result was the same error.

Virtuoso 42000 Error TN...: Exceeded 1000000000 bytes in transitive temp memory.  use t_distinct, t_max or more T_MAX_memory options to limit the search or increase the pool

Differences between past runs and this one are Virtuoso version and UniProt data.

|     | name                       |    version | thread    | opsys | date        | git_head | UniProt         |
|-----+----------------------------+------------+-----------+-------+-------------+----------+-----------------|
| old | OpenLink Virtuoso   Server | 07.20.3233 | -pthreads | Linux | Nov  4 2021 | 60ea9a9  | release-2021_02 |
| new | OpenLink Virtuoso   Server | 07.20.3234 | -pthreads | Linux | Jun  1 2022 | 8312faa  | release-2022_01 |

Thank you for the instructions on how to set this up. But the error was not resolved and reproduced.
I added the following to virtuoso.ini, but it gave me an error. Please let me know if the settings are incorrect.

[Flags]
tn_max_memory = 10000000000

→ 

Virtuoso 42000 Error TN...: Exceeded 1410065408 bytes in transitive temp memory.  use t_distinct, t_max or more T_MAX_memory options to limit the search or increase the pool

Thank you again for your cooperation.

13

When you set:

[Flags]
tn_max_memory = 10000000000

Did you restart the Virtuoso server for the setting to take effect, which is required. If it is set with the __dbf_set() function then a server restart is not required and values can be dynamically set at will with the need to restart the server …

14

Yes, I tried this after editing virtuoso.ini and restarting Virtuoso, but the result did not change.

I tried the function DB after running it in isql as follows,

__dbf_set('tn_max_memory', 10000000000);

but the result was the same and I got the following error

Virtuoso 42000 Error TN...: Exceeded 1410065408 bytes in transitive temp memory.  use t_distinct, t_max or more T_MAX_memory options to limit the search or increase the pool
15

We are checking to see why why the __dbf_set('tn_max_memory', 10000000000); setting does not appear to be taking effect when querying via the ~/sparql endpoint, as with 10000000000 being larger than 1410065408 that transitive memory size should be allowed, as it presumably is when executing the query via isql, which you indicates executes successfully …

16

Hi,
have you found a solution for this?
I am facing very similar issue with one query.
First i got error

Virtuoso 42000 Error TN...: Exceeded 1000000000 bytes in transitive temp memory.  use t_distinct, t_max or more T_MAX_memory options to limit the search or increase the pool

I edited virtuoso to add

[Flags]
tn_max_memory = 10000000000

now i get error

Virtuoso 42000 Error TN...: Exceeded 1410065408 bytes in transitive temp memory.  use t_distinct, t_max or more T_MAX_memory options to limit the search or increase the pool

my query tries to find nodes that are connected via some path (endpoint: Virtuoso SPARQL Query Editor) . I tried with and without the OPTIONS in the query, no difference

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX sosa:  <http://www.w3.org/ns/sosa/>
PREFIX geo: <http://www.opengis.net/ont/geosparql#>


SELECT distinct ?s ?foi ?wkt 
from <http://w3id.org/glosis/open/LUCAS/glosis-test/>
WHERE {
    
    ?foi geo:hasGeometry ?geom .
    ?geom geo:asWKT ?wkt .
    {
       SELECT ?s
       from <http://w3id.org/glosis/open/LUCAS/glosis-test/>
       from <http://w3id.org/glosis/model/>
       WHERE {
          ?s rdf:type/rdfs:subClassOf* sosa:Observation .   
       }

    }
    ?s ((<>|!<>)|^(<>|!<>))* ?foi
#    OPTION (TRANSITIVE, T_DISTINCT, T_EXISTS, T_DIRECTION 2, T_IN(?s), T_OUT(?foi), T_MIN(0), T_STEP(?s) AS ?link, T_STEP('step_no') AS ?step ) .
}
17

The original issue has not been addressed yet to my knowledge, but will check with development.

Does the query run successfully when executed via the Virtuoso isql command line tool as was the case for the previous user ?

Looking at the version of the Virtuoso SPARQL Endpoint I note it is version 07.20.3230 from Jan 9 2019, which is very old and would recommend updating to the latest stable/7 release to see if this solves the problem.

18

I’m getting the same error on this query:

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX obo: <http://purl.obolibrary.org/obo/>

SELECT *
WHERE {
  obo:NCIT_C163759 rdfs:subClassOf+ ?mid.
}

using this endpoint: https://sparql.hegroup.org/sparql/

19

Looking at your Virtuoso version it is an old Virtuoso 07.20.3217 version from 2017 and would recommend updating to the latest stable/7 release to see if this solves the problem.

20

Thank you @hwilliams. Sadly this is not a server under my control.