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

[Jeremy Schneider]

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, especially by developers who came from a
SQL server background, for some reason.

I certainly don't think we need to optimize for this workload - which is
not a best practice on PostreSQL. I'd just want to be careful not to
make PostgreSQL logical replication crumble underneath it, if PG was
previously keeping up with difficulty. That would be a sad upgrade
experience!

-Jeremy

-- 
http://about.me/jeremy_schneider

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

[Jeremy Schneider]

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 parameter if users really want to take the performance
>> hit.
> 
> A sequence's cache length is already configurable, no?
> 

Cache length isn't related to the problem here.

The problem is that PostgreSQL sequences are entirely unsafe to use from
a durability perspective, unless there's DML in the same transaction.

Users might normally think that "commit" makes things durable.
Unfortunately, IIUC, that's not true for sequences in PostgreSQL.

-Jeremy


PS. my bad on the documentation thing... I just noticed that I said a
year ago I'd take a swing at a doc update, and I never did that!!
Between Nate and I we'll get something proposed.


-- 
http://about.me/jeremy_schneider

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

[Jeremy Schneider]

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#4cfe7217c829419b769339465e8c2915
> 
> 
> >
> 
> I did try, and I haven't been able to reproduce that behavior (on
> master, at least).
> 
> 
> I agree with this,  the commit would flush the xlog and persist the change. 

On that older thread, there were exact reproductions in the first email
from Vini - two of them - available here:

https://gist.github.com/vinnix/2fe148e3c42e11269bac5fcc5c78a8d1

Nathan help me realize a mistake I've made here.

The second reproduction involved having psql run nextval() inside of an
explicit transaction. I had assumed that the transaction would be
committed when psql closed the session without error. This is because in
Oracle SQLPlus (my original RDBMS background), the "exitcommit" setting
has a default value giving this behavior.

This was a silly mistake on my part. When PostgreSQL psql closes the
connection with an open transaction, it turns out that the PostgreSQL
server will abort the transaction rather than committing it. (Oracle
DBAs be-aware!)

Nonetheless, Vini's first reproduction did not make this same mistake.
It involved 10 psql sessions in parallel using implicit transactions,
suspending I/O (via the linux device mapper), and killing PG while the
I/O is suspended.

Given my mistake on the second repro, I want to look a little closer at
this first reproduction and revisit whether it's actually demonstrating
a corner case where one could claim that durability isn't being handled
correctly - that "COMMIT" is returning successfully to the application,
and yet the sequence numbers are being repeated. Maybe there's something
involving the linux I/O path coming into play here.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Reports on obsolete Postgres versions

[Jeremy Schneider]

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 potentially different adjectives that could apply to "version" and 
>> "release".
>>
>> The version numbers can be called major and minor, because that just 
>> describes their ordering and significance.
>>
>> But I do agree that "minor release" isn't quite as clear, because one could 
>> also interpret that as "a release, but a bit smaller this time". (Also might 
>> not translate well, since "minor" and "small" could translate to the same 
>> thing.)
> 
> Some of the user confusion likely stems from us using the same nomenclature as
> SemVer, but for different things.  SemVer has become very widely adopted, to
> the point where it's almost assumed by many, so maybe we need to explicitly
> state that we *don't* use SemVer (we don't mention that anywhere in the docs 
> or
> on the website).

Semantic Versioning was definitely part of what led to my confusion
up-thread here. I was also mistaken in what I said up-thread about
MySQL, who also calls "5.7" the "major" version.


>> One could instead, for example, describe those as "maintenance releases":
> 
> That might indeed be a better name for what we provide.

The latest PostgreSQL news item uses the word "update" and seems pretty
well written in this area already (at least to me)

Also I just confirmed, the bug reporting form also seems well written:

"Make sure you are running the latest available minor release for your
major version before reporting a bug. The current list of supported
versions is 16.2, 15.6, 14.11, 13.14, 12.18."

This all looks good, but I do still agree that a gradual shift toward
saying "maintenance update" instead of "minor" might still promote more
clarity in the long run?

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Reports on obsolete Postgres versions

[Jeremy Schneider]

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 not sure we want to put
>> that into the official page (or maybe tone down/qualify it a bit), but I
>> think a lot of users stay on older minor versions because they dread
>> their internal testing policies.
> 
> I think we need a more conservative language since a minor release might fix a
> planner bug that someone's app relied on and their plans will be worse after
> upgrading.  While rare, it can for sure happen so the official wording should
> probably avoid such bold claims.
> 
>> The other thing that could maybe be made a bit better is the fantastic
>> patch release schedule, which however is buried in the "developer
>> roadmap". I can see how this was useful years ago, but I think this page
>> should be moved to the end-user part of the website, and maybe (also)
>> integrated into the support/versioning page?
> 
> Fair point.

Both of the above points show inconsistency in how PG uses the terms
"minor" and "patch" today.

It's not just roadmaps and release pages where we mix up these terms
either, it's even in user-facing SQL and libpq routines: both
PQserverVersion and current_setting('server_version_num') return the
patch release version in the numeric patch field, rather than the
numeric minor field (which is always 0).

In my view, the best thing would be to move toward consistently using
the word "patch" and moving away from the word "minor" for the
PostgreSQL quarterly maintenance updates.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Reports on obsolete Postgres versions

[Jeremy Schneider]

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
>>>> the word "patch" and moving away from the word "minor" for the
>>>> PostgreSQL quarterly maintenance updates.
> 
>>> I think "minor" is a better term since it contrasts with "major".  We
>>> don't actually supply patches to upgrade minor versions.
> 
>> I tend to agree with Bruce, and major/minor seems to be the more
>> common usage within the industry; iirc, debian, ubuntu, gnome, suse,
>> and mariadb all use that nomenclature; and ISTR some distro's who
>> release packaged versions of postgres with custom patches applied (ie
>> 12.4-2 for postgres 12.4 patchlevel 2).
> 
> Agreed, we would probably add confusion not reduce it if we were to
> change our longstanding nomenclature for this.


"Longstanding nomenclature"??

Before v10, the quarterly maintenance updates were unambiguously and
always called patch releases

I don't understand the line of thinking here

Bruce started this whole thread because of "an increasing number of
bug/problem reports on obsolete Postgres versions"

Across the industry the word "minor" often implies a release that will
be maintained, and I'm trying to point out that the change in v10 to
change terminology from "patch" to "minor" actually might be part of
what's responsible for the increasing number of bug reports on old patch
releases, because people don't understand that patch releases are the
way those bugfixes were already delivered.

Just taking MySQL as an example, it's clear that a "minor" like 5.7 is a
full blown release that gets separate patches from 5.6 - so I don't
understand how we're making an argument it's the opposite?

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Reports on obsolete Postgres versions

[Jeremy Schneider]

> 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.
> 
>> Before v10, the quarterly maintenance updates were unambiguously and
>> always called patch releases
> 
> I think that's highly revisionist history.  I've always called them
> minor releases, and I don't recall other people using different
> terminology.  I believe the leadoff text on
> 
> https://www.postgresql.org/support/versioning/
> 
> is much older than when we switched from two-part major version
> numbers to one-part major version numbers.

Huh, that wasn’t what I expected. I only started (in depth) working with PG 
around 9.6 and I definitely thought of “6” as the minor version. This is an 
interesting mailing list thread.

-Jeremy


Sent from my TI-83

proposal: change behavior on collation version mismatch

[Jeremy Schneider]

and also I
realize that I'm not often involved in discussions here on the hackers
email list. (I usually catch up on hackers from archives irregularly,
for areas I'm interested in, and I'm involved more regularly with public
slack and user groups.) But I'm a bit unsatisfied with the current state
of things and want to bring up the topic here and see what happens.

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

[Jeremy Schneider]

On 11/27/23 12:29 PM, Jeff Davis wrote:
>> 2) "most users would rather have ease-of-use than 100% safety, since
>> it's uncommon"
>>
>> And I think this led to the current behavior of issuing a warning
>> rather
>> than an error
> The elevel trade-off is *availability* vs safety, not ease-of-use vs
> safety. It's harder to reason about what most users might want in that
> situation.

I'm not in agreement with the idea that this is hard to reason about;
I've always thought durability & correctness is generally supposed to be
prioritized over availability in databases. For many enterprise
customers, if they ask why their database wouldn't accept connections
after an OS upgrade and we explained that durability & correctness is
prioritized over availability, I think they would agree we're doing the
right thing.

In practice this always happens after a major operating system update of
some kind (it would be an unintentional bug in a minor OS upgrade).  In
most cases, I hope the error will happen immediately because users
ideally won't even be able to connect (for DB-level glibc and for ICU
default setting).  Giving a hard error quickly after an OS upgrade is
actually pretty easy for most people to deal with. For most users,
they'll immediately understand that something went wrong related to the
OS upgrade.  And basic testing would turn up connection errors before
the production upgrade as long as a connection was attempted as part of
the test.

It seems to me that much of the hand-wringing is around taking a hard
line on not allowing in-place OS upgrades. We're all aware that when
you're talking about tens of terrabytes, in-place upgrade is just a lot
more convenient and easy than the alternatives. And we're aware that
some other relational databases support this (and also bundle collation
libs directly in the DB rather than using external libraries).

I myself wouldn't frame this as an availability issue, I think it's more
about ease-of-use in the sense of allowing low-downtime major OS
upgrades without the complexity of logical replication (but perhaps with
a risk of data loss, because with unicode nobody can actually be 100%
sure there's no risky characters stored in the DB, and even those of us
with extensive expert knowledge struggle to accurately characterize the
risk level).

The hand-wringing often comes down to the argument "but MAYBE en_US
didn't change in those 3 major version releases of ICU that you jumped
across to land a new Ubuntu LTS release" ~~ however I believe it's one
thing to 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 thing to do is stay safe and don't change ICU
versions (or glibc versions) for existing databases in-place.

-Jeremy


-- 
Jeremy Schneider
Performance Engineer
Amazon Web Services

Re: proposal: change behavior on collation version mismatch

[Jeremy Schneider]

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"
>>
>> And I think this led to the current behavior of issuing a warning rather
>> than an error,
> There's a technical reason for this being a warning.
> If it was an error, any attempt to do anything with the collation
> would fail, which includes REINDEX on indexes  using
> that collation.
> And yet that's precisely what you're supposed to do in that
> situation.


Indexes are the most obvious and impactful corruption, so the focus is
understandable, but there's a bit of a myth in the general public that
REINDEX means you fixed your database.  I'm concerned that too many
people believe this falsehood, and don't realize that things like
constraints and partitions can also be affected by a major OS update
when leaving PG data files in place.  Also there's a tendancy to use
amcheck and validate btree indexes, but skip other index types.  And of
course none of this is possible when people mistakenly use a different
major OS for the hot standby (but Postgres willingly sends incorrect
query results to users).

This is why my original proposal included an update to the ALTER ...
REFRESH/COLLATION docs.  Today's conventional wisdom suggests this is a
safe command.  It's really not, if you're using unicode (which everyone
is). Fifteen years ago, you needed to buy a french keyboard to type
french accented characters.  Today it's a quick tap on your phone to get
chinese, russian, tibetan, emojis, and any other character you can dream
of.  All of those surprising characters eventually get stored in Postres
databases, often to the surprise of devs and admins, after they discover
corruption from an OS upgrade.

And to recap some data about historical ICU versions from the torture test:

ICU Version | OS Version | en-US characters changed collation |
zh-Hans-CN characters changed collation | fr-FR characters changed collation
55.1-7ubuntu0.5 | Ubuntu 16.04.7 LTS | 286,654 | 286,654 | 286,654
60.2-3ubuntu3.1 | Ubuntu 18.04.6 LTS | 23,741 | 24,415 | 23,741
63.1-6 | Ubuntu 19.04 | 688 | 688 | 688
66.1-2ubuntu2 | Ubuntu 20.04.3 LTS | 6,497 | 6,531 | 6,497
70.1-2 | Ubuntu 22.04 LTS | 879 | 887 | 879

The very clear trend here is that most changes are made in the root
collation rules, affecting all locales.  This means that worrying about
specific collation versions of different locales is really focusing on
an irrelevant edge case.  In ICU development, all the locales tend to
change.

If anyone thinks the Collation Apocalypse is bad now, I predict the
Kubernetes wave will be mayhem.  Fifteen years ago it was rare to
physically move PG datafiles to a new major OS.  Most people would dump
and load their databases, sized in GBs.  Today's multi-TB Postgres
databases have meant an increase of in-place OS upgrades in recent
years.  People started to either detach/attach their storage, or they
used a hot standby. Kubernetes will make these moves across major OS's a
daily, effortless occurrence.

ICU doesn't fix anything directly.  We do need ICU - only because it
finally enables us to compile that old version of ICU forever on every
new OS we move to going forward. This was simply impossible with glibc.
Over the past couple decades, not even Oracle or IBM has managed to
deprecate a single version of ICU from a relational database, and not
for lack of desire.

-Jeremy

-- 
Jeremy Schneider
Performance Engineer
Amazon Web Services

Re: Built-in CTYPE provider

[Jeremy Schneider]

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], specifying both a "Standard" variant and a
> "POSIX Compatible" variant. The main difference with the POSIX variant
> is that symbols count as punctuation.
> 
> === LOWER()/INITCAP()/UPPER() ===
> 
> The LOWER() and UPPER() functions are defined in the SQL spec with
> surprising detail, relying on specific Unicode General Category
> assignments. How to map characters seems to be left (implicitly) up to
> Unicode. If the input string is normalized, the output string must be
> normalized, too. Weirdly, there's no room in the SQL spec to localize
> LOWER()/UPPER() at all to handle issues like [1]. Also, the standard
> specifies one example, which is that "ß" becomes "SS" when folded to
> upper case. INITCAP() is not in the SQL spec.
> 
> === Questions ===
> 
> * Is a built-in ctype provider a reasonable direction for Postgres as
>   a project?
> * Does it feel like it would be simpler or more complex than what
>   we're doing now?
> * Do we want to just try to improve our ICU support instead?
> * Do we want the built-in provider to be one thing, or have a few
>   options (e.g. "standard" or "posix" character classification;
>   "simple" or "full" case mapping)?


Generally, I am in favor of this - I think we need to move in the
direction of having an in-database option around unicode for PG users,
given how easy it is for administrators to mis-manage dependencies.
Especially when OS admins can be different from DB admins, and when
nobody really understands risks of changing libs with in-place moves to
new operating systems - except for like 4 of us on the mailing lists.

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 already be stored in old databases before libs
are updated. When users start to notice that regex [[:digit:]] or
upper/lower functions aren't working correctly with characters in their
DB, they'll probably come asking for fixes. And we may end up with
something like the timezone database where we need to periodically add a
more current ruleset - albeit alongside as a new version in this case.

Here are direct links to charts of newly assigned characters from the
last few Unicode updates:

2022: https://www.unicode.org/charts/PDF/Unicode-15.0/
2021: https://www.unicode.org/charts/PDF/Unicode-14.0/
2020: https://www.unicode.org/charts/PDF/Unicode-13.0/
2019: https://www.unicode.org/charts/PDF/Unicode-12.0/

If I'm reading the Unicode 15 update correctly, PostgreSQL regex
expressions with [[:digit:]] will not correctly identify Kaktovik or Nag
Mundari or Kawi digits without that update to character type specs.

If I'm reading the Unicode 12 update correctly, then upper/lower
functions aren't going to work correctly on Latin Glottal A and I and U
characters without that update to character type specs.

Overall I see a lot fewer Unicode updates involving upper/lower than I
do with digits - especially since new scripts often involve their own
numbering characters which makes new digits more common.

But lets remember that people like to build indexes on character
classification functions like upper/lower, for case insensitive
searching. It's another case where the index will be corrupted if
someone happened to store Latin Glottal vowels in their database and
then we update libs to the latest character type rules.

So even with something as basic as character type, if we're going to do
it right, we still need to either version it or definitively decide that
we're not going to every support newly added Unicode characters like
Latin Glottals.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: encoding affects ICU regex character classification

[Jeremy Schneider]

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/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.ca...@j-davis.com
> 
>>   It seems a bit strange to use different systems for
>> classification and mapping.  If you do implement mapping too, you
>> have
>> to decide if you believe it is language-dependent or not, I think?
> 
> A complete solution would need to do the language-dependent case
> mapping. But that seems to only be 3 locales ("az", "lt", and "tr"),
> and only a handful of mapping changes, so we can handle that with the
> builtin provider as well.

This thread has me second-guessing the reply I just sent on the other
thread.

Is someone able to test out upper & lower functions on U+A7BA ... U+A7BF
across a few libs/versions?  Theoretically the upper/lower behavior
should change in ICU between Ubuntu 18.04 LTS and Ubuntu 20.04 LTS
(specifically in ICU 64 / Unicode 12).  And I have no idea if or when
glibc might have picked up the new unicode characters.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 already be stored in old databases before
>> libs
>> are updated.
> 
> Is the concern only about unassigned code points?
> 
> I already committed a function "unicode_assigned()" to test whether a
> string contains only assigned code points, which can be used in a
> CHECK() constraint. I also posted[5] an idea about a per-database
> option that could reject the storage of any unassigned code point,
> which would make it easier for users highly concerned about
> compatibility.

I didn't know about this.  Did a few smoke tests against today's head on
git and it's nice to see the function working as expected.  :)


test=# select unicode_version();
 unicode_version
-
 15.1

test=# select chr(3212),unicode_assigned(chr(3212));
 chr | unicode_assigned
-+--
 ಌ   | t

-- unassigned code point inside assigned block
test=# select chr(3213),unicode_assigned(chr(3213));
 chr | unicode_assigned
-+--
 ಍   | f

test=# select chr(3214),unicode_assigned(chr(3214));
 chr | unicode_assigned
-+--
 ಎ   | t

-- unassigned block
test=# select chr(67024),unicode_assigned(chr(67024));
 chr | unicode_assigned
-+--
 𐗐   | f

test=# select chr(67072),unicode_assigned(chr(67072));
 chr | unicode_assigned
-+--
 𐘀   | t

Looking closer, patches 3 and 4 look like an incremental extension of
this earlier idea; the perl scripts download data from unicode.org and
we've specifically defined Unicode version 15.1 and the scripts turn the
data tables inside-out into C data structures optimized for lookup. That
C code is then checked in to the PostgreSQL source code files
unicode_category.h and unicode_case_table.h - right?

Am I reading correctly that these two patches add C functions
pg_u_prop_* and pg_u_is* (patch 3) and unicode_*case (patch 4) but we
don't yet reference these functions anywhere? So this is just getting
some plumbing in place?



>> And we may end up with
>> something like the timezone database where we need to periodically
>> add a
>> more current ruleset - albeit alongside as a new version in this
>> case.
> 
> There's a build target "update-unicode" which is run to pull in new
> Unicode data files and parse them into static C arrays (we already do
> this for the Unicode normalization tables). So I agree that the tables
> should be updated but I don't understand why that's a problem.

I don't want to get stuck on this. I agree with the general approach of
beginning to add a provider for locale functions inside the database. We
have awhile before Unicode 16 comes out. Plenty of time for bikeshedding

My prediction is that updating this built-in provider eventually won't
be any different from ICU or glibc. It depends a bit on how we
specifically built on this plumbing - but when Unicode 16 comes out, i
I'll try to come up with a simple repro on a default DB config where
changing the Unicode version causes corruption (it was pretty easy to
demonstrate for ICU collation, if you knew where to look)... but I don't
think that discussion should derail this commit, because for now we're
just starting the process of getting Unicode 15.1 into the PostgreSQL
code base. We can cross the "update" bridge when we come to it.

Later on down the road, from a user perspective, I think we should be
careful about confusion where providers are used inconsistently. It's
not great if one function follow built-in Unicode 15.1 rules but another
function uses Unicode 13 rules because it happened to call an ICU
function or a glibc function. We could easily end up with multiple
providers processing different parts of a single SQL statement, which
could lead to strange results in some cases.

Ideally a user just specifies a default provider their database, and the
rules for that version of Unicode are used as consistently as possible -
unless a user explicitly overrides their choice in a table/column
definition, query, etc. But it might take a little time and work to get
to this point.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: encoding affects ICU regex character classification

[Jeremy Schneider]

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 proceed in using unassigned code
> points and accept the risk of future changes, or wait for the provider
> to be upgraded.

This does not seem to me like a good way to view the situation.

Earlier this summer, a day or two after writing a document, I was
completely surprised to open it on my work computer and see "unknown
character" boxes. When I had previously written the document on my home
computer and when I had viewed it from my cell phone, everything was
fine. Apple does a very good job of always keeping iPhones and MacOS
versions up-to-date with the latest versions of Unicode and latest
characters. iPhone keyboards make it very easy to access any character.
Emojis are the canonical example here. My work computer was one major
version of MacOS behind my home computer.

And I'm probably one of a few people on this hackers email list who even
understands what the words "unassigned code point" mean. Generally DBAs,
sysadmins, architects and developers who are all part of the tangled web
of building and maintaining systems which use PostgreSQL on their
backend are never going to think about unicode characters proactively.

This goes back to my other thread (which sadly got very little
discussion): PosgreSQL really needs to be safe by /default/ ... having
GUCs is fine though; we can put explanation in the docs about what users
should consider if they change a setting.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 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], specifying both a "Standard" variant and a
> "POSIX Compatible" variant. The main difference with the POSIX variant
> is that symbols count as punctuation.
> 
> === LOWER()/INITCAP()/UPPER() ===
> 
> The LOWER() and UPPER() functions are defined in the SQL spec with
> surprising detail, relying on specific Unicode General Category
> assignments. How to map characters seems to be left (implicitly) up to
> Unicode. If the input string is normalized, the output string must be
> normalized, too. Weirdly, there's no room in the SQL spec to localize
> LOWER()/UPPER() at all to handle issues like [1]. Also, the standard
> specifies one example, which is that "ß" becomes "SS" when folded to
> upper case. INITCAP() is not in the SQL spec.

I'll be honest, even though this is primarily about CTYPE and not
collation, I still need to keep re-reading the initial email slowly to
let it sink in and better understand it... at least for me, it's complex
to reason through. 🙂

I'm trying to make sure I understand clearly what the user impact/change
is that we're talking about: after a little bit of brainstorming and
looking through the PG docs, I'm actually not seeing much more than
these two things you've mentioned here: the set of regexp_* functions PG
provides, and these three generic functions. That alone doesn't seem
highly concerning.

I haven't checked the source code for the regexp_* functions yet, but
are these just passing through to an external library? Are we actually
able to easily change the CTYPE provider for them? If nobody
knows/replies then I'll find some time to look.

One other thing that comes to mind: how does the parser do case folding
for relation names? Is that using OS-provided libc as of today? Or did
we code it to use ICU if that's the DB default? I'm guessing libc, and
global catalogs probably need to be handled in a consistent manner, even
across different encodings.

(Kindof related... did you ever see the demo where I create a user named
'🏃' and then I try to connect to a database with non-unicode encoding?
💥😜  ...at least it seems to be able to walk the index without decoding
strings to find other users - but the way these global catalogs work
scares me a little bit)

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 building in most (or perhaps all)
>> of the functionality.
> 
> I'll be honest, even though this is primarily about CTYPE and not
> collation, I still need to keep re-reading the initial email slowly to
> let it sink in and better understand it... at least for me, it's complex
> to reason through. 🙂
> 
> I'm trying to make sure I understand clearly what the user impact/change
> is that we're talking about: after a little bit of brainstorming and
> looking through the PG docs, I'm actually not seeing much more than
> these two things you've mentioned here: the set of regexp_* functions PG
> provides, and these three generic functions. That alone doesn't seem
> highly concerning.

I missed citext, which extends impact to replace(), split_part(),
strpos() and translate().  There are also the five *_REGEX() functions
from the SQL standard which I assume are just calling the PG functions.

I just saw the krb_caseins_users GUC, which reminds me that PLs also
have their own case functions. And of course extensions. I'm not saying
any of this is in scope for the change here, but I'm just trying to wrap
my brain around all the places we've got CTYPE processing happening, to
better understand the big picture. It might help tease out unexpected
small glitches from changing one thing but not another one.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 able to get
>>> a net decrease in complexity by just building in most (or perhaps all)
>>> of the functionality.
>>
>> I'll be honest, even though this is primarily about CTYPE and not
>> collation, I still need to keep re-reading the initial email slowly to
>> let it sink in and better understand it... at least for me, it's complex
>> to reason through. 🙂
>>
>> I'm trying to make sure I understand clearly what the user impact/change
>> is that we're talking about: after a little bit of brainstorming and
>> looking through the PG docs, I'm actually not seeing much more than
>> these two things you've mentioned here: the set of regexp_* functions PG
>> provides, and these three generic functions. That alone doesn't seem
>> highly concerning.
> 
> I missed citext, which extends impact to replace(), split_part(),
> strpos() and translate().  There are also the five *_REGEX() functions
> from the SQL standard which I assume are just calling the PG functions.

found some more. here's my running list of everything user-facing I see
in core PG code so far that might involve case:

* upper/lower/initcap
* regexp_*() and *_REGEXP()
* ILIKE, operators ~* !~* ~~ !~~ ~~* !~~*
* citext + replace(), split_part(), strpos() and translate()
* full text search - everything is case folded
* unaccent? not clear to me whether CTYPE includes accent folding
* ltree
* pg_trgm
* core PG parser, case folding of relation names


-- 
http://about.me/jeremy_schneider

Re: Set log_lock_waits=on by default

[Jeremy Schneider]

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 this on by default.
> 
> 
> I think it's a very good idea. On all heavily loaded systems I have
> observed so far, we always have turned it on. 1s (default
> deadlock_timeout) is quite large value for web/mobile apps, meaning that
> default frequency of logging is quite low, so any potential suffering
> from observer effect doesn't happen -- saturation related active session
> number happens much, much earlier, even if you have very slow disk IO
> for logging.

FWIW, enabling this setting has also been a long-time "happiness hint"
that I've passed along to people.

What would be the worst case amount of logging that we're going to
generate at scale? I think the worst case would largely scale according
to connection count? So if someone had a couple thousand backends on a
busy top-end system, then I guess they might generate up to a couple
thousand log messages every second or two under load after this
parameter became enabled with a 1 second threshold?

I'm not aware of any cases where enabling this parameter with a 1 second
threshold overwhelmed the logging collector (unlike, for example,
log_statement=all) but I wanted to pose the question in the interest of
being careful.


> At the same time, I like the idea by Robert to separate logging of log
> waits and deadlock_timeout logic -- the current implementation is a
> quite confusing for new users. I also had cases when people wanted to
> log lock waits earlier than deadlock detection. And also, most always
> lock wait logging lacks the information another the blocking session
> (its state, and last query, first of all), but is maybe an off topic
> worthing another effort of improvements.

I agree with this, though it's equally true that proliferation of new
GUCs is confusing for new users. I hope the project avoids too low of a
bar for adding new GUCs. But using the deadlock_timeout GUC for this
completely unrelated log threshold really doesn't make sense.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 (numbers below).
> 
> It's really nice to finally be able to have platform-independent tests
> that work on any UTF-8 database.

Thanks for all your work on this, Jeff

I didn't know about the Unicode stability policy. Since it's formal
policy, I agree this provides some assumptions we can safely build on.

I'm working my way through these patches but it's taking a little time
for me. I hadn't tracked with the "builtin" thread last summer so I'm
coming up to speed on that now too. I'm hopeful that something along
these lines gets into pg17. The pg17 cycle is going to start heating up
pretty soon.

I agree with merging the threads, even though it makes for a larger
patch set. It would be great to get a unified "builtin" provider in
place for the next major.

I also still want to parse my way through your email reply about the two
groups of callers, and what this means for user experience.

https://www.postgresql.org/message-id/7774b3a64f51b3375060c29871cf2b02b3e85dab.camel%40j-davis.com

> Let's separate it into groups.
> (1) Callers that use a collation OID or pg_locale_t:
> (2) A long tail of callers that depend on what LC_CTYPE/LC_COLLATE are
> set to, or use ad-hoc ASCII-only semantics:

In the first list it seems that some callers might be influenced by a
COLLATE clause or table definition while others always take the database
default? It still seems a bit odd to me if different providers can be
used for different parts of a single SQL. But it might not be so bad - I
haven't fully thought through it yet and I'm still kicking the tires on
my test build over here.

Is there any reason we couldn't commit the minor cleanup (patch 0001)
now? It's less than 200 lines and pretty straightforward.

I wonder if, after a year of running the builtin provider in production,
whether we might consider adding to the builtin provider a few locales
with simple but more reasonable ordering for european and asian
languages? Maybe just grabbing a current version of DUCET with no
intention of ever updating it? I don't know how bad sorting is with
plain DUCET for things like french or spanish or german, but surely it's
not as unusable as code point order? Anyone who needs truly accurate or
updated or customized linguistic sorting can always go to ICU, and take
responsibility for their ICU upgrades, but something basic and static
might meet the needs of 99% of postgres users indefinitely.

By the way - my coworker Josh (who I don't think posts much on the
hackers list here, but shares an unhealthy inability to look away from
database unicode disasters) passed along this link today which I think
is a fantastic list of surprises about programming and strings
(generally unicode).

https://jeremyhussell.blogspot.com/2017/11/falsehoods-programmers-believe-about.html#main

Make sure to click the link to show the counterexamples and discussion,
that's the best part.

-Jeremy


PS. I was joking around today that the the second best part is that it's
proof that people named Jeremy are always brilliant within their field.
😂 Josh said its just a subset of "always trust people whose names start
with J" which seems fair. Unfortunately I can't yet think of a way to
shoehorn the rest of the amazing PG hackers on this thread into the joke.

-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

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-x-icu" for both
> collation and case mapping (numbers below).
> 
> It's really nice to finally be able to have platform-independent tests
> that work on any UTF-8 database.

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 exist
LINE 8:   d.datlocale as "Locale",
  ^
HINT:  Perhaps you meant to reference the column "d.daticulocale".
LOCATION:  errorMissingColumn, parse_relation.c:3720

=

This is interesting. Jeff your original email didn't explicitly show any
other initcap() results, but on Ubuntu 22.04 (glibc 2.35) I see
different results:

=# SELECT initcap('axxE áxxÉ DŽxxDŽ Džxxx džxxx');
 initcap
--
 Axxe Áxxé DŽxxdž DŽxxx DŽxxx

=# SELECT initcap('axxE áxxÉ DŽxxDŽ Džxxx džxxx' COLLATE C_UTF8);
 initcap
--
 Axxe Áxxé Džxxdž Džxxx Džxxx

The COLLATE sql syntax feels awkward to me. In this example, we're just
using it to attach locale info to the string, and there's not actually
any collation involved here. Not sure if COLLATE comes from the
standard, and even if it does I'm not sure whether the standard had
upper/lowercase in mind.

That said, I think the thing that mainly matters will be the CREATE
DATABASE syntax and the database default.

I want to try a few things with table-level defaults that differ from
database-level defaults, especially table-level ICU defaults because I
think a number of PostgreSQL users set that up in the years before we
supported DB-level ICU. Some people will probably keep using their
old/existing schema-creation scripts even after they begin provisioning
new systems with new database-level defaults.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 exist
>> LINE 8:   d.datlocale as "Locale",
>>
> 
> Thank you. I'll fix this in the next patch set.

Very minor nitpick/request. Not directly with this patch set but with
the category_test which is related and also recently committed.

I'm testing out "make update-unicode" scripts, and due to my system ICU
version being different from the PostgreSQL unicode version I get the
expected warnings from category_test:

Postgres Unicode Version:   15.1
ICU Unicode Version:14.0
Skipped 5116 codepoints unassigned in ICU due to Unicode version mismatch.
category_test: All tests successful!

I know it's minor, but I saw the 5116 skipped codepoints and saw "all
tests succeeded" but I thought the output might be a little nicer if we
showed the count of tests that succeeded. For example:

Postgres Unicode Version:   15.1
ICU Unicode Version:14.0
Skipped 5116 codepoints unassigned in ICU due to Unicode version mismatch.
category_test: All 1108996 tests successful!

It's a minor tweak to a script that I don't think even runs in the
standard build; any objections?  Patch attached.

-Jeremy


-- 
http://about.me/jeremy_schneider
From 5ff09eb7371abc14cd8537b4e3265e35f030794f Mon Sep 17 00:00:00 2001
From: Jeremy Schneider 
Date: Wed, 10 Jan 2024 07:25:17 +
Subject: [PATCH] Output count of checked codepoints.

---
 src/common/unicode/category_test.c | 8 +++-
 1 file changed, 7 insertions(+), 1 deletion(-)

diff --git a/src/common/unicode/category_test.c 
b/src/common/unicode/category_test.c
index 6cd7cd1a5f..62ff345066 100644
--- a/src/common/unicode/category_test.c
+++ b/src/common/unicode/category_test.c
@@ -53,6 +53,7 @@ main(int argc, char **argv)
int icu_unicode_version = 
parse_unicode_version(U_UNICODE_VERSION);
int pg_skipped_codepoints = 0;
int icu_skipped_codepoints = 0;
+   int checked_codepoints = 0;
 
printf("Postgres Unicode Version:\t%s\n", PG_UNICODE_VERSION);
printf("ICU Unicode Version:\t\t%s\n", U_UNICODE_VERSION);
@@ -90,6 +91,10 @@ main(int argc, char **argv)
exit(1);
}
}
+   else
+   {
+   checked_codepoints++;
+   }
}
 
if (pg_skipped_codepoints > 0)
@@ -99,7 +104,8 @@ main(int argc, char **argv)
printf("Skipped %d codepoints unassigned in ICU due to Unicode 
version mismatch.\n",
   icu_skipped_codepoints);
 
-   printf("category_test: All tests successful!\n");
+   printf("category_test: All %d tests successful!\n",
+  checked_codepoints);
exit(0);
 #else
printf("ICU support required for test; skipping.\n");
-- 
2.34.1

Re: Configurable FP_LOCK_SLOTS_PER_BACKEND

[Jeremy Schneider]

On 8/8/23 3:04 PM, Andres Freund wrote:
> On 2023-08-08 16:44:37 -0400, Robert Haas wrote:
>> On Mon, Aug 7, 2023 at 6:05 PM Andres Freund  wrote:
>>> I think the biggest flaw of the locking scheme is that the LockHash locks
>>> protect two, somewhat independent, things:
>>> 1) the set of currently lockable objects, i.e. the entries in the hash 
>>> table [partition]
>>> 2) the state of all the locks [in a partition]
>>>
>>> It'd not be that hard to avoid the shared hashtable lookup in a number of
>>> cases, e.g. by keeping LOCALLOCK entries around for longer, as I suggest
>>> above.  But we can't, in general, avoid the lock on the partition anyway, as
>>> the each lock's state is also protected by the partition lock.
>>
>> Yes, and that's a huge problem. The main selling point of the whole
>> fast-path mechanism is to ease the pressure on the lock manager
>> partition locks, and if we did something like what you described in
>> the previous email without changing the locking regimen, we'd bring
>> all of that contention back. I'm pretty sure that would suck.
> 
> Yea - I tried to outline how I think we could implement the fastpath locking
> scheme in a less limited way in the earlier email, that I had quoted above
> this bit.  Here I was pontificating on what we possibly should do in addition
> to that. I think even if we had "unlimited" fastpath locking, there's still
> enough pressure on the lock manager locks that it's worth improving the
> overall locking scheme.


Has anyone considered whether increasing NUM_LOCK_PARTITIONS to
something bigger than 16 might offer cheap/easy/small short-term
improvements while folks continue to think about the bigger long-term ideas?

cf.
https://www.postgresql.org/message-id/flat/VI1PR05MB62031A41186ACC3FC91ACFC70%40VI1PR05MB6206.eurprd05.prod.outlook.com

I haven't looked deeply into it myself yet. Didn't 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

[Jeremy Schneider]

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.14
>  69.1| 13.0| 153.14
>  70.1| 14.0| 153.112
> (21 rows)
> 
> This is good information, because it tells us that major library
> versions change more often than collation versions, empirically-
> speaking.


It seems to me that the collator_version field is not a good version
identifier to use.

Just taking a quick glance at the ICU home page right now, it shows that
all of the last 5 versions of ICU have included "additions and
corrections" to locale data itself, including 68 to 69 where the
collator version did not change.

Is it possible that this "collator_version" only reflects the code that
processes collation data to do comparisons/sorts, but it does not
reflect updates to the locale data itself?

https://icu.unicode.org/

ICU v72 -> CLDR v42
ICU v71 -> CLDR v41
ICU v70 -> CLDR v40
ICU v69 -> CLDR v39
ICU v68 -> CLDR v38

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Collation version tracking for macOS

[Jeremy Schneider]

On 6/3/22 9:21 AM, Tom Lane wrote:
> 
> According to that document, they changed it in macOS 11, which came out
> a year and a half ago.  Given the lack of complaints, it doesn't seem
> like this is urgent enough to mandate a post-beta change that would
> have lots of downside (namely, false-positive warnings for every other
> macOS update).


Sorry, I'm going to rant for a minute... it is my very strong opinion
that using language like "false positive" here is misguided and dangerous.

If new version of sort order is released, for example when they recently
updated backwards-secondary sorting in french [CLDR-2905] or matching of
v and w in swedish and finnish [CLDR-7088], it is very dangerous to use
language like “false positive” to describe a database where there just
didn't happen to be any rows with accented french characters at the
point in time where PostgreSQL magically changed which version of sort
order it was using from the 2010 french version to the 2020 french version.

No other piece of software that calls itself a database would do what
PostgreSQL is doing: just give users a "warning" after suddenly changing
the sort order algorithm (most users won't even read warnings in their
logs). Oracle, DB2, SQL Server and even MySQL carefully version
collation data, hardcode a pseudo-linguistic collation into the DB (like
PG does for timezones), and if they provide updates to linguistic sort
order (from Unicode CLDR) then they allow the user to explicitly specify
which version of french or german ICU sorting they are want to use.
Different versions are treated as different sort orders; they are not
conflated.

I have personally seen PostgreSQL databases where an update to an old
version of glibc was applied (I'm not even talking 2.28 here) and it
resulted in data loss b/c crash recovery couldn't replay WAL records and
the user had to do a PITR. That's aside from the more common issues of
segfaults or duplicate records that violate unique constraints or wrong
query results like missing data. And it's not just updates - people can
set up a hot standby on a different version and see many of these
problems too.

Collation versioning absolutely must be first class and directly
controlled by users, and it's very dangerous to allow users - at all -
to take an index and then use a different version than what the index
was built with.

Not to mention all the other places in the DB where collation is used...
partitioning, constraints, and any other place where persisted data can
make an assumption about any sort of string comparison.

It feels to me like we're still not 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

[Jeremy Schneider]

> 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 apologize for that - it was grumpy and impulsive and Tom isn’t wrong 
that rants don’t usually help move things forward.

Thomas - thanks for the link back to one of the threads. I spent some time 
reading through that and it’s a lot of material; I haven’t read the whole 
thread yet. If you have some others that would also be particularly good 
background, let me know. I’m doing a chunk of this in my spare time at the 
moment, but I do want to keep getting more up to speed. I was pulled into a 
bunch of various things related to PostgreSQL and ICU and collation and OS’s 
over the past couple years, so I learned a lot from on-the-ground experience 
and I am interested in trying to get a little more involved in the conversation 
here.

Personally, I really do think there should at least be an *option* to tell the 
DB to fully error rather than just warn on version mismatch. Correctness 
matters to many users, and being able to *trust* string comparisons are correct 
is pretty damn fundamental all throughout a database. It really doesn’t get any 
more basic and the potential for bad things to happen is pretty astronomical, 
if you can’t trust those. I understand the consternation about dealing with 
upgrades of large & busy databases, but I’m still surprised that the community 
consensus arrived at the present behavior, and I have a lot of reading to do, 
to really understand how that happened and where the dialogue is today.

Multiple versions of ICU sounds nice for users who need real linguistic 
collation (like what Oracle and DB2 offer), but I still feel like there needs 
to be a super simple basic “pseudo-linguistic” collation baked in, that’s “good 
enough” for 99% of users and that is guaranteed to be the same everywhere on 
every platform and just won’t ever change. I think glibc needs to be phased out 
somehow. At a minimum, not the default for new users… to stop the bleeding. If 
MySQL wasn’t GPL then I’d say to just copy their collations. I’d be reluctant 
to spend too much time on a POC now though, it feels like my idea is the 
outlier and the general PG hacker consensus would be to reject this idea. (But 
maybe I’m wrong?)

Anyway, again, apologies for my pants-on-fire email last week. I hope I can 
enjoy a few beers someday - or coffee for the non-drinkers - with a few other 
PG collation nerds (which I never set out to be, but it may have befallen me 
).

-Jeremy


Sent from my TI-83

Re: Collation version tracking for macOS

[Jeremy Schneider]

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, but
collation rules reflect local dialects and customs, and there are
changes quite regularly for a variety of reasons. A brief perusal of
CLDR changelogs and CLDR jiras can give some insight here:

https://github.com/unicode-org/cldr

https://unicode-org.atlassian.net/jira/software/c/projects/CLDR/issues/?jql=project%20%3D%20%22CLDR%22%20AND%20text%20~%20%22collation%22%20ORDER%20BY%20created%20DESC

The difference between the unicode consortium and the GNU C Library is
that unicode is maintained by people who are specifically interested in
working with language and internationalization challenges. I've spoken
to a glibc maintainer who directly told me that they dislike working
with the collation code, and try to avoid it. It's not even ISO 14651
anymore with so many custom glibc-specific changes layered on top. I
looked at the first few commits in the glibc source that were
responsible for the big 2.28 changes - there were a serious of quite a
few commits and some were so large they wouldn't even load in the github
API.

Here's one such commit:

https://github.com/bminor/glibc/commit/9479b6d5e08eacce06c6ab60abc9b2f4eb8b71e4

It's reasonable to expect that Red Hat and Debian will keep things
stable on one particular major, and to expect that every new major OS
version will update to the latest collation algorithms and locale data
for glibc.

Another misunderstanding that seems to persist is that this only relates
to exotic locales or that it's only the 2.28 version.

My github repo is out-of-date (I know of more cases that I still need to
publish) but the old data already demonstrates changes to the root/DUCET
collation rules (evident in en_US without any tailoring) for glibc
versions 2.13, 2.21 and 2.26

https://github.com/ardentperf/glibc-unicode-sorting/

If a PosgreSQL user is unlucky enough to have one of those unicode
characters stored in a table, they can get broken indexes even if they
only use the default US english locale, and without touching glibc 2.28
- and all you need is an index on a field where end users can type any
string input.


> It's pretty clear that glibc as a project doesn't take the issue very
> seriously, because they see it as a problem of the GUI sorting a table
> in a way that seems slightly suboptimal to scholars of a natural
> language. 

I disagree that glibc maintainers are doing anything wrong.

While the quality of glibc collations aren't great when compared with
CLDR, I think the glibc maintainers have done versioning exactly right:
they are clear about which patches are allowed to contain collation
updates, and the OS distributions are able to ensure stability on major
OS release. I haven't yet found a Red Hat minor release that changed
glibc collation.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Collation version tracking for macOS

[Jeremy Schneider]

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 regularly for a variety of reasons. A brief perusal of
>> CLDR changelogs and CLDR jiras can give some insight here:
> 
>> Another misunderstanding that seems to persist is that this only relates
>> to exotic locales or that it's only the 2.28 version.
> 
> I'm not defending the status quo, and I think that I'm better informed
> than most about the problems in this area. My point was that it hardly
> matters that we don't necessarily see outright corruption. This was
> based in part on a misunderstanding of Tom's point, though.


I think I was guilty of the same misunderstanding - apologies Tom!
Thanks Peter for calling that out explicitly.

For my for my part, gut feeling is that MacOS major releases will be
similar to any other OS major release, which may contain updates to
collation algorithms and locales. ISTM like the same thing PG is looking
for on other OS's to trigger the warning. But it might be good to get an
official reference on MacOS, if someone knows where to find one?  (I don't.)

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Collation version tracking for macOS

[Jeremy Schneider]

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 wouldn’t believe some stuff I’ve seen people enter 
in the “name” field for web apps… 🙄 It’ll get some undefined or default sort 
behavior for unrecognized or unassigned code points.

When the libs are updated, those new chars begin to sort correctly, which is a 
change and breaks indexes (and potentially other stuff).

-Jeremy

Sent from my TI-83

> On Jun 8, 2022, at 16:34, Thomas Munro  wrote:
> On Thu, Jun 9, 2022 at 5:42 AM Tom Lane  wrote:
>> I'm sure that Apple are indeed updating the UTF8 data behind
>> their proprietary i18n APIs, but the libc APIs are mostly getting benign
>> neglect.
> 
> As for how exactly they might be doing that, I don't know, but a bit
> of light googling tells me that a private, headerless,
> please-don't-call-me-directly copy of ICU arrived back in macOS
> 10.3[1].  I don't see it on my 12.4 system, but I also know that 12.x
> started hiding system libraries completely (the linker is magic and
> pulls libraries from some parallel dimension, there is no
> /usr/lib/libSystem.B.dylib file on disk, and yet otool -L
>  references it).
> 
> It's a lovely client machine, but I don't know if anyone really runs
> meaningful database server stuff on macOS.  I think if I did I'd be
> very keen to use ICU for everything directly, rather than trying to
> unpick any of that and talk to Apple's API...  I think the
> how-to-support-multiple-ICUs subrant/subthread is a much more
> interesting topic.  I have no idea if the dlopen() concept I mentioned
> is the right way forward, but FWIW the experimental patch I posted
> seems to work just fine on a Mac, using multiple ICU libraries
> installed by MacPorts, which might be useful to developers
> contemplating that stuff.
> 
> [1] https://lists.apple.com/archives/xcode-users/2005/Jun/msg00633.html

Re: Collation version tracking for macOS

[Jeremy Schneider]

> 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 platforms?
>> 
> Yeah.  Well I couldn't resist doing some (very!) experimental hacking.
> See attached. 


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 the new? Can 
it be done without downtime? Can it be done without modifying a running 
application? Avoiding “collate” clauses on SQL statements requires working 
behind the scenes with defaults and indexes and partitions and constraints and 
everything else. I’m having a hard time coming up with a way this would be 
possible in practice, with all the places collations can show up.

Is the idea of “alter database” to change the default collation even realistic?

I’m having a bit of trouble picturing what the end game is here

-Jeremy


Sent from my TI-83

Re: Collation version tracking for macOS

[Jeremy Schneider]

> 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 the new?
> 
> They simply REINDEX, without changing anything. The details are still
> fuzzy, but at least that's what I was thinking of.
> 
>> Can it be done without downtime? Can it be done without modifying a running 
>> application?
> 
> Clearly the only way that we can ever transition to a new "physical
> collation" is by reindexing using a newer ICU version. And clearly
> there is going to be a need to fully deprecate any legacy version of
> ICU on a long enough timeline. There is just no getting around that.


I’m probably just going to end up rehashing the old threads I haven’t read yet…

One challenge with this approach is you have things like sort-merge joins that 
require the same collation across multiple objects. So I think you’d need to 
keep all the old indexes around until you have new indexes available for all 
objects in a database, and somehow the planner would need to be smart enough to 
dynamically figure out old vs new versions on a query-by-query basis. May need 
an atomic database-wide cutover; running a DB with internally mixed collation 
versions doesn’t seem like a small challenge. It would require enough disk 
space for two copies of all indexes, and queries would change which indexes 
they use in a way that wouldn’t be immediately obvious to users or app dev. 
Suddenly switching to or from a differently-bloated index could result in 
confusing and sudden performance changes.

Also there would still need to be a plan to address all the other non-index 
objects where collation is used, as has been mentioned before.

And given the current architecture, that final “alter database update default 
collation” command still seems awful risky, bug-prone and difficult to get 
correct. At least it seems that way to me.

At a minimum, this is a very big project and it seems to me like it may be wise 
to get more end-to-end fleshing out of the plans before committing incremental 
pieces in core (which could end up being misguided if the plan doesn’t work as 
well as assumed). Definitely doesn’t seem to me like anything that will happen 
in a year or two.

And my opinion is that the problems caused by depending on OS libraries for 
collation need to be addressed on a shorter timeline than what’s realistic for 
inventing a new way for a relational database to offer transparent or online 
upgrades of linguistic collation versions.

Also I still think folks are overcomplicating this by focusing on linguistic 
collation as the solution. Like 1% of users actually need or care about having 
the latest technically correct local-language-based sorting, at a database 
level. MySQL did the right thing here by doing what every other RDBMS did, and 
just making a simple “good-enough” collation hardcoded in the DB, same across 
all platforms, that never changes.

The 1% of users who need true linguistic collation can probably deal with the 
trade-off of dump-and-load upgrades for their ICU indexes and databases for a 
few more years.

-Jeremy


Sent from my TI-83

Re: Collation version tracking for macOS

[Jeremy Schneider]

> 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 
> somehow) and pick the one that provides 1234.  It doesn't matter whether it's 
> the same ICU version that the collation was originally created with, as long 
> as the collation version stays the same.

Does Unicode CDLR provide (or even track) versioning of collation or other i18n 
functionality for individual locale settings? I’m thinking it might not even 
have that concept in the original source repo/data, but I might be remembering 
wrong.

It would require not only watching for changes in the per-locale tailoring 
rules but also being cognizant of changes in root/DUCET behavior and 
understanding the impact of changes there.

(Common mistake I’ve seen folks make when comparing OS glibc versions is only 
looking at locale data, not realizing there have been changes to root behavior 
that didn’t involve any changes to local data files)

-Jeremy

Re: Collation version tracking for macOS

[Jeremy Schneider]




> 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 around DUCET stability and versioning.

https://unicode.org/reports/tr10/#Stable_DUCET


> With some system of symlinks to make it all work with defaults for
> those who don't care, a libc could have
> /usr/share/locale/en...@cldr34.utf-8 etc so you could
> setlocale(LC_COLLATE, "en_US@CLDR34"), or something.  I suppose they
> don't want to promise to be able to interpret the old data in future
> releases, and, as you say, sometimes the changes are in C code, due to
> bugs or algorithm changes, not the data.

If I understand correctly, files in /usr/share/locale aren’t enough because 
those only have the tailoring rules, and core algorithm and data (before 
applying locale-specific tweaks) also change between versions. I’m pretty sure 
glibc works similar to UCA in this regard (albeit based on ISO 14651 and not 
CDLR), and the Unicode link above is a good illustration of default collation 
rules that underly the locale-specific tweaks.

-Jeremy

Sent from my TI-83

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

[Jeremy Schneider]

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 instance in the cloud. Many a
>> times users or DBAs or developers would want to get and analyze
>> following:
> 
> Uh, are we going to implement everything that is only available at the
> command line as an extension just for people who are using managed cloud
> services where the command line is not available and the cloud provider
> has not made that information accessible?  Seems like this might lead to
> a lot of duplicated effort.

No. For most command line utilities, there's no reason to expose them in
SQL or they already are exposed in SQL. For example, everything in
pg_controldata is already available via SQL functions.

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 year, but there are more lurking out there.

Having the extension in core is actually the only way to avoid
duplicated effort, by having shared code which the pg_dump binary and
the extension both wrap or call.

-Jeremy

-- 
http://about.me/jeremy_schneider

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

[Jeremy Schneider]

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 year, but there are more lurking out there.
> 
> Uh, why is pg_waldump more important than other command line tool
> information?

Going down the list of all other utilities in src/bin:

* pg_amcheck, pg_config, pg_controldata: already available in SQL
* psql, pgbench, pg_dump: already available as client-side apps
* initdb, pg_archivecleanup, pg_basebackup, pg_checksums, pg_ctl,
pg_resetwal, pg_rewind, pg_upgrade, pg_verifybackup: can't think of any
possible use case outside server OS access, most of these are too low
level and don't even make sense in SQL
* pg_test_fsync, pg_test_timing: marginally interesting ideas in SQL,
don't feel any deep interest myself

Speaking selfishly, there are a few reasons I would be specifically
interested in pg_waldump (the only remaining one on the list).

.

First, to better support existing features around logical replication
and decoding.

In particular, it seems inconsistent to me that all the replication
management SQL functions take LSNs as arguments - and yet there's no
SQL-based way to find the LSNs that you are supposed to pass into these
functions.

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-REPLICATION

Over the past few years I've been pulled in to help several large PG
users who ran into these bugs, and it's very painful - because the only
real remediation is to drop and recreate the replication slot, which
means either re-copying all the data to the downstream system or
figuring out a way to resync it. Some PG users have 3rd party tools like
HVR which can do row-by-row resync (IIUC), but no matter how you slice
it, we're talking about a lot of pain for people replicating large data
sets between multiple systems. In most cases, the only/best option even
with very large tables is to just make a fresh copy of the data - which
can translate to a business outage of hours or even days.

My favorite example is the SQL function pg_replication_slot_advance() -
this could really help PG users find less painful solutions to broken
decoding, however it's not really possible to /know/ an LSN to advance
to without inspecting WAL. ISTM there's a strong use case here for a SQL
interface on WAL inspection.

.

Second: debugging and troubleshooting logical replication and decoding bugs.

I helped track down a few logical replication bugs and get fixed into
code at postgresql.org this past year. (But I give credit to others who
are much better at C than I am, and who did a lot more work than I did
on these bugs!)

Logical decoding bugs are some of the hardest to fix - because all you
have is a WAL stream, but you don't know the SQL or workload patterns or
PG code paths which created that WAL stream.

Dumping the WAL - knowing which objects and which types of operations
are involved and stats like number of changes or number of
subtransactions - this helps identify which transaction and what SQL in
the application triggered the failure, which can help find short-term
workarounds. Businesses need those short-term workarounds so they can
keep going while we work on finding and fixing bugs, which can take some
time. This is another place where I think a SQL interface to WAL would
be helpful to PG users. Especially the ability to filter and trace a
single transaction through a large number of WAL files in the directory.

.

Third: statistics on WAL - especially full page writes. Giving users the
full power of SQL allows much more sophisticated analysis of the WAL
records. Personally, I'd probably find myself importing all the WAL
stats into the DB anyway because SQL is my preferred data manipulation
language.


>> Having the extension in core is actually the only way to avoid
>> duplicated effort, by having shared code which the pg_dump binary and
>> the extension both wrap or call.
> 
> Uh, aren't you duplicating code by having pg_waldump as a command-line
> tool and an extension?

Well this whole conversation is just theoretical anyway until the code
is shared.  :)  But if Bharath is writing functions to decode WAL, then
wouldn't we just have pg_waldump use these same functions in order to
avoid duplicating code?

Bharath - was some code already posted and I just missed it?

Looking at the proposed API from the initial email, I like that there's
both stats functionality and WAL record inspection functionality
(similar to pg_waldump). I like that there's the ability to pull the
records as raw bytea data, however I think we're also going to want an
ability in v1 of the patch to decode it (similar to pageinspect
heap_page_item_attrs, etc).

An

Re: Proposal: Document ABI Compatibility

[Jeremy Schneider]

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 - misspelled "considerd"

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Built-in CTYPE provider

[Jeremy Schneider]

> 
> 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 matching index tuple within 
> index "iexpr"
> SELECT bt_index_parent_check('iexpr', heapallindexed => true);
> -- ERROR:  heap tuple (0,1) from table "t" lacks matching index tuple within 
> index "ipred"
> SELECT bt_index_parent_check('ipred', heapallindexed => true);


Other databases do still ship built-in ancient versions of unicode (Db2 ships 
4.0+ and Oracle ships 6.1+), and they have added new Unicode versions alongside 
the old but not removed the old versions. They claim to have “deprecated” old 
versions… but it seems they haven’t been able to get rid of them yet. Maybe 
some customer is willing to pay to continue deferring painful rebuilds needed 
to get rid of the old collation versions in commercial DBs?

For reference, see the table on slide 56 at 
https://www.pgevents.ca/events/pgconfdev2024/schedule/session/95-collations-from-a-to-z/
 and also see 
https://ardentperf.com/2024/05/22/default-sort-order-in-db2-sql-server-oracle-postgres-17/
 

Thanks for the illustration with actual Unicode 16 draft data.

Also, not directly related to this email… but reiterating a point I argued for 
in the recorded talk at pgconf.dev in Vancouver: a very strong argument for 
having the DB default to a stable unchanging built-in collation is that the 
dependency tracking makes it easy to identify objects in the database using 
non-default collations, and it’s easy to know exactly what needs to be rebuilt 
for a user to safely change some non-default collation provider’s behavior.

-Jeremy


Sent from my TI-83

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 breaking changes at all, even in
> cases where the provider is clearly wrong.



FWIW, using external ICU libraries is a nice solution for users who need
strict and up-to-date Unicode support.

Cell phones do often get support for new code points before databases. So
databases can end up storing characters before they are aware of the
meaning. (Slide 27 in the pgconf.dev talk illustrates a recent timeline of
Unicode & phone updates.)

-Jeremy

>

collation settings table in v16 docs

[Jeremy Schneider]

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 description column for every row in the table, just to understand
which keys I might be interested in.

I like how Peter's recent blog used the alias to organize the keys.

http://peter.eisentraut.org/blog/2023/05/16/overview-of-icu-collation-settings

I'd suggest that we add a column to this table with the alias, and also
have a consistent first sentence of each description, generally aligned
with the description from the upstream XML that Peter also referenced in
his blog.

I have an example below (and patch attached); I think this would make
the table a bit more understandable.

-Jeremy


===

Key: ka
Alias: colAlternate **<-added**
Values: noignore, shifted
Default: noignore
Description: **Collation parameter key for alternate handling.** If set
to shifted, causes some characters (e.g. punctuation or space) to be
ignored in comparison. Key ks must be set to level3 or lower to take
effect. Set key kv to control which character classes are ignored.

===

Key: kb
Alias: colBackwards **<-added**
Values: true, false
Default: false
Description: **Collation parameter key for backwards comparison of** the
level 2 differences. For example, locale und-u-kb sorts 'àe' before 'aé'.



-- 
http://about.me/jeremy_schneiderdiff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index ed84465996..5f81afdb85 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -1269,10 +1269,12 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false



-   
+   
+   

 
  Key
+ Alias
  Values
  Default
  Description
@@ -1281,6 +1283,7 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false

 
  co
+ 
  emoji, phonebk, 
standard, ...
  standard
  
@@ -1289,9 +1292,11 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false
 
 
  ka
+ colAlternate
  noignore, shifted
  noignore
  
+  Collation parameter key for alternate handling.
   If set to shifted, causes some characters
   (e.g. punctuation or space) to be ignored in comparison. Key
   ks must be set to level3 or
@@ -1301,20 +1306,24 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false
 
 
  kb
+ colBackwards
  true, false
  false
  
-  Backwards comparison for the level 2 differences. For example,
+  Collation parameter key for
+  backwards comparison of the level 2 differences. For example,
   locale und-u-kb sorts 'àe'
   before 'aé'.
  
 
 
  kc
+ colCaseLevel
  true, false
  false
  
   
+   Collation parameter key for case level.
Separates case into a "level 2.5" that falls between accents and
other level 3 features.
   
@@ -1327,12 +1336,14 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false
 
 
  kf
+ colCaseFirst
  
   upper, lower,
   false
  
  false
  
+  Collation parameter key for ordering by case.
   If set to upper, upper case sorts before lower
   case. If set to lower, lower case sorts before
   upper case. If set to false, the sort depends on
@@ -1341,9 +1352,11 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false
 
 
  kn
+ colNumeric
  true, false
  false
  
+  Collation parameter key for numeric handling.
   If set to true, numbers within a string are
   treated as a single numeric value rather than a sequence of
   digits. For example, 'id-45' sorts before
@@ -1352,11 +1365,13 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false
 
 
  kk
+ colNormalization
  true, false
  false
  
   
-   Enable full normalization; may affect performance. Basic
+   Collation parameter key for
+   full normalization; may affect performance. Basic
normalization is performed even when set to
false. Locales for languages that require full
normalization typically enable it by default.
@@ -1375,6 +1390,7 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false
 
 
  kr
+ colReorder
  
   space, punct,
   symbol, currency,
@@ -1383,6 +1399,7 @@ SELECT 'x-y' = 'x_y' COLLATE level4; -- false
  
  
   
+   Collation reorder codes.
Set to one or more of the 

Re: Let's make PostgreSQL multi-threaded

[Jeremy Schneider]

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.
> 
> 
> While I've long been in favor of a multi-threaded implementation, now in
> my old age, I tend to agree with Tom. I'd be interested in Konstantin's
> thoughts (and PostgresPro's experience) of multi-threaded vs. internal
> pooling with the current process-based model. I recall looking at and
> playing with Konstantin's implementations of both, which were
> impressive. Yes, the latter doesn't solve the same issues, but many
> real-world ones where multi-threaded is argued. Personally, I think
> there would be not only a significant amount of time spent dealing with
> in-the-field stability regressions before a multi-threaded
> implementation matures, but it would also increase the learning curve
> for anyone trying to start with internals development.

To me, processes feel just a little easier to observe and inspect, a
little easier to debug, and a little easier to reason about. Tooling
does exist for threads - but operating systems track more things at a
process level and I like having the full arsenal of unix process-based
tooling at my disposal.

Even simple things, like being able to see at a glance from "ps" or
"top" output which process is the bgwriter or the checkpointer, and
being able to attach gdb only on that process without pausing the whole
system. Or to a single backend.

A thread model certainly has advantages but I do feel that some useful
things might be lost here.

And for the record, just within the past few weeks I saw a small mistake
in some C code which smashed the stack of another thread in the same
process space. It manifested as unpredictable periodic random SIGSEGV
and SIGBUS with core dumps that were useless gibberish, and it was
rather difficult to root cause.

But one interesting outcome of that incident was learning from my
colleague Josh that apparently SUSv2 and C99 contradict each other: when
snprintf() is called with size=0 then SUSv2 stipulates an unspecified
return value less than 1, while C99 allows str to be NULL in this case,
and gives the return value (as always) as the number of characters that
would have been written in case the output string has been large enough.

So long story short... I think the robustness angle on the process model
shouldn't be underestimated either.

-Jeremy


-- 
http://about.me/jeremy_schneider

Re: Let's make PostgreSQL multi-threaded

[Jeremy Schneider]

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 multi-threaded on Windows
> and multi-process on all other platforms.
> I _think_ starting with 19c you can optionally run it multi-threaded on
> Linux as well.
Looks like it actually became publicly available in 12c. AFAICT Oracle
supports both modes today, with a config parameter to switch between them.

This is a very interesting case study.

Concepts Manual:

https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/process-architecture.html#GUID-4B460E97-18A0-4F5A-A62F-9608FFD43664

Reference:

https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/THREADED_EXECUTION.html#GUID-7A668A49-9FC5-4245-AD27-10D90E5AE8A8

List of Oracle process types, which ones can run as threads and which
ones always run as processes:

https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/background-processes.html#GUID-86184690-5531-405F-AA05-BB935F57B76D

Looks like they have four processes that will never run in threads:
* dbwriter (writes dirty blocks in background)
* process monitor (cleanup after process crash to avoid full server
restarts) 
* process spawner (like postmaster)
* time keeper process

Per Tim Hall's oracle-base, it seems that plenty of people are sticking
with the process model, and that one use case for threads was:
"consolidating lots of instances onto a single server without using the
multitennant option. Without the multithreaded model, the number of OS
processes could get very high."

https://oracle-base.com/articles/12c/multithreaded-model-using-threaded_execution_12cr1

I did google search for "oracle threaded_execution" and browsed a bit;
didn't see anything that seems earth shattering so far.

Ludovico Caldara and Martin Bach published blogs when it was first
released, which just introduced but didn't test or hammer on it. The
feature has existed for 10 years now and I don't see any blog posts
saying that "everyone should use this because it doubles your
performance" or anything like that. I think if there were really
significant performance gains then there would be many interesting blog
posts on the internet by now from the independent Oracle professional
community - I know many of these people.

In fact, there's an interesting blog by Kamil Stawiarski from 2015 where
he actually observed one case of /slower/ performance with threads. That
blog post ends with: "So I raise the question: why and when use threaded
execution? If ever?"

https://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/

I'm not sure if he ever got an answer

-Jeremy

-- 
http://about.me/jeremy_schneider

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

[Jeremy Schneider]

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 help? Collation-related dependency changes are a different thing
from major version DB upgrades

Tom’s point about how the levels are directly tied to concrete differences
in behavior (planner/executor) makes a lot of sense to me

-Jeremy

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

[Jeremy Schneider]

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 be to stick with
> some
> > > Unicode version and never modify it, ever.
> >
> > I think that's a completely non-viable way forward. Even if everyone
> > here voted in favor of that, five years from now there will be someone
> > who shows up to say "I can't use your crappy software because the
> > Unicode tables haven't been updated in five years, here's a patch!".
> > And, like, what are we going to do? Still keeping shipping the 2024
> > version of Unicode four hundred years from now, assuming humanity and
> > civilization and PostgreSQL are still around then? Holding something
> > still "forever" is just never going to work.
>
> I hear you.  It would be interesting to know what other RDBMS do here.


Other RDBMS are very careful not to corrupt databases, afaik including
function based indexes, by changing Unicode. I’m not aware of any other
RDBMS that updates Unicode versions in place; instead they support multiple
Unicode versions and do not drop the old ones.

See also:
https://www.postgresql.org/message-id/E8754F74-C65F-4A1A-826F-FD9F37599A2E%40ardentperf.com

I know Jeff mentioned that Unicode tables copied into Postgres for
normalization have been updated a few times. Did anyone ever actually
discuss the fact that things like function based indexes can be corrupted
by this, and weigh the reasoning? Are there past mailing list threads
touching on the corruption problem and making the argument why updating
anyway is the right thing to do? I always assumed that nobody had really
dug deeply into this before the last few years.

I do agree it isn’t as broad of a problem as linguistic collation itself,
which causes a lot more widespread corruption when it changes (as we’ve
seen from glibc 2.28 and also other older hacker mailing list threads about
smaller changes in older glibc versions corrupting databases). For now,
Postgres only has code-point collation and the other Unicode functions
mentioned in this thread.

-Jeremy

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

[Jeremy Schneider]

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 suspect the reason we don't hear
> about ctype problems now is that the collation problems are worse and
> happen in similar situations. But if all the collation problems went
> away, a subset of the same users would then be unhappy about ctype.


I have seen and created indexes on upper() functions a number of times too,
and I think this is not an uncommon pattern for case insensitive searching

Before glibc 2.28, there was at least one mailing list thread where an
unhappy person complained about collation problems; but for a number of
years before 2.28 I guess the collation changes were uncommon so it didn’t
get enough momentum to be considered a real problem until the problem
became widespread a few years ago?

https://www.postgresql.org/message-id/flat/BA6132ED-1F6B-4A0B-AC22-81278F5AB81E%40tripadvisor.com

I myself would prefer an approach here that sets a higher bar for
pg_upgrade not corrupting indexes, rather than saying it’s ok as long as
it’s rare

-Jeremy

Re: Built-in CTYPE provider

[Jeremy Schneider]

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 schedule
> is
> > too compressed for anyone, do share.
>
> My opinion is that it is okay to release as is.


Like Jeff, I don’t think counting votes or putting names on one side or
another is the best way to decide things. Everyone has unique opinions and
nuances, it’s not like there’s two groups that all agree together on
everything and disagree with the other group. I don’t want my name put on a
list this way; there are some places where I agree and some places where I
disagree with most people 🙂

I don’t know the code as intimately as some others on the lists, but I’m
not aware of any one-way doors that would create major difficulties for
future v18+ ideas being discussed

fwiw, I don’t want to pull this feature out of v17, I think it’s okay to
release it

-Jeremy

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

[Jeremy Schneider]

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 unique index on UPPER(somecol).


I doubt it’s common to have unique on upper()

But non-unique indexes for case insensitive searches will be more common.
Historically this is the most common way people did case insensitive on
oracle.

Changing ctype would mean these queries can return wrong results

The impact would be similar to the critical problem TripAdvisor hit in 2014
with their read replicas, in the Postgres email thread I linked above

-Jeremy

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

[Jeremy Schneider]

> 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

[Jeremy Schneider]

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 be happy to submit patches for this in the
>> next commitfest.
>
> That's a worthy goal.

I'm also interested in this item and I helped Nathan with a little of
the initial testing.  Also, having changed redo sizes on other
database platforms a couple times (a simple & safe runtime operation
there), it seems to me that a feature like this would benefit
PostgreSQL.

I would add that we increased the max segment size in pg10 - but the
handful of users who are in the most pain with very high activity
rates on running systems are still limited to logical upgrades or
dump-and-load to get the benefit of larger WAL segment sizes.  From a
technical perspective, it doesn't seem like it should be too
complicated to implement this in pg_upgrade since you're moving into a
new cluster anyway.

On Fri, Nov 10, 2017 at 7:46 AM, Bossart, Nathan  wrote:
> We've had success with our initial testing of upgrades to larger WAL
> segment sizes, including post-upgrade pgbench runs.

Just to fill this out a little; our very first test was to take a
9.6.5 16mb-wal post-pgbench db and pg_upgrade it to 10.0 128mb-wal
with no changes except removing the WAL size from check_control_data()
then doing more pgbench runs on the same db post-upgrade. Checked for
errors or problematic variation in TPS. More of a smoke-screen than a
thorough test, but everything looks good so far.

On Fri, Nov 10, 2017 at 7:46 AM, Bossart, Nathan  wrote:
> Beyond adjusting
> check_control_data(), it looks like the 'pg_resetwal -l' call in
> copy_xact_xlog_xid() may need to be adjusted to ensure that the WAL
> starting address is set to a valid value.

This was related to one interesting quirk we observed.  The pg_upgrade
tried to call pg_resetwal on the *new* database with a log sequence
number that assumes the *old* wal size.  In our test, it called
"pg_resetwal -l 000100020071" which is an invalid filename
with 128mb wal segment.  In order to get a sensible filename,
PostgreSQL took the "71" and wrapped three times and added to get a
new WAL filename of "000100050011".

This actually raises a really interesting concern with pg_upgrade and
different WAL segment sizes.  We have WAL filenames and then we have
XLogSegNo.  If pg_upgrade just chooses the next valid filename, then
XLogSegNo will decrease and overlap when the WAL segment size goes up.
If pg_upgrade calculates the next XLogSegNo then the WAL segment
filename will decrease and overlap when the WAL segment size goes
down.

from xlog_internal.h:
#define XLogFileName(fname, tli, logSegNo, wal_segsz_bytes)\
snprintf(fname, MAXFNAMELEN, "%08X%08X%08X", tli,\
 (uint32) ((logSegNo) / XLogSegmentsPerXLogId(wal_segsz_bytes)), \
 (uint32) ((logSegNo) % XLogSegmentsPerXLogId(wal_segsz_bytes)))

...

#define XLogFromFileName(fname, tli, logSegNo, wal_segsz_bytes)\
do {\
uint32 log;\
uint32 seg;\
sscanf(fname, "%08X%08X%08X", tli, &log, &seg); \
*logSegNo = (uint64) log *
XLogSegmentsPerXLogId(wal_segsz_bytes) + seg; \
} while (0)

If there's an archive_command script that simply copies WAL files
somewhere then it might overwrite old logs when filenames overlap.  I
haven't surveyed all the postgres backup tools & scripts out there but
it also seems conceivable that some tools will do the equivalent of
XLogFromFileName() so that they can be aware of there are missing logs
in a recovery scenario.  Those tools could conceivably get broken by
an overlapping/decremented XLogSegNo.

I haven't fully thought through replication to consider whether
anything could break there, but that's another open question.

There are a few different approaches that could be taken to determine
the next WAL sequence number.
1) simplest: increment filename's middle digit by 1, zero out the
right digit.  no filename overlap, don't need to know WAL segment
size.  has XLogSegNo overlap.
2) use the next valid WAL filename with segment size awareness. no
filename overlap, has XLogSegNo overlap.
3) translate old DB filename to XLogSegNo, XLogSegNo++, translate to
new DB filename.  no XLogSegNo overlap, has filename overlap.
4) most complex: XLogSegNo++, translate to new DB filename, then
increase filename until it's greater than last used filename in old
db.  Always has gaps, never overlaps.

I'm thinking option 4 sounds the most correct.  Any thoughts from
others to the contrary?

Anything else that is worth testing to look for potential problems
after pg_upgrade with different WAL segment sizes?

-Jeremy

-- 
http://ab

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

[Jeremy Schneider]

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 conflate something unrelated with
>>> pg_upgrade goals.
>
> FWIW, I agree with Andres' position here.  I think the charter of
> pg_upgrade is to duplicate the old cluster as closely as it can,
> not to modify its configuration.  A close analogy is that it does not
> attempt to upgrade extension versions while migrating the cluster.

Having pg_upgrade simply allow an upgrade where the WAL sizes don't match
between the old cluster and the new cluster seems fairly trivial.
pg_upgrade isn't
changing anything; it's just *not* requiring the new and old clusters
to match in this
way. I'll admit I'm much newer to postgresql than everyone else on
this list, but I
haven't yet thought of any technical reason this check is actually
needed. (Just the
WAL sequencing/naming concern I outlined earlier.)

Writing code to change the WAL size on an existing cluster will be a little more
complex.  We will need to delete all WAL files and recreate them at the new
sizes. We will need to either (1) be absolutely sure that there was a
clean shutdown
or (2) copy WAL data from the old files to the new files.  We will
need to think harder
through the issue of gaps being introduced in the sequence of WAL filenames and
effects on backup/recovery.

These two ideas don't seem mutually exclusive to me.  If pg_upgrade
allows working
with different WAL sizes, it doesn't mean we can't still introduce a
utility to change the
WAL size on running clusters.

So yeah - having a utility command to change the WAL size on a running cluster
is a great idea.  But why are we wanting to maintain a check in pg_upgrade
which doesn't actually seem technically necessary?  Or am I missing something
that would break if the WAL sizes didn't match across two clusters when
pg_upgrade moved the data between them?

-Jeremy

-- 
http://about.me/jeremy_schneider

(Disclaimer: I work for AWS and my opinions are my own.)

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

[Jeremy Schneider]

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 much higher than any backend would
> realistically need.
> 
> The first challenge I envision is that without any feedback (either to
> the planner or executor), it may break queries quite easily. It just
> takes the planner to add one more sort / hash join / materialize
> (which it can do arbitrarily, as it has no concept of the memory
> limit), and now the query can't run.
> 
> And secondly, there are allocations that we don't restrict by
> work_mem, but this memory limit would include them, ofc. The main
> example that I can think of is hash join, where we (currently) don't
> account for the BufFile arrays, and that can already lead to annoying
> OOM issues, see e.g. [1] [2] and [3] (I'm sure there are more threads
> about the issue).

Hi James!

While I don't have a detailed design in mind, I'd like to add a strong
+1 on the general idea that work_mem is hard to effectively use because
queries can vary so widely in how many nodes might need work memory.

I'd almost like to have two limits:

First, a hard per-connection limit which could be set very high - we
can track total memory usage across contexts inside of palloc and pfree
(and maybe this could also be exposed in pg_stat_activity for easy
visibility into a snapshot of memory use across all backends). If
palloc detects that an allocation would take the total over the hard
limit, then you just fail the palloc with an OOM. This protects
postgres from a memory leak in a single backend OOM'ing the whole
system and restarting the whole database; failing a single connection
is better than failing all of them.

Second, a soft per-connection "total_connection_work_mem_target" which
could be set lower. The planner can just look at the total number of
nodes that it expects to allocate work memory, divide the target by
this and then set the work_mem for that query.  There should be a
reasonable floor (minimum) for work_mem - maybe the value of work_mem
itself becomes this and the new target doesn't do anything besides
increasing runtime work_mem.

Maybe even could do a "total_instance_work_mem_target" where it's
divided by the number of average active connections or something.

In practice this target idea doesn't guarantee anything about total work
memory used, but it's the tool I'd most like as an admin. And the
per-connection hard limit is the tool I'd like to have for protecting
myself against memory leaks or individual connections going bonkers and
killing all my connections for an instance restart.

-Jeremy

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

[Jeremy Schneider]

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 plan IDs with a new pg_stat_plans extension in
> contrib, that also records the first plan text with EXPLAIN (COSTS
> OFF)
> 
> My overall perspective is that (1) is best done in-core to keep
> overhead low, whilst (2) could be done outside of core (or merged
> with a future pg_stat_statements) and is included here mainly for
> illustration purposes.

And 2025 is starting with a bang! Nice to see this email! Being able to
collect telemetry that indicates when plan changes happened would be
very useful.

The specifics of how a plan ID is generated are going to have some edge
cases (as you noted)

I concur that the ideal place for this to eventually land would be
alongside queryid in pg_stat_activity

-Jeremy

Re: RFC: Allow EXPLAIN to Output Page Fault Information

[Jeremy Schneider]

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.   
> >
> > Surely these numbers would be too unstable to be worth anything.  
> 
> What makes you think that? I'd expect them to be similarly stable to
> the numbers we get for BUFFERS. i.e. Sure they won't be completely
> stable, but I expect them to be quite helpful when debugging perf
> issues, because large numbers indicate that the query is disk-bound
> and small numbers indicate that it is not.
> 
> These numbers seem especially useful for setups where shared_buffers
> is significantly smaller than the total memory available to the
> system. In those cases the output from BUFFERS might give the
> impression that that you're disk-bound, but if your working set still
> fits into OS cache then the number of page faults is likely still
> low. Thus telling you that the numbers that you get back from BUFFERS
> are not as big of a problem as they might seem.

We'd probably need to combine both pg_buffercache_evict() and
/proc/sys/vm/drop_caches to get stable numbers - which is something I
have done in the past for testing.

Another thought would be splitting out the IO timing information into
two values - IO timing for reads that triggered major faults, versus
IO timing for reads that did not.

And system views like pg_stat_database seem worth considering too.

-Jeremy

Re: RFC: Allow EXPLAIN to Output Page Fault Information

[Jeremy Schneider]

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.  
> > 
> > Surely these numbers would be too unstable to be worth anything.  
> 
> Thanks for your comment!
> 
> Hmm, I didn't know these are unstable. If there are any reasons, I'd 
> like to know about them.
> 
> I would like to explore alternative methods for measuring resource 
> usage, but
> I am not aware of other approaches.
> (IIUC pg_stat_kcache[1], which is said to provide information about 
> filesystem layer I/O usage also gets metrics from getrusage(2))

What I'd really like to see is a column added to pg_stat_database
called blks_read_majflts

It would be great if we could calculate a cache hit ratio that took OS
major page faults into account

Yes this could also be done in pg_stat_kcache but why not do it right
in pg_stat_database? I think it would pretty widely appreciated and
used.

-Jeremy

Re: Query regarding pg_prewarm extension

[Jeremy Schneider]

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 within a page can move to
> different pages over time (due to operations like VACUUM FULL or
> REINDEX), how should I handle this when selecting the pages to
> prewarm?

For my part, I've only used the block offsets when I wanted to fire off
several jobs in parallel, attempting to prewarm a relation faster. I've
never tried to track the location of specific rows for purposes of
prewarming.

You might try the "autoprewarm" feature. After adding pg_prewarm to
your shared_preload_libraries, it will automatically keep track of the
contents of the buffer cache and after a restart it will automatically
prewarm the buffer cache with the blocks that were there before.

https://www.enterprisedb.com/blog/autoprewarm-new-functionality-pgprewarm

Alternatively you could just prewarm a few of your most important hot
tables and indexes with a script after restarts.

For most smaller databases, slightly slower performance for a short
period after startup isn't a problem - while reading blocks from disk
for the first time. After the first read, blocks that are frequently
accessed will remain in the cache. The Postgres cache management
algorithm works well in general.

This is my two cents, anyway

-Jeremy

-- 
http://about.me/jeremy_schneider

Re: llvm dependency and space concerns

[Jeremy Schneider]

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 building postgres, and expect
postgres to continue working :)  Very happy to learn it works this way!

Also thanks to Christoph Moench-Tegeder who pointed out in the other
email that the PGDG rpm packagers already split LLVM out to a separate
package, so it's already been done over there.

-Jeremy

llvm dependency and space concerns

[Jeremy Schneider]

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
33% of a postgres container's bytes (143MB / 434MB) [1].

Per the details in the referenced emails, dpkg Installed-Size:
libllvm16 120542 KB
libz3-4   22767 KB

(Note that libz3 is a dependency of libllvm.)

For plperl, plpython and pltcl we are able to split those into separate
debian packages because Postgres' extension framework enables us to
install the files separately without a recompile, and Postgres can be
compiled with these configure flags but still works fine if the files
aren't present.

I haven't yet looked closely, but my assumption is that the --with-llvm
flag may not work the same. I'm going to spend some time taking a look,
but if someone knows off the top of their head and can give me a head
start that would be appreciated!

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] package. There are use 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

[Jeremy Schneider]

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
> 33% of a postgres container's bytes (143MB / 434MB) [1].
> 
> Per the details in the referenced emails, dpkg Installed-Size:
> libllvm16 120542 KB
> libz3-4   22767 KB

forgot the links :)

1: https://postgr.es/m/2025022847.486d05e2%40jeremy-ThinkPad-T430s
2: https://postgr.es/m/20250109005301.3b145092%40jeremy-ThinkPad-T430s

Re: llvm dependency and space concerns

[Jeremy Schneider]

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] package.  
> 
> Build without --with-llvm.  Alternatively, split lib/llvmjit.so and
> lib/bitcode/ into a separate package.  These are matters for packagers
> not the core project ...

This was my initial idea too, I was thinking to have a "normal"
postgres package and an additional a "slim" or "nojit" postgres package
(this was my original topic on the packagers list).

We would have to update dependencies for the dozens of packages like
extensions, wal2json, pgtop, and all the rest to take either of these
base postgres packages, and we have to deal with the two base packages
that potentially conflict with each other.

It's a cleaner solution if JIT works more like an extension, and we can
run a single build and split JIT into a separate package.

-Jeremy

Re: Update Unicode data to Unicode 16.0.0

[Jeremy Schneider]

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 conversion files, the snowball files, etc.
> >  We need 
> > to move all of these things forward to keep up with the aspects of
> > the 
> > real world that this data reflects.  
> 
> Should we consider bundling multiple versions of the generated tables
> (header files) along with Postgres?
> 
> That would enable a compile-time option to build with an older version
> of Unicode if you want, solving the packager concern that Noah raised.
> It would also make it easier for people to coordinate the Postgres
> version of Unicode and the ICU version of Unicode.

FWIW, after adding ICU support I personally don't think there's a
pressing need to continue updating the tables anymore. I think ICU is
the best solution for people who need the latest linguistic collation
rules.

On the user side, my main concerns are the same as they've always
been: 100% confidence that Postgres updates will not corrupt any data
or cause incorrect query results, and not being forced to rebuild
everything (or logically copy data to avoid pg_upgrade). I'm at a large
company with many internal devs using Postgres in ways I don't know
about, and many users storing lots of unicode data I don't know about.

I'm working a fair bit with Docker and Kubernetes and CloudNativePG
now, so our builds come through the debian PGDG repo. Bundling multiple
tables doesn't bother me, as long as it's not a precursor to removing
current tables from the debian PGDG builds we consume in the future.

Ironically it's not really an issue yet for us on docker because
support for pg_upgrade is pretty limited at the moment.  :)  But I
think pg_upgrade support will rapidly improve in docker, and will
become common on large databases.

If Postgres does go the path of multiple tables, does the community
want to accumulate a new set of tables every year? That could add up
quickly. Maybe we don't add new tables every year, but follow the
examples of Oracle and DB2 in accumulating them on a less frequent
basis?

-Jeremy

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

[Jeremy Schneider]

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 India comply with the Ministry of
> Corporate Affairs (MCA) Guidelines
> (https://www.mca.gov.in/Ministry/pdf/AuditAuditorsAmendmentRules_24032021.pdf)
> As per the MCA guidelines it is mandatory to capture details of what
> data was changed, when it was changed and who made the change.
> 
> PostgreSQL generates and stores (change data capture) information in
> transaction log, which is in turn read by Oracle GoldenGate and
> stored in XML files. These XML files are processed by AVDF and stored
> in AVDF database.

Hi Sumanth -

I think your question would be better suited to the general (users)
list, since it's more of a "user" question. This "hackers" email list
is used by developers working on Postgres internals.

I think you might misunderstand Oracle's auditing features. IIUC,
neither the traditional SYS.AUD$ table nor the new unified audit trail
in Oracle are populated from redo, but both are populated by directly
intercepting events.

A common solution following a similar model in the Postgres space is
pgaudit. I would suggest to start out by reading the pgaudit
documentation here:

https://github.com/pgaudit/pgaudit/blob/main/README.md

Pgaudit is an "extension" that's installed separately and added on to
Postgres, with its own distinct group of maintainers. I'm not sure if it
has a dedicated forum for questions and discussion, but I'm sure you
could ask questions on the community Postgres slack, IRC, telegram, and
other popular online Postgres community forums.

I don't know if it would be considered out of place to ask questions
about pgaudit on the pgsql-general list (because it's an extension and
doesn't come from postgresql.org) but I am sure there are a lot of
pgaudit users here, so questions might be ok over on the general (users)
mailing list.

There are a lot of people (including my company) using Postgres in
regulated industries around the world and it has robust capabilites to
meet regulations. Oracle is a great database too. Good luck with your
project!

-Jeremy

Re: Update Unicode data to Unicode 16.0.0

[Jeremy Schneider]

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 builtin provider, only expression indexes and a few
> other things are vulnerable, only during a major version upgrade, and
> mostly (but not entirely) when using recently-assigned Cased letters.
> 
> To me, problem A seems about 100 times worse than B almost any way I
> can imagine measuring it: number of objects vulnerable, severity of
> the problem when it does happen, likelihood of a vulnerable object
> having an actual problem, etc. If you disagree, I'd like to hear more.

Jeff - you and several others have literally put years into making this
better, and it's deeply appreciated. I agree that with the builtin
provider we're in a much better place.

I don't quite understand Tom's argument about why Unicode 15 must
eventually become untenable. Why are we assuming it will? In Oracle's
entire history, I think they have only ever supported four versions of
Unicode. [1] MySQL seems to have added their second only recently. [2]
And again - we have ICU if I need the latest emoji characters. Frankly,
Unicode 15 is pretty good. Most updates to unicode these days are fairly
minor.

Maybe Postgres can be the first database to always ship support for the
latest Unicode with each major version - but I think we should design
that right if we're going to do it. If we just stay on Unicode 15 for
now then there are no problems with case insensitive indexes or range
partitioned tables returning wrong query results after a major version
upgrades.

There's been a lot of discussion about indexes, but this SQL also seems
to work:

postgres=# create table test_events(customer_name text, ts timestamp,
message text) partition by range((lower(customer_name)));

I'm sure that people shouldn't do this ... but if anyone /did/ then it
wouldn't be as simple as an index rebuild after their major version
upgrade.

I had never really considered it before, but this SQL also seems to work

postgres=# create table test_events(id uuid, ts timestamp, message
text) partition by range((ts at time zone 'America/Sao_Paulo')); 

I'm sure that people shouldn't do that either ... but if anyone did then
would their rows would be in the wrong partition after they upgraded
from 11.4 to 11.5?

The difficulty here is that I work at a company with thousands of
developers and lots of Postgres and I see people do things all the time
that we might think they "shouldnt" do.

Before we bump the unicode version, perseonally I'd just like to have
some tools to make it so people actually can't do the things they
shouldn't do.

-Jeremy


1:
https://docs.oracle.com/en/database/oracle/oracle-database/23/nlspg/appendix-A-locale-data.html#GUID-CC85A33C-81FC-4E93-BAAB-1B3DB9036060__CIABEDHB

2:
https://dev.mysql.com/blog-archive/mysql-character-sets-unicode-and-uca-compliant-collations/

Re: Update Unicode data to Unicode 16.0.0

[Jeremy Schneider]

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.postgresql.org/message-id/3481161.1742055...@sss.pgh.pa.us

Waiting to update Unicode is not what creates the problem, as long as
we support in-place major upgrades without rebuilding indexes.


It seems that we think that it's really uncommon to make indexes on
timezone data. We think that breaking primary keys must definitely be
avoided. We think case-insensitive indexes are probably uncommon, so as
long as its "rare" we can let them break.

I'm not asking for an extreme definition of "IMMUTABLE" but I'd be
very happy with a GUC "data_safety=radical_like_jeremy" where Postgres
simply won't start if the control file says it was from a different
operating system or architecture or ICU/glibc collation version. I can
disable the GUC (like a maintenance mode) to rebuild my indexes and
update my collation versions, and ideally this GUC would also mean that
indexes simply aren't allowed to be created on functions that might
change within the guarantees that are made. (And range-based partitions
can't use them, and FDWs can't rely on them for query planning, etc.)

I get that we don't want to break stuff people are currently doing -
but I think there are a bunch of people who would be happy with an
option to reduce functionality going forward in exchange for clearly
defined behavior. I just don't want to have to think about this on
behalf of all my Postgres users.

-Jeremy

Re: Update Unicode data to Unicode 16.0.0

[Jeremy Schneider]

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 attached a
> SQL file that demonstrates what can happen for a PG17->PG18 upgrade,
> assuming that we've updated Unicode to 16.0.0 in PG18.
> 
> The change in Unicode that I'm focusing on is the addition of U+A7DC,
> which is unassigned in Unicode 15.1 and assigned in Unicode 16, which
> lowercases to U+019B. The examples assume that the user is using
> unassigned code points in PG17/Unicode15.1 and the PG_C_UTF8
> collation.

It seems the consensus is to update unicode in core... FWIW, I'm still
in favor of leaving it alone because ICU is there for when I need
up-to-date unicode versions.

From my perspective, the whole point of the builtin collation was to
one option that avoids these problems that come with updating both ICU
and glibc.

So I guess the main point of the builtin provider just that it's faster
than ICU?

-Jeremy

Re: Update Unicode data to Unicode 16.0.0

[Jeremy Schneider]

> 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.
> 
> That's a good point.

Jeff - thanks for the reminder that this is just about character semantics and 
not ordering. Obviously C collation by definition (code point ordering) doesn’t 
change sort order… two weeks ago I was working on updating the torture test 
GitHub page with glibc collation changes up through Ubuntu 24.10 so my mind was 
definitely over there. No detected changes in en-US so that’s great news. 🙂

Is the simple answer that functions & clauses related to both time zones and 
character semantics should just all be considered STABLE instead of IMMUTABLE?

I think if that were the case then changes across a minor version would simply 
be allowed by definition right? No need for warnings.

This would impact the ability to create case-insensitive indexes.

-Jeremy

Sent from my TI-83

Re: queryId constant squashing does not support prepared statements

[Jeremy Schneider]

On Fri, 2 May 2025 14:56:56 +0200
Álvaro Herrera  wrote:

> On 2025-May-02, Michael Paquier wrote:
> 
> > That depends.  If we conclude that tracking this information through
> > the parser based on the start and end positions in a query string
> > for a set of values is more relevant, then we would be redesigning
> > the facility from the ground, so the old approach would not be
> > really relevant..  
> 
> I disagree that a revert is warranted for this reason.  If you want to
> change the implementation later, that's fine, as long as the user
> interface doesn't change.
> 

FWIW, i'm +1 on leaving it in pg18. Prepared statements often look a
little different in other ways, and there are a bunch of other quirks
in how queryid's are calculated too. Didn't there used to be something
with CALL being handled as a utility statement making stored procs look
different from functions?



-- 
To know the thoughts and deeds that have marked man's progress is to
feel the great heart throbs of humanity through the centuries; and if
one does not feel in these pulsations a heavenward striving, one must
indeed be deaf to the harmonies of life.

Helen Keller. Let Us Have Faith. Doubleday, Doran & Company, 1940.

protocol support for labels

[Jeremy Schneider]

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
arbitrary labels; and we need protocol support to pass those labels
inline with the query instead of having to require a separate round
trip. it would also be good if these labels were somehow accessible in
logging formats.

observability frameworks like OpenTelemetry support tracing through all
layers of a stack, and trace_ids can even be passed into sql with
extensions like sqlcommenter. however sqlcommenter puts the trace_id
into a comment which effectively breaks all the utility of
pg_stat_statements since each query has a unique trace_id.

if protocol enhancements are on the table, I think it would be great
for this topic to get a little discussion.

-Jeremy

Re: protocol support for labels

[Jeremy Schneider]

> 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
>> extensions like sqlcommenter. however sqlcommenter puts the trace_id
>> into a comment which effectively breaks all the utility of
>> pg_stat_statements since each query has a unique trace_id.
>> 
> There are some other use-cases:
> 1) RO-RW routing. Users can pass target-session-attrs to the server
> within query labels to hint about its need. Useful when some kind of
> proxy (odyssey,pgbouncer,spqr,pgpool II, pgcat, pg_doorman) is used.
> 2) pg_comment_stats uses comments in the query to accumulate statistics. [0]


Thinking a bit more, my root issue is specifically around pg_stat_statements so 
maybe it’s also solvable with some changes to how query jumbling is done

But that topic seems like one where we’d never get consensus

Should query jumbling for calculating query_id be customizable somehow? How 
would we resolve multiple concurrent opinions about how queries should be 
jumbled (eg if comment_stats needs different tweaks than sqlcommenter)? Was 
there previous discussion about this already? I’ll need to go search mailing 
list history a bit

-Jeremy


Sent from my TI-83

Re: protocol support for labels

[Jeremy Schneider]

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(...)` and also the next query
> one after the other without waiting for the response to the first.
> The server could similarly batch the two responses.

Isn't multiple queries in one packet only possible with the simple
protocol, but not possible with the extended protocol? So this would be
entirely incompatible with prepared/parameterized statements?

-Jeremy

Re: Update Unicode data to Unicode 16.0.0

[Jeremy Schneider]

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 accept code points known to that version
> > of
> > Unicode. Or maybe this shouldn't be entirely new encodings but
> > something vaguely akin to a typmod, so that you could have columns
> > of type text[limited_to_unicode_v16_0_0] or whatever. If we actually
> > exclude unassigned code points, then we know they aren't there, and
> > we
> > can make deductions about what is safe to do based on that
> > information.  
> 
> I like this line of thinking, vaguely similar to my STRICT_UNICODE
> database option proposal. Maybe these aren't exactly the right things
> to do, but I think there are some possibilities here, and we shouldn't
> give up and assume there's a problem when usually there is not.

There is "the iPhone paradox" here; if we reject unassigned code
points, then websites are going to start throwing database errors for
anyone with the latest iPhone who uses a new emoji.

(Unless the database is updated very quickly, which is atypical.) Apple
tends to get new emojis into consumers hands a year or less after the
new Unicode release.

-Jeremy