[BeginnerQuestion]Why these is 6 rows in my SELECT statement?

[BeginnerC]

Hello community,
I am checking my pg_stat_activity view,but something confused me.
Just like this:

postgres=# SELECT wait_event_type, wait_event FROM pg_stat_activity;
wait_event_type | wait_event
-+-
Activity| AutoVacuumMain
Activity| LogicalLauncherMain
|
Activity| BgWriterHibernate
Activity| CheckpointerMain
Activity| WalWriterMain
(6 rows)

As you can see, the third row is empty,but I don't know why.
Can anyone provide some solution to me?
Thanks in advance!

Yours,
BeginnerC.

Re: [BeginnerQuestion]Why these is 6 rows in my SELECT statement?

[Ian Lawrence Barwick]

2022年9月8日(木) 17:37 BeginnerC :
>
> Hello community,
> I am checking my pg_stat_activity view,but something confused me.
> Just like this:
>
> postgres=# SELECT wait_event_type, wait_event FROM pg_stat_activity;
>  wait_event_type | wait_event
> -+-
>  Activity| AutoVacuumMain
>  Activity| LogicalLauncherMain
>  |
>  Activity| BgWriterHibernate
>  Activity| CheckpointerMain
>  Activity| WalWriterMain
> (6 rows)
>
> As you can see, the third row is empty,but I don't know why.
> Can anyone provide some solution to me?

It's empty because that's your own client session and it's not waiting
on anything :).

 postgres=# SELECT pg_backend_pid();
 pg_backend_pid

  24941
(1 row)

   postgres=# SELECT pid, backend_type, wait_event_type, wait_event
FROM pg_stat_activity;
  pid  | backend_type | wait_event_type |
wait_event

---+--+-+-
 20859 | autovacuum launcher  | Activity| AutoVacuumMain
 20860 | logical replication launcher | Activity|
LogicalLauncherMain
 21554 | walsender| Activity| WalSenderMain
 24941 | client backend   | |
 20856 | background writer| Activity| BgWriterHibernate
 20855 | checkpointer | Activity| CheckpointerMain
 20858 | walwriter| Activity| WalWriterMain
(7 rows)

Regards

Ian Barwick

Re: Missing query plan for auto_explain.

[Matheus Martin]

We do have JIT enabled `jit=on` with `jit_above_cost=10`.

I am sorry but I don't quite understand what role JIT plays in
this situation with `auto_explain`. Could you please elaborate on that?

On Tue, 6 Sept 2022 at 00:29, Maxim Boguk  wrote:

>
>
> On Tue, Aug 30, 2022 at 1:38 PM Matheus Martin <
> matheus.mar...@voidbridge.com> wrote:
>
>> Our Postgres recently started reporting considerably different execution 
>> times for the same query. When executed from our JDBC application the 
>> Postgres logs report an average execution time of 1500 ms but when the query 
>> is manually executed through `psql` it doesn't take longer than 50 ms.
>>
>> With a view to investigate discrepancies in the plan we enabled 
>> `auto_explain` in `session_preload_libraries` with 
>> `auto_explain.log_min_duration = '1s'`. All application servers were bounced 
>> to ensure new connections were created and picked up the changes. However 
>> this trouble query does not have an explain plan printed, even when its 
>> execution time exceeds the threshold (other queries do though).
>>
>> Does anyone have ideas of why the explain plan is not being printed?
>>
>> Sample log entry for trouble query executed from application:
>> ```
>> Aug 26 09:11:33 db-931 postgres[8106]: [66-1] 2022-08-26 09:11:33 GMT 
>> [8106]: [5-1] db=betwave,user=betwave_app_readonly_user LOG:  duration: 
>> 1423.481 ms  bind :
>>
>>
> My understanding of how to auto_explain work - it deals only for execution
> calls, but in your case duration: 1423.481 ms on BIND call, before query
> execution.
> At least in my understanding - auto_explain cannot work and will not help
> in case of a slow BIND call (because it's a time when the query is planned
> but not executed).
> According documentation:
> "Query planning typically occurs when the Bind message is processed. If
> the prepared statement has no parameters, or is executed repeatedly, the
> server might save the created plan and re-use it during subsequent Bind
> messages for the same prepared statement."
> Hard to say what the reason for slow planning, but one (there could be
> others) likely reason is JIT work. Do you have JIT enabled?
>
>
> --
> Maxim Boguk
> Senior Postgresql DBA
> https://dataegret.com/
>
> Phone UA: +380 99 143 
> Phone AU: +61  45 218 5678
>
> LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
> Skype: maxim.boguk
>
> "Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно
> когда я так делаю ещё раз?"
>
>

Re: [BeginnerQuestion]Why these is 6 rows in my SELECT statement?

[hubert depesz lubaczewski]

On Thu, Sep 08, 2022 at 04:37:00PM +0800, BeginnerC wrote:
> Hello community,
> I am checking my pg_stat_activity view,but something confused me.
> Just like this:
> 
> postgres=# SELECT wait_event_type, wait_event FROM pg_stat_activity;
> wait_event_type | wait_event
> -+-
> Activity| AutoVacuumMain
> Activity| LogicalLauncherMain
> |
> Activity| BgWriterHibernate
> Activity| CheckpointerMain
> Activity| WalWriterMain
> (6 rows)
> 
> As you can see, the third row is empty,but I don't know why.
> Can anyone provide some solution to me?
> Thanks in advance!

Please do:

\pset null '-- null --'
SELECT wait_event_type, wait_event FROM pg_stat_activity;

it will most likely show -- null -- in these fields. which means values
in these columns are null.SELECT wait_event_type, wait_event FROM
pg_stat_activity;

You can find more about this/these backends by doing:

select * from pg_stat_activity where wait_event_type is null \gx

depesz

WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

[Perry Smith]

This is an issue when PostgreSQL is running inside a container.  In my quest to 
find an answer, I’ve discovered three instances that it has come up and various 
people have talked about fixes but no one seemed to notice what I found.

I opened an issue here[1].

From within the container, files which I assume are created by PostgreSQL are 
ending up being owned by root rather than Postgres.  Thus, to me, it appears to 
NOT be an issue of mapping internal UIDs and GIDs to external IDs since there 
should not be anything outside the PostgreSQL container creating files inside 
Postgres’ data directory.

The reason I’m sending this note to the general list is to ask how bad is this 
error?  Some “solutions” are to make the pg_stat_tmp directory internal to the 
image and that somehow resolves the issue but I don’t think anyone really 
understands why and things like that bother me.  But I’m also curious if that 
appears to be a viable solution.  The result will be that when the Postgres is 
stopped and the container exited, the next time Postgres starts back up, the 
pg_stat_tmp directory will be gone.  Is that ok?  Does Postgres store anything 
that that needs to survive a restart?

Thank you for your help,
Perry
[1] https://github.com/docker-library/docs/issues/2188#issue-1367170047 




signature.asc
Description: Message signed with OpenPGP

Re: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

[Tom Lane]

Perry Smith  writes:
> From within the container, files which I assume are created by
> PostgreSQL are ending up being owned by root rather than Postgres.

If it looks that way from *inside* the container, that's not good
--- wouldn't that prevent Postgres from reading the files?

> The reason I’m sending this note to the general list is to ask how bad
> is this error?  Some “solutions” are to make the pg_stat_tmp directory
> internal to the image and that somehow resolves the issue but I don’t
> think anyone really understands why and things like that bother me.  But
> I’m also curious if that appears to be a viable solution.  The result
> will be that when the Postgres is stopped and the container exited, the
> next time Postgres starts back up, the pg_stat_tmp directory will be
> gone.  Is that ok?

pg_stat_tmp exists specifically because it holds only temporary files,
cf

https://www.postgresql.org/docs/devel/storage-file-layout.html

It's explicitly cleared out during server start.

The only reason to put it outside the data directory is to make it
*less* persistent than the rest of PG's files, say by putting it
on a RAM disk.  You sound like you've set it up to be *more*
persistent (ie outside the container not inside), which surely is
exactly backwards.

regards, tom lane

Re: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

[Mladen Gogala]

On 9/8/22 23:05, Perry Smith wrote:
This is an issue when PostgreSQL is running inside a container.  In my 
quest to find an answer, I’ve discovered three instances that it has 
come up and various people have talked about fixes but no one seemed 
to notice what I found.


I opened an issue here[1].

From within the container, files which I assume are created by 
PostgreSQL are ending up being owned by root rather than Postgres. 
 Thus, to me, it appears to NOT be an issue of mapping internal UIDs 
and GIDs to external IDs since there should not be anything outside 
the PostgreSQL container creating files inside Postgres’ data directory.


The reason I’m sending this note to the general list is to ask how bad 
is this error?  Some “solutions” are to make the pg_stat_tmp directory 
internal to the image and that somehow resolves the issue but I don’t 
think anyone really understands why and things like that bother me. 
 But I’m also curious if that appears to be a viable solution.  The 
result will be that when the Postgres is stopped and the container 
exited, the next time Postgres starts back up, the pg_stat_tmp 
directory will be gone.  Is that ok?  Does Postgres store anything 
that that needs to survive a restart?


Thank you for your help,
Perry
[1] https://github.com/docker-library/docs/issues/2188#issue-1367170047


Hi Perry,

You probably need to fix your Dockerfile because your postgres seems to 
be running as root. Here is my Dockerfile:


FROM oraclelinux:8
LABEL Description="This install PostgreSQL 14 on top of Oracle Linux 8"
LABEL maintainer="Mladen Gogala"
RUN dnf -y update
COPY RPMS/pgdg-redhat-repo-latest.noarch.rpm /tmp
COPY RPMS/pg_hint_plan14-1.4-1.el8.x86_64.rpm /tmp/
COPY RPMS/pg_hint_plan14-llvmjit-1.4-1.el8.x86_64.rpm /tmp/
# Install PostgreSQL software
RUN dnf localinstall -y /tmp/pgdg-redhat-repo-latest.noarch.rpm
RUN dnf -qy module disable postgresql
RUN dnf -y install postgresql14
RUN dnf -y install postgresql14-libs
RUN dnf -y install postgresql14-server
RUN dnf -y install postgresql14-llvmjit
RUN dnf -y install postgresql14-contrib
# Install pg_hint_plan 1.4
RUN cd /tmp;dnf -y localinstall `ls *.rpm`
# Cleanup
RUN rm -f /tmp/*.rpm
RUN dnf clean all

# Copy postgresql.auto.conf (modified parameters) and run initdb
USER postgres
ARG PGPASSWD="qwerty"
ENV PGDATA=/var/lib/pgsql/14/data
ENV PATH=/usr/pgsql-14/bin:/usr/bin:/usr/local/bin
RUN echo "$PGPASSWD">/var/lib/pgsql/14/pgcluster.pwd
RUN initdb -A password --pwfile=/var/lib/pgsql/14/pgcluster.pwd
RUN echo "host all all 0.0.0.0/0 md5">>$PGDATA/pg_hba.conf
COPY --chown=postgres:postgres RPMS/postgresql.auto.conf 
/var/lib/pgsql/14/data

# Finish
EXPOSE 5432/tcp
ENTRYPOINT ["postgres"]

Please note that I switch users in the file and that owners are 
switched. I don't have any issues with the container built this way. I 
have another container which is built by untaring backup of my sample 
database. I use ADD command to untar it to $PGDATA. Also, my trick with 
putting the modified parameters into postgresql.auto.conf goes contrary 
to the Postgres recommendations. You may want to avoid doing that. I 
haven't pushed the image to the Docker hub because I'm still working on it.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: ***SPAM*** Re: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

[Perry Smith]

> On Sep 8, 2022, at 10:43 PM, Tom Lane  wrote:
> 
> Perry Smith  writes:
>> From within the container, files which I assume are created by
>> PostgreSQL are ending up being owned by root rather than Postgres.
> 
> If it looks that way from *inside* the container, that's not good
> --- wouldn't that prevent Postgres from reading the files?
> 
>> The reason I’m sending this note to the general list is to ask how bad
>> is this error?  Some “solutions” are to make the pg_stat_tmp directory
>> internal to the image and that somehow resolves the issue but I don’t
>> think anyone really understands why and things like that bother me.  But
>> I’m also curious if that appears to be a viable solution.  The result
>> will be that when the Postgres is stopped and the container exited, the
>> next time Postgres starts back up, the pg_stat_tmp directory will be
>> gone.  Is that ok?
> 
> pg_stat_tmp exists specifically because it holds only temporary files,
> cf
> 
> https://www.postgresql.org/docs/devel/storage-file-layout.html
> 
> It's explicitly cleared out during server start.
> 
> The only reason to put it outside the data directory is to make it
> *less* persistent than the rest of PG's files, say by putting it
> on a RAM disk.  You sound like you've set it up to be *more*
> persistent (ie outside the container not inside), which surely is
> exactly backwards.

The data directory is outside so it is persistent.  The pg_stat_tmp is inside 
the data directory.  This is how whoever builds the “official” Postgres images 
set things up.

If the right solution is to not make it part of the data directory, I can 
change that.  One of the solutions explains how which solves the Postgres issue 
but still leaves open the question of how and why do some of the files get 
owned by root (which probably is a Docker or a Linux issue).

Re: ***SPAM*** Re: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

[Mladen Gogala]

On 9/9/22 00:08, Perry Smith wrote:

The data directory is outside so it is persistent.  The pg_stat_tmp is inside 
the data directory.


Ah, that's the reason. Docker daemon runs as root so if you do binding 
mount, files will be owned by root. You may want to use normal Docker 
volume and not an external directory.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com