From c9f2c29661e8d95215966aa52228febe0fec50cf Mon Sep 17 00:00:00 2001 From: Ansgar Burchardt Date: Fri, 11 May 2012 15:09:52 +0200 Subject: [PATCH] Use with statement instead of temporary table SQLAlchemy might use server-side cursors and prepend a DECLARE "..." CURSOR WITHOUT HOLD FOR to the statement. This does not work if the statement starts with creating a temporary table. --- daklib/contents.py | 27 ++++++++------------------- 1 file changed, 8 insertions(+), 19 deletions(-) diff --git a/daklib/contents.py b/daklib/contents.py index 6db19a71..e808da62 100755 --- a/daklib/contents.py +++ b/daklib/contents.py @@ -66,20 +66,14 @@ class BinaryContentsWriter(object): } sql = ''' -create temp table newest_binaries ( - id integer primary key, - package text); - -create index newest_binaries_by_package on newest_binaries (package); +with -insert into newest_binaries (id, package) - select distinct on (package) id, package from binaries +newest_binaries as + (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; - -with + order by package, version desc), unique_override as (select o.package, s.section @@ -172,19 +166,14 @@ class SourceContentsWriter(object): } sql = ''' -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 +with + newest_sources as + (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) select sc.file, string_agg(s.source, ',' order by s.source) as pkglist from newest_sources s, src_contents sc -- 2.39.5