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@xxxxxxxxxx> 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@xxxxxxxxxx
> <mailto:wolle321@xxxxxxxxxx>> 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@xxxxxxxxxx
> <mailto:wolle321@xxxxxxxxxx>
> > <mailto:wolle321@xxxxxxxxxx <mailto:wolle321@xxxxxxxxxx>>> 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@xxxxxxxxx
> <mailto:vijaykumarjain.github@xxxxxxxxx>
> > <mailto:vijaykumarjain.github@xxxxxxxxx
> <mailto:vijaykumarjain.github@xxxxxxxxx>>
> > > <mailto:vijaykumarjain.github@xxxxxxxxx
> <mailto:vijaykumarjain.github@xxxxxxxxx>
> > <mailto:vijaykumarjain.github@xxxxxxxxx
> <mailto:vijaykumarjain.github@xxxxxxxxx>>>> 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
