From d594c288046f7eff586e0ac3dbc717d5d8b9b998 Mon Sep 17 00:00:00 2001 From: Ansgar Burchardt Date: Fri, 11 May 2012 23:32:28 +0200 Subject: [PATCH] Go back to temporary tables. Using with statements as done in c9f2c29661e8d95215966aa52228febe0fec50cf requires more resources, so revert back to using temporary tables. We keep the statements to create and fill the temporary table separate from the SELECT query itself which should fix the original problem in a different way. --- daklib/contents.py | 35 +++++++++++++++++++++++++---------- 1 file changed, 25 insertions(+), 10 deletions(-) diff --git a/daklib/contents.py b/daklib/contents.py index e808da62..81ce2226 100755 --- a/daklib/contents.py +++ b/daklib/contents.py @@ -65,15 +65,23 @@ class BinaryContentsWriter(object): 'type': self.overridetype.overridetype, } - sql = ''' -with + sql_create_temp = ''' +create temp table newest_binaries ( + id integer primary key, + package text); + +create index newest_binaries_by_package on newest_binaries (package); -newest_binaries as - (select distinct on (package) id, package from binaries +insert into newest_binaries (id, package) + select distinct on (package) id, package from binaries where type = :type and (architecture = :arch_all or architecture = :arch) and id in (select bin from bin_associations where suite = :suite) - order by package, version desc), + order by package, version desc;''' + self.session.execute(sql_create_temp, params=params) + + sql = ''' +with unique_override as (select o.package, s.section @@ -165,16 +173,23 @@ class SourceContentsWriter(object): 'component_id': self.component.component_id, } - sql = ''' -with - newest_sources as - (select distinct on (source) s.id, s.source from source s + sql_create_temp = ''' +create temp table newest_sources ( + id integer primary key, + source text); + +create index sources_binaries_by_source on newest_sources (source); + +insert into newest_sources (id, source) + select distinct on (source) s.id, s.source from source s join files f on f.id = s.file join location l on l.id = f.location where s.id in (select source from src_associations where suite = :suite_id) and l.component = :component_id - order by source, version desc) + order by source, version desc;''' + self.session.execute(sql_create_temp, params=params) + sql = ''' select sc.file, string_agg(s.source, ',' order by s.source) as pkglist from newest_sources s, src_contents sc where s.id = sc.source_id group by sc.file''' -- 2.39.5