Pg 15.4
I'm dropping the large table "bar" in a test database, and it's taking a long time. Thus, I thought I'd watch it deleting files.
To my surprise, though, the files that store the index are growing. What's up with that?
What does pg_locks show ?
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS blocked_by
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON
blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype =
blocked_locks.locktype AND NOT blocking_locks.database IS
DISTINCT FROM blocked_locks.database
AND NOT blocking_locks.relation IS DISTINCT FROM
blocked_locks.relation AND NOT blocking_locks.page IS DISTINCT
FROM blocked_locks.page AND NOT blocking_loc
ks.tuple IS DISTINCT FROM blocked_locks.tuple AND NOT
blocking_locks.virtualxid IS DISTINCT FROM
blocked_locks.virtualxid AND NOT blocking_locks.transactioni
d IS DISTINCT FROM blocked_locks.transactionid AND NOT
blocking_locks.classid IS DISTINCT FROM blocked_locks.classid
AND NOT blocking_locks.objid IS DISTINCT
FROM blocked_locks.objid AND NOT blocking_locks.objsubid IS
DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid
<> blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON
blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
foo=# select * from pg_class where oid = 41533;
-[ RECORD 1 ]-------+----------
oid | 41533
relname | i_bar_su1
relnamespace | 2200
$ pwd
/var/lib/pgsql/15/data/base/16482
$ la -aFl 41533*
-rw------- 1 postgres postgres 1073741824 2023-11-16 11:15:06 41533
-rw------- 1 postgres postgres 189792256 2023-11-16 11:24:15 41533.1
Five minutes later, 41533.1 is 4.3MB larger.
$ dir 41533*
-rw------- 1 postgres postgres 1073741824 2023-11-16 11:15:06 41533
-rw------- 1 postgres postgres 194101248 2023-11-16 11:29:44 41533.1
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt
