Opened 11 years ago
Closed 11 years ago
#11096 closed defect (fixed)
Avoid an `OperationalError: too many SQL variables`
Reported by: | Steffen Hoffmann | Owned by: | Steffen Hoffmann |
---|---|---|---|
Priority: | normal | Component: | TagsPlugin |
Severity: | normal | Keywords: | |
Cc: | Ryan J Ollos, Jun Omae | Trac Release: |
Description
The two-stepped approach in tractags.model.tagged_resources
doesn't work at all for large quantities of tagged resources and must be replaced to avoid an OperationalError: too many SQL variables
.
This surfaced while changing ticket tags processing for #4503. I was hit well over 10.000 tickets. Many Trac projects might not reach that number (too soon), even fewer will reach that count for other resources like wiki pages.
And issues mentioned in #4503 might actually have prevented this issue from getting known as well, because in these Trac applications TagsPlugin would have been dropped long before reaching the critical mark (resource count per tag realm). I've seen ticket view load times beyond 15 s/page with KeywordSuggestPlugin using TagsPlugin for not more than 5.000 tickets, that dropped to about 4 seconds when deactivating get_tagged_resources
(done per request) by TicketTagProvider
.
Still I consider it a more or less accidental find, because I've been trying to improve code, not to fix it.
Attachments (0)
Change History (3)
comment:1 Changed 11 years ago by
comment:2 Changed 11 years ago by
With [trac] debug_sql = enabled
, the SQL in tractags.ticket
is logged twice:
08:23:23 PM Trac[ticket] DEBUG: SELECT * FROM (SELECT id, keywords, COALESCE(keywords, '') AS std_fields FROM ticket AS tkt WHERE NOT EXISTS (SELECT * FROM tags WHERE tagspace=%s AND name=CAST(tkt.id AS text)) AND status != 'closed') AS s WHERE std_fields != '' ORDER BY id 08:23:23 PM Trac[util] DEBUG: SQL: DELETE FROM tags WHERE tagspace=%s AND NOT EXISTS (SELECT * FROM ticket AS tkt WHERE tkt.id=CAST(tags.name AS integer) AND status != 'closed') 08:23:23 PM Trac[util] DEBUG: args: ('ticket',) 08:23:23 PM Trac[util] DEBUG: prefetch: 0 rows 08:23:23 PM Trac[ticket] DEBUG: ENTER_TAG_DB_CHECKOUT 08:23:23 PM Trac[util] DEBUG: SQL: SELECT * FROM (SELECT id, keywords, COALESCE(keywords, '') AS std_fields FROM ticket AS tkt WHERE NOT EXISTS (SELECT * FROM tags WHERE tagspace=%s AND name=CAST(tkt.id AS text)) AND status != 'closed') AS s WHERE std_fields != '' ORDER BY id
I'm thinking we could remove this line:
-
tagsplugin/trunk/tractags/ticket.py
diff --git a/tagsplugin/trunk/tractags/ticket.py b/tagsplugin/trunk/tractags/tic index e7afda4..bf46ce7 100644
a b class TicketTagProvider(DefaultTagProvider): 220 220 ORDER BY id 221 221 """ % (','.join(self.fields), db.concat(*fields), 222 222 db.cast('tkt.id', 'text'), ignore) 223 self.env.log.debug(sql)224 223 # Obtain cursors for reading tickets and altering tags db table. 225 224 # DEVEL: Use appropriate cursor typs from Trac 1.0 db API. 226 225 ro_cursor = db.cursor()
(In [13165]) TagsPlugin: Mirror ticket tags into normalized
tags
db table, refs #4503, #9194 and #11096.Parsing ticket fields per request has been a known performance hog, that's why using the
tags
db table for regular access yields a considerable speed-up.As a side-effect 'ticket' is the pilot for fixing a recently announced issue with
get_tagged_resources
in the default tag provider implementation. Nicely polished by stretching unit test coverage to this part of the code.ToDo:
DefaultTagProvider
must be fixed as well, and other tag providers should be reviewed too as a precaution.