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

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

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

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

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

https://aws.amazon.com/blogs/database/implement-an-early-warning-system-for-transaction-id-wraparound-in-amazon-rds-for-postgresql/

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

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

--

Regards,
Vijay