From: | WR <wolle321(at)freenet(dot)de> |
---|---|
To: | Ninad Shah <nshah(dot)postgres(at)gmail(dot)com> |
Cc: | Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Obsolete or dead serverconnections after reboot |
Date: | 2021-07-26 07:31:03 |
Message-ID: | 0fc1f047-9be7-9fbf-7f9f-e66f30b6bfef@freenet.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)freenet(dot)de
> <mailto: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>
> > <mailto: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>>
> > > <mailto: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>>>
> > > > <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
> <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>>>
> > > >
> <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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2021-07-26 11:44:28 | Re: PostgreSQL reference coffee mug |
Previous Message | Pavel Stehule | 2021-07-26 07:05:39 | Re: PostgreSQL reference coffee mug |