Vijaykumar Jain cabecada

This recipe is a work in progress and has never been run as-is.

timeouts are in ms
lock timeout: in postgres, when a statement that wants a restrictive lock waits on another lock, other statements that want locks can't jump the queue. so even though the statement that is waiting might only take a very short amount of time, when it starts running, while it is waiting no other statements can begin. So we set the lock timeout pretty low and retry if we don't get it.
statement timeout: we set a short statement timeout before statements which do lock and which we expect to take a short amount of time, just in case something about our assumptions/understanding is wrong and the statement ends up taking a long time. if this happens the statement will bail early without causing harm, and we can investigate what is wrong with

	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

	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);

	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

	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)

	#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

	many times there are tables with 10s of references in other tables. when we try to delete data cascade, sometimes deletes are very slow.
	we have an index on the column of the primary table and some of the foreign tables, but we dont know why the delete is slow.
	here we try to make use of auto_explain with gucs to track plans of foreign tables to figure out slow deletes and how we fix them



	postgres@ubuntu:/tmp$ cat db1/postgresql.auto.conf \| grep -v '^#'
	port=5432
	session_preload_libraries = auto_explain
	auto_explain.log_min_duration = 0

	postgres@ubuntu:/tmp$ psql -p 5432
	psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.1))
	Type "help" for help.

	postgres=# create database demo;
	CREATE DATABASE
	postgres=# \c demo
	You are now connected to database "demo" as user "postgres".
	demo=# select * from pg_database;
	demo=# select oid,datname from pg_database;

	postgres=# create table t(col1 int primary key, col2 int, col3 int);
	CREATE TABLE
	postgres=# create table r(col4 int primary key, col1 int references t(col1) on update cascade on delete cascade);
	CREATE TABLE
	postgres=# insert into t select x,x,x from generate_series(1, 1000000) x;
	insert into r select col1, 1+ col1 % 10 from t;
	INSERT 0 1000000
	INSERT 0 1000000
	postgres=# create table to_be_deleted as select col1, null::int as col2 from t where col2 % 9 = 3;
	SELECT 111111