Re: PostgreSQL temp table blues

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(dot)net(at)gmail(dot)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/