Avoid an `OperationalError: too many SQL variables`

[Steffen Hoffmann]

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.

[Steffen Hoffmann]

(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.

[Ryan J Ollos]

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

@@ -220 +220 @@
              ORDER BY id
             """ % (','.join(self.fields), db.concat(*fields),
                    db.cast('tkt.id', 'text'), ignore)
-        self.env.log.debug(sql)
         # Obtain cursors for reading tickets and altering tags db table.
         # DEVEL: Use appropriate cursor typs from Trac 1.0 db API.
         ro_cursor = db.cursor()

[Steffen Hoffmann]

In 13815:

TagsPlugin: Completing preparation for v0.7 release.

Availability of that code as stable, tagged release
closes #2429, #3359, #3610, #3624, #3677, #3754, #3864, #3947, #3983, #4078, #4277, #4503, #4799, #5523, #7787, #7857, #8638, #9057, #9058, #9059, #9060, #9061, #9062, #9063, #9149, #9210, #9521, #9630, #9636, #10032, #10416, #10636, #11096, #11147, #11152, #11274, #11302, #11658 and #11659.

Additionally there are some issues and enhancement requests showing progress,
but known to require more work to resolve them satisfactorily, specifically
refs #2804, #4200, #8747 and #9064.

Thanks to all contributors and followers, that enabled and encouraged a good
portion of this development work.