Not everyone knows, but at since PostgreSQL 9.6, we have some built-in roles.
Of course, there is always superuser (usually called postgres), but I'm not talking about it. I'm talking about magical roles that have names starting with pg_.
Tag: guc
Configuration changes across Pg versions
Back in 2018 I wrote why-upgrade.depesz.com – aggregator of changelogs between various versions of Pg.
Want to know what you will get when upgrading from 12.1 to 12.9? Here you go. Longer time changes? Like, from 9.5.20 to 14.1? I've got your back.
It even has a way to list every change that relates to anything related to indexes.
Today I updated the code, again to show which GUCs (configuration parameters) have changed between given versions. For example, getting diff from 13.5 to 14.1 shows you that:
- two parameters were removed, and are no longer there
- there are 17 new parameters
- three parameters had their default values changed
And each GUC that is listed is (well, is supposed to but in some cases it can't) linked to relevant part of docs that describe what it is.
To make it work I compiled every version of Pg, since 7.2 (there have been 410 of them!), and extracted list of config params, and their default values.
Then, I fetched docs for all major versions of Pg, and extracted list of documentation fragments that relate to each config parameter.
This will require more work on each subsequent release, but I think I can manage it.
Any way – hope you'll find it helpful.
Display “settings” from plans on explain.depesz.com
Some time ago I wrote about new options for explains – one that prints settings that were modified from default.
This looks like this:
Aggregate (cost=35.36..35.37 rows=1 width=8) -> Index Only Scan using pg_class_oid_index on pg_class (cost=0.27..34.29 rows=429 width=0) Settings: enable_seqscan = 'off'
Finally, today, I pushed a change that displays them on explain.depesz.com. To make it more usable, it will also provide links to documentation about each such options.
For example, check this plan.
Hope you'll find it useful.
Starting with Pg – where are logs?
Just so that it will be perfectly clear: the logs I have in mind are the ones for DBAs to read – with slow queries, errors, and other interesting information.
So, how does one find them?
Starting with Pg – where/how can I set configuration parameters?
Previously I wrote about locating config files.
The thing is – postgresql.conf is not the only place you can set your configuration in.
In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from.
Continue reading Starting with Pg – where/how can I set configuration parameters?
Starting with Pg – where is the config?
Over the years I saw some people find themselves in position where they have to start dealing with PostgreSQL with minimal, or none, prior exposure. This leads to problems with seemingly easy tasks – how to change config? How to find stuff in logs?
So I decided to write some blogposts to be able to point such people to pre-made tutorials.
And I start today, with information on how to find PostgreSQL config files.
Waiting for PostgreSQL 14 – Add idle_session_timeout.
On 6th of January 2021, Tom Lane committed patch:
Add idle_session_timeout. This GUC variable works much like idle_in_transaction_session_timeout, in that it kills sessions that have waited too long for a new client query. But it applies when we're not in a transaction, rather than when we are. Li Japin, reviewed by David Johnston and Hayato Kuroda, some fixes by me Discussion: https://postgr.es/m/763A0689-F189-459E-946F-F0EC4458980B@hotmail.com
Continue reading Waiting for PostgreSQL 14 – Add idle_session_timeout.
How to limit rows to at most N per category – fix
A bit ago I wrote a blog post that was supposed to show how to keep number of rows in table to N per some category.
Unfortunately, I overlooked a problem related to concurrency.
Continue reading How to limit rows to at most N per category – fix
How to limit rows to at most N per category
The question was asked relatively recently on irc. And it proved to be non-trivial.
Surely, if you want to have one row per category (one address per user), it's trivial – add user_id column to addresses, make it unique, and we're done. But what if we want to allow five addresses? Or five thousands?
Let's see.
Continue reading How to limit rows to at most N per category
Waiting for PostgreSQL 14 – Rename wal_keep_segments to wal_keep_size.
On 20th of July 2020, Fujii Masao committed patch:
Rename wal_keep_segments to wal_keep_size. max_slot_wal_keep_size that was added in v13 and wal_keep_segments are the GUC parameters to specify how much WAL files to retain for the standby servers. While max_slot_wal_keep_size accepts the number of bytes of WAL files, wal_keep_segments accepts the number of WAL files. This difference of setting units between those similar parameters could be confusing to users. To alleviate this situation, this commit renames wal_keep_segments to wal_keep_size, and make users specify the WAL size in it instead of the number of WAL files. There was also the idea to rename max_slot_wal_keep_size to max_slot_wal_keep_segments, in the discussion. But we have been moving away from measuring in segments, for example, checkpoint_segments was replaced by max_wal_size. So we concluded to rename wal_keep_segments to wal_keep_size. Back-patch to v13 where max_slot_wal_keep_size was added. Author: Fujii Masao Reviewed-by: Álvaro Herrera, Kyotaro Horiguchi, David Steele Discussion: https://postgr.es/m/574b4ea3-e0f9-b175-ead2-ebea7faea855@oss.nttdata.com
Continue reading Waiting for PostgreSQL 14 – Rename wal_keep_segments to wal_keep_size.