Target Pharmacology returns 500 error for page numbers after 10000 items

[jakhag]

First reported here: http://support.openphacts.org/helpdesk/tickets/176

This link returns results: https://beta.openphacts.org/2.0/target/pharmacology/pages?app_id=15a18100&app_key=528a8272f1cd961d215f318a0315dd3d&uri=http%3A%2F%2Fwww.uniprot.org%2Funiprot%2FP00918&_pageSize=250&_page=40

However, the next page returns a 500 error: https://beta.openphacts.org/2.0/target/pharmacology/pages?app_id=15a18100&app_key=528a8272f1cd961d215f318a0315dd3d&uri=http%3A%2F%2Fwww.uniprot.org%2Funiprot%2FP00918&_pageSize=250&_page=41

Changing the page size allows to return a different number of pages, but every page with the count over 10000 items gives a server error. This happens also with other targets over the count of 10000.

[danidi]

As a workaround, you can apply activity filters to decrease the result size. E.g. you can first query with min-activity_value=1000, and then with maxEx-activity_value=1000. Both will give a resultset under 10.000, and if you join the results you should have a full list of all items. This shows that there is nothing wrong with the items which could result in a server error.

I am aware of a maximum limit of 10.000 items if you use _pageSize=all, but so far I thought browsing through the pages should work fine.

[danidi]

Also reported by http://support.openphacts.org/helpdesk/tickets/177.

[randykerber]

Fixed. Was a Virtuoso default limit. Changed the limit from 10000 to 40000 by adding the following line to the [Parameters] section of the 'virtuoso.ini' file.

MaxSortedTopRows = 40000

[danidi]

Can you please further increase the limit? e.g. this query has the same issue at 40.000 now: https://beta.openphacts.org/2.1/target/pharmacology/pages?uri=http%3A%2F%2Fwww.conceptwiki.org%2Fconcept%2F651bcc3b-cc05-4970-b9d2-45585643e109&app_id=f91c5b2b&app_key=18a5d823d0e4933ac5fe22a3d52974c1&_page=401&_pageSize=100&_pageSize=100. The total count of this target is over 90.000.

Maybe @agaulton knows what the maximum number of activities for a target or compound would currently be. The highest target count I saw while testing so far is 115.315 (in ChEMBL20) for Q9UNA4. For compounds I think it could be even higher.

[randykerber]

Changed it to:

MaxSortedTopRows = 120000