Thread: 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 connectionhas 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 like > > IDLE: 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 maystill do its job in clarifying that the query is not running.