idle_in_transaction_session_timeout not getting log in PG v12.2

Started by Ishan Joshialmost 6 years ago9 messagesgeneral

Ishan.Joshi@amdocs.com

almost 6 years ago

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

Adrian Klaver

adrian.klaver@aklaver.com

almost 6 years ago

In reply to: Ishan Joshi (#1)

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://www.amdocs.com/about/email-terms-of-service*

--
Adrian Klaver
adrian.klaver@aklaver.com

Ishan Joshi

Ishan.Joshi@amdocs.com

almost 6 years ago

In reply to: Adrian Klaver (#2)

RE: idle_in_transaction_session_timeout not getting log in PG v12.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 <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*&amp;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 <https://www.amdocs.com/about/email-terms-of-service>

Adrian Klaver

adrian.klaver@aklaver.com

almost 6 years ago

In reply to: Ishan Joshi (#3)

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

www.amdocs.com%2Fabout%2Femail-terms-of-service*&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&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

Ishan Joshi

Ishan.Joshi@amdocs.com

almost 6 years ago

In reply to: Adrian Klaver (#4)

RE: idle_in_transaction_session_timeout not getting log in PG v12.2

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*&amp;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 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>

Adrian Klaver

adrian.klaver@aklaver.com

almost 6 years ago

In reply to: Ishan Joshi (#5)

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

Ishan Joshi

Ishan.Joshi@amdocs.com

almost 6 years ago

In reply to: Adrian Klaver (#6)

RE: idle_in_transaction_session_timeout not getting log in PG v12.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 <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

almost 6 years ago

In reply to: Ishan Joshi (#7)

Re: idle_in_transaction_session_timeout not getting log in PG v12.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

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

Adrian Klaver

adrian.klaver@aklaver.com

almost 6 years ago

In reply to: Ishan Joshi (#7)

Re: idle_in_transaction_session_timeout not getting log in PG v12.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
adrian.klaver@aklaver.com