Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iBKXU-00051l-2h for pgsql-hackers@arkaria.postgresql.org; Fri, 20 Sep 2019 15:09:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iBKXT-0005ds-0B for pgsql-hackers@arkaria.postgresql.org; Fri, 20 Sep 2019 15:09:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iBKXS-0005dg-KZ for pgsql-hackers@lists.postgresql.org; Fri, 20 Sep 2019 15:09:38 +0000 Received: from mail.thelabyrinth.net ([45.56.70.56]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iBKXP-0002Yb-Uu for pgsql-hackers@postgresql.org; Fri, 20 Sep 2019 15:09:37 +0000 Received: from Davids-iMac.local (c-73-39-102-148.hsd1.md.comcast.net [73.39.102.148]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: dsteele) by mail.thelabyrinth.net (Postfix) with ESMTPSA id 278D554683; Fri, 20 Sep 2019 15:09:35 +0000 (UTC) Subject: Re: backup manifests To: Robert Haas Cc: "pgsql-hackers@postgresql.org" References: <4b6244ea-4094-9055-080b-7afb274bf270@pgmasters.net> From: David Steele Openpgp: preference=signencrypt Autocrypt: addr=david@pgmasters.net; prefer-encrypt=mutual; keydata= mQINBFSheh0BEADGNb3c1kOaX5OGUDhqHTwJ3JW7XAllfWLdknpRg+/fihgLPPRnQHZhjq28 nmMLqM+C8TjezT2BgVACeu8qqASnuzN/E5+bAl9hVcgCP4eV/lEIWwVxmhcHqSdeWqWZSBRB tf07fLBnz22+3LwVsESN+fanWxnoqqvE0+hyGnCEDhOBkihgbYHuq4vw1+xgoGN1uDk4NShV El0ZWO90P4e5mOWA2Zsex+8YhGJlfRwMpU69okFCvjpWJKE+ufeJdlrxEpSSKW7SQ0kG1VF7 CvDKpF70TCWkb4Bagb6c7lwZrMjxuBnlIvOlV3RRY67PAaVIFRRvQJeJABOPHp8HnH8cwici 7gwwfWBrZZeAdPqs6VNjANqI9uaDasRnD3yHP6CcHIAeCl5hYnzNTkIqBRlnmD5y8s0VRDLr 6JR3FxscOsvwXPJ+He1RpRlxQDlBboaRFUz8endeQHhGUeHyekQcSYUaz4PX3OJBG0Xi7nr8 EOP6zOeP61z3dL8NuO3QjgPiygvS0qwMEPvOYo52nEGYn08nvFVajI8yxOXFnIwnmpgr5gdq nL9ga72tvSyJQAUx+YB7r1bKjRn/Mlt/fPIS5PXONofiO1K8uX+pzvIX6dnm7ihpFf1XC+uG 5+DKBnU7yS7jCRL52GxWHbuC3mT4tvzqH2b13TLng1Y7Izi+hwARAQABtCJEYXZpZCBTdGVl bGUgPGRhdmlkQHBnbWFzdGVycy5uZXQ+iQI5BBMBCAAjAhsDAh4BAheABQsJCAcDBRUKCQgL BBYCAwEFAlShuEECGQEACgkQn830JKJznPPsLhAAtKkIu0mFfxcVqApg4PNjZKYY4TWeN+I+ go/VKX8QWMadr33t8Sxcgyn48bjBYzgWQXhF4rMcFxDxI3ly5bdE8QKZkhFJJT7N2fBoo9Zm ZOsOvPd+5hz07VZpgNSiTXMAphj8+GSTLbYufm/OUnfpS1eovPtPdYPQpNC3RVHA0gtisz26 NgMLg6QN0iQuITs9N+wiaGtyVIornl4XAY2dM/Hvfr1aDFkukNNr52sc/mGqocG/dKC7dONA 3860y2MLszQcbAE45iEkmwpGkvxyc3i3tvdD3VNlVI1MKJUBnxFPUEI0lVq5WpApFSIU31+j aNunKIcfVxRnM8FTJBsw3RnFRpSsEtiLaZRm5sOZHa9+dy/DWDb+8O1I+momCo88+Jksd6pZ 2Tz9msOiJiTnFsCZgfbbmayH6w8ZyfHuO0DuxScM1UK+6CLC2VVjQFuH6WHqJpaENg5INswS qpUc05iC4i+6dZOe44cviX6LpHuP8vWM0E63CMun0l42yHWy+k6nLvx/fj7t++tF2JIJ92OG n/2MkM5lIKknRg27UjYAspD6f3NSZUMIe6tGyWPTtLOu8zOL5CeRyCHMWwUVDKJSBzlK2mV8 e0EbmtUJFsoNWXWKPKUIxX7JXPN3v523HmOhtyz5SnkGtwEqCZiv/JccYmh+S2IokAuscLNP EJq5Ag0EVKF6HQEQALPpUsP67xfcj6NUMyySXSytUzMIVi5XNTyazuNcVfFZzT9QL8wFQkgy i/HOD3tmvdGxz6H8V+v+cszp88gCd8TwClss7u+iPsLlAQQWC4q0WiX4OhwFv2H004iuft8i odjiWDkYEgIFxlPNxQfHFmchkyODIJXQmnzbgsXwGZtJzAxEVh6AgYJqE/Ev/+kTJhJ7703n oIb+PXvafoB5axnU4nf7DuFa9X+q2/ZfzyAXUCKvzDvxb24iUgWFzlWBx0CnI21Kh6aS6hR6 o00D2OBiU+yCpW9eRTi3SuDuslmtCUaZz5V4SHo37ochEYfZ2LYOPsBO3PUMx8T7MDYrmv/x 4W5rdjugz4NXWROStNNLpS74LW20rdrEKMDltph7U2zfjjS663K+CUxoXn/ha2WnzBocoY5o KwdViLln4s0ivOTa4clHJJUsYiAxLShAe2AP+5kJim24tAabbEOsrRTn9RpT7J8jVT/G0YDE glujEt0U04Akkj5NO4G7vJP/AyX5zKZetkp/HFiFBZMkUuXD/KgDc5EM2fpQtaxGGY2fcQqY VHUbbxstxyl5d349M9rok7O90hw9BkP0Q8UEpgmlZ6ftwx+ZfZmBEdQ7HcMHnSPSNJkXt/Vi ozlcrSnkVXzCXvH9DHrIpgenp8btsJ2XyjSdrEu4jAqYeirW+3qRABEBAAGJAh8EGAEIAAkF AlSheh0CGwwACgkQn830JKJznPNmERAApYaQK6i9F6hM2RUD/PqQC45WmzOXK9bDSS9hdk4p Nc5jSW2+FkcMFe1XFG2NyGf1v5KBPxuDTQp35PIwRsL59xNmPICu0ae6zmCPUpxbMIEzb/VU wXqeuNp3cRVll06goDt5w+2LlAtBrxuDjiWxby9LH+vsIUMLWYu+M50pdtOcXOsO1+zyfs3w U6xRZ3PaFDKO1cB68okDFG/cFHnYfY5aETV7LgVBHEOQLwL01U01QC/iqY/aNO4iUH/+PBc0 R6mYlP7IqDQOQVTJ0dXP365thl0Q7c9e2JQSS9wuSv2EEiR//nnZREtLyrQNLwsXE8UFBDe7 uA8Lm2ldmQYpHHKAcorGVTHYJaaus2LjH8tZ1i4RGg4hKMJSHil/5C+Ghl+PmcACVeGaxB0c mJ8+0jyWDkLCDMAXJJyvXRWIONTAxVt5kKg1/iMAq1IQYNd66agnmgxdSvKLPUo0JJ5fhQJp 2bqDDTrINTZg+Rub2HeoOMxYJsMypK+6aY4iVlm2Z7A9SgyQDjGAXzD9+XaezNFeXxfPbjo6 46qA9SycdYhiwaTcTQakiLkZWRIkSjGQwNCK9NktE1fYnXfR0+WaWFduQ4BfM5uta1bt8esB /mLEjVYS9RRAWZ/untJrR9JYGTC4Mu6tcLGZD7385yX3TNM1iaIUGxlKkHHCZCnILyq5Ag0E VKGipwEQALe8Af9Kgql5rC6NPrOeuOuuqwXMNIwKsTLG8PmmoHKEHKukYuX/YfnGWFU1Ir6t y0qXe2XZs8AnLNI753b/7Z7ftMuMWzWqzfU8Pb+8/NrOQqfO05++gtsczY/tflVW2YWJWDZ4 lhytY4frlOSRuMOI4TjM3NwgkVri4sM4U+OdM8tdkW9IdajLCP0J+wFO+U2dU9b3yCss0GNY c0dlbmI6sOmpV70ldAb8i0BqfkGpq3xDcnzf4EQDdF/mBLZwBe0v6dzlxHh2HqL9yztjGXnk NtRFYGzKw+ZBuOaSj+OPG7iaBSgeNxDYjww2KTzdSE3DbfGqwrxn/ejD+ZueNcqxsoaT3oNr ADeluXmN63C1CcdFmJjAiCCjt3diLgmVL3MyBV+LcSwEz+2B/tlRHV1FWvy8VX1Nj2CwCO9u RuJ9HrF05TXjjNC2o6fFME/TkEzsklzvLxP/Bs/wKDvZQKGlzshk8EsmRhnJjzekOg4gOyVM LvjbGiBPMh309i0nVgExz0LIvI5Wp8G1YXHiuf6i52pTgeiGfiV6D12b69ABqInOfvROSvfe F36OLmoRicJcd8wEwkNPoSHHn/Zxf0fMHe7ZV7EtNSeqB+179SxDqeIEAOhpye6/q57LqvpZ qm0ADa5O3J6IMpId+ov0el553e7HxfDmaWJpm+NemLWvABEBAAGJBD4EGAEIAAkFAlShoqcC GwICKQkQn830JKJznPPBXSAEGQEIAAYFAlShoqcACgkQgDa4glDlIUCXfQ//aozIciE9Je5m kUlKECjXut8vvpO9fbAbxY5VIEWANV2PpIw1LO/8WVstCl38vBZtOxCcZqccLge12Ow72WPF 5G+v+8OJRwHNjrdI/WvFeMUfqlWB2ok+OOlm9t4U0lTK7YX4K6PvjXiMh1Kpvx/JsxrnGJJx X+Ky4icNWdiyaVBs+NDROasZXmy+7S8yeR9ECPxu/DPziC+aPW5lJ3tGqFEPADpmeD8G5K7U QyDJ4GRgD+Agez8r60qyhiwFyUXfcn0bOsIx/og4gae8GnadPl1+93+PI0L8Xb+oYcJnvVGE zJZJIMsp1Zjhi3PnJEulvDNOxkY7Og2dEwkNSHrru3Z5odl8s629VaYyT1b0Q/GnDAnKtZc3 tKZVKCvvAf9UmbuBfbuP084dBFeMeFWl3ET9UWpGhKm22KXg8TrMKoM62bUSjW5gIxC8ywLT mu7ivcKbxj0rXFB5AdYUff9QsCZFdDZG3fUymOQmYgNhwXAAIwKDKD8CINc2CIVc0qMhh3Hy WyyA8JZKw/iK+652WHoKEMmxVunSvvlDIMDf/bYN/HxoUEBcCSWsISKW6xGu1hPKfYK2t6/9 CVjluTOPeFiBt1ELXsOlpRPe4f0M2Cc/AEFukE1ZoJWlUhtU4zuOpvDtGST9D9imYsgMaMa8 TtEGFWGDmOfws4yU4tTTUNqFRRAArysPyXEhNNbLncfR5lPGPfZORPqLmIdkNMcDAwmzvC80 g1WH46wPzIcUUTc7DnP0pZNWGmI2NAnAW/c4Ii3aiXQXZDNVRfdZKL5rEY6dtgxdHl4hc6KN KH17wsX4T3dglMqP7VikYT23sNQJVE1oIGjfVR1sOe+DleI5Awa50DgGyLZS9vLfZz/9F28J 2YM1DExVPxbj75bwfOl65hh8B5BCqqrYp5n/JqV2x5+OSPgCHmd7rJtTbTl89MVKc0uR79ZZ hxUBU6m5v2UGHakSlgdbh+qmr6rdFkqRxRFhdepD7u9qvPKHdVIPp23dqkx9r7gIe+RjfkT6 aq12wQhqgGYaae5bqlg0ls1CpfZxsxJmwmO/DJ2W1hdrPhs/7ml9rv552FBQuQ7i9rddeSju lkFsXVWOsbLf45pKAWTwqZihb3nSIoBv53EH0V+1SPzDbgQk2CaQ+DR6k7eujgsI/srPpMtl ehqEQ7kzaKFACBgXBOxkheDR/kJzVJxx0a1rIFnvJGL/h5lgo8onxP5scCpotNx/bJjLkBVk XQvKDUIu9YPy0yENT58C91WWX/Nac3B5W5ZkmWHh6Lh+5eOiRSLV9MDau6ziULNNpuIA90iY 1onr0xRNO2DMyn0NzZzL+Y59WlMyATUcQdTgtmlmTcaxWeRiBRlUBLvHvgHLV94= Message-ID: <146910fc-3df7-eeb4-ac5b-2f57a01f646f@pgmasters.net> Date: Fri, 20 Sep 2019 11:09:34 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 9/20/19 9:46 AM, Robert Haas wrote: > On Thu, Sep 19, 2019 at 11:06 PM David Steele wrote: > >> My experience is that JSON is simple to implement and has already dealt >> with escaping and data structure considerations. A home-grown solution >> will be at least as complex but have the disadvantage of being non-standard. > > I think that's fair and just spent a little while investigating how > difficult it would be to disentangle the JSON parser from the backend. > It has dependencies on the following bits of backend-only > functionality: > - elog() / ereport(). Kind of a pain. We could just kill the program > if an error occurs, but that seems a bit ham-fisted. Refactoring the > code so that the error is returned rather than thrown might be the way > to go, but it's not simple, because you're not just passing a string. Seems to me we are overdue for elog()/ereport() compatible error-handling in the front end. Plus mem contexts. It sucks to make that a prereq for this project but the longer we kick that can down the road... > https://www.pgcon.org/2013/schedule/events/595.en.html This talk was good fun. The largest number of tables we've seen is a few hundred thousand, but that still adds up to more than a million files to backup. > This gets at another problem that I just started to think about. If > the file is just a series of lines, you can parse it one line and a > time and do something with that line, then move on. If it's a JSON > blob, you have to parse the whole file and get a potentially giant > data structure back, and then operate on that data structure. At > least, I think you do. JSON can definitely be parsed incrementally, but for practical reasons certain structures work better than others. > There's probably some way to create a callback > structure that lets you presuppose that the toplevel data structure is > an array (or object) and get back each element of that array (or > key/value pair) as it's parsed, but that sounds pretty annoying to get > working. And that's how we do it. It's annoying and yeah it's complicated but it is very fast and memory-efficient. > Or we could just decide that you have to have enough memory > to hold the parsed version of the entire manifest file in memory all > at once, and if you don't, maybe you should drop some tables or buy > more RAM. I assume you meant "un-parsed" here? > That still leaves you with bypassing the 1GB size limit on > StringInfo, maybe by having a "huge" option, or perhaps by > memory-mapping the file and then making the StringInfo point directly > into the mapped region. Perhaps I'm overthinking this and maybe you > have a simpler idea in mind about how it can be made to work, but I > find all this complexity pretty unappealing. Our String object has the same 1GB limit. Partly because it works and saves a bit of memory per object, but also because if we find ourselves exceeding that limit we know we've probably made a design error. Parsing in stream means that you only need to store the final in-memory representation of the manifest which can be much more compact. Yeah, it's complicated, but the memory and time savings are worth it. Note that our Perl implementation took the naive approach and has worked pretty well for six years, but can choke on really large manifests with out of memory errors. Overall, I'd say getting the format right is more important than having the perfect initial implementation. > Here's a competing proposal: let's decide that lines consist of > tab-separated fields. If a field contains a \t, \r, or \n, put a " at > the beginning, a " at the end, and double any " that appears in the > middle. This is easy to generate and easy to parse. It lets us > completely ignore encoding considerations. Incremental parsing is > straightforward. Quoting will rarely be needed because there's very > little reason to create a file inside a PostgreSQL data directory that > contains a tab or a newline, but if you do it'll still work. The lack > of quoting is nice for humans reading the manifest, and nice in terms > of keeping the manifest succinct; in contrast, note that using JSON > doubles every backslash. There's other information you'll want to store that is not strictly file info so you need a way to denote that. It gets complicated quickly. > I hear you saying that this is going to end up being just as complex > in the end, but I don't think I believe it. It sounds to me like the > difference between spending a couple of hours figuring this out and > spending a couple of months trying to figure it out and maybe not > actually getting anywhere. Maybe the initial implementation will be easier but I am confident we'll pay for it down the road. Also, don't we want users to be able to read this file? Do we really want them to need to cook up a custom parser in Perl, Go, Python, etc.? -- -David david@pgmasters.net