Re: [External] Re: PostgreSQL temp table blues - Mailing list pgsql-general
| From | Vijaykumar Jain |
|---|---|
| Subject | Re: [External] Re: PostgreSQL temp table blues |
| Date | |
| Msg-id | CAE7uO5gG-UavdMMdm1Sppf4KmD=yCkduUv_r=tf5xe9f+dgLTA@mail.gmail.com Whole thread Raw |
| In response to | Re: PostgreSQL temp table blues (Rene Romero Benavides <rene.romero.b@gmail.com>) |
| List | pgsql-general |
May be I am wrong here, but is it not the classic case of connections open too long idle in TX and xid wraparound ?
How is connection pool (and which one ?)
adding to the woes?
I mean the same can be a problem with direct connections too right ?
We use pgbouncer with mostly TX level pooling which closes the connection after a commit or a rollback.
We have both idle TX timeouts at front end and back end of the pgbouncer setting.
And we have monitoring of bloat, idle in TX sessions and pgbouncer connections.
We have dbs of various sizes but all less than 1TB.
So I do not know if I am comparing with the same set of resources, but just that we use temp tables with connection pooling but with the right monitoring and reasonable constraints and we yet to bite that bullet.
So I guess we’ll add to the monitoring something like this too
But I guess you have had a long day, but thanks for sharing.
On Thu, 14 Mar 2019 at 11:45 AM Rene Romero Benavides <rene.romero.b@gmail.com> wrote:
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.romero.b@gmail.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.net@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----
Vijay
pgsql-general by date: