launchpad-reviewers team mailing list archive

["Jeroen T. Vermeulen" <jtv@xxxxxxxxxxxxx>]

Jeroen T. Vermeulen has proposed merging lp:~jtv/launchpad/index-798297 into lp:launchpad.

Requested reviews:
  Stuart Bishop (stub): db
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #798297 in Launchpad itself: "No way to get DistroSeriesDifferenceComments via the web service"
  https://bugs.launchpad.net/launchpad/+bug/798297

For more details, see:
https://code.launchpad.net/~jtv/launchpad/index-798297/+merge/67046

= Summary =

In order to support date-filtering of DistroSeriesDifferenceComments, we need the ability to search Message, efficiently, for specific datecreated limits.


== Proposed fix ==

Index.

This is a lightweight patch, so it has a non-zero patch number (the last of the triplet of version numbers).


== Pre-implementation notes ==

Discussed with Julian and Robert.  Robert told me this lightweight patch could land in devel.


== Implementation details ==

This appears to be the first lightweight database patch to be proposed for devel.  It also appears to be the first one where an engineer other than Stuart allocates their own patch number.

The index will be created manually on the slaves, with the CONCURRENTLY option.  That won't work in the patch since patches are run transactionally, and CREATE INDEX CONCURRENTLY doesn't work inside a transaction.


== Demo and Q/A ==

Once the API call for getting DistroSeriesDifferenceComments lands, it will be dramatically slow on realistic data unless this patch works.


= Launchpad lint =

Checking for conflicts and issues in changed files.

Linting changed files:
  database/schema/patch-2208-76-2.sql


== Schema ==

database/sampledata/current.sql
    database/sampledata/lintdata.sql differs from database/sampledata/current.sql.
    Patches to the schema, or manual edits to database/sampledata/current.sql
    do not match the dump of the launchpad_ftest_template database.
    If database/sampledata/lintdata.sql is correct, copy it to
    database/sampledata/current.sql.
    Otherwise update database/sampledata/current.sql and run:
        make schema
        make newsampledata
        cd database/sampledata
        cp newsampledata.sql database/sampledata/current.sql
    Run make schema again to update the test/dev database.

database/sampledata/current.sql
    database/sampledata/lintdata-dev.sql differs from database/sampledata/current-dev.sql.
    Patches to the schema, or manual edits to database/sampledata/current-dev.sql
    do not match the dump of the launchpad_dev_template database.
    If database/sampledata/lintdata-dev.sql is correct, copy it to
    database/sampledata/current-dev.sql.
    Otherwise update database/sampledata/current-dev.sql and run:
        make schema
        make newsampledata
        cd database/sampledata
        cp newsampledata-dev.sql database/sampledata/current-dev.sql
    Run make schema again to update the test/dev database.
-- 
https://code.launchpad.net/~jtv/launchpad/index-798297/+merge/67046
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~jtv/launchpad/index-798297 into lp:launchpad.

=== added file 'database/schema/patch-2208-76-2.sql'
--- database/schema/patch-2208-76-2.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-76-2.sql	2011-07-06 14:01:31 +0000
@@ -0,0 +1,8 @@
+-- Copyright 2011 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE INDEX message_datecreated ON Message(datecreated);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 76, 2);