Hi all,
I'd like to share my (painful) experience, in which temp tables caused PostgreSQL shutdown.
TL;DR. Do not use temp tables in PostgreSQL with connection pool.
* My app uses connection pool AND temp tables, with default setting of ON COMMIT PRESERVE ROWS.
* I found out later that autovacuum doesn't deal with temp tables.
* The database ages as long as the connection is not closed.
* So when the database age reaches XID STOP LIMIT, the database refuses to process any new transaction requests, saying "database is not accepting commands to avoid wraparound data
loss... HINT: Stop the postmaster and use a
standalone backend to vacuum that database. "
After reading the docs, I expected this much. What happens after this surprised me.
* Now the database needs to be shutdown. When shutting down, it tries to remove temp tables (of course), but since the database is not accepting any commands, ... The temp tables are then ORPHANED, although there was no database crash!
* Because of these orphan temp tables, vacuuming the database in single mode won't work, as suggested by HINT. The orphaned temp tables must be manually dropped in single mode, and only then the database can be vacuumed back to normal state. Without dropping temp tables, vacuuming just takes (quite possibly a long) time and do (almost) nothing.
Well, that's all. All of the above facts are documented, albeit tersely. If anybody I know ask me about temp tables in PostgreSQL, I'd just say "DON'T."
Best Regards,
Jahwan
