Thread: idle_in_transaction_session_timeout not getting log in PG v12.2

This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service

On 5/19/20 2:08 AM, Ishan Joshi wrote:
> Hi Team,
> 
> I have set idle_in_transaction_session_timeout on user level and it is 
> set for 5 min. I can see after 5 min the session with “idle in 
> transaction” state got killed and it log the details in postgresql log 
> file with “FATAL: terminating connection due to idle-in-transaction 
> timeout” in v11.5
> 
> But I have testing the same in v12.2, The session getting killed with 
> state “idle in transaction”  but the log is not generated in postgresql 
> log file.
> 
> Is there any specific changes related to this for v12.2 or any 
> additional parameters need to set for generating logs.

Looking at code in ~/backend/tcop/postgres.c it has not changed.

Are you reusing the same *.conf in 12 as you did in 11?

What are your logging settings?

> 
> Thanks & Regards,
> 
> Ishan Joshi
> 
> *This email and the information contained herein is proprietary and 
> confidential and subject to the Amdocs Email Terms of Service, which you 
> may review at**https://www.amdocs.com/about/email-terms-of-service*
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service

On 5/19/20 7:12 AM, Ishan Joshi wrote:
> Thank Adrian for response.
> 
> Yes I have the same *.conf file as v11.5 in fact I have copied the same 
> conf files in v12.2
> 
> Logging parameter are as follows

I'm not seeing anything obviously wrong with below.

Are you sure you are looking at the correct log?

> 
> *Parameter*
> 
>     
> 
> *Values*
> 
> log_autovacuum_min_duration
> 
>     
> 
>   1s
> 
>   log_checkpoints
> 
>     
> 
>   on
> 
>   log_connections
> 
>     
> 
>   on
> 
>   log_destination
> 
>     
> 
>   stderr
> 
>   log_directory
> 
>     
> 
>   log
> 
>   log_disconnections
> 
>     
> 
>   on
> 
>   log_duration
> 
>     
> 
>   off
> 
>   log_error_verbosity
> 
>     
> 
>   default
> 
>   log_executor_stats
> 
>     
> 
>   off
> 
>   log_file_mode
> 
>     
> 
> 600
> 
> log_filename
> 
>     
> 
>   postgresql-%Y-%m-%d_%H%M%S.log
> 
> log_hostname
> 
>     
> 
>   off
> 
>   log_line_prefix
> 
>     
> 
>   %m %d %u %l %s pid:%p xid:%x ip:%h
> 
>   log_lock_waits
> 
>     
> 
>   on
> 
>   log_min_duration_statement
> 
>     
> 
> -1
> 
> log_min_error_statement
> 
>     
> 
>   error
> 
>   log_min_messages
> 
>     
> 
>   warning
> 
>   log_parser_stats
> 
>     
> 
>   off
> 
>   log_planner_stats
> 
>     
> 
>   off
> 
>   log_replication_commands
> 
>     
> 
>   on
> 
>   log_rotation_age
> 
>     
> 
>   1h
> 
>   log_rotation_size
> 
>     
> 
> 0
> 
> log_statement
> 
>     
> 
>   none
> 
>   log_statement_stats
> 
>     
> 
>   off
> 
>   log_temp_files
> 
>     
> 
> 0
> 
> log_transaction_sample_rate
> 
>     
> 
> 0
> 
> log_truncate_on_rotation
> 
>     
> 
>   on
> 
>   logging_collector
> 
>     
> 
>   on
> 
> Thanks & Regards,
> 
> Ishan Joshi
> 
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Tuesday, May 19, 2020 7:28 PM
> To: Ishan Joshi <Ishan.Joshi@amdocs.com>; pgsql-general@postgresql.org
> Subject: Re: idle_in_transaction_session_timeout not getting log in PG v12.2
> 
> On 5/19/20 2:08 AM, Ishan Joshi wrote:
> 
>  > Hi Team,
> 
>  >
> 
>  > I have set idle_in_transaction_session_timeout on user level and it is
> 
>  > set for 5 min. I can see after 5 min the session with “idle in
> 
>  > transaction” state got killed and it log the details in postgresql log
> 
>  > file with “FATAL: terminating connection due to idle-in-transaction
> 
>  > timeout” in v11.5
> 
>  >
> 
>  > But I have testing the same in v12.2, The session getting killed with
> 
>  > state “idle in transaction”  but the log is not generated in
> 
>  > postgresql log file.
> 
>  >
> 
>  > Is there any specific changes related to this for v12.2 or any
> 
>  > additional parameters need to set for generating logs.
> 
> Looking at code in ~/backend/tcop/postgres.c it has not changed.
> 
> Are you reusing the same *.conf in 12 as you did in 11?
> 
> What are your logging settings?
> 
>  >
> 
>  > Thanks & Regards,
> 
>  >
> 
>  > Ishan Joshi
> 
>  >
> 
>  > *This email and the information contained herein is proprietary and
> 
>  > confidential and subject to the Amdocs Email Terms of Service, which
> 
>  > you may review
> 
>  > at**https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2F
> 
>  > 
> www.amdocs.com%2Fabout%2Femail-terms-of-service*&data=02%7C01%7CIs 
> <http://www.amdocs.com%2Fabout%2Femail-terms-of-service*&data=02%7C01%7CIs>
> 
>  > han.Joshi%40amdocs.com%7C7d8dce6a7c7d4349a7ec08d7fbfcccd5%7Cc8eca3ca12
> 
>  > 7646d59d9da0f2a028920f%7C0%7C0%7C637254935514522274&sdata=Gi7j8rno
> 
>  > ogMRCpaAWM0UuH%2FzorFKy9vnpeEfNfu1dLo%3D&reserved=0
> 
>  >
> 
> --
> 
> Adrian Klaver
> 
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> *This email and the information contained herein is proprietary and 
> confidential and subject to the Amdocs Email Terms of Service, which you 
> may review at**https://www.amdocs.com/about/email-terms-of-service*
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Yes Adrian...!!!

Thanks & Regards,
Ishan Joshi

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, May 19, 2020 7:53 PM
To: Ishan Joshi <Ishan.Joshi@amdocs.com>; pgsql-general@postgresql.org
Subject: Re: idle_in_transaction_session_timeout not getting log in PG v12.2

On 5/19/20 7:12 AM, Ishan Joshi wrote:
> Thank Adrian for response.
>
> Yes I have the same *.conf file as v11.5 in fact I have copied the
> same conf files in v12.2
>
> Logging parameter are as follows

I'm not seeing anything obviously wrong with below.

Are you sure you are looking at the correct log?

>
> *Parameter*
>
>
>
> *Values*
>
> log_autovacuum_min_duration
>
>
>
>   1s
>
>   log_checkpoints
>
>
>
>   on
>
>   log_connections
>
>
>
>   on
>
>   log_destination
>
>
>
>   stderr
>
>   log_directory
>
>
>
>   log
>
>   log_disconnections
>
>
>
>   on
>
>   log_duration
>
>
>
>   off
>
>   log_error_verbosity
>
>
>
>   default
>
>   log_executor_stats
>
>
>
>   off
>
>   log_file_mode
>
>
>
> 600
>
> log_filename
>
>
>
>   postgresql-%Y-%m-%d_%H%M%S.log
>
> log_hostname
>
>
>
>   off
>
>   log_line_prefix
>
>
>
>   %m %d %u %l %s pid:%p xid:%x ip:%h
>
>   log_lock_waits
>
>
>
>   on
>
>   log_min_duration_statement
>
>
>
> -1
>
> log_min_error_statement
>
>
>
>   error
>
>   log_min_messages
>
>
>
>   warning
>
>   log_parser_stats
>
>
>
>   off
>
>   log_planner_stats
>
>
>
>   off
>
>   log_replication_commands
>
>
>
>   on
>
>   log_rotation_age
>
>
>
>   1h
>
>   log_rotation_size
>
>
>
> 0
>
> log_statement
>
>
>
>   none
>
>   log_statement_stats
>
>
>
>   off
>
>   log_temp_files
>
>
>
> 0
>
> log_transaction_sample_rate
>
>
>
> 0
>
> log_truncate_on_rotation
>
>
>
>   on
>
>   logging_collector
>
>
>
>   on
>
> Thanks & Regards,
>
> Ishan Joshi
>
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Tuesday, May 19, 2020 7:28 PM
> To: Ishan Joshi <Ishan.Joshi@amdocs.com>; pgsql-general@postgresql.org
> Subject: Re: idle_in_transaction_session_timeout not getting log in PG
> v12.2
>
> On 5/19/20 2:08 AM, Ishan Joshi wrote:
>
>  > Hi Team,
>
>  >
>
>  > I have set idle_in_transaction_session_timeout on user level and it
> is
>
>  > set for 5 min. I can see after 5 min the session with "idle in
>
>  > transaction" state got killed and it log the details in postgresql
> log
>
>  > file with "FATAL: terminating connection due to idle-in-transaction
>
>  > timeout" in v11.5
>
>  >
>
>  > But I have testing the same in v12.2, The session getting killed
> with
>
>  > state "idle in transaction"  but the log is not generated in
>
>  > postgresql log file.
>
>  >
>
>  > Is there any specific changes related to this for v12.2 or any
>
>  > additional parameters need to set for generating logs.
>
> Looking at code in ~/backend/tcop/postgres.c it has not changed.
>
> Are you reusing the same *.conf in 12 as you did in 11?
>
> What are your logging settings?
>
>  >
>
>  > Thanks & Regards,
>
>  >
>
>  > Ishan Joshi
>
>  >
>
>  > *This email and the information contained herein is proprietary and
>
>  > confidential and subject to the Amdocs Email Terms of Service,
> which
>
>  > you may review
>
>  >
> at**https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2F
>
>  >
> https://eur01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.a
> mdocs.com%2F&data=02%7C01%7CIshan.Joshi%40amdocs.com%7C17d82e5803e
> f4868071808d7fc004b39%7Cc8eca3ca127646d59d9da0f2a028920f%7C0%7C0%7C637
> 254950504363804&sdata=zCV9eeGBWKtq73TxXMLg3va2bNmqjT6UbHiDQbkGL8Y%
> 3D&reserved=0%2Fabout%2Femail-terms-of-service*&data=02%7C01%7
> CIs
> <http://www.amdocs.com%2Fabout%2Femail-terms-of-service*&data=02%7
> C01%7CIs>
>
>  >
> han.Joshi%40amdocs.com%7C7d8dce6a7c7d4349a7ec08d7fbfcccd5%7Cc8eca3ca12
>
>  >
> 7646d59d9da0f2a028920f%7C0%7C0%7C637254935514522274&sdata=Gi7j8rno
>
>  > ogMRCpaAWM0UuH%2FzorFKy9vnpeEfNfu1dLo%3D&reserved=0
>
>  >
>
> --
>
> Adrian Klaver
>
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> *This email and the information contained herein is proprietary and
> confidential and subject to the Amdocs Email Terms of Service, which
> you may review
> at**https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2F
> www.amdocs.com%2Fabout%2Femail-terms-of-service*&data=02%7C01%7CIs
> han.Joshi%40amdocs.com%7C17d82e5803ef4868071808d7fc004b39%7Cc8eca3ca12
> 7646d59d9da0f2a028920f%7C0%7C0%7C637254950504363804&sdata=63uX3IKT
> vCMAYnfcoLpNy4eTRwox8EZ99ZN4F1kf4SM%3D&reserved=0
>


--
Adrian Klaver
adrian.klaver@aklaver.com
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms
ofService, which you may review at https://www.amdocs.com/about/email-terms-of-service
<https://www.amdocs.com/about/email-terms-of-service>

On 5/19/20 7:40 AM, Ishan Joshi wrote:
> Yes Adrian...!!!

Well then:

1) What OS and version and did it change between 11 and 12?

2) How are you starting the server and did that change?

3) When did you copy the 11 *.conf files over and did you
restart the 12 server after?

4) What is being logged to the file e.g. connections/disconnects, etc?

> 
> Thanks & Regards,
> Ishan Joshi
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service

On 5/19/20 8:07 AM, Ishan Joshi wrote:
> Hi Adrian,
> 
> 1) What OS and version and did it change between 11 and 12? --> it is 
> cent os 7 an both v11.5 and v12.2 are on same server.
> 
> 2) How are you starting the server and did that change?  --> through 
> systemctl start postgresql-12
> 
> 3) When did you copy the 11 *.conf files over and did you restart the 12 
> server after? --> copy and restart the server 12
> 
> 4) What is being logged to the file e.g. connections/disconnects, etc? 
> --> yes these are getting logged

Hmm, I'm at a loss for an explanation. All I have left is:

1) Do you see anything relevant in the logs when the session is killed?

2) Is the transaction actually being killed? In other words does it 
still show up in?:

https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

> 
> Thanks & Regards,
> 
> Ishan Joshi
> 
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Tuesday, May 19, 2020 8:28 PM
> To: Ishan Joshi <Ishan.Joshi@amdocs.com>; pgsql-general@postgresql.org
> Subject: Re: idle_in_transaction_session_timeout not getting log in PG v12.2
> 
> On 5/19/20 7:40 AM, Ishan Joshi wrote:
> 
>  > Yes Adrian...!!!
> 
> Well then:
> 
> 1) What OS and version and did it change between 11 and 12?
> 
> 2) How are you starting the server and did that change?
> 
> 3) When did you copy the 11 *.conf files over and did you restart the 12 
> server after?
> 
> 4) What is being logged to the file e.g. connections/disconnects, etc?
> 
>  >
> 
>  > Thanks & Regards,
> 
>  > Ishan Joshi
> 
>  >
> 
> --
> 
> Adrian Klaver
> 
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> *This email and the information contained herein is proprietary and 
> confidential and subject to the Amdocs Email Terms of Service, which you 
> may review at**https://www.amdocs.com/about/email-terms-of-service*
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 5/19/20 8:07 AM, Ishan Joshi wrote:
> Hi Adrian,
> 
> 1) What OS and version and did it change between 11 and 12? --> it is 
> cent os 7 an both v11.5 and v12.2 are on same server.
> 
> 2) How are you starting the server and did that change?  --> through 
> systemctl start postgresql-12
> 
> 3) When did you copy the 11 *.conf files over and did you restart the 12 
> server after? --> copy and restart the server 12
> 
> 4) What is being logged to the file e.g. connections/disconnects, etc? 
> --> yes these are getting logged

Just an FYI it worked on 12.2 and 12.3 instances on my machines:

--2020-05-19 10:01:22.489 PDT-0LOG:  parameter 
"idle_in_transaction_session_timeout" changed to "30s"
[unknown]-[unknown]-2020-05-19 10:01:46.970 PDT-0LOG:  connection 
received: host=[local]
[unknown]-aklaver-2020-05-19 10:01:46.971 PDT-0LOG:  connection 
authorized: user=aklaver database=task_manager application_name=psql
psql-aklaver-2020-05-19 10:02:22.869 PDT-0FATAL:  terminating connection 
due to idle-in-transaction timeout
psql-aklaver-2020-05-19 10:02:22.869 PDT-0LOG:  disconnection: session 
time: 0:00:35.899 user=aklaver database=task_manager host=[local]

> 
> Thanks & Regards,
> 
> Ishan Joshi
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com