Re: PostgreSQL temp table blues

In conjunction with some parameter to renew idle connections and those that
have been opened for too long will help you prevent this in the future,
this also helps prevent server processes from becoming too big memory wise.

On Wed, Mar 13, 2019 at 4:32 PM Rene Romero Benavides <
rene(dot)romero(dot)b(at)gmail(dot)com> wrote:

> 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/
>
>

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/