Code review comment for lp:~gmb/launchpad/bugjob-indices-bug-539382

On 28 April 2010 10:53, Stuart Bishop <email address hidden> wrote:
> Review: Approve db
> The following version is approved. Although there is no real functional difference, a little more metadata ends up stored in the database using the ALTER TABLE syntax for defining uniqueness.
>
> SET client_min_messages=ERROR;
>
> -- Indices for BugJob
> ALTER TABLE BugJob ADD CONSTRAINT bugjob__job__key UNIQUE (job);
> CREATE INDEX bugjob__bug__job_type__idx ON BugJob(bug, job_type);
>
> -- Indices for Job
> CREATE INDEX job__scheduled_start__idx ON Job(scheduled_start);
> CREATE INDEX job__lease_expires__idx ON Job(lease_expires);
>
> INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 53, 0);

Thanks. I'll update the patch accordingly.

>
> Your example query is better written as following BTW:
>
> SELECT BugJob.*
> FROM BugJob, Job
> WHERE
>    BugJob.bug = 1
>    AND BugJob.job_type = 1
>    AND BugJob.job = Job.id
>    AND Job.status = 1
>    AND (
>        Job.lease_expires IS NULL
>        OR Job.lease_expires < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'))
>    AND (
>        Job.scheduled_start IS NULL
>        OR Job.scheduled_start <= (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'))
> LIMIT 1;
>

Noted. Although what I pasted was the Storm-generated query that came
from an OOPS report.