Thread: Postgres 11.5 not logging all sqls

Postgres 11.5 not logging all sqls

From
shashidhar Reddy
Date:
Hello

We are using postgres 11.5 on Ubuntu we have enabled logging and set log_statements to all but when I checked the statements in the logs I can see only first few lines of the query and also when I check in pgadmin for current queries I could see only few lines. Some one please help me to get full queries in the logs and in pgadmin4.

Re: Postgres 11.5 not logging all sqls

From
Laurenz Albe
Date:
On Mon, 2023-09-25 at 16:54 +0530, shashidhar Reddy wrote:
> We are using postgres 11.5 on Ubuntu we have enabled logging and set
> log_statements to all but when I checked the statements in the logs I
> can see only first few lines of the query

I don't know about pgAdmin, but the log will certainly have the complete
statement.

Yours,
Laurenz Albe



Re: Postgres 11.5 not logging all sqls

From
shashidhar Reddy
Date:
Hi Laurenz,

Iog is also showing few lines that is incomplete statement.

On Mon, 25 Sept, 2023, 5:05 pm Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
On Mon, 2023-09-25 at 16:54 +0530, shashidhar Reddy wrote:
> We are using postgres 11.5 on Ubuntu we have enabled logging and set
> log_statements to all but when I checked the statements in the logs I
> can see only first few lines of the query

I don't know about pgAdmin, but the log will certainly have the complete
statement.

Yours,
Laurenz Albe

Re: Postgres 11.5 not logging all sqls

From
Erik Wienhold
Date:
On 2023-09-25 16:54 +0530, shashidhar Reddy wrote:
> We are using postgres 11.5 on Ubuntu we have enabled logging and set
> log_statements to all but when I checked the statements in the logs I can
> see only first few lines of the query and also when I check in pgadmin for
> current queries I could see only few lines. Some one please help me to get
> full queries in the logs and in pgadmin4.

* Could be that the logging collector truncates messages.  Are all
  messages truncated to the same length?

* pgAdmin reads pg_stat_activity.query which is subject to config
  track_activity_query_size[1].  That should, however, not affect
  logging.

* Maybe a bug 11.5 which is already 4 years old.

[1] https://www.postgresql.org/docs/11/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE

-- 
Erik



Re: Postgres 11.5 not logging all sqls

From
shashidhar Reddy
Date:
Thank you Erik,

Not all queries are truncated at same size,different queries different sizes but all 11.5 servers are behaving in same manner.


On Mon, 25 Sept, 2023, 5:33 pm Erik Wienhold, <ewie@ewie.name> wrote:
On 2023-09-25 16:54 +0530, shashidhar Reddy wrote:
> We are using postgres 11.5 on Ubuntu we have enabled logging and set
> log_statements to all but when I checked the statements in the logs I can
> see only first few lines of the query and also when I check in pgadmin for
> current queries I could see only few lines. Some one please help me to get
> full queries in the logs and in pgadmin4.

* Could be that the logging collector truncates messages.  Are all
  messages truncated to the same length?

* pgAdmin reads pg_stat_activity.query which is subject to config
  track_activity_query_size[1].  That should, however, not affect
  logging.

* Maybe a bug 11.5 which is already 4 years old.

[1] https://www.postgresql.org/docs/11/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE

--
Erik

Re: Postgres 11.5 not logging all sqls

From
Adrian Klaver
Date:
On 9/25/23 11:55, shashidhar Reddy wrote:
> Thank you Erik,
> 
> Not all queries are truncated at same size,different queries different 
> sizes but all 11.5 servers are behaving in same manner.

FYI, the last minor release for version is 11.21. You are missing 16 
bug/security fixes. I would update to 11.21 and then see if the issue 
persists.

> 
> 
> On Mon, 25 Sept, 2023, 5:33 pm Erik Wienhold, <ewie@ewie.name 
> <mailto:ewie@ewie.name>> wrote:
> 
>     On 2023-09-25 16:54 +0530, shashidhar Reddy wrote:
>      > We are using postgres 11.5 on Ubuntu we have enabled logging and set
>      > log_statements to all but when I checked the statements in the
>     logs I can
>      > see only first few lines of the query and also when I check in
>     pgadmin for
>      > current queries I could see only few lines. Some one please help
>     me to get
>      > full queries in the logs and in pgadmin4.
> 
>     * Could be that the logging collector truncates messages.  Are all
>        messages truncated to the same length?
> 
>     * pgAdmin reads pg_stat_activity.query which is subject to config
>        track_activity_query_size[1].  That should, however, not affect
>        logging.
> 
>     * Maybe a bug 11.5 which is already 4 years old.
> 
>     [1]
>     https://www.postgresql.org/docs/11/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE
<https://www.postgresql.org/docs/11/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE>
> 
>     -- 
>     Erik
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Postgres 11.5 not logging all sqls

From
shashidhar Reddy
Date:
Thank you Adrian! I will apply the patch and see.

On Tue, 26 Sept, 2023, 12:53 am Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 9/25/23 11:55, shashidhar Reddy wrote:
> Thank you Erik,
>
> Not all queries are truncated at same size,different queries different
> sizes but all 11.5 servers are behaving in same manner.

FYI, the last minor release for version is 11.21. You are missing 16
bug/security fixes. I would update to 11.21 and then see if the issue
persists.

>
>
> On Mon, 25 Sept, 2023, 5:33 pm Erik Wienhold, <ewie@ewie.name
> <mailto:ewie@ewie.name>> wrote:
>
>     On 2023-09-25 16:54 +0530, shashidhar Reddy wrote:
>      > We are using postgres 11.5 on Ubuntu we have enabled logging and set
>      > log_statements to all but when I checked the statements in the
>     logs I can
>      > see only first few lines of the query and also when I check in
>     pgadmin for
>      > current queries I could see only few lines. Some one please help
>     me to get
>      > full queries in the logs and in pgadmin4.
>
>     * Could be that the logging collector truncates messages.  Are all
>        messages truncated to the same length?
>
>     * pgAdmin reads pg_stat_activity.query which is subject to config
>        track_activity_query_size[1].  That should, however, not affect
>        logging.
>
>     * Maybe a bug 11.5 which is already 4 years old.
>
>     [1]
>     https://www.postgresql.org/docs/11/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE <https://www.postgresql.org/docs/11/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE>
>
>     --
>     Erik
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Postgres 11.5 not logging all sqls

From
Adrian Klaver
Date:
On 9/25/23 19:30, shashidhar Reddy wrote:
> Thank you Adrian! I will apply the patch and see.

Just to be clear 11.21 represents the changes from 16 different minor 
releases.

-- 
Adrian Klaver
adrian.klaver@aklaver.com