From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Lars Ingebrigtsen Newsgroups: gmane.emacs.devel Subject: Re: sqlite3 usage for multisession variable storage Date: Tue, 14 Dec 2021 14:27:28 +0100 Message-ID: <874k7bqptr.fsf@gnus.org> References: <87tufmjyai.fsf@gnus.org> <87lf0nr2b4.fsf@gnus.org> <87ee6f5wmy.fsf@telefonica.net> <87czlzqy3a.fsf@gnus.org> Mime-Version: 1.0 Content-Type: text/plain Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="31522"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/29.0.50 (gnu/linux) Cc: emacs-devel@gnu.org To: Robin Tarsiger Original-X-From: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Tue Dec 14 14:36:33 2021 Return-path: Envelope-to: ged-emacs-devel@m.gmane-mx.org Original-Received: from lists.gnu.org ([209.51.188.17]) by ciao.gmane.io with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mx7yq-0007vP-CH for ged-emacs-devel@m.gmane-mx.org; Tue, 14 Dec 2021 14:36:32 +0100 Original-Received: from localhost ([::1]:36140 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1mx7yn-0006Qa-Cc for ged-emacs-devel@m.gmane-mx.org; Tue, 14 Dec 2021 08:36:29 -0500 Original-Received: from eggs.gnu.org ([209.51.188.92]:52450) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1mx7qG-0005RS-5J for emacs-devel@gnu.org; Tue, 14 Dec 2021 08:27:40 -0500 Original-Received: from [2a01:4f9:2b:f0f::2] (port=58706 helo=quimby.gnus.org) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1mx7qD-0002ta-LS for emacs-devel@gnu.org; Tue, 14 Dec 2021 08:27:39 -0500 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnus.org; s=20200322; h=Content-Type:MIME-Version:Message-ID:In-Reply-To:Date: References:Subject:Cc:To:From:Sender:Reply-To:Content-Transfer-Encoding: Content-ID:Content-Description:Resent-Date:Resent-From:Resent-Sender: Resent-To:Resent-Cc:Resent-Message-ID:List-Id:List-Help:List-Unsubscribe: List-Subscribe:List-Post:List-Owner:List-Archive; bh=+V96lr8s6MLzAy5880/5jWx0MYX+j/Et0mWfi1e7BlM=; b=Pb64O3KOyMUQezlmY6wcb6RkWU g3V9KpC6ev/Hf0gh2Q07T0R5bjmIikcFedWkKKVw0U3gSU66ib5onE/XTFjhBgBxkUfuW52ptcAt7 MpXVflcGA87pH1VfI8hfXEBq2g0wP/lT1KC+wihjLqfFnNesfxfsFhl1GomjY/0OsMIw=; Original-Received: from [84.212.220.105] (helo=xo) by quimby.gnus.org with esmtpsa (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mx7q7-0000tH-D6; Tue, 14 Dec 2021 14:27:34 +0100 Face: iVBORw0KGgoAAAANSUhEUgAAADAAAAAwBAMAAAClLOS0AAAABGdBTUEAALGPC/xhBQAAACBj SFJNAAB6JgAAgIQAAPoAAACA6AAAdTAAAOpgAAA6mAAAF3CculE8AAAAD1BMVEXp5OKzpqKKeHVS PTv///9X6rKaAAAAAWJLR0QEj2jZUQAAAAd0SU1FB+UMDg0OMB/wNNIAAAGSSURBVDjLbZMLtsMg CETRbgBxA4obaHT/e3sDfpK2j9M0jdeBwRIKhEiNI9OJqBTJnwNAuUHoADP+UZDnyh8gKGUqW1G+ FMl3tvJRY6Zi/gV8in+DsH7m8lVjg1hiXbs5WY0bhLqyjMofYKUKqfWCzn8AtTau/xQkBsZxFesG quMNgM7hg5I8wRWbAQ+AGlaqBlDQudQFcIMpzjiPUQRZUtIKoC5lsh1SAXBXRLxCQrUA0EZNwTLJ AlXQc0hpDBgj3y72SSpFVNjAFUhPgFbp6qAracfjXAbIVdMGQ+b+jgvZsM2AzAY5ApinLuJgnLPS 1n3SNNdsQE2BC+m6tX5AWwqYtWGJAJeDtP5o1LT5enVpVzup7shdxgTVgWUh+3r1tEAhq30GyhQz +LFqrq8b8BqzBdpcv4j4U6ELuCLcoLbu4E3TLq3X5AA2EA1M1auOCeirxgY2u/xk+QBbfXQC0JZb 4mcy1d0epsT9LMdNp8KWps8FJG/F7ANgn8nYbu10Yc1f9gO6vxrknoN747COMPEfhaRNUtUweIMA AAAldEVYdGRhdGU6Y3JlYXRlADIwMjEtMTItMTRUMTM6MTQ6NDcrMDA6MDDwvXo3AAAAJXRFWHRk YXRlOm1vZGlmeQAyMDIxLTEyLTE0VDEzOjE0OjQ3KzAwOjAwgeDCiwAAAABJRU5ErkJggg== X-Now-Playing: David Sylvian's _Brilliant Trees_: "The Ink In The Well" In-Reply-To: (Robin Tarsiger's message of "Tue, 14 Dec 2021 06:07:02 -0600") X-Host-Lookup-Failed: Reverse DNS lookup failed for 2a01:4f9:2b:f0f::2 (failed) Received-SPF: pass client-ip=2a01:4f9:2b:f0f::2; envelope-from=larsi@gnus.org; helo=quimby.gnus.org X-Spam_score_int: -35 X-Spam_score: -3.6 X-Spam_bar: --- X-Spam_report: (-3.6 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, RCVD_IN_DNSWL_MED=-2.3, RDNS_NONE=0.793, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-devel@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "Emacs development discussions." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Original-Sender: "Emacs-devel" Xref: news.gmane.io gmane.emacs.devel:281897 Archived-At: Robin Tarsiger writes: > I'm curious about some of these pragma settings more generally. > > - Why journal_mode WAL rather than TRUNCATE or MEMORY? WAL makes the file > less easily moveable and introduces the shm requirement, which feels weird > in an Emacs context. Since Emacs itself often works along the lines of "write > new file and replace" or "copy to backup and then overwrite", DELETE is > natural, but TRUNCATE feels slightly better for managed data stores. MEMORY > would expose the DB to corruption on application crashes, but if you're > outside a transaction almost all the time, it might be fine... and I don't > see the files backend currently using backup files (or even locking) anyway, > yes? (Or is that intended to be added later?) MEMORY seems inappropriate, since several instances will be using the file at the same time. I'm not sure what the TRUNCATE would bring us. The `files' backup doesn't lock anything, because it just wants whatever instance manages to write the file last to do so. But I was pondering writing to a temp file in the same directory and doing a rename instead. > - Doesn't that say "bsynchronous" with an extra b? (Unrecognized pragmas are > normally no-ops.) Also, if you're using synchronous NORMAL rather than OFF > for SQLite, then presumably make sure write-region-inhibit-fsync is nil while > comparing... Fixed the typo. I didn't see any noticeable difference between NORMAL and OFF on this laptop, so I left it at NORMAL. > - Having auto_vacuum be FULL similarly seems like a waste, adding a bunch of > extra work to each transaction commit. Likewise, I didn't see any difference between FULL and INCREMENTAL here, and FULL means less work, so I left it at FULL. > - Given that people have expressed concerns over in-place modification leaving > unwanted traces of old data, setting secure_delete to ON may be useful here. I thought the vacuum was supposed to take care of most of that? > - Setting trusted_schema to OFF is recommended for new applications. I'm not sure that's relevant for this usag? > - You might consider using application_id to make the file identifiable as a > specific format rather than probing for the existence of the main table. Sure. > - Doesn't the current code in multisession.el not execute the pragmas on subsequent > opens? Only some of these are persistent. Good point. I've now moved the pragmas. (But that didn't seem to affect performance, either.) Thanks for the tips. -- (domestic pets only, the antidote for overdose, milk.) bloggy blog: http://lars.ingebrigtsen.no