{"url":"https://api.github.com/repos/simonw/sqlite-utils/issues/430","repository_url":"https://api.github.com/repos/simonw/sqlite-utils","labels_url":"https://api.github.com/repos/simonw/sqlite-utils/issues/430/labels{/name}","comments_url":"https://api.github.com/repos/simonw/sqlite-utils/issues/430/comments","events_url":"https://api.github.com/repos/simonw/sqlite-utils/issues/430/events","html_url":"https://github.com/simonw/sqlite-utils/issues/430","id":1224112817,"node_id":"I_kwDOCGYnMM5I9nqx","number":430,"title":"Document how to use `PRAGMA temp_store` to avoid errors when running VACUUM against huge databases","user":{"login":"rayvoelker","id":9308268,"node_id":"MDQ6VXNlcjkzMDgyNjg=","avatar_url":"https://avatars.githubusercontent.com/u/9308268?v=4","gravatar_id":"","url":"https://api.github.com/users/rayvoelker","html_url":"https://github.com/rayvoelker","followers_url":"https://api.github.com/users/rayvoelker/followers","following_url":"https://api.github.com/users/rayvoelker/following{/other_user}","gists_url":"https://api.github.com/users/rayvoelker/gists{/gist_id}","starred_url":"https://api.github.com/users/rayvoelker/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rayvoelker/subscriptions","organizations_url":"https://api.github.com/users/rayvoelker/orgs","repos_url":"https://api.github.com/users/rayvoelker/repos","events_url":"https://api.github.com/users/rayvoelker/events{/privacy}","received_events_url":"https://api.github.com/users/rayvoelker/received_events","type":"User","user_view_type":"public","site_admin":false},"labels":[{"id":993377885,"node_id":"MDU6TGFiZWw5OTMzNzc4ODU=","url":"https://api.github.com/repos/simonw/sqlite-utils/labels/help%20wanted","name":"help wanted","color":"008672","default":true,"description":"Extra attention is needed"},{"id":1405404904,"node_id":"MDU6TGFiZWwxNDA1NDA0OTA0","url":"https://api.github.com/repos/simonw/sqlite-utils/labels/documentation","name":"documentation","color":"be3ddb","default":true,"description":""}],"state":"open","locked":false,"assignees":[],"milestone":null,"comments":2,"created_at":"2022-05-03T13:33:58Z","updated_at":"2022-06-14T23:26:37Z","closed_at":null,"assignee":null,"author_association":"NONE","active_lock_reason":null,"sub_issues_summary":{"total":0,"completed":0,"percent_completed":0},"issue_dependencies_summary":{"blocked_by":0,"total_blocked_by":0,"blocking":0,"total_blocking":0},"body":"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.\r\n\r\nHere's the bit that's causing the error, and the resulting error output:\r\n```python\r\n# combine these columns into 1 table \"bib_properties\" :\r\n# best_title\r\n# bib_level_code\r\n# mat_type\r\n# material_code\r\n# best_author\r\ndb[\"circ_trans\"].extract(\r\n    [\"best_title\", \"bib_level_code\", \"mat_type\", \"material_code\", \"best_author\"], \r\n    table=\"bib_properties\", \r\n    fk_column=\"bib_properties_id\"\r\n)\r\n\r\ndb[\"circ_trans\"].extract(\r\n    [\"call_number\"], \r\n    table=\"call_number\", \r\n    fk_column=\"call_number_id\",\r\n    rename={\"call_number\": \"value\"}\r\n)\r\n```\r\n\r\n```python\r\n---------------------------------------------------------------------------\r\nOperationalError                          Traceback (most recent call last)\r\nInput In [17], in <cell line: 7>()\r\n      1 # combine these columns into 1 table \"bib_properties\" :\r\n      2 # best_title\r\n      3 # bib_level_code\r\n      4 # mat_type\r\n      5 # material_code\r\n      6 # best_author\r\n----> 7 db[\"circ_trans\"].extract(\r\n      8     [\"best_title\", \"bib_level_code\", \"mat_type\", \"material_code\", \"best_author\"], \r\n      9     table=\"bib_properties\", \r\n     10     fk_column=\"bib_properties_id\"\r\n     11 )\r\n     13 db[\"circ_trans\"].extract(\r\n     14     [\"call_number\"], \r\n     15     table=\"call_number\", \r\n     16     fk_column=\"call_number_id\",\r\n     17     rename={\"call_number\": \"value\"}\r\n     18 )\r\n\r\nFile ~/jupyter/venv/lib/python3.10/site-packages/sqlite_utils/db.py:1764, in Table.extract(self, columns, table, fk_column, rename)\r\n   1761         column_order.append(c.name)\r\n   1763 # Drop the unnecessary columns and rename lookup column\r\n-> 1764 self.transform(\r\n   1765     drop=set(columns),\r\n   1766     rename={magic_lookup_column: fk_column},\r\n   1767     column_order=column_order,\r\n   1768 )\r\n   1770 # And add the foreign key constraint\r\n   1771 self.add_foreign_key(fk_column, table, \"id\")\r\n\r\nFile ~/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)\r\n   1524 with self.db.conn:\r\n   1525     for sql in sqls:\r\n-> 1526         self.db.execute(sql)\r\n   1527     # Run the foreign_key_check before we commit\r\n   1528     if pragma_foreign_keys_was_on:\r\n\r\nFile ~/jupyter/venv/lib/python3.10/site-packages/sqlite_utils/db.py:465, in Database.execute(self, sql, parameters)\r\n    463     return self.conn.execute(sql, parameters)\r\n    464 else:\r\n--> 465     return self.conn.execute(sql)\r\n\r\nOperationalError: database or disk is full\r\n```\r\n\r\nThis 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\r\n\r\nI'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?) \r\n\r\n```python\r\n# SET the temp file store to be a file ...\r\nprint(db.execute('PRAGMA temp_store').fetchall())\r\nprint(db.execute('PRAGMA temp_store=FILE').fetchall())\r\n\r\nprint(db.execute('PRAGMA temp_store').fetchall())\r\n\r\n# the users home directory ...\r\nprint(db.execute(\"PRAGMA temp_store_directory='/home/plchuser/'\").fetchall())\r\nprint(db.execute(\"PRAGMA sqlite3_temp_directory='/home/plchuser/'\").fetchall())\r\n\r\nprint(db.execute(\"PRAGMA temp_store_directory\").fetchall())\r\nprint(db.execute(\"PRAGMA sqlite3_temp_directory\").fetchall())\r\n```\r\n```text\r\n[(1,)]\r\n[]\r\n[(1,)]\r\n[]\r\n[]\r\n[('/home/plchuser/',)]\r\n[]\r\n```\r\n\r\nHere's the docs on the Temporary File Storage Locations \r\nhttps://www.sqlite.org/tempfiles.html","closed_by":null,"reactions":{"url":"https://api.github.com/repos/simonw/sqlite-utils/issues/430/reactions","total_count":0,"+1":0,"-1":0,"laugh":0,"hooray":0,"confused":0,"heart":0,"rocket":0,"eyes":0},"timeline_url":"https://api.github.com/repos/simonw/sqlite-utils/issues/430/timeline","performed_via_github_app":null,"state_reason":null,"pinned_comment":null}