[08:16] <wgrant> Perhaps it is time to bite the bullet and have a generic artifact cross-reference table without strong foreign keys.
[08:23] <wgrant> Referential integrity doesn't really buy us much for BugBranch, SpecificationBranch, SpecificationBug
[08:23] <jtv> wgrant: I wrote a weakly-linked table once...  TranslatableTemplate or something.  Probably gone now, but it was really useful at the time.
[08:23] <jtv> Sometimes with relational databases, painful as it may be, you just have to admit that it's... relational.
[08:24] <jtv> And hi.  :)
[08:29] <wgrant> jtv: Hi!
[08:29] <wgrant> There's SuggestivePOTemplate, but I don't recall TranslatableTemplate.
[08:30] <jtv> (Ahem.  Obviously I didn't write a weakly-linked table "once" — I mean I had one in Launchpad once.))
[08:31] <wgrant> Heh
[08:31] <jtv> Dammit, internets, you can deliver my messages within the hour, can't you?
[08:44] <jtv> wgrant: yes, that's the one!
[08:45] <jtv> It eliminated a subquery of a few dozen ms that was repeated in many queries per translation page, but maintaining referential integrity would have defeated the purpose.
[08:47] <jtv> (Actually I've had this long-standing suspicion that those big PO template headers that were only used in one place were unnecessarily slowing down the POTemplate table, but that's another story)
[08:47] <wgrant> Yeah, we had that problem with SourcePackageRelease.copyright
[08:47] <wgrant> I hacked it to only load when that specific field is requested on the the one page that uses it, saving megabytes of database transfers on hot pages.
[08:49] <jtv> Not to mention decoding...
[08:50] <jtv> I used to want a Django lazy-loading field type for this.  But then I got greedier: if we could remove these long strings from the tables altogether, it would increase data density.
[08:50] <jtv> Could matter to lots and lots of joins.
[08:50] <wgrant> Indeed, though I suspect most of these would be TOASTed so not affect density much.
[08:51] <jtv> (Even when you're using an index, postgres still needs to look at the actual table to check tuple liveness.)
[08:51] <jtv> True, if they're out in separate storage, that'll help.
[08:52] <jtv> I don't know how the TOASTing works in terms of table representation; POTemplate is so hot for joins that even just dropping a 4-byte field may help.
[08:53] <wgrant> cjwatson: Do you think https://bugs.launchpad.net/launchpad/+bug/1170301 is still a problem? Now that everything actually gets closed by copies, Launchpad-Bugs-Fixed may not actually be used in any normal case any more.
[08:53] <mup> Bug #1170301: It would be nice to have bugs-autoclosing working for intermediate revisions non accepted to the archive but included in a new upload <bugs> <packages> <soyuz-core> <soyuz-upload> <Launchpad itself:Triaged> <https://launchpad.net/bugs/1170301>
[08:54] <wgrant> jtv: Large values are stored as references to tuples in a separate TOAST relation in a separate file on disk.
[08:54] <wgrant> This doesn't help LP as much as it could, as we almost always get all fields.
[08:54] <wgrant> But it does mean we're not inflating large dead rows just to check whether they're dead.
[08:58] <cjwatson> wgrant: Seb filed that bug six months after we switched to proposed-migration and everything being closed by copies ...
[08:58] <cjwatson> So not sure I'm convinced by that argument
[08:59] <wgrant> Hm, indeed.
[08:59] <wgrant> Perhaps the ancestry calculation is just dodgy.
[08:59] <cjwatson> Yeah, I've had my suspicions about that before
[09:00] <jtv> wgrant: I just looked it up...  the default TOAST threshold seems to be 2KB, and I suspect most PO headers would hit the "sour spot" just below that.
[09:00] <jtv> In which case, massive potential for moving them out-of-line.  :)
[09:03] <jtv> Set only on import, read only on export.
[09:12] <cjwatson> wgrant: Thanks for reviewing https://code.launchpad.net/~cjwatson/launchpad/livefsbuild-version/+merge/271193, but did you notice that it relies on https://code.launchpad.net/~cjwatson/launchpad/db-livefsbuild-version/+merge/271171 ?
[09:43] <wgrant> cjwatson: I reviewed that one, but I guess my request on livefsbuild-version only made it through because closing db-livefsbuild-version got me down to four tabs...
[09:43] <wgrant> fixing
[09:54] <wgrant> cjwatson: Any thoughts on the cross-artifact link table proliferation?
[10:03] <cjwatson> wgrant: Limited to only two non-null reference columns?  That would make it easier to decide what to do on deletion (e.g. no bug/specification/branch all linked together and then you have to decide what to do if the branch is deleted)
[10:04] <cjwatson> db-livefsbuild-version> thanks
[10:05] <wgrant> cjwatson: Or ditch the dozens-of-columns approach and not bother with FKs.
[10:05] <cjwatson> what would the table contain then?
[10:05] <wgrant> Just store a type and an ID for each end.
[10:06] <cjwatson> hm, yeah, I guess there's no reason to believe that would have perf problems
[10:06] <cjwatson> seems reasonable ...
[10:06] <wgrant> None whatsoever.
[10:06] <cjwatson> better density too
[10:07] <wgrant> I guess it'd also have a JSON metadata column too, where we'd store eg. the git repo hint.
[10:08] <cjwatson> in that case it would be a commit sha-1 rather than an id
[10:09] <cjwatson> no obvious id for the link-to-commit case (unless you use the repo hint as the id, but that complicates what to do on deletion)
[10:11] <wgrant> (plus this makes the way toward splitting clearer)
[10:11] <wgrant> Right.
[10:11] <wgrant> Some kind of identifier
[10:12] <wgrant> Not a specifically integral ID.
[10:13] <wgrant> Just needs to be some bijective mapping.
[10:15] <cjwatson> maybe having an id column at least available would be quicker for searching in the cases where we have one
[10:15] <cjwatson> id integer, identifier json, check (null_count(id, identifier) == 1) or some such
[10:16] <wgrant> Maybe.
[10:17] <wgrant> A string is only slightly longer, and conversion isn't too verbose or slow.
[10:17] <wgrant> I considered using URLs, but that raises issues with staging that I can't be bothered working out this week.
[10:20] <wgrant> (this could then be generalised to other services, which could even maybe register callbacks to describe the status of the referenced external artifact to be displayed on the internal artifact's LP page)
[10:21] <wgrant> But using simple internal identifiers is a good first step.
[10:21] <cjwatson> Yeah, ids could have service names attached for splitting
[10:22] <cjwatson> I like this plan
[10:22] <wgrant> That was easier than I expected.
[10:22] <cjwatson> Are there any cases where inhibiting deletion is more than an inconvenience?
[10:23] <wgrant> Not in the tables I'm considering right now.
[10:23] <wgrant> I think that this solution should be restricted to those where it shouldn't be inhibited.
[10:23] <cjwatson> Deleting active MPs, but presumably you aren't looking at that
[10:23] <cjwatson> Yeah
[10:23] <wgrant> Right, no.
[10:23] <wgrant> Recipes are a murky case.
[10:23] <cjwatson> Especially if you're thinking of it being a cross-service thing
[10:24] <wgrant> There's a good argument that recipes should be text, and use cross-references for backlinks from branches.
[10:24] <wgrant> Due to the fact they can be created by anyone and block deletion of any referenced branch.
[10:24] <wgrant> But certainly not an immediate target :)
[10:24] <cjwatson> Right, I think the path for recipes is to make it possible to detach branches from them after which they become unbuildable but still exist.
[10:25] <cjwatson> If I get a few solid days to make progress on git recipes then that might be a sensible thing to attempt as part of it.
[10:25] <wgrant> Indeed.
[10:26] <cjwatson> <cjwatson@niejwein ~/src/canonical/git-build-recipe/git-build-recipe (master)>$ git diff --cached --shortstat
[10:26] <cjwatson>  10 files changed, 4050 insertions(+)
[10:26] <cjwatson> one of these days
[10:27] <wgrant> Impressive.
[10:29] <cjwatson> I had an otherwise unused train trip
[20:27] <blr> morning
[21:50] <rpadovani> cjwatson, hey :-) I'm a bit confused about the relation between template files and python files. I'm trying to fix #185328, so I understood that +publishinghistory of a package is in soyuz, I found distributionsourcepackage-publishinghistory.pt, and the table I'm lookin for is generated by
[21:50] <rpadovani> <tal:block repeat="publishing publications/currentBatch"
[21:50] <rpadovani>                     replace="structure publishing/@@+listing-summary">
[21:50] <mup> Bug #185328: Publishing history has only distribution series codenames, not version numbers <lp-soyuz> <trivial> <ui> <Launchpad itself:Triaged> <https://launchpad.net/bugs/185328>
[21:50] <rpadovani> I tried to grep a bit these things, but I really don't understand where I'm supposed to look
[21:54] <rpadovani> early in the file there is tal:define="publications view/batchnav, so I suppose I have to find this view
[21:54] <rpadovani> but what is it?
[23:24] <cjwatson> rpadovani: I'm not sure what I think about that bug in general, would probably take some playing around.  But as to your specific question, template files are evaluated according to http://pythonic.zoomquiet.io/data/20050615194557/index.html; in general the "context" is model code (i.e. corresponding roughly to a database object) and the "view" is browser code (i.e. the part of the presentation layer that isn't just HTML templating).  ...
[23:24] <cjwatson> ... To find which view is associated with a given template, look for the template in lib/lp/*/browser/configure.zcml - in this case it's in soyuz, and it turns out that the same template may be applied to either lp.soyuz.browser.distributionsourcepackagerelease.DistributionSourcePackageReleasePublishingHistoryView or lp.registry.browser.distributionsourcepackage.DistributionSourcePackagePublishingHistoryView
[23:26] <cjwatson> rpadovani: the +listing-summary page is also defined in lib/lp/soyuz/browser/configure.zcml, and in this case it's going to be for a SourcePackagePublishingHistory, so that will end up in sourcepackagepublishinghistory-listing-summary.pt
[23:26] <cjwatson> rpadovani: and then that goes off into @@publishinghistory-macros and you get to repeat the process again :)