Re: [BUG] Logical replication failure "ERROR: could not map filenode "base/13237/442428" to relation OID" with catalog modifying txns

On 10/10/21 23:27, Masahiko Sawada wrote: > > After more thought, given DDLs are not likely to happen than DML in > practice, ... I haven't looked closely at the patch, but I'd be careful about workloads where people create and drop "temporary tables". I've seen this pattern used a few times, esp

Re: Sequence's value can be rollback after a crashed recovery.

On 11/22/21 12:31, Tom Lane wrote: > "Bossart, Nathan" writes: >> I periodically hear rumblings about this behavior as well. At the >> very least, it certainly ought to be documented if it isn't yet. I >> wouldn't mind trying my hand at that. Perhaps we could also add a new >> configuration par

Re: Sequence's value can be rollback after a crashed recovery.

On 11/23/21 05:49, Andy Fan wrote: > > > I think at this thread[1], which claimed to get this issue even after > > commit, I haven't tried it myself though. > > > > [1] > > https://www.postgresql.org/message-id/flat/ea6485e3-98d0-24a7-094c-87f9d5f9b18f%40amazon.com#4cfe7217c82

Re: Reports on obsolete Postgres versions

On 3/15/24 3:17 AM, Daniel Gustafsson wrote: >> On 14 Mar 2024, at 16:48, Peter Eisentraut wrote: >> On 13.03.24 18:12, Bruce Momjian wrote: > >>> I think "minor" is a better term since it contrasts with "major". We >>> don't actually supply patches to upgrade minor versions. >> >> There are pot

Re: Reports on obsolete Postgres versions

On 3/12/24 3:56 AM, Daniel Gustafsson wrote: >>> but that is far down the page. Do we need to improve this? > >> I liked the statement from Laurenz a while ago on his blog >> (paraphrased): "Upgrading to the latest patch release does not require >> application testing or recertification". I am no

Re: Reports on obsolete Postgres versions

On 3/13/24 11:21 AM, Tom Lane wrote: > Robert Treat writes: >> On Wed, Mar 13, 2024 at 1:12 PM Bruce Momjian wrote: >>> On Wed, Mar 13, 2024 at 09:21:27AM -0700, Jeremy Schneider wrote: >>>> In my view, the best thing would be to move toward consistently using &g

Re: Reports on obsolete Postgres versions

> On Mar 13, 2024, at 11:39 AM, Tom Lane wrote: > > Jeremy Schneider writes: >>> On 3/13/24 11:21 AM, Tom Lane wrote: >>> Agreed, we would probably add confusion not reduce it if we were to >>> change our longstanding nomenclature for this. > >>

proposal: change behavior on collation version mismatch

Respectfully, Jeremy 1: https://www.postgresql.org/message-id/flat/CA%2BfnDAZufFS-4-6%3DO3L%2BqG9iFT8tm6BvtZXNnSm1dkJ8GciCkA%40mail.gmail.com#beefde2f9e54dcee813a8f731993247d 2: https://github.com/ardentperf/glibc-unicode-sorting/ 3: https://ardentperf.com/2023/03/26/did-postgres-lose-my-data/ -- Jeremy Schneider Database and Performance Engineer Amazon Web Services

Re: proposal: change behavior on collation version mismatch

make this argument with ISO 8859 but in the unicode world en_US has default sort rules for japanese, chinese, arabic, cyrilic, nepalese, and all kinds of strings with nonsensical combinations of all these characters.  After some years of ICU and PG, I'm just coming to a conclusion that the right th

Re: proposal: change behavior on collation version mismatch

On 11/28/23 2:12 AM, Daniel Verite wrote: > Jeremy Schneider wrote: >> 1) "collation changes are uncommon" (which is relatively correct) >> 2) "most users would rather have ease-of-use than 100% safety, since >> it's uncommon" >> >>

Re: Built-in CTYPE provider

On 12/5/23 3:46 PM, Jeff Davis wrote: > === Character Classification === > > Character classification is used for regexes, e.g. whether a character > is a member of the "[[:digit:]]" ("\d") or "[[:punct:]]" > class. Unicode defines what character properties map into these > classes in TR #18 [1],

Re: encoding affects ICU regex character classification

On 12/12/23 1:39 PM, Jeff Davis wrote: > On Sun, 2023-12-10 at 10:39 +1300, Thomas Munro wrote: >> Unless you also >> implement built-in case mapping, you'd still have to call libc or ICU >> for that, right? > > We can do built-in case mapping, see: > > https://postgr.es/m/ff4c2f2f9c8fc7ca27c1c24

Re: Built-in CTYPE provider

On 12/13/23 5:28 AM, Jeff Davis wrote: > On Tue, 2023-12-12 at 13:14 -0800, Jeremy Schneider wrote: >> My biggest concern is around maintenance. Every year Unicode is >> assigning new characters to existing code points, and those existing >> code points can of course alr

Re: encoding affects ICU regex character classification

On 12/14/23 7:12 AM, Jeff Davis wrote: > The concern over unassigned code points is misplaced. The application > may be aware of newly-assigned code points, and there's no way they > will be mapped correctly in Postgres if the provider is not aware of > those code points. The user can either procee

Re: Built-in CTYPE provider

On 12/5/23 3:46 PM, Jeff Davis wrote: > CTYPE, which handles character classification and upper/lowercasing > behavior, may be simpler than it first appears. We may be able to get > a net decrease in complexity by just building in most (or perhaps all) > of the functionality. > > === Character Cla

Re: Built-in CTYPE provider

On 12/20/23 3:47 PM, Jeremy Schneider wrote: > On 12/5/23 3:46 PM, Jeff Davis wrote: >> CTYPE, which handles character classification and upper/lowercasing >> behavior, may be simpler than it first appears. We may be able to get >> a net decrease in complexity by just buildin

Re: Built-in CTYPE provider

On 12/20/23 4:04 PM, Jeremy Schneider wrote: > On 12/20/23 3:47 PM, Jeremy Schneider wrote: >> On 12/5/23 3:46 PM, Jeff Davis wrote: >>> CTYPE, which handles character classification and upper/lowercasing >>> behavior, may be simpler than it first appears. We may be abl

Re: Set log_lock_waits=on by default

On 12/21/23 6:58 AM, Nikolay Samokhvalov wrote: > On Thu, Dec 21, 2023 at 05:29 Laurenz Albe > wrote: > > Here is a patch to implement this. > Being stuck behind a lock for more than a second is almost > always a problem, so it is reasonable to turn th

Re: Built-in CTYPE provider

On 12/28/23 6:57 PM, Jeff Davis wrote: > > Attached a more complete version that fixes a few bugs, stabilizes the > tests, and improves the documentation. I optimized the performance, too > -- now it's beating both libc's "C.utf8" and ICU "en-US-x-icu" for both > collation and case mapping (number

Re: Built-in CTYPE provider

On 12/28/23 6:57 PM, Jeff Davis wrote: > On Wed, 2023-12-27 at 17:26 -0800, Jeff Davis wrote: > Attached a more complete version that fixes a few bugs, stabilizes the > tests, and improves the documentation. I optimized the performance, too > -- now it's beating both libc's "C.utf8" and ICU "en-US-

Re: Built-in CTYPE provider

On 1/9/24 2:31 PM, Jeff Davis wrote: > On Tue, 2024-01-09 at 14:17 -0800, Jeremy Schneider wrote: >> I think we missed something in psql, pretty sure I applied all the >> patches but I see this error: >> >> =# \l >> ERROR:  42703: column d.datlocale does not

Re: Configurable FP_LOCK_SLOTS_PER_BACKEND

see a mention in this thread or in Matt's gitlab research ticket. Maybe it doesn't actually help. Anyway Alexander Pyhalov's email about LWLock optimization and NUM_LOCK_PARTITIONS is out there, and I wondered about this. -Jeremy -- Jeremy Schneider Performance Engineer Amazon Web Services

Re: Collation version tracking for macOS

On 11/28/22 6:54 PM, Jeff Davis wrote: > > =# select * from pg_icu_collation_versions('en_US') order by > icu_version; > icu_version | uca_version | collator_version > -+-+-- > ... > 67.1| 13.0| 153.14 > 68.2| 13.0| 153.

Re: Collation version tracking for macOS

really thinking clearly about this within the PG community, and that the seriousness of this issue is not fully understood. -Jeremy Schneider -- http://about.me/jeremy_schneider

Re: Collation version tracking for macOS

> On Jun 6, 2022, at 17:10, Jim Nasby wrote: > Ignoring broken backups, segfaults and data corruption as a "rant" implies > that we simply throw in the towel and tell users to suck it up or switch > engines. Well now, let’s be clear, I was the one who called my email a “rant”. 🙂 And I do

Re: Collation version tracking for macOS

On 6/7/22 12:53 PM, Peter Geoghegan wrote: > > Collations by their very nature are unlikely to change all that much. > Obviously they can and do change, but the details are presumably > pretty insignificant to a native speaker. This idea does seem to persist. It's not as frequent as timezones,

Re: Collation version tracking for macOS

On 6/7/22 1:51 PM, Peter Geoghegan wrote: > On Tue, Jun 7, 2022 at 1:24 PM Jeremy Schneider > wrote: >> This idea does seem to persist. It's not as frequent as timezones, but >> collation rules reflect local dialects and customs, and there are >> changes quite regul

Re: Collation version tracking for macOS

New emoji are getting added with some frequency, it’s a thing lately… New Unicode chars use existing but unassigned code points. All code points are able to be encoded, claimed or unclaimed. Someone on old glibc or ICU can still store the new characters. As long as there’s an input field. You w

Re: Collation version tracking for macOS

> On Jun 8, 2022, at 03:19, Thomas Munro wrote: > > On Wed, Jun 8, 2022 at 12:23 PM Peter Geoghegan wrote: >> ISTM that there are two mostly-distinct questions here: >> >> 1. How do we link to multiple versions of ICU at the same time, in a >> way that is going to work smoothly on mainstream

Re: Collation version tracking for macOS

> On Jun 8, 2022, at 22:40, Peter Geoghegan wrote: > > On Wed, Jun 8, 2022 at 10:24 PM Jeremy Schneider > wrote: >> Even if PG supports two versions of ICU, how does someone actually go about >> removing every dependency on the old version and replacing it with t

Re: Collation version tracking for macOS

> On Jun 14, 2022, at 14:10, Peter Eisentraut > wrote: >  > Conversely, why are we looking at the ICU version instead of the collation > version. If we have recorded the collation as being version 1234, we need to > look through the available ICU versions (assuming we can load multiple ones

Re: Collation version tracking for macOS

  > On Jun 14, 2022, at 19:06, Thomas Munro wrote: > One difference would be the effect if ICU ever ships a minor library > version update that changes the reported collversion. If I’m reading it correctly, ICU would not change collation in major versions, as an explicit matter of policy arou

Re: pg_walinspect - a new extension to get raw WAL data and WAL stats

On 10/5/21 15:07, Bruce Momjian wrote: > On Wed, Sep 8, 2021 at 07:18:08PM +0530, Bharath Rupireddy wrote: >> While working on one of the internal features, we found that it is a >> bit difficult to run pg_waldump for a normal user to know WAL info and >> stats of a running postgres database insta

Re: pg_walinspect - a new extension to get raw WAL data and WAL stats

On 10/5/21 17:43, Bruce Momjian wrote: > On Tue, Oct 5, 2021 at 03:30:07PM -0700, Jeremy Schneider wrote: >> Specifically exposing pg_waldump functionality in SQL could speed up >> finding bugs in the PG logical replication code. We found and fixed a >> few over this past yea

Re: Proposal: Document ABI Compatibility

On 6/26/24 12:23 PM, David E. Wheeler wrote: > On Jun 26, 2024, at 15:20, David E. Wheeler wrote: > >> CF: https://commitfest.postgresql.org/48/5080/ >> PR: https://github.com/theory/postgres/pull/6 > > Aaaand v2 without the unnecessary formatting of unrelated documentation 🤦🏻‍♂️. Minor nit - m

Re: Built-in CTYPE provider

> > On Jul 6, 2024, at 12:51 PM, Noah Misch wrote: > Behavior after that: > > -- 2 rows w/ seq scan, 0 rows w/ index scan > SELECT 1 FROM t WHERE s ~ '[[:alpha:]]'; > SET enable_seqscan = off; > SELECT 1 FROM t WHERE s ~ '[[:alpha:]]'; > > -- ERROR: heap tuple (0,1) from table "t" lacks match

Re: Built-in CTYPE provider

On Tue, Jul 9, 2024 at 4:00 AM Laurenz Albe wrote: > > My personal exprience is that very few users are aware of or care about > the strict accuracy of the collation sort order and other locale aspects. > But they care a lot about index corruption. > > So I'd argue that we should not have any bre

collation settings table in v16 docs

Looking at the "collation settings" table in the v16 docs, I think some readers may have a little difficulty understanding what each row means. https://www.postgresql.org/docs/devel/collation.html#ICU-COLLATION-SETTINGS The "Key" column isn't meaningful and it's a bit arduous to read the whole de

Re: Let's make PostgreSQL multi-threaded

On 6/5/23 2:07 PM, Jonah H. Harris wrote: > On Mon, Jun 5, 2023 at 8:18 AM Tom Lane > wrote: > > For the record, I think this will be a disaster.  There is far too much > code that will get broken, largely silently, and much of it is not > under our control.

Re: Let's make PostgreSQL multi-threaded

On 6/7/23 2:39 PM, Thomas Kellerer wrote: > Tomas Vondra schrieb am 07.06.2023 um 21:20: >> Also, which other projects did this transition? Is there something we >> could learn from them? Were they restricted to much smaller list of >> platforms? > > Not open source, but Oracle was historically mu

Re: [18] Policy on IMMUTABLE functions and Unicode updates

On Tue, Jul 16, 2024 at 3:28 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > I'd teach pg_upgrade to inspect the post-upgraded catalog of in-use > dependencies and report on any of these it finds and remind the DBA that > this latent issue may exist in their system. > Would this hel

Re: [18] Policy on IMMUTABLE functions and Unicode updates

On Tue, Jul 23, 2024 at 1:11 AM Laurenz Albe wrote: > On Mon, 2024-07-22 at 13:55 -0400, Robert Haas wrote: > > On Mon, Jul 22, 2024 at 1:18 PM Laurenz Albe > wrote: > > > I understand the difficulty (madness) of discussing every Unicode > > > change. If that's unworkable, my preference would b

Re: [18] Policy on IMMUTABLE functions and Unicode updates

On Wed, Jul 24, 2024 at 6:20 AM Robert Haas wrote: > > I note in passing that the last time I saw a customer query with > UPPER() in the join clause was... yesterday. The problems there had > nothing to do with CTYPE, but there's no reason to suppose that it > couldn't have had such a problem. I

Re: Built-in CTYPE provider

On Wed, Jul 24, 2024 at 9:27 AM Peter Eisentraut wrote: > > The last vote arrived 6 days ago. So far, we have votes from Jeff, > Noah, Tom, > > Daniel, and Laurenz. I'll keep the voting open for another 24 hours > from now > > or 36 hours after the last vote, whichever comes last. If that sche

Re: [18] Policy on IMMUTABLE functions and Unicode updates

On Wed, Jul 24, 2024 at 12:47 PM Robert Haas wrote: On Wed, Jul 24, 2024 at 1:45 PM Jeff Davis wrote: > There's a qualitative difference between a collation update which can > break your PKs and FKs, and a ctype update which definitely will not. I don't think that's true. All you need is a uniq

Re: New function pg_stat_statements_reset_query() to reset statistics of a specific query

> On Jun 20, 2018, at 12:30 AM, Haribabu Kommi wrote: > > Attached is a simple patch with implementation. Comments? Seems useful to me too! What are the odds we could have a column telling the timestamp when a particular query was last reset? Would that be complicated to add? -Jeremy

Re: [HACKERS] pg_upgrade to clusters with a different WAL segment size

On Fri, Nov 10, 2017 at 4:04 PM, Michael Paquier wrote: > On Sat, Nov 11, 2017 at 12:46 AM, Bossart, Nathan wrote: >> Allowing changes to the WAL segment size during pg_upgrade seems like a >> nice way to avoid needing a dump and load, so I would like to propose >> adding support for this. I'd b

Re: [HACKERS] pg_upgrade to clusters with a different WAL segment size

On Mon, Nov 13, 2017 at 3:09 PM, Tom Lane wrote: >> On Tue, Nov 14, 2017 at 7:32 AM, Andres Freund wrote: >>> Even if that's the case, I fail to see why it'd be a good idea to have >>> any sort of pg_upgrade integration here. We should make pg_resetwal's >>> checks for this good enough, and not

Re: Add the ability to limit the amount of memory that can be allocated to backends.

On Sat, 28 Dec 2024 15:57:44 +0100 Tomas Vondra wrote: > > Not sure a simple memory limit like in the patch (which just adds > memory accounting + OOM after hitting the limit) can work as anything > but a the last safety measure. It seems to me the limit would have to > be set to a value that's m

Re: [PATCH] Optionally record Plan IDs to track plan changes for a query

On Thu, 2 Jan 2025 12:46:04 -0800 Lukas Fittl wrote: > this proposed patch set adds: > > 1. An updated in-core facility to optionally track Plan IDs based on > hashing the plan nodes during the existing treewalk in setrefs.c - > controlled by the new "compute_plan_id" GUC > 2. An example user of

Re: RFC: Allow EXPLAIN to Output Page Fault Information

On Fri, 27 Dec 2024 15:15:40 +0100 "Jelte Fennema-Nio" wrote: > On Tue Dec 24, 2024 at 4:52 PM CET, Tom Lane wrote: > > torikoshia writes: > >> I have attached a PoC patch that modifies EXPLAIN to include page > >> fault information during both the planning and execution phases of > >> a query

Re: RFC: Allow EXPLAIN to Output Page Fault Information

On Thu, 26 Dec 2024 13:15:59 +0900 torikoshia wrote: > On 2024-12-25 00:52, Tom Lane wrote: > > torikoshia writes: > >> I have attached a PoC patch that modifies EXPLAIN to include page > >> fault > >> information during both the planning and execution phases of a > >> query. > > > > Surel

Re: Query regarding pg_prewarm extension

On Fri, 13 Dec 2024 16:16:16 +0530 Ayush Vatsa wrote: > How can I decide which range of pages to prewarm? > I assume that it is related to hot pages in the relation, > but how can I identify which pages are likely to be hot > before they are even in the buffer cache? > Additionally, since tuples

Re: llvm dependency and space concerns

On Sat, 11 Jan 2025 19:03:43 -0500 Andres Freund wrote: > If llvmjit.so isn't available, jit is silently disabled. Thanks - this is exactly the bit I wasn't sure about. The reason I came to the hackers list is because I thought this wasn't the case. Normally I don't delete arbitrary files after

llvm dependency and space concerns

se cases like embedded and IoT - in addition to containers - where some postgres users value this level of space savings over JIT. Thanks -Jeremy Schneider

Re: llvm dependency and space concerns

On Sat, 11 Jan 2025 12:56:19 -0800 Jeremy Schneider wrote: > I'm running Postgres in containers, and recently did some analysis of > the total container sizes. I posted some analysis over on the debian > packaging mailing list [1] [2]. The TLDR is that LLVM alone makes up >

Re: llvm dependency and space concerns

On Sat, 11 Jan 2025 16:14:19 -0500 Tom Lane wrote: > Jeremy Schneider writes: > > Given the large number of bytes that LLVM pulls into a postgres > > build, I think it would be a good idea to have the ability to split > > it into a separate [recommended, but optional]

Re: Update Unicode data to Unicode 16.0.0

On Mon, 20 Jan 2025 13:39:35 -0800 Jeff Davis wrote: > On Fri, 2024-11-15 at 17:09 +0100, Peter Eisentraut wrote: > > The practice of regularly updating the Unicode files is older than > > the > > builtin collation provider.  It is similar to updating the time > > zone files, the encoding conver

Re: New feature request for adding session information to PostgreSQL transaction log

On Wed, 15 Jan 2025 08:54:06 + Sumanth Vishwaraj wrote: > Oracle Audit Vault and Database Firewall (AVDF) audits/monitors > database activities. This product helps enterprises to manage the > security posture of Oracle , PostgreSQL and other databases. > > Oracle AVDF helps customers in Indi

protocol support for labels

pgconf.dev is coming up soon. I won't be able to make it to Montreal, but I saw that Dave Cramer posted on twitter asking about postgres protocol topics. while I don't have a patch, I wanted to send an email about something: it'd be great to have a place - perhaps like application_name - for arbit

Re: protocol support for labels

> On Mar 11, 2025, at 3:03 AM, Kirill Reshke wrote: > > On Tue, 11 Mar 2025 at 11:09, Jeremy Schneider > wrote: > >> observability frameworks like OpenTelemetry support tracing through all >> layers of a stack, and trace_ids can even be passed into sql with >

Re: protocol support for labels

On Tue, 11 Mar 2025 14:03:12 -0500 Nico Williams wrote: > How about using a `set_config()` to deonte the "application_name" (and > any other details) for the _next_ query, then have those details > appear in the pg_stat_statements rows and logs? > > Clients would send a `SELECT set_config(...)` a

Re: Update Unicode data to Unicode 16.0.0

On Fri, 07 Mar 2025 13:11:18 -0800 Jeff Davis wrote: > On Wed, 2025-03-05 at 20:43 -0600, Nathan Bossart wrote: > > I see.  Do we provide any suggested next steps for users to assess > > the > > potentially-affected relations? > > I don't know exactly where we should document it, but I've atta

Re: Update Unicode data to Unicode 16.0.0

> On Mar 15, 2025, at 10:22 AM, Jeff Davis wrote: > > On Sat, 2025-03-15 at 12:15 -0400, Tom Lane wrote: >> On the other hand, if we keep up with the Joneses by updating the >> Unicode data, we can hopefully put those behavioral changes into >> effect *before* they'd affect any real data. > >

Re: Update Unicode data to Unicode 16.0.0

On Tue, 18 Mar 2025 08:53:56 -0700 Jeff Davis wrote: > What do you think of Tom's argument that waiting to update Unicode is > what creates the problem in the first place? > > "by then they might well have instances of the newly-assigned code > points in their database"[1] > > [1] > https://www

Re: Update Unicode data to Unicode 16.0.0

On Tue, 18 Mar 2025 19:33:00 -0700 Jeff Davis wrote: > If we compare the following two problems: > > A. With glibc or ICU, every text index, including primary keys, are > highly vulnerable to inconsistencies after an OS upgrade, even if > there's no Postgres upgrade; vs. > > B. With the bui

Re: Update Unicode data to Unicode 16.0.0

On Fri, 21 Mar 2025 13:45:24 -0700 Jeff Davis wrote: > > Maybe we should actually move in the direction of having encodings > > that are essentially specific versions of Unicode. Instead of just > > having UTF-8 that accepts whatever, you could have UTF-8.v16.0.0 or > > whatever, which would only