Wow, thanks for sharing your experience. What kind of connection pooling are we talking about? some connection pools implement a DISCARD ALL statement after a session close, that may help if possible to configure.
On Wed, Mar 13, 2019 at 4:21 AM Jahwan Kim <blgl13....@gmail.com> wrote: > 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 > > > > > -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
