Re: Obsolete or dead serverconnections after reboot

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(at)freenet(dot)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(at)freenet(dot)de
> > <mailto:wolle321(at)freenet(dot)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(at)freenet(dot)de
> > <mailto:wolle321(at)freenet(dot)de>
> > > <mailto:wolle321(at)freenet(dot)de <mailto:wolle321(at)freenet(dot)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(dot)github(at)gmail(dot)com
> > <mailto:vijaykumarjain(dot)github(at)gmail(dot)com>
> > > <mailto:vijaykumarjain(dot)github(at)gmail(dot)com
> > <mailto:vijaykumarjain(dot)github(at)gmail(dot)com>>
> > > > <mailto:vijaykumarjain(dot)github(at)gmail(dot)com
> > <mailto:vijaykumarjain(dot)github(at)gmail(dot)com>
> > > <mailto:vijaykumarjain(dot)github(at)gmail(dot)com
> > <mailto:vijaykumarjain(dot)github(at)gmail(dot)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>>>
> > > >
> > > >
> > > >
> > > > 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
>