ClientRead on ROLLABACK
I have a question regarding postgresql waiting for the client. I queried
the pg_stat_activity because I noticed a connection that had not been
released for days!!! I saw that the wait_event was ClientRead and the query
was ROLLBACK. What the server is waiting for from the client? It is a
simple ROLLBACK. I'd expect postgresql to abort the transaction, that's it!
This was the client thread stack trace:
at sun.nio.ch.Net.poll(Native Method)
at sun.nio.ch.NioSocketImpl.park()
at sun.nio.ch.NioSocketImpl.park()
at sun.nio.ch.NioSocketImpl.implRead()
at sun.nio.ch.NioSocketImpl.read()
at sun.nio.ch.NioSocketImpl$1.read()
at java.net.Socket$SocketInputStream.read()
at sun.security.ssl.SSLSocketInputRecord.read()
at sun.security.ssl.SSLSocketInputRecord.readFully()
at sun.security.ssl.SSLSocketInputRecord.decodeInputRecord()
at sun.security.ssl.SSLSocketInputRecord.decode()
at sun.security.ssl.SSLTransport.decode()
at sun.security.ssl.SSLSocketImpl.decode()
at sun.security.ssl.SSLSocketImpl.readApplicationRecord()
at sun.security.ssl.SSLSocketImpl$AppInputStream.read()
at
org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)
at
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128)
at
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113)
at
org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
at org.postgresql.core.PGStream.receiveChar(PGStream.java:453)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2120)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:316)
at
org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:879)
at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:922)
at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:396)
at
com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java)
at
org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:121)
at
org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.rollback(JdbcResourceLocalTransactionCoordinatorImpl.java:304)
at
org.hibernate.engine.transaction.internal.TransactionImpl.rollback(TransactionImpl.java:142)
at
org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:589)
...
So I ended up in a situation where both client and server were reading from
the socket :(. I'm not sure why. Something went wrong between client and
server, network problems? The connection was held for 5 days until it was
manually terminated. But why the server was waiting in the first place?
--
Simone Giusso
Simone Giusso <simonedevs@gmail.com> writes:
I have a question regarding postgresql waiting for the client. I queried
the pg_stat_activity because I noticed a connection that had not been
released for days!!! I saw that the wait_event was ClientRead and the query
was ROLLBACK. What the server is waiting for from the client?
You are misunderstanding that display. If the wait state is ClientRead
then the server has nothing to do and is awaiting a fresh SQL command
from the client. The query that's shown is the last-executed query.
(We used to show "<IDLE>" in the query column in this state, but that
was deemed less helpful than the current behavior.)
So I ended up in a situation where both client and server were reading from
the socket :(. I'm not sure why. Something went wrong between client and
server, network problems?
Yeah, a dropped packet could explain this perhaps.
regards, tom lane
On Thu, 27 Jun 2024 at 17:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
(We used to show "<IDLE>" in the query column in this state, but that
was deemed less helpful than the current behavior.)
I think this is a super common confusion among users. Maybe we should
consider making it clearer that no query is currently being executed.
Something like
IDLE: last query: SELECT * FROM mytable;
Oh, I see. So the ROLLBACK command was executed! So I suppose the client was waiting just for the ACK and the connection has been left open.
I think this is a super common confusion among users. Maybe we should
consider making it clearer that no query is currently being executed.
Something likeIDLE: last query: SELECT * FROM mytable;
I think the clearest option would be leave the query column empty and add a new column last_query. But this suggestion may still do its job in clarifying that the query is not running.