Will there be orphaned files after a session is terminated?
When creating a table or database, the database stops immediately or breaks down for some reasons. In this case, the connected session is terminated. Will there be orphaned relation files after the database starts up?
After a session is terminated, there will none orphaned files.
Redrock Postgres records undo logs when creating table files or data directories. If the connected session is terminated due to certain reasons, the database checks the aborted transactions after restarted. The database rolls back the aborted transactions and clears the remaining table files and directories based on the recorded undo logs.
Below we start a transaction block, create a table
t in the transaction, insert some data.
BEGIN; CREATE TABLE t (id integer, name text); INSERT INTO t (id, name) SELECT i, repeat('Pg', 32) FROM generate_series(1, 1000000) AS s(i);
Let’s look at the file path of the table
SELECT pg_relation_filepath('t'); pg_relation_filepath ---------------------- base/11779/16390
Let’s stop the database immediately, simulate the database failure with abnormal downtime, and restart the database service:
$ pg_ctl stop --mode=immediate $ pg_ctl start
After the database restarts, the abnormally aborted transaction is rolled back, and the remaining table files are cleaned up according to the undo log recorded by the transaction. (Note: The remaining table files are not immediately cleaned up when the transaction is rolled back, and the actual cleanup of the table files is done during the checkpoint process)
$ ls data/base/11779/16390 ls: cannot access data/base/11779/16390: No such file or directory