Temp table exists after session disconnects from pgbouncer in Pivotal Greenplum
search cancel

Temp table exists after session disconnects from pgbouncer in Pivotal Greenplum

book

Article ID: 296326

calendar_today

Updated On:

Products

VMware Tanzu Greenplum

Issue/Introduction

When using pgbouncer to connect to Greenplum DB, you may find the temp table is not removed after closing the session with the '\q' command, as below.

Connect to DB via pgbouncer and create a temp table: 
# create temp table test_temp as select now();
# \d test_temp
                   Table "pg_temp_8539.test_temp"
 Column |           Type           | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
 now    | timestamp with time zone |           |          |

Quit the connection and restart a new one and observe the temp table is still there.

# \q
# psql -h 192.168.6.201 -U gpadmin
# \d test_temp
                   Table "pg_temp_8539.test_temp"
 Column |           Type           | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
 now    | timestamp with time zone |           |          |

Note: This is an expected behavior when the pgbouncer uses "transaction pooling" mode.


Environment

Product Version: 6.4

Resolution

There is a setting in pgbouncer called server_reset_query, which has a default value of "DISCARD ALL"

The server_reset_query is a query sent to server on connection release before making it available to other clients. At that moment no transaction is in progress so it should not include ABORT or ROLLBACK.

By default, when using modes such as session pooling,  the client will send the "server_reset_query" to to DB at the time you exit the psql client (default is "DISCARD ALL"). This means it releases all temporary resources associated with the current session and resets the session to its initial state. As a result, all temporary tables created in this connection will be removed. 

For more details regarding the discard command, please refer to this link: https://gpdb.docs.pivotal.io/6-4/ref_guide/sql_commands/DISCARD.html

However, when transaction pooling mode is used, the server_reset_query is not used. This means it will not run "DISCARD ALL" when calling '\q' and the temp table will remain in the system.  

See this link for more details: https://www.pgbouncer.org/config.html 
server_reset_query

Query sent to server on connection release, before making it available to other clients. At that moment no transaction is in progress so it should not include ABORT or ROLLBACK.

The query is supposed to clean any changes made to the database session so that the next client gets the connection in a well-defined state. The default is DISCARD ALL which cleans everything, but that leaves the next client no pre-cached state. It can be made lighter, e.g. DEALLOCATE ALL to just drop prepared statements, if the application does not break when some state is kept around.

When transaction pooling is used, the server_reset_query is not used, as clients must not use any session-based features as each transaction ends up in a different connection and thus gets a different session state.

Default: DISCARD ALL

Please refer to the below example:

SQL to be executed: 
# select 'i am old session';  
# \q
# select 'i am new session';
Run the query in session pooling mode. Observe that the logs shows it will run "DISCARD ALL" when you quit the connection: 
2020-04-08 13:35:30.708840 CST,"gpadmin","gpadmin",p128448,th1425123456,"192.168.6.201","53970",2020-04-08 13:35:30 CST,0,con8537,cmd1,seg-1,,,,sx1,"LOG","00000","statement: SET application_name='psql';",,,,,,"SET application_name='psql';",0,,"postgres.c",1654,
2020-04-08 13:35:30.718326 CST,"gpadmin","gpadmin",p128448,th1425123456,"192.168.6.201","53970",2020-04-08 13:35:30 CST,0,con8537,cmd2,seg-1,,,,sx1,"LOG","00000","statement: select 'i am old session';",,,,,,"select 'i am old session';",0,,"postgres.c",1654,
2020-04-08 13:35:31.429235 CST,"gpadmin","gpadmin",p128448,th1425123456,"192.168.6.201","53970",2020-04-08 13:35:30 CST,0,con8537,cmd4,seg-1,,,,sx1,"LOG","00000","statement: DISCARD ALL",,,,,,"DISCARD ALL",0,,"postgres.c",1654,
2020-04-08 13:35:43.917461 CST,"gpadmin","gpadmin",p128448,th1425123456,"192.168.6.201","53970",2020-04-08 13:35:30 CST,0,con8537,cmd5,seg-1,,,,sx1,"LOG","00000","statement: SET application_name='psql';",,,,,,"SET application_name='psql';",0,,"postgres.c",1654,
2020-04-08 13:35:43.918494 CST,"gpadmin","gpadmin",p128448,th1425123456,"192.168.6.201","53970",2020-04-08 13:35:30 CST,0,con8537,cmd6,seg-1,,,,sx1,"LOG","00000","statement: select 'i am new session';",,,,,,"select 'i am new session';",0,,"postgres.c",1654,
Run the query in transaction pooling mode. Observe that the logs shows there is no "DISCARD ALL"  issued when you quit the connection: 
2020-04-08 13:37:35.750477 CST,"gpadmin","gpadmin",p128555,th1425123456,"192.168.6.201","53972",2020-04-08 13:37:27 CST,0,con8538,cmd1,seg-1,,,,sx1,"LOG","00000","statement: SET application_name='psql';",,,,,,"SET application_name='psql';",0,,"postgres.c",1654,
2020-04-08 13:37:35.771497 CST,"gpadmin","gpadmin",p128555,th1425123456,"192.168.6.201","53972",2020-04-08 13:37:27 CST,0,con8538,cmd2,seg-1,,,,sx1,"LOG","00000","statement: select 'i am old session';",,,,,,"select 'i am old session';",0,,"postgres.c",1654,
2020-04-08 13:37:44.829535 CST,"gpadmin","gpadmin",p128555,th1425123456,"192.168.6.201","53972",2020-04-08 13:37:27 CST,0,con8538,cmd4,seg-1,,,,sx1,"LOG","00000","statement: select 'i am new session';",,,,,,"select 'i am new session';",0,,"postgres.c",1654,


Powered by Wolken Software