Viswamitra's blog

We have an internal application using PostgreSQL as backend with application connection pooling. After few days we started seeing a peculiar issue where the disk allocated for database is getting full and application is running too slow. After restarting the application the space got released and application is working as expected.

After checking the tables which are consuming more space we found that temporary tables are consuming more space(to know more about temporary tables visit this). After checking it further, temporary tables will be dropped once the session is closed but as we are using the application connection pooling which is not managed properly causing this issue.

Before closing or establishing the connection it is recommended to execute the “DISCARD ALL” which will drop all the temporary tables or resources allocated in that session.

test_temp=# create temporary table test_emp(id int, name varchar);
 CREATE TABLE
 test_temp=#
 test_temp=# insert into test_emp values (1,'Viswamitra');
 INSERT 0 1
 test_temp=#
 test_temp=# select * from test_emp ;
  id |    name
 ----+------------
   1 | Viswamitra
 (1 row)
 test_temp=#
 test_temp=# discard all;
 DISCARD ALL
 test_temp=# select * from test_emp ;
 ERROR:  relation "test_emp" does not exist
 LINE 1: select * from test_emp ;
                       ^
 test_temp=#

In the tomcat connection pooling configuration (validationquery) set the command “discard all” which will release the resources occupied by the session before returning the connection back to the pool.

Reference:

PostgreSQL: Documentation: 11: DISCARD