Question: Why does pg_repack change the current tablespace to pg_default, by default?

[mridude23]

Just noticed that when running pg_repack on an on-prem server, it changes the current tablespace to pg_default.

Is this by design?

It's not an issue running the command with --tablespace=TBLSPC attached, but it seems counterintuitive, given the manual wording:
-s, --tablespace=TBLSPC move repacked tables to a new tablespace

I would expect that by default it will use the current tablespace, and only if I use it then the tablespace would change.

[MichaelDBA]

Using the latest version of pg_repack, 1.4.8, on OnPrem PG v13 and v14, I am not able to reproduce your wrong results.

# \d sample.usrtblspace_tbl
           Table "sample.usrtblspace_tbl"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 akey    | integer |           | not null |
 astring | text    |           |          |
Indexes:
    "usrtblspace_tbl_pkey" PRIMARY KEY, btree (akey)
Tablespace: "usrtblspc_ts"

pg_repack -p 5414 -d mydb -U postgres  -t sample.usrtblspace_tbl -D


# \d sample.usrtblspace_tbl
           Table "sample.usrtblspace_tbl"
 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 akey    | integer |           | not null |
 astring | text    |           |          |
Indexes:
    "usrtblspace_tbl_pkey" PRIMARY KEY, btree (akey)
Tablespace: "usrtblspc_ts"

Please use the latest version of pg_repack.

[MichaelDBA]

I'm sorry, I think I was wrong. You were not just talking about repacking a table in a user-defined tablespace, but a user-defined tablespace that is the default tablespace. This is still an ongoing issue. See #363.

[mridude23]

Not only this, but when repacking an index while using --tablespace=abc, it seems that at first it will use the partition where pg_default is defined, to repack the index and only at the end it uses the actual partition where tablespace abc is defined.
Example:

• 3 partitions: /, /data and /data2
• pg data directory is on /
• database has tablespace on /data
• moving an index, for example to a new tablespace on /data2, I can see that it firsts uses the / partition as a temporary storage before actually writing it on /data2, instead of directly using /data2.

Am I missing something?