PG-15.6: timeout parameters erroring out

On Sunday, February 9, 2025, Mukesh Tanuku <mukesh.postgres@gmail.com>
wrote:

*idle_in_transaction_session_timeout = '1min'idle_session_timeout = '5min'*

I suspect our docs may need an update. They say you may include white
space before the unit; it probably needs to be changed to “must”. I
believe we’ve recently tightened up the parsing of literals in this area.

other way also, like below

*idle_in_transaction_session_timeout = 60000idle_session_timeout = 300000*

If those give error messages regarding trailing junk something else must be
going on.

we see these errors reporting in the logs after we enabled those parameters
*log:*
2025-02-10 04:17:19.156 GMT [2467573] ERROR: trailing junk after numeric
literal at or near "1m" at character 43
2025-02-10 04:17:19.156 GMT [2467573] STATEMENT: SET
idle_in_transaction_session_timeout = 1min
2025-02-10 04:17:19.845 GMT [2467575] ERROR: trailing junk after numeric
literal at or near "1m" at character 43
2025-02-10 04:17:19.845 GMT [2467575] STATEMENT: SET
idle_in_transaction_session_timeout = 1min
...

David J.

On Mon, 10 Feb 2025 at 17:34, Mukesh Tanuku <mukesh.postgres@gmail.com> wrote:

2025-02-10 04:22:00.823 GMT [2468337] ERROR: trailing junk after numeric literal at or near "5m" at character 28
2025-02-10 04:22:00.823 GMT [2468337] STATEMENT: SET idle_session_timeout = 5min
2025-02-10 04:22:03.487 GMT [2468342] ERROR: trailing junk after numeric literal at or near "5m" at character 28
2025-02-10 04:22:03.487 GMT [2468342] STATEMENT: SET idle_session_timeout = 5min
2025-02-10 04:22:04.470 GMT [2468345] ERROR: trailing junk after numeric literal at or near "5m" at character 28

Is there any know issue/bug with these paramters, can someone help me to get this worked since our app idle connections are getting pilled up and reaching max connections.

I don't see any issues with the postgresql.conf, but it looks like
you're just missing single quotes in your SET statements. See [1]https://www.postgresql.org/docs/17/sql-set.html,
where it says:

"New value of parameter. Values can be specified as string constants,
identifiers, numbers, or comma-separated lists of these, as
appropriate for the particular parameter."

String constants [2]https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS require single quotes or the dollar quoting
format and 5min isn't a valid number.

David

[1]: https://www.postgresql.org/docs/17/sql-set.html
[2]: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

Mukesh Tanuku <mukesh.postgres@gmail.com> writes:

We unabled the postgres timeout parameters in the postgresql.conf file

*idle_in_transaction_session_timeout = '1min'idle_session_timeout = '5min'*

Did you actually run them together on one line like that? The normal
thing is one setting per line. It might be that the config file
parser will let you get away with just spaces rather than a newline
between settings, but I'm not terribly surprised if it fails without
any whitespace at all. However, since you showed us neither the exact
config file contents nor the error message you got, this is all just
speculation.

2025-02-10 04:17:19.156 GMT [2467573] ERROR: trailing junk after numeric
literal at or near "1m" at character 43
2025-02-10 04:17:19.156 GMT [2467573] STATEMENT: SET
idle_in_transaction_session_timeout = 1min

This case however is pretty clear: you need single quotes around the
value '1min', and you didn't provide them.

regards, tom lane

On Sunday, February 9, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Mukesh Tanuku <mukesh.postgres@gmail.com> writes:

We unabled the postgres timeout parameters in the postgresql.conf file

*idle_in_transaction_session_timeout = '1min'idle_session_timeout =

'5min'*

Did you actually run them together on one line like that?

I think that is an email client artifact, dealing with RTF or something.

I do agree too much is missing here. The only known errors involve SET
which configuration file settings don’t use. And those errors are indeed
the lack of quoting.

The lack of space I mentioned earlier, in the config text literal, is fine.

David J.

Thanks for your quick responses.

I have just added the session related timeout parameters in the
postgresql.conf file also tried tweaking with different values/patterns
like ('1min', '1 min', 80000, 10s, '10s', '10 s')

but i get an immediate error after reloading the configuration file.

*postgres.log file*
2025-02-10 10:27:10.748 GMT [934108] LOG: received SIGHUP, reloading
configuration files
2025-02-10 10:27:10.749 GMT [934108] LOG: parameter
"idle_in_transaction_session_timeout" changed to "80000"
2025-02-10 10:27:16.117 GMT [2531150] ERROR: trailing junk after numeric
literal at or near "80s" at character 43
2025-02-10 10:27:16.117 GMT [2531150] STATEMENT: SET
idle_in_transaction_session_timeout = 80s

though we are not firing any SQL statements explicitly we see this error.

i guess something else is the issue other than spacing.

Regards
Mukesh Tanuku

On Mon, Feb 10, 2025 at 10:38 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Mon, 2025-02-10 at 16:06 +0530, Mukesh Tanuku wrote:

Thanks for your quick responses.

I have just added the session related timeout parameters in the
postgresql.conf file also tried tweaking with different
values/patterns like ('1min', '1 min', 80000, 10s, '10s', '10 s')

but i get an immediate error after reloading the configuration file. 

postgres.log file
2025-02-10 10:27:10.748 GMT [934108] LOG:  received SIGHUP, reloading
configuration files
2025-02-10 10:27:10.749 GMT [934108] LOG:  parameter
"idle_in_transaction_session_timeout" changed to "80000"
2025-02-10 10:27:16.117 GMT [2531150] ERROR:  trailing junk after
numeric literal at or near "80s" at character 43
2025-02-10 10:27:16.117 GMT [2531150] STATEMENT:  SET
idle_in_transaction_session_timeout = 80s

though we are not firing any SQL statements explicitly we see this
error.

i guess something else is the issue other than spacing.

I'd say that 934108 is the config file reload and that 2531150 is from
somebody running psql from a shell or SQL code coming out from an
application.

Thanks for clarifying, got it.
Is this issue specifically reported in only the PG 15 version? more syntax
sensitivity.

On Mon, Feb 10, 2025 at 6:09 PM rob stone <floriparob@tpg.com.au> wrote:

I see there is some open PR in awx-operator (
https://github.com/ansible/awx/issues/15406) which is related to this. AWX
is connected to DB in my case.

On Mon, Feb 10, 2025 at 7:04 PM Mukesh Tanuku <mukesh.postgres@gmail.com>
wrote:

Mukesh Tanuku <mukesh.postgres@gmail.com> writes:

Is this issue specifically reported in only the PG 15 version? more syntax
sensitivity.

PG 15 and up report the syntax error differently.

regression=# set foo = 15min;
ERROR: trailing junk after numeric literal at or near "15min"
LINE 1: set foo = 15min;
^

In v14 that'd look like

regression=# set foo = 15min;
ERROR: syntax error at or near "min"
LINE 1: set foo = 15min;
^

There are variants of this (lack of space between a number and an
identifier) that will be rejected by 15+ although older versions
accepted them. But in a SET command I think no version will take it.

regards, tom lane

Thanks for all of you for responding and clarifying things. Really
appreciate it.

We finally confirmed that it is a SET command issue where the AWX
application is firing the query without quotes and it's a known bug from
their end.

Thank you once again.
Regards
Mukesh Tanuku

On Mon, Feb 10, 2025 at 9:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: