View Issue Details

IDProjectCategoryView StatusLast Update
0000448Pgpool-IIBugpublic2018-12-06 17:32
ReporteramarAssigned Tot-ishii 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionopen 
PlatformLinuxOSUbuntuOS Version14.04
Product Version3.7.5 
Target Version3.7.8Fixed in Version 
Summary0000448: pgpool holds the connections as idle_in_transaction which are timed out due to idle_in_transaction setting on backend nodes
DescriptionWe have a PG cluster with streaming replication running on PG pool 3.7.5 and PG 9.6.

idle_in_transaction_session_timeout is set on PG master to some 'x' ms.
So, the master timeout the queries which are idle_in_transaction for more than 'x' ms.
Even when the transaction on master is timed out.

1. ps -ef from pgpool still shows the idle_in_transactions whereas the pg_stat_activity on master has no traces since the transactions are already times out.

postgres 46211 113280 0 Nov26 ? 00:00:00 pgpool: username db xxx.xx.xxx.xxx(56796) idle in transaction
postgres 46225 113280 0 Nov26 ? 00:00:00 pgpool: username db xxx.xx.xxx.xxx(53464) idle in transaction
postgres 48762 113280 0 Nov26 ? 00:00:00 pgpool: username db xxx.xx.xxx.xxx(50008) idle in transaction
postgres 48768 113280 0 Nov26 ? 00:00:00 pgpool: username db xxx.xx.xxx.xxx(45138) idle in transaction
postgres 48828 113280 0 Nov26 ? 00:00:00 pgpool: username db xxx.xx.xxx.xxx(45562) idle in transaction
postgres 48912 113280 0 Nov26 ? 00:00:01 pgpool: username db xxx.xx.xxx.xxx(46498) idle in transaction

2. netstat -an | grep 5432 shows the connections in CLOSE_WAIT state

tcp 1 0 xxx.xx.xxx.xxx:15625 xxx.xx.xxx.xxx:5432 CLOSE_WAIT
tcp 1 0 xxx.xx.xxx.xxx:15868 xxx.xx.xxx.xxx:5432 CLOSE_WAIT
tcp 1 0 xxx.xx.xxx.xxx:63032 xxx.xx.xxx.xxx:5432 CLOSE_WAIT
tcp 1 0 xxx.xx.xxx.xxx:62956 xxx.xx.xxx.xxx:5432 CLOSE_WAIT

Shouldn't the pgpool close these connections when the transactions are timeout?




Steps To Reproduce1. Set the idle_in_transaction_session_timeout to some 'x' ms on pgmaster.
2. Initiate a connection to pg cluster via pg pool which ends up in idle_in_trasaction.
3. Wait till the transaction gets timed out on pgmaster.
4. Do a netstat and ps -ef to check the connection still exists even after the timeout.
TagsNo tags attached.

Activities

t-ishii

2018-11-28 11:33

developer   ~0002294

Thanks for the report. I was able to reproduce the problem. To fix this, we need to disconnect session from Pgpool-II to client just like the case "connection was terminated due to conflict with recovery" error. Attached patch does that.

idle_in_transaction.diff (2,671 bytes)
diff --git a/src/include/protocol/pool_proto_modules.h b/src/include/protocol/pool_proto_modules.h
index d8bd3a64..a4ab34d9 100644
--- a/src/include/protocol/pool_proto_modules.h
+++ b/src/include/protocol/pool_proto_modules.h
@@ -167,6 +167,7 @@ extern int	detect_deadlock_error(POOL_CONNECTION * master, int major);
 extern int	detect_serialization_error(POOL_CONNECTION * master, int major, bool unread);
 extern int	detect_active_sql_transaction_error(POOL_CONNECTION * backend, int major);
 extern int	detect_query_cancel_error(POOL_CONNECTION * backend, int major);
+extern int	detect_idle_in_transaction_sesion_timeout_error(POOL_CONNECTION * backend, int major);
 extern bool is_partition_table(POOL_CONNECTION_POOL * backend, Node *node);
 extern POOL_STATUS pool_discard_packet(POOL_CONNECTION_POOL * cp);
 extern void query_cache_register(char kind, POOL_CONNECTION * frontend, char *database, char *data, int data_len);
diff --git a/src/protocol/pool_process_query.c b/src/protocol/pool_process_query.c
index cb951bca..22dd5479 100644
--- a/src/protocol/pool_process_query.c
+++ b/src/protocol/pool_process_query.c
@@ -70,6 +70,7 @@
 #define QUERY_CANCEL_ERROR_CODE "57014"
 #define ADMIN_SHUTDOWN_ERROR_CODE "57P01"
 #define CRASH_SHUTDOWN_ERROR_CODE "57P02"
+#define IDLE_IN_TRANSACTION_SESSION_TIMEOUT_ERROR_CODE "25P03"
 
 static int	reset_backend(POOL_CONNECTION_POOL * backend, int qcnt);
 static char *get_insert_command_table_name(InsertStmt *node);
@@ -4286,6 +4287,19 @@ detect_query_cancel_error(POOL_CONNECTION * backend, int major)
 	return r;
 }
 
+
+int
+detect_idle_in_transaction_sesion_timeout_error(POOL_CONNECTION * backend, int major)
+{
+	int			r = extract_message(backend, IDLE_IN_TRANSACTION_SESSION_TIMEOUT_ERROR_CODE, major, 'E', true);
+
+	if (r == SPECIFIED_ERROR)
+		ereport(DEBUG1,
+				(errmsg("detecting idle in transaction session timeout error"),
+				 errdetail("idle in transaction session timeout error message from backend")));
+	return r;
+}
+
 /*
  * extract_message: extract specified error by an error code.
  * returns 0 in case of sucess or 1 in case of specified error.
@@ -4822,6 +4836,17 @@ SELECT_RETRY:
 
 				}
 
+				/*
+				 * connection was terminated due to idle_in_transaction_sesion_timeout expired
+				 */
+				r = detect_idle_in_transaction_sesion_timeout_error(CONNECTION(backend, i), MAJOR(backend));
+				if (r == SPECIFIED_ERROR)
+				{
+					ereport(FATAL,
+							(pool_error_code(IDLE_IN_TRANSACTION_SESSION_TIMEOUT_ERROR_CODE),
+							 errmsg("terminating connection due to idle-in-transaction timeout")));
+				}
+
 				/*
 				 * admin shutdown postmaster or postmaster goes down
 				 */
idle_in_transaction.diff (2,671 bytes)

amar

2018-11-28 13:24

reporter   ~0002295

Thanks for the quick response.
Do you have a plan to patch it in 3.7.8?

t-ishii

2018-11-28 13:36

developer   ~0002296

Yes, once you confirm the patch, basically we are going to apply it to all supported branches including 3.7. That means we are going to release 3.7.9 etc. in the future (the release schedule is not yet decided though).

t-ishii

2018-12-06 17:32

developer   ~0002306

I have pushed the patch to all supported branches including 3.7 stable. So the patch will appear in the next minor releases.

Issue History

Date Modified Username Field Change
2018-11-28 00:02 amar New Issue
2018-11-28 09:44 administrator Assigned To => t-ishii
2018-11-28 09:44 administrator Status new => assigned
2018-11-28 11:33 t-ishii File Added: idle_in_transaction.diff
2018-11-28 11:33 t-ishii Note Added: 0002294
2018-11-28 11:34 t-ishii Status assigned => feedback
2018-11-28 11:34 t-ishii Target Version => 3.7.8
2018-11-28 13:24 amar Note Added: 0002295
2018-11-28 13:24 amar Status feedback => assigned
2018-11-28 13:36 t-ishii Note Added: 0002296
2018-12-06 17:32 t-ishii Note Added: 0002306
2018-12-06 17:32 t-ishii Status assigned => resolved