[
https://issues.apache.org/jira/browse/CONNECTORS-797?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13810554#comment-13810554
]
Graeme Seaton commented on CONNECTORS-797:
------------------------------------------
Hi Karl,
Applied the patch and it works (can't provide any metrics about speed increase
as I was waiting for the REINDEX to complete while waiting for the screen to
refresh).
Was going suggest using jobid to take advantage of the existing index but you
beat me to it :-)
On a similar note I notice quite a few 'SELECT COUNT(dochash) AS doccount FROM
jobqueue t1 LIMIT 500001' queries which have a plan of:
"Limit (cost=432498.24..432498.25 rows=1 width=41)"
" -> Aggregate (cost=432498.24..432498.25 rows=1 width=41)"
" -> Seq Scan on jobqueue t1 (cost=0.00..404109.99 rows=11355299
width=41)"
Would it be worthwhile to change it to 'SELECT COUNT(id)....' to utilise the
primary key index (or add a where clause to force the use of an index)?
> Database Exception on Status and Job Management screen
> ------------------------------------------------------
>
> Key: CONNECTORS-797
> URL: https://issues.apache.org/jira/browse/CONNECTORS-797
> Project: ManifoldCF
> Issue Type: Bug
> Components: Framework crawler agent
> Affects Versions: ManifoldCF 1.4
> Environment: Postgresql 9.2.5
> Reporter: Graeme Seaton
> Assignee: Karl Wright
> Priority: Critical
> Fix For: ManifoldCF 1.5
>
> Attachments: CONNECTORS-797.patch, CONNECTORS-797.patch.2
>
>
> Receive the following message on the ' Status and Job Management' screen:
> Error: Database exception: SQLException doing query (42601): ERROR: syntax
> error at or near "status" Position: 60
> Digging through the debug logs found:
> DEBUG 2013-10-31 12:29:11,911 (http-apr-8081-exec-5) - Requested query:
> [SELECT COUNT(dochash) AS doccount FROM jobqueue WHERE id=?status IN
> (?,?,?,?,?,?) LIMIT 500001]
> DEBUG 2013-10-31 12:29:11,911 (Thread-16) - Actual query: [SELECT
> COUNT(dochash) AS doccount FROM jobqueue WHERE id=?status IN (?,?,?,?,?,?)
> LIMIT 500001]
> DEBUG 2013-10-31 12:29:11,912 (Thread-16) - Parameter 0: '1380815567276'
> DEBUG 2013-10-31 12:29:11,912 (Thread-16) - Parameter 1: 'A'
> DEBUG 2013-10-31 12:29:11,912 (Thread-16) - Parameter 2: 'a'
> DEBUG 2013-10-31 12:29:11,912 (Thread-16) - Parameter 3: 'P'
> DEBUG 2013-10-31 12:29:11,912 (Thread-16) - Parameter 4: 'F'
> DEBUG 2013-10-31 12:29:11,912 (Thread-16) - Parameter 5: 'f'
> DEBUG 2013-10-31 12:29:11,912 (Thread-16) - Parameter 6: 'G'
> DEBUG 2013-10-31 12:29:11,918 (http-apr-8081-exec-5) - Reinterpreting
> exception 'Database exception: SQLException doing query (42601): ERROR:
> syntax error at or near "status"
> Position: 60'. The exception type is 4
> DEBUG 2013-10-31 12:29:11,918 (http-apr-8081-exec-5) - Exception Database
> exception: SQLException doing query (42601): ERROR: syntax error at or near
> "status"
> Position: 60 is possibly a transaction abort signal
> DEBUG 2013-10-31 12:29:11,918 (http-apr-8081-exec-5) - Exception Database
> exception: SQLException doing query (42601): ERROR: syntax error at or near
> "status"
> Position: 60 is NOT a transaction abort signal
> Appears that the SQL request is missing a space at 'WHERE id=?status IN (....'
--
This message was sent by Atlassian JIRA
(v6.1#6144)
