From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp2.migadu.com ([2001:41d0:303:e16b::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms8.migadu.com with LMTPS id eDiHGBIA1WUuDAAAe85BDQ:P1 (envelope-from ) for ; Tue, 20 Feb 2024 20:40:02 +0100 Received: from aspmx1.migadu.com ([2001:41d0:303:e16b::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp2.migadu.com with LMTPS id eDiHGBIA1WUuDAAAe85BDQ (envelope-from ) for ; Tue, 20 Feb 2024 20:40:02 +0100 X-Envelope-To: larch@yhetil.org Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org" ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1708458002; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding:resent-cc: resent-from:resent-sender:resent-message-id:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post; bh=i0yHsCeOi9s1NOcqC2J3Q7+eam68f5wgbp2W+mcrT5E=; b=l3KaFD/SnAvr2oJUdypNt7VSLrXr90FZR2JJLD9KSt9/i/3piRtNp47+RkAnbCurhRtVkC FWVWVxv2ulSo6LtPb0ZEqh+vKVeeilezSO4Tx6djm7tskR5wWqNyxIPoxnHAQtFmvmezfE yh2DRZm1DH+/KibYy56Kg0fA99oVmSq0eFIvoJADFmdyz1ihDw2T9vTOhJx/poa9pDYUTo dhEbUgoSVERJYElnlJXdvUFvDKZJyL/6qTDKi8I9Nw/PdNBzdTpwUItQ13E5byBFWAjeBL sX3bhw3/INaOKM+XIvxOogD8O01kGCO9UoPZMMcOeG7OGuChQc0KvCgNXeSKPA== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org" ARC-Seal: i=1; s=key1; d=yhetil.org; t=1708458002; a=rsa-sha256; cv=none; b=OxRG84epiogPHmsnK75Jcqg/8L3Ntj4ywwWW9ZbCYl22mwZhqtG+Jpphn6aLFwq+Jnnf/k u2JQcHQqR+CSJPA0f7dpFOUfjZGQ9FMewF032qOutFtwgoPp8FzIhKQTO/T4WPhgg6XcMn Xcvn+IXzKS3o22XTRQRqhK++5TN7SX2cLLQmz6PVsddxlhZ8aW3rJYPUwlITPm7j7Zku7V LuSSs8rd8Q9+WUDmot5RM3r0BUupln+3YonI2V9GZ0PTwJgm33Vi45QRFfo6A8/gr2UxCt LgqQH6fCDnBqf7BZfAuQ7RO2NnZJfEA1mzh8i54wGzu5qFUuSGqXDryibUju+g== Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 2D8B23E604 for ; Tue, 20 Feb 2024 20:40:01 +0100 (CET) Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1rcVy0-0002MJ-MJ; Tue, 20 Feb 2024 14:39:48 -0500 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1rcVxw-0002Ll-Ha for guix-patches@gnu.org; Tue, 20 Feb 2024 14:39:44 -0500 Received: from debbugs.gnu.org ([2001:470:142:5::43]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1rcVxv-0000Pb-Mh; Tue, 20 Feb 2024 14:39:43 -0500 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1rcVyE-0002Rz-9a; Tue, 20 Feb 2024 14:40:02 -0500 X-Loop: help-debbugs@gnu.org Subject: [bug#69292] [PATCH 4/6] store: database: Stop finalizing prepared statements. Resent-From: Christopher Baines Original-Sender: "Debbugs-submit" Resent-CC: guix@cbaines.net, dev@jpoiret.xyz, ludo@gnu.org, othacehe@gnu.org, rekado@elephly.net, zimon.toutoune@gmail.com, me@tobias.gr, guix-patches@gnu.org Resent-Date: Tue, 20 Feb 2024 19:40:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 69292 X-GNU-PR-Package: guix-patches X-GNU-PR-Keywords: patch To: 69292@debbugs.gnu.org Cc: Christopher Baines , Josselin Poiret , Ludovic =?UTF-8?Q?Court=C3=A8s?= , Mathieu Othacehe , Ricardo Wurmus , Simon Tournier , Tobias Geerinckx-Rice X-Debbugs-Original-Xcc: Christopher Baines , Josselin Poiret , Ludovic =?UTF-8?Q?Court=C3=A8s?= , Mathieu Othacehe , Ricardo Wurmus , Simon Tournier , Tobias Geerinckx-Rice Received: via spool by 69292-submit@debbugs.gnu.org id=B69292.17084579749354 (code B ref 69292); Tue, 20 Feb 2024 19:40:02 +0000 Received: (at 69292) by debbugs.gnu.org; 20 Feb 2024 19:39:34 +0000 Received: from localhost ([127.0.0.1]:46608 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rcVxl-0002Qn-OD for submit@debbugs.gnu.org; Tue, 20 Feb 2024 14:39:34 -0500 Received: from mira.cbaines.net ([212.71.252.8]:43142) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rcVxi-0002QW-6i for 69292@debbugs.gnu.org; Tue, 20 Feb 2024 14:39:32 -0500 Received: from localhost (unknown [212.132.255.10]) by mira.cbaines.net (Postfix) with ESMTPSA id BAD1627BBEB for <69292@debbugs.gnu.org>; Tue, 20 Feb 2024 19:39:07 +0000 (GMT) Received: from localhost (localhost [local]) by localhost (OpenSMTPD) with ESMTPA id 6e2d8eda for <69292@debbugs.gnu.org>; Tue, 20 Feb 2024 19:39:07 +0000 (UTC) From: Christopher Baines Date: Tue, 20 Feb 2024 19:39:04 +0000 Message-ID: X-Mailer: git-send-email 2.41.0 In-Reply-To: <4b6a268daab5e0b307dff2229d551a47c9fe1ebc.1708457946.git.mail@cbaines.net> References: <4b6a268daab5e0b307dff2229d551a47c9fe1ebc.1708457946.git.mail@cbaines.net> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-BeenThere: guix-patches@gnu.org List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-patches-bounces+larch=yhetil.org@gnu.org Sender: guix-patches-bounces+larch=yhetil.org@gnu.org X-Migadu-Flow: FLOW_IN X-Migadu-Country: US X-Migadu-Spam-Score: -3.48 X-Spam-Score: -3.48 X-Migadu-Queue-Id: 2D8B23E604 X-Migadu-Scanner: mx13.migadu.com X-TUID: ATxFlgZ+0FnA Especially since we're asking for these to be cached. Management of prepared statements isn't trivial, since you don't want to keep them forever as this can lead to poor query performance, but I don't think that finalizing them immediately is the right solution. Change-Id: I61706b4d09d771835bb8f074b8f6a6ee871f5e2d * guix/store/database.scm (sqlite-step-and-reset): New procedure. (last-insert-row, path-id, update-or-insert, add-references): Don't finalize prepared statements. Change-Id: I2a2c6deb43935d67df9e43000a5105343d72b3e6 --- guix/store/database.scm | 40 ++++++++++++++++++---------------------- 1 file changed, 18 insertions(+), 22 deletions(-) diff --git a/guix/store/database.scm b/guix/store/database.scm index 7e3a2873ce..8d8b7346e0 100644 --- a/guix/store/database.scm +++ b/guix/store/database.scm @@ -167,16 +167,19 @@ (define-syntax with-database ((_ file db exp ...) (call-with-database file (lambda (db) exp ...))))) +(define (sqlite-step-and-reset statement) + (let ((val (sqlite-step statement))) + (sqlite-reset statement) + val)) + (define (last-insert-row-id db) ;; XXX: (sqlite3) currently lacks bindings for 'sqlite3_last_insert_rowid'. ;; Work around that. - (let* ((stmt (sqlite-prepare db "SELECT last_insert_rowid();" - #:cache? #t)) - (result (sqlite-fold cons '() stmt))) - (sqlite-finalize stmt) - (match result - ((#(id)) id) - (_ #f)))) + (let ((stmt (sqlite-prepare db + "SELECT last_insert_rowid();" + #:cache? #t))) + (vector-ref (sqlite-step-and-reset stmt) + 0))) (define* (path-id db path) "If PATH exists in the 'ValidPaths' table, return its numerical @@ -187,11 +190,9 @@ (define* (path-id db path) SELECT id FROM ValidPaths WHERE path = :path" #:cache? #t))) (sqlite-bind-arguments stmt #:path path) - (let ((result (sqlite-fold cons '() stmt))) - (sqlite-finalize stmt) - (match result - ((#(id) . _) id) - (_ #f))))) + (match (sqlite-step-and-reset stmt) + (#(id) id) + (#f #f)))) (define-inlinable (assert-integer proc in-range? key number) (unless (integer? number) @@ -228,9 +229,8 @@ (define* (update-or-insert db #:key path deriver hash nar-size time) (sqlite-bind-arguments stmt #:id id #:deriver deriver #:hash hash #:size nar-size #:time time) - (sqlite-fold cons '() stmt) - (sqlite-finalize stmt) - (last-insert-row-id db)) + (sqlite-step-and-reset stmt) + id) (let ((stmt (sqlite-prepare db " @@ -240,8 +240,7 @@ (define* (update-or-insert db #:key path deriver hash nar-size time) (sqlite-bind-arguments stmt #:path path #:deriver deriver #:hash hash #:size nar-size #:time time) - (sqlite-fold cons '() stmt) ;execute it - (sqlite-finalize stmt) + (sqlite-step-and-reset stmt) (last-insert-row-id db))))) (define (add-references db referrer references) @@ -254,13 +253,10 @@ (define (add-references db referrer references) VALUES (:referrer, :reference)" #:cache? #t))) (for-each (lambda (reference) - (sqlite-reset stmt) (sqlite-bind-arguments stmt #:referrer referrer #:reference reference) - (sqlite-fold cons '() stmt) ;execute it - (last-insert-row-id db)) - references) - (sqlite-finalize stmt))) + (sqlite-step-and-reset stmt)) + references))) (define (timestamp) "Return a timestamp, either the current time of SOURCE_DATE_EPOCH." -- 2.41.0