Re: Obsolete or dead serverconnections after reboot - Mailing list pgsql-general

Hello Ninad Shah,

Thank you for clarification.
The state of the mentioned connections was "Idle" in pg_stat_activity.

Now on my Laptops the shutdown of the postgres-service works too.

I had to switch off the "Schnellstart" (in english something like "fast 
start" or "fast boot"), which can be found under Settings > System > 
Energy Options > Behavior when pushing the Power-Button / Closing the 
Computer

There in the lower part of the dialog "Settings for Shutdown"

The Option is then disabled first, but can be enabled with a link in the 
upper Part of the dialog.

(Dont ask me why it is so complicated, and what this Option has to do 
with the Power Button / closing the Display, it affects every kind of 
shutdown)

Thanks you for your help.
Wolfgang



Am 23.07.2021 um 16:31 schrieb Ninad Shah:
> Just to make it clear, "idle in transaction" and "idle" are different 
> terms. "Idle in transaction" refers to connections that are waiting for 
> either commit or rollback.
> 
> Additionally, this could be a bug as well. However, it's difficult to 
> conclude at this stage. You may report this to postgresql-bugs group.
> 
> 
> Regards,
> Ninad Shah
> 
> On Fri, 23 Jul 2021 at 17:25, WR <wolle321@freenet.de 
> <mailto:wolle321@freenet.de>> wrote:
> 
>     Hello,
> 
>     unfortunately, it's my companys choice of OS.
>     In private life I'm riding the penguin.
> 
>     One last thing: I have 2 Laptops, where the shutdown doesn't work, and
>     one VirtualBox machine where it works like a charm. Maybe it has
>     something to do with the power management.
> 
>     As a workaround I set the value
>     idle_in_transaction_session_timeout
>     to 5min. So the server kills this idle connections from before the
>     power
>     cycle (and all others too) after 5 minutes.
> 
>     Thank you, guys
> 
>     (PS.: still no answer from EDB)
> 
>     Am 22.07.2021 um 16:51 schrieb Ninad Shah:
>      > Factually, Windows itself has a number of issues. Hence, it is
>     always
>      > suggested to use Linux with it.
>      >
>      > It can be explored further by querying the table from which
>      > pg_stat_activity gathers data.
>      >
>      >
>      > Regards,
>      > Ninad Shah
>      >
>      >
>      > On Thu, 22 Jul 2021 at 12:51, WR <wolle321@freenet.de
>     <mailto:wolle321@freenet.de>
>      > <mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>>> wrote:
>      >
>      >     Hello Ninad Shah,
>      >
>      >     I think, nobody holds the connections, because the state is
>     idle. But
>      >     I'm not shure what means: the connection is idle.
>      >
>      >     One interesting fact was: when I dont stop the psql
>     commandline on the
>      >     client (and dont start another query, wht ends up in a connection
>      >     reset,
>      >     while the server is down), psql uses the same connection
>     after reboot
>      >     and the state goes from idle back to active.
>      >
>      >     So there are two questions: what will happen to idle
>     connctions after a
>      >     while, if the client doesnt exist anymore.
>      >     And is this desired behavior, the the postgres-Server-Service
>     does
>      >     not a
>      >     shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)
>      >
>      >     Thanks you, Greeting from germany,
>      >     Wolfgang
>      >
>      >     Am 21.07.2021 um 16:10 schrieb Ninad Shah:
>      >      > Hello,
>      >      >
>      >      > Would you be able to verify the process trees for those
>     PIDs on
>      >     Windows?
>      >      > You may be able to see who holds the connections?
>      >      >
>      >      >
>      >      > Regards,
>      >      > Ninad Shah
>      >      >
>      >      > On Wed, 21 Jul 2021 at 19:15, WR <wolle321@freenet.de
>     <mailto:wolle321@freenet.de>
>      >     <mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>>
>      >      > <mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>
>     <mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>>>> wrote:
>      >      >
>      >      >     Hello Vijaykumar Jain,
>      >      >
>      >      >     thank you for fast answer, today I'm not able to
>     access the
>      >     hardware,
>      >      >     I'll be back tomorrow and will do the required tests.
>      >      >
>      >      >     Wolfgang
>      >      >
>      >      >     Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
>      >      >      > select pg_stat_reset();
>      >      >      >
>      >      >      > Can you run above function, to check if stats are
>     reset and
>      >      >     things are
>      >      >      > fine without a pg restart?
>      >      >      > I not able to understand how new connection stats
>     are added,
>      >      >     along with
>      >      >      > old stale states, if I assume stats collector
>     process is
>      >     having
>      >      >     issues ,
>      >      >      > or the stats folder is corrupt etc.
>      >      >      > That would also mean, all table stats would be off
>     or not
>      >     updated
>      >      >     too?
>      >      >      > Is that so? Or analyse works fine on tables without
>     a restart?
>      >      >      >
>      >      >      > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
>      >      >      > <vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>
>      >     <mailto:vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>>
>      >      >     <mailto:vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>
>      >     <mailto:vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>>>
>      >      >      > <mailto:vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>
>      >     <mailto:vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>>
>      >      >     <mailto:vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>
>      >     <mailto:vijaykumarjain.github@gmail.com
>     <mailto:vijaykumarjain.github@gmail.com>>>>> wrote:
>      >      >      >
>      >      >      >
>      >      >      >
>      >      >      >         Is there a way to avoid this (without
>     restarting
>      >     the service
>      >      >      >         after every
>      >      >      >         reboot). Is this a bug or a normal behavior?
>      >      >      >
>      >      >      >
>      >      >      >     I have less knowledge of windows.
>      >      >      >
>     https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>
>      >      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>>
>      >      >      >   
>       <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>
>      >      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>>>
>      >      >      >
>      >      >      >
>      >      >      >
>      >      >      >     Do you see shutdown/termination messages in the
>     db logs or
>      >      >     windows
>      >      >      >     event logs when the machine is rebooted?
>      >      >      >
>      >      >      >     You get the same pid and query, does it also
>     has the same
>      >      >     age( time
>      >      >      >     since it started),
>      >      >      >     I mean is the stats table updated with new data
>     for stale
>      >      >      >     connections or they remain static.
>      >      >      >
>      >      >      >     Do you see the same issue when the machine is power
>      >     cycled.
>      >      >      >
>      >      >      >     Maybe windows might be preserving the memory
>     state on
>      >     disk and
>      >      >      >     reading it back on reboot (like sleep) unless
>     there are
>      >      >     instructions
>      >      >      >     to shutdown the db server on reboot. Idk.
>      >      >      >
>      >      >      >     What are the state of the connections in
>      >     pg_stat_activity abd
>      >      >      >     process explorer before and after reboot. The
>     sockets
>      >     exists and
>      >      >      >     active,  or are residual in pg stats only.
>      >      >      >
>      >      >      >
>      >      >
>      >      >
>      >      >
>      >
>      >
>      >     --
>      >     May the source be with you
>      >
> 
> 
>     -- 
>     May the source be with you
> 


-- 
May the source be with you