make `FETCH NEXT FROM cur` a blocking call

[lmatz]

Is your feature request related to a problem? Please describe.

https://risingwave-labs.slack.com/archives/C0358PVT2AX/p1723721407947399:

Hi folks. Is there anyway to make FETCH NEXT FROM cur block until the data arrives? The current design is not good for event-driven architecture:

while True:
  # Fetch the next row from the cursor
  cur.execute("FETCH NEXT FROM cur;")
  row = cur.fetchone()

  if row is None:
    # Sleep for 1 second if no row is fetched
    time.sleep(1)
    continue

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[npadbidri]

Agree with @lmatz . Sans this ability to be notified, we are forced to poll Rising Wave. We query Rising Wave every Clock Second to check on new data arrival. For those vast majority instances, when there is no data, we continue to inundate Rising Wave every clock second, with our request to return data. Having Pub/Sub support would basically act like Long Polling; this would also lead to lesser resource consumption on the Rising Wave Server.