ubuntu-translations-coordinators team mailing list archive

[Pierre Slamich <pierre.slamich@xxxxxxxxx>]

** Also affects: ubuntu-translations
   Importance: Undecided
       Status: New

-- 
You received this bug notification because you are a member of Ubuntu
Translations Coordinators, which is subscribed to Ubuntu Translations.
Matching subscriptions: Ubuntu Translations bug mail
https://bugs.launchpad.net/bugs/807383

Title:
  Timeout trying to view package's +translate page

Status in Launchpad itself:
  Triaged
Status in Ubuntu Translations:
  New

Bug description:
  I get this error on a regular basis. Trying again in a couple of
  minutes, even days, doesn't work. What's wrong here?

  OOPS-2022K28 OOPS-2022CH25

  An example of the two queries that seem to be eating up the request
  time (notice that the section beginning with "AND POTMsgSet.id IN "
  seems identical):

  1.	5526.0	1	SQL-launchpad-main-slave
  SELECT POTMsgSet.commenttext,
         POTMsgSet.context,
         POTMsgSet.filereferences,
         POTMsgSet.flagscomment,
         POTMsgSet.id,
         POTMsgSet.msgid_plural,
         POTMsgSet.msgid_singular,
         POTMsgSet.sequence,
         POTMsgSet.sourcecomment
  FROM POTMsgSet,
       TranslationTemplateItem
  WHERE (TranslationTemplateItem.potemplate = $INT
    AND TranslationTemplateItem.potmsgset = POTMsgSet.id
    AND TranslationTemplateItem.sequence > $INT
    AND POTMsgSet.id IN ( -- Step $INTa: get POTMsgSets where msgid_singular contains `text` -- To avoid seqscans on POMsgID table (what LIKE usually -- does), we do ILIKE comparison on them in a subselect first -- filtered by this POTemplate. SELECT POTMsgSet.id FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND TranslationTemplateItem.potemplate=$INT WHERE (POTMsgSet.msgid_singular IS NOT NULL AND POTMsgSet.msgid_singular IN ( SELECT POMsgID.id FROM POMsgID WHERE id IN ( SELECT DISTINCT(msgid_singular) FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id WHERE TranslationTemplateItem.potemplate=$INT AND TranslationTemplateItem.sequence > $INT ) AND msgid ILIKE $STRING || $STRING || $STRING)) UNION -- Step $INTb: like above, just on msgid_plural. SELECT POTMsgSet.id FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND TranslationTemplateItem.potemplate=$INT WHERE (POTMsgSet.msgid_plural IS NOT NULL AND POTMsgSet.msgid_plural IN ( SELECT POMsgID.id FROM POMsgID WHERE id IN ( SELECT DISTINCT(msgid_plural) FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id WHERE TranslationTemplateItem.potemplate=$INT AND TranslationTemplateItem.sequence > $INT ) AND msgid ILIKE $STRING || $STRING || $STRING)) UNION -- Find translations containing `text`. -- Like in findPOTMsgSetsContaining(), to avoid seqscans on -- POTranslation table, we do ILIKE comparison on them in -- a subselect which is first filtered by the POFile. SELECT TranslationMessage.potmsgset FROM TranslationMessage JOIN TranslationTemplateItem ON TranslationMessage.potmsgset = TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationMessage.language = $INT AND TranslationMessage.msgstr0 IN ( SELECT POTranslation.id FROM POTranslation WHERE POTranslation.id IN ( SELECT DISTINCT(msgstr0) FROM TranslationMessage AS tm_ids JOIN TranslationTemplateItem ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationTemplateItem.sequence > $INT AND tm_ids.language=$INT ) AND POTranslation.translation ILIKE $STRING || $STRING || $STRING) UNION -- Find translations containing `text`. -- Like in findPOTMsgSetsContaining(), to avoid seqscans on -- POTranslation table, we do ILIKE comparison on them in -- a subselect which is first filtered by the POFile. SELECT TranslationMessage.potmsgset FROM TranslationMessage JOIN TranslationTemplateItem ON TranslationMessage.potmsgset = TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationMessage.language = $INT AND TranslationMessage.msgstr1 IN ( SELECT POTranslation.id FROM POTranslation WHERE POTranslation.id IN ( SELECT DISTINCT(msgstr1) FROM TranslationMessage AS tm_ids JOIN TranslationTemplateItem ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationTemplateItem.sequence > $INT AND tm_ids.language=$INT ) AND POTranslation.translation ILIKE $STRING || $STRING || $STRING) )) ORDER BY TranslationTemplateItem.sequence LIMIT $INT OFFSET $INT
  2.	3008.0	1	SQL-launchpad-main-slave
  SELECT COUNT(*)
  FROM POTMsgSet,
       TranslationTemplateItem
  WHERE (TranslationTemplateItem.potemplate = $INT
    AND TranslationTemplateItem.potmsgset = POTMsgSet.id
    AND TranslationTemplateItem.sequence > $INT
    AND POTMsgSet.id IN ( -- Step $INTa: get POTMsgSets where msgid_singular contains `text` -- To avoid seqscans on POMsgID table (what LIKE usually -- does), we do ILIKE comparison on them in a subselect first -- filtered by this POTemplate. SELECT POTMsgSet.id FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND TranslationTemplateItem.potemplate=$INT WHERE (POTMsgSet.msgid_singular IS NOT NULL AND POTMsgSet.msgid_singular IN ( SELECT POMsgID.id FROM POMsgID WHERE id IN ( SELECT DISTINCT(msgid_singular) FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id WHERE TranslationTemplateItem.potemplate=$INT AND TranslationTemplateItem.sequence > $INT ) AND msgid ILIKE $STRING || $STRING || $STRING)) UNION -- Step $INTb: like above, just on msgid_plural. SELECT POTMsgSet.id FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND TranslationTemplateItem.potemplate=$INT WHERE (POTMsgSet.msgid_plural IS NOT NULL AND POTMsgSet.msgid_plural IN ( SELECT POMsgID.id FROM POMsgID WHERE id IN ( SELECT DISTINCT(msgid_plural) FROM POTMsgSet JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id WHERE TranslationTemplateItem.potemplate=$INT AND TranslationTemplateItem.sequence > $INT ) AND msgid ILIKE $STRING || $STRING || $STRING)) UNION -- Find translations containing `text`. -- Like in findPOTMsgSetsContaining(), to avoid seqscans on -- POTranslation table, we do ILIKE comparison on them in -- a subselect which is first filtered by the POFile. SELECT TranslationMessage.potmsgset FROM TranslationMessage JOIN TranslationTemplateItem ON TranslationMessage.potmsgset = TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationMessage.language = $INT AND TranslationMessage.msgstr0 IN ( SELECT POTranslation.id FROM POTranslation WHERE POTranslation.id IN ( SELECT DISTINCT(msgstr0) FROM TranslationMessage AS tm_ids JOIN TranslationTemplateItem ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationTemplateItem.sequence > $INT AND tm_ids.language=$INT ) AND POTranslation.translation ILIKE $STRING || $STRING || $STRING) UNION -- Find translations containing `text`. -- Like in findPOTMsgSetsContaining(), to avoid seqscans on -- POTranslation table, we do ILIKE comparison on them in -- a subselect which is first filtered by the POFile. SELECT TranslationMessage.potmsgset FROM TranslationMessage JOIN TranslationTemplateItem ON TranslationMessage.potmsgset = TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationMessage.language = $INT AND TranslationMessage.msgstr1 IN ( SELECT POTranslation.id FROM POTranslation WHERE POTranslation.id IN ( SELECT DISTINCT(msgstr1) FROM TranslationMessage AS tm_ids JOIN TranslationTemplateItem ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset WHERE TranslationTemplateItem.potemplate = $INT AND TranslationTemplateItem.sequence > $INT AND tm_ids.language=$INT ) AND POTranslation.translation ILIKE $STRING || $STRING || $STRING) ))

To manage notifications about this bug go to:
https://bugs.launchpad.net/launchpad/+bug/807383/+subscriptions