From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Robin Tarsiger Newsgroups: gmane.emacs.devel Subject: Re: sqlite3 usage for multisession variable storage Date: Tue, 14 Dec 2021 06:07:02 -0600 Message-ID: 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; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="2836"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.4.0 Cc: emacs-devel@gnu.org To: Lars Ingebrigtsen Original-X-From: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Tue Dec 14 13:10:01 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 1mx6d3-0000ST-A5 for ged-emacs-devel@m.gmane-mx.org; Tue, 14 Dec 2021 13:09:58 +0100 Original-Received: from localhost ([::1]:36086 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1mx6d2-0002pm-7p for ged-emacs-devel@m.gmane-mx.org; Tue, 14 Dec 2021 07:09:56 -0500 Original-Received: from eggs.gnu.org ([209.51.188.92]:36974) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1mx6aP-0000ts-2t for emacs-devel@gnu.org; Tue, 14 Dec 2021 07:07:13 -0500 Original-Received: from wout3-smtp.messagingengine.com ([64.147.123.19]:41037) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1mx6aK-000854-Ti for emacs-devel@gnu.org; Tue, 14 Dec 2021 07:07:11 -0500 Original-Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id 9C88D3200AC9; Tue, 14 Dec 2021 07:07:04 -0500 (EST) Original-Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Tue, 14 Dec 2021 07:07:05 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dasyatidae.com; h=message-id:date:mime-version:to:cc:references:from:subject :in-reply-to:content-type:content-transfer-encoding; s=fm3; bh=h RdqdcFnD4Ny1C+jootR5a9rJG8mpcxotrt1HMUp7TU=; b=OQu4cTKlxXAVVePD3 gQcc0NFEjMYNZk5FyY7OSpNbSYeUkKdxt+ZWWyOGLRJxPSEuzQqgduMf7h+rOyyc +LlCYGS5r8Fq+QB9sj9yORKYf0zIeBoZH5NsOXdmbFPROJwIbw266WkHl1vsjmkj Wx3hKh1xOVPvaml1YNHOAaYoLkDErVoozRhTfcFA7Qr3OYOX3NIpXqKofrRtotlO hYLSb432qhzrPb33c/da1F36QNeOfvh1UERof75dq+mqX0wS1u20a2i+7xMXVQbk t5Gx9fhGOY1iZhmbI33ZhrgeF4O3029Cikveg+DQCng7r3/0RaHNh4q3IcYdA3Fo 3eEPA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm1; bh=hRdqdcFnD4Ny1C+jootR5a9rJG8mpcxotrt1HMUp7 TU=; b=mo+ptxnH56pqZfu+u+PPKkgTgJ2oi7AENGH+0NDMNe5oVNEsSinYg2Pjd 8Hqyf5SoK46Ewxkt8huT48asAplHRXp2/p9qjGWHyN3UK81naEpQZ5/XYms6V0OB EuLQZERsmwyd2DKtxcGVsIGky2eiyEx5+taKPfJQQdNLApuJgjS2/HCLYD3/Txcf Ap+b8HbKRlmLT9jagZGjIQiEu7hEmP4GaSebhzmvBCq917bf8Ij0b9YckDDO4Hzd y/ZTx2b2XXbujbBX3pdQsL6EOZL0pXrdUV9XohJBrYcbK1NwuMOR4qrgBovhkyyR EKnjlSa8udXKXBNOgWgCkBC4Ysp/A== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvuddrledtgdefjecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfvfhfhufgjtgfgsehtkeertddtfeejnecuhfhrohhmpeftohgsihhn ucfvrghrshhighgvrhcuoehrthhtsegurghshigrthhiuggrvgdrtghomheqnecuggftrf grthhtvghrnhephfehffegveetvddtheeiuddujeetleehgfffvdekvddvfefgtdelfeet leejjeeinecuffhomhgrihhnpehsqhhlihhtvgdrohhrghenucevlhhushhtvghrufhiii gvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehrthhtsegurghshigrthhiuggrvgdr tghomh X-ME-Proxy: Original-Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 14 Dec 2021 07:07:03 -0500 (EST) Content-Language: en-US-large In-Reply-To: <87czlzqy3a.fsf@gnus.org> Received-SPF: pass client-ip=64.147.123.19; envelope-from=rtt@dasyatidae.com; helo=wout3-smtp.messagingengine.com X-Spam_score_int: -37 X-Spam_score: -3.8 X-Spam_bar: --- X-Spam_report: (-3.8 / 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, NICE_REPLY_A=-0.962, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H2=-0.001, SPF_HELO_PASS=-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:281889 Archived-At: Lars Ingebrigtsen wrote: > Óscar Fuentes writes: > >> Forgot to mention that modern databases usually offer several strategies >> for persisting data. A quick look at SQLite documentation shows that it >> can be configured to use a write ahead log, which is faster than the >> default rollback journal: >> >> https://sqlite.org/wal.html > > Yes, I used the WAL in my tests. (The implementation is still on the > scratch/sqlite branch, for those who are curious.) > 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?) - 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... - Having auto_vacuum be FULL similarly seems like a waste, adding a bunch of extra work to each transaction commit. - 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. - Setting trusted_schema to OFF is recommended for new applications. - 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. - Doesn't the current code in multisession.el not execute the pragmas on subsequent opens? Only some of these are persistent. (As a reminder for anyone following, the documentation for SQLite pragmas is available at .) -RTT