Document how to use `PRAGMA temp_store` to avoid errors when running VACUUM against huge databases

I'm trying to figure out a way to get the table.extract() method to complete successfully -- I'm not sure if maybe the cause (and a possible solution) of this on Ubuntu Server 22.04 is to adjust some of the PRAGMA values within SQLite itself ... on another Linux system (PopOS), using this method on this same database appears to work just fine.

Here's the bit that's causing the error, and the resulting error output:

# combine these columns into 1 table "bib_properties" :
# best_title
# bib_level_code
# mat_type
# material_code
# best_author
db["circ_trans"].extract(
    ["best_title", "bib_level_code", "mat_type", "material_code", "best_author"], 
    table="bib_properties", 
    fk_column="bib_properties_id"
)

db["circ_trans"].extract(
    ["call_number"], 
    table="call_number", 
    fk_column="call_number_id",
    rename={"call_number": "value"}
)

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Input In [17], in <cell line: 7>()
      1 # combine these columns into 1 table "bib_properties" :
      2 # best_title
      3 # bib_level_code
      4 # mat_type
      5 # material_code
      6 # best_author
----> 7 db["circ_trans"].extract(
      8     ["best_title", "bib_level_code", "mat_type", "material_code", "best_author"], 
      9     table="bib_properties", 
     10     fk_column="bib_properties_id"
     11 )
     13 db["circ_trans"].extract(
     14     ["call_number"], 
     15     table="call_number", 
     16     fk_column="call_number_id",
     17     rename={"call_number": "value"}
     18 )

File ~/jupyter/venv/lib/python3.10/site-packages/sqlite_utils/db.py:1764, in Table.extract(self, columns, table, fk_column, rename)
   1761         column_order.append(c.name)
   1763 # Drop the unnecessary columns and rename lookup column
-> 1764 self.transform(
   1765     drop=set(columns),
   1766     rename={magic_lookup_column: fk_column},
   1767     column_order=column_order,
   1768 )
   1770 # And add the foreign key constraint
   1771 self.add_foreign_key(fk_column, table, "id")

File ~/jupyter/venv/lib/python3.10/site-packages/sqlite_utils/db.py:1526, in Table.transform(self, types, rename, drop, pk, not_null, defaults, drop_foreign_keys, column_order)
   1524 with self.db.conn:
   1525     for sql in sqls:
-> 1526         self.db.execute(sql)
   1527     # Run the foreign_key_check before we commit
   1528     if pragma_foreign_keys_was_on:

File ~/jupyter/venv/lib/python3.10/site-packages/sqlite_utils/db.py:465, in Database.execute(self, sql, parameters)
    463     return self.conn.execute(sql, parameters)
    464 else:
--> 465     return self.conn.execute(sql)

OperationalError: database or disk is full

This database is about 17G in total size, so I'm assuming the error is coming from the vacuum ... where i'm assuming it's maybe trying to do the temp storage in a location that doesn't have sufficient room. The disk space is more than ample on the host in question (1.8T is free in the directory where the sqlite db resides) The /tmp directory however is limited on a smaller disk associated with the OS

I'm trying to think if there's a way to set the PRAGMA temp_store or maybe if it's temp_store_directory that I'm after ... to use the same local directory of where the file is located (maybe this is a property of the version of sqlite on the system?)

# SET the temp file store to be a file ...
print(db.execute('PRAGMA temp_store').fetchall())
print(db.execute('PRAGMA temp_store=FILE').fetchall())

print(db.execute('PRAGMA temp_store').fetchall())

# the users home directory ...
print(db.execute("PRAGMA temp_store_directory='/home/plchuser/'").fetchall())
print(db.execute("PRAGMA sqlite3_temp_directory='/home/plchuser/'").fetchall())

print(db.execute("PRAGMA temp_store_directory").fetchall())
print(db.execute("PRAGMA sqlite3_temp_directory").fetchall())

[(1,)]
[]
[(1,)]
[]
[]
[('/home/plchuser/',)]
[]

Here's the docs on the Temporary File Storage Locations
https://www.sqlite.org/tempfiles.html

Start with SQLite's Temporary File Storage Locations documentation and the reported table.extract() traceback, then verify how PRAGMA temp_store and the temporary-directory settings behave for a large VACUUM. Done means documenting a supported way to place temporary storage on a suitable filesystem, including any version or platform limitations.