Support for NOTIFY/LISTEN

[LifeIsStrange]

Hi, I was wondering whether

@Query("LISTEN request_event_notification", nativeQuery = true)
fun getNewRequestStream(): Flux<RequestModel> 

is supported?

// 1
Using the PGconnection manually it is a two step process:
connection.createStatement("LISTEN request_event_notification").execute()
					.flatMap(PostgresqlResult::getRowsUpdated).subscribe();
// 2
connection.getNotifications().map(Notification::getParameter);

So I was wondering whether spring data r2dbc would support it directly in Repositories, and correctly serialize the json/text from postgres into the corresponding type (RequestModel)

corresponding SQL:

CREATE OR REPLACE FUNCTION notify_event() RETURNS TRIGGER AS
$$
DECLARE
    payload JSON;
BEGIN
    payload = row_to_json(NEW);
    PERFORM pg_notify('request_event_notification', payload::text);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS notify_request_event ON request;

CREATE TRIGGER notify_request_event
    AFTER INSERT OR UPDATE OR DELETE
    ON request
    FOR EACH ROW
EXECUTE PROCEDURE notify_event();

[LifeIsStrange]

I'm wondering whether the support for OUT parameters is needed for this https://github.com/spring-projects/spring-data-r2dbc/issues/645 I have no expertise in stored procedures so I don't really know

[mp911de]

Subscribing through a query is supported but the result of the query is going to be an update count (no rows). Additionally, connections used by Spring Data R2DBC are not sticky, meaning that if you run the command once and you have a connection pool, then obtaining notifications may target a different connection that isn't listening.

I suggest using connections directly. The payload of notifications is binary and we cannot reason about the serialization format.

I'll keep this ticket open as the idea of supporting listen/notify makes a lot of sense in general.