cabecada

postgres=# set client_min_messages TO debug1;

SET

postgres=# create table t(col1 int primary key);

DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"

DEBUG: building index "t_pkey" on table "t" serially

cabecada

sudo apt-get -y -q install libipc-run-perl lcov build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache pkg-config libicu-dev


sudo mkdir /opt/postgresql/17
sudo chown -R postgres:postgres /opt/postgresql/17
cd postgres
./configure --prefix=/opt/postgresql/17 --enable-debug --enable-cassert --enable-tap-tests --enable-coverage CFLAGS="-ggdb3 -O0"
make -j4 install

cabecada

https://jumpcloud.com/blog/how-to-upgrade-ubuntu-20-04-to-ubuntu-22-04
https://askubuntu.com/questions/1098480/attempting-to-upgrade-from-ubuntu-server-16-04-to-18
https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/13273
https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/
https://github.com/ardentperf/glibc-unicode-sorting


2.27 > 2.31 
index corruption

cabecada

The Ultimate PostgreSQL Guide for Software Engineers

Table of Contents

• The Ultimate PostgreSQL Guide for Software Engineers
  • Table of Contents
  • Introduction to PostgreSQL
    • What is PostgreSQL?
    • Why PostgreSQL Stands Out
    • PostgreSQL vs. Other Database Systems
    • When to Choose PostgreSQL
• Understanding Relational Database Concepts

cabecada

https://jumpcloud.com/blog/how-to-upgrade-ubuntu-20-04-to-ubuntu-22-04
https://askubuntu.com/questions/1098480/attempting-to-upgrade-from-ubuntu-server-16-04-to-18
https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/13273
https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/

https://www.youtube.com/watch?v=0E6O-V8Jato
Sorting Out glibc Collation Challenges: Joe Conway - PGCon 2023


glibc upgrades from 2.27 to 2.31, which changes the ordering

cabecada

https://www.enterprisedb.com/blog/partitioning-large-table-without-long-running-lock

create table orig_table
( id int generated always as identity not null,
  data float default random()
);

create index orig_data_index on orig_table(data);
create index orig_id_index on orig_table(id);

cabecada

INSTALL VIA
    apt-get -- 10.14-0ubuntu0.18.04.1
    homebrew -- stable 12.4
        brew install postgresql@11
    aws RDS -- major version 12
    aws RDS Aurora
        single-master 9.6.18 10.13 11.8
        global 10.13 11.8

cabecada

postgres=# create table t(col1 int, col2 int);
CREATE TABLE
Time: 3.670 ms
postgres=# insert into t select x,x from generate_series(1, 1000000) x;
INSERT 0 1000000
Time: 3495.578 ms (00:03.496)
postgres=# select 't'::regclass::oid;
  oid
-------
 16395

cabecada

postgresql developer options are a list of configs users can play with in the sandbox environment for debugging.
i used it to study various locks taken when a statement is executed on an object
just to get things started, there is a GUC where one can enable lock tracing on a particular table only
this is cool as i dont care what else is going on in the system otherwise, but i want to learn what locks are taken by what sql statements
infact one can make use of https://pglocks.org/ to run the various statements on this site (by hussein) and understand the locks taken
these would be very useful when one is doing migrations on large objects which are blocking in nature

https://github.com/ankane/strong_migrations (is a good collection of those cases and the links to the blogs)

cabecada

#this is mimicking https://github.com/postgres/postgres/blob/a68159ff2b32f290b1136e2940470d50b8491301/src/test/modules/xid_wraparound/t/002_limits.pl
#but many times users dont look at source code, so these examples are missed

```
./configure --prefix=/opt/postgresql  --with-openssl   --enable-debug  --enable-profiling --enable-cassert --enable-tap-tests  CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG -DBTREE_BUILD_STATS -DWAL_DEBUG "
make
cd /home/ubuntu/github/postgres/src/test/modules/xid_wraparound
make check PG_TEST_EXTRA='xid_wraparound'
sudo make install
sudo chown -R postgres:postgres /opt/postgresql