I mean, maybe it's because I've been awake since... 7am yesterday, but it seems to me that if Postgres fails catastrophically silently (and I would say "it looks like all your data in this table disappeared because of some arcane locale / btree issue that no one except Tom Lane even knows exists" -- see the replies about hardware issues and ON CONFLICT as an example) -- then maybe that is... not good, and Postgres shouldn't do that?
Not only that, it's only indices which have non-ASCII or whatever in them that silently fail, so it's like 95% of your indices work just fine, but the ones that don't... look fine. They're not corrupt on disk, they have their full size, etc. How is anyone supposed to know about this issue? I've been using Postgres since 1999, built the Postgres website, worked with Neil and Gavin on Postgres, submitted patches to Postgres and various Postgres-related projects, and this is the first time I've become aware of it. I mean, maybe I'm dumb, and... fine. But your average user is going to have no idea about this. Why can't some "locale signature" or something be encoded into the index so Postgres can at least warn you? Or not use the messed up index altogether instead of silently returning no data? On Sun, Jun 6, 2021 at 2:06 PM Omar Kilani <omar.kil...@gmail.com> wrote: > > We do use ON CONFLICT… it doesn’t work because the index is both “good” and > “bad” at the same time. > > On Sun, Jun 6, 2021 at 2:03 PM Justin Pryzby <pry...@telsasoft.com> wrote: >> >> On Sun, Jun 06, 2021 at 03:54:48AM -0700, Omar Kilani wrote: >> > What I sort of don't get is... before we insert anything into these >> > tables, we always check to see if a value already exists. And Postgres >> > must be returning no results for some reason. So it goes to insert a >> > duplicate value which somehow succeeds despite the unique index, but >> > then a reindex says it's a duplicate. Pretty weird. >> >> In addition to the other issues, this is racy. >> >> You 1) check if a key exists, and if not then 2) INSERT (or maybe you UPDATE >> if >> it did exist). >> >> https://en.wikipedia.org/wiki/Time-of-check_to_time-of-use >> >> Maybe you'll say that "this process only runs once", but it's not hard to >> imagine that might be violated. For example, if you restart a multi-threaded >> process, does the parent make sure that the child processes die before itself >> dying? Do you create a pidfile, and do you make sure the children are dead >> before removing the pidfile ? >> >> The right way to do this since v9.6 is INSERT ON CONFLICT, which is also more >> efficient in a couple ways. >> >> -- >> Justin
