idle_in_transaction_session_timeout not getting log in PG v12.2

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

idle_in_transaction_session_timeout not getting log in PG v12.2

Ishan Joshi-2

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.

 

 

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

Reply | Threaded
Open this post in threaded view
|

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

RE: idle_in_transaction_session_timeout not getting log in PG v12.2

Ishan Joshi-2

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

 

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 <[hidden email]>
Sent: Tuesday, May 19, 2020 7:28 PM
To: Ishan Joshi <[hidden email]>; [hidden email]
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*&amp;data=02%7C01%7CIs

> han.Joshi%40amdocs.com%7C7d8dce6a7c7d4349a7ec08d7fbfcccd5%7Cc8eca3ca12

> 7646d59d9da0f2a028920f%7C0%7C0%7C637254935514522274&amp;sdata=Gi7j8rno

> ogMRCpaAWM0UuH%2FzorFKy9vnpeEfNfu1dLo%3D&amp;reserved=0

>

 

 

--

Adrian Klaver

[hidden email]

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

Reply | Threaded
Open this post in threaded view
|

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

Adrian Klaver-4
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 <[hidden email]>
> Sent: Tuesday, May 19, 2020 7:28 PM
> To: Ishan Joshi <[hidden email]>; [hidden email]
> 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*&amp;data=02%7C01%7CIs
> <http://www.amdocs.com%2Fabout%2Femail-terms-of-service*&amp;data=02%7C01%7CIs>
>
>  > han.Joshi%40amdocs.com%7C7d8dce6a7c7d4349a7ec08d7fbfcccd5%7Cc8eca3ca12
>
>  > 7646d59d9da0f2a028920f%7C0%7C0%7C637254935514522274&amp;sdata=Gi7j8rno
>
>  > ogMRCpaAWM0UuH%2FzorFKy9vnpeEfNfu1dLo%3D&amp;reserved=0
>
>  >
>
> --
>
> Adrian Klaver
>
> [hidden email] <mailto:[hidden email]>
>
> *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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

RE: idle_in_transaction_session_timeout not getting log in PG v12.2

Ishan Joshi-2
Yes Adrian...!!!

Thanks & Regards,
Ishan Joshi

-----Original Message-----
From: Adrian Klaver <[hidden email]>
Sent: Tuesday, May 19, 2020 7:53 PM
To: Ishan Joshi <[hidden email]>; [hidden email]
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 <[hidden email]>
> Sent: Tuesday, May 19, 2020 7:28 PM
> To: Ishan Joshi <[hidden email]>; [hidden email]
> 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&amp;data=02%7C01%7CIshan.Joshi%40amdocs.com%7C17d82e5803e
> f4868071808d7fc004b39%7Cc8eca3ca127646d59d9da0f2a028920f%7C0%7C0%7C637
> 254950504363804&amp;sdata=zCV9eeGBWKtq73TxXMLg3va2bNmqjT6UbHiDQbkGL8Y%
> 3D&amp;reserved=0%2Fabout%2Femail-terms-of-service*&amp;data=02%7C01%7
> CIs
> <<a href="http://www.amdocs.com%2Fabout%2Femail-terms-of-service*&amp;data=02%7">http://www.amdocs.com%2Fabout%2Femail-terms-of-service*&amp;data=02%7
> C01%7CIs>
>
>  >
> han.Joshi%40amdocs.com%7C7d8dce6a7c7d4349a7ec08d7fbfcccd5%7Cc8eca3ca12
>
>  >
> 7646d59d9da0f2a028920f%7C0%7C0%7C637254935514522274&amp;sdata=Gi7j8rno
>
>  > ogMRCpaAWM0UuH%2FzorFKy9vnpeEfNfu1dLo%3D&amp;reserved=0
>
>  >
>
> --
>
> Adrian Klaver
>
> [hidden email] <mailto:[hidden email]>
>
> *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*&amp;data=02%7C01%7CIs
> han.Joshi%40amdocs.com%7C17d82e5803ef4868071808d7fc004b39%7Cc8eca3ca12
> 7646d59d9da0f2a028920f%7C0%7C0%7C637254950504363804&amp;sdata=63uX3IKT
> vCMAYnfcoLpNy4eTRwox8EZ99ZN4F1kf4SM%3D&amp;reserved=0
>


--
Adrian Klaver
[hidden email]
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 <https://www.amdocs.com/about/email-terms-of-service>



Reply | Threaded
Open this post in threaded view
|

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

RE: idle_in_transaction_session_timeout not getting log in PG v12.2

Ishan Joshi-2

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

 

Thanks & Regards,

Ishan Joshi

 

-----Original Message-----
From: Adrian Klaver <[hidden email]>
Sent: Tuesday, May 19, 2020 8:28 PM
To: Ishan Joshi <[hidden email]>; [hidden email]
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

[hidden email]

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

Reply | Threaded
Open this post in threaded view
|

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

Adrian Klaver-4
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 <[hidden email]>
> Sent: Tuesday, May 19, 2020 8:28 PM
> To: Ishan Joshi <[hidden email]>; [hidden email]
> 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
>
> [hidden email] <mailto:[hidden email]>
>
> *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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: idle_in_transaction_session_timeout not getting log in PG v12.2

Adrian Klaver-4
In reply to this post by Ishan Joshi-2
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
[hidden email]