Feature: Listen/Notify Support with Transaction Pooling

[merarischroeder]

Problem

• NOTIFY/LISTEN doesn't work with Transaction Pooling, only with Session Pooling
• NOTIFY/LISTEN is quite useful for SIGNALLING (not for queued data), so a NOTIFIED process knows to Query again (instead of polling on an interval).
• I would like to use just PostgreSQL and not have to add more infrastructure

Inadequate workarounds

• Session Mode doesn't scale. If there are 1000x pgbouncer clients there needs to be 1000x PostgreSQL connections (no pooling).
• If other infrastructure is used, there needs to be a connection-per-client maintained anyway, and when SIGNALLED there will be a maximum of 2x Connections per client (one for PosgreSQL and one for some other Signalling system)
• Some PostgreSQL installations don't have integration with other infrastructure. That is, if you planned to call NOTIFY in a trigger, there may be no alternative when using other infrastructure (with limitations from the IaaS provider).

Solution

• Maintain a single [Listen Connection] to the PostgreSQL server
• Isolate LISTEN commands from connections in Transaction Pooling mode
• Read the LISTEN command and map it against the PGBounce TCP connection - using a simple PUB/SUB mechanism
• Send each distinct LISTEN command onward to the [Listen Connection] instead
• Upon NOTIFY from the [Listen Connection] convert to an internal PUB message and distribute to subscribers (forwarding the original NOTIFY message).

Resourcing

• Does PGBouncer have a bounty/donation system? I will pay for this, or have my team build it

[petere]

As a general rule, PgBouncer doesn't look inside packets containing SQL commands or the packets containing query results. So any scheme that relies on it recognizing certain SQL commands and doing something with them will not fit the current architecture.

[merarischroeder]

Thanks @petere. Thanks for taking the time to reply.

1. The General Rule - I understand, having rules and principles certainly helps guide the bulk of design decisions efficiently. I suggest then that:

• the Primary principle of PgBouncer is to make PostgreSQL scale better
• the "don't look inside PQ messages" rule has been a Secondary principle that has been useful until now
• It's work breaking the Second principle for the Primary one.

What do you think?

2. Current architecture

So any scheme that relies on it recognizing certain SQL commands and doing something with them will not fit the current architecture.

I haven't looked at this codebase before, so I will have a look, and learn, and see if I can better understand where you are coming from, and maybe I will be able to suggest a suitable way forward.

2a) My code research

• Upon first glance it looks like Session-Mode connections are byte-level copying. For Transaction-Mode I'm expecting at least some sort of message-level (header) parsing. The header can be described as the "outside" of the message, while the contents the "inside" - of which the principle is to avoid parsing.
• get_header in proto.c:31 parses the pkt header from the buffer. This identifies PKT_CANCEL, PKT_SSLREQ, PKT_GSSENCREQ, PKT_STARTUP, and PKT_STARTUP_V2. Any other header type returns false. It does not identify a Query type.
• get_header is used by server_proto in server.c:508. server_proto is a pointer that is bound within objects.c and server_cache. This is used via slab_alloc(server_cache) command. So a kind of object initializer (with constructor). This means the get_header function is only used initially, and not for ongoing message forwarding. I'm still looking for the message forwarding code then. I could still be wrong about most of this though.
• I like launch_new_connection best as a lead. It gets provided a "pool" which might either be the client pool or server pool. It initializes a client OR server connection
• (that's interesting, accordingly to the comment near finish_client_login, the server parameters can be cached for middleware-client forwarding)
• (there is some sort of client reconnect protocol with accept_cancel_request that I don't yet understand)
• check_fast_fail is the best candidate to trace for launch_new_connection. Every line in that function literally reads like the reverse of what I was expecting. Bailing on that line of exploration.
• (Searching with transaction). handle_server_work in server.c looks like the server-side processing. Comment: "process packets on logged in connection". NotificationResponse (A) message is identified (but only for server-state processing). The client socket is bound via server->link - quite strong. There is no code the explicitly processes POOL_TX but it does have exceptional handling for POOL_STMT (Statement mode). So Session-Mode and Transaction-Mode are quite similar in that way. sbuf_prepare_send is called which assigns variables to sbuf - probably for some other code to do the sending later.
• Tracing sbuf->dst. sbuf_send_pending refers to this in the expected way. This function is called by sbuf_process_pending. I assume it will send data after the header until the size of the message (as indicated in the header) is exhausted.
• Interesting: scan_text_result in proto.c - perhaps result DataRows can be logged
• Is there any SimpleQuery message-type identification code currently? I expect to find that in client.c if anywhere. In client_proto it calls handle_client_work.
• handle_client_work does work with case 'Q' - but currently that's only to optionally suppress these kinds of messages "PQexec disallowed" - via cf_disable_pqexec setting.
• (Searching free-text for 'Q').
• admin_handle_client which identifies a Query message and then calls admin_parse_query using regexec. This is not used for a pooled connection but instead as a Pg-Client interface to PgBouncer process. It's not a middleware, it's a PQ-compatible server.

2b) My suggestion for how the PgBouncer codebase might accommodate this Feature:

• Detect "Listen" after the cf_disable_pqexec check in handle_client_work @ [client.c:895]. Make a function call to do the detection
• How to detect: In order to avoid the full scope of SQL parsing, keep the initial support for LISTEN very simple. The Listen statement must be the only statement in the Query message. It must begin with Listen (case-insensitive), only include two standard single-quote marks, and no escape sequences.
• When the LISTEN directive is detected, it is parsed for Listen pooling. There is a single Server object for Listen pooling. The listen pool, does a pool on a topic found in the LISTEN directive. If it's the first time a topic is encountered (case-sensitive), the LISTEN directive will be built and sent on the Listen Connection.
• If the Listen Connection disconnects, it will be reconnected, and all of the distinct LISTEN directives will be resent.
• On the Listen Connection, a new function handle_listenserver_work will be used (instead of the usual handle_server_work). When a NotificationResponse ('A') is received, it is not SQL. It's a structured message. The message body will be parsed into a Notification object structure. This will be passed to the Listen Pool for processing. The Listen Pool use the Notification object AND the raw message bytes. It will lookup the subscribed Client connections, enumerate them, and call a new function on the client objects client->notify to send the message bytes.

(More details are necessary, but this section demonstrates how basic SQL processing can be used suitably within the current code architecture of PGBouncer).

[AndrewJackson2020]

+1 for this feature.

One of the draws of postgres is the breadth of roles that it can take on. One of those roles is as a substitute for a pub sub system for those who can't set up dedicated infra for it/integrate with it. I have seen a number of times where a pgbouncer onboarding will stall because there is heavy use in the codebase of listen/notify which nullifies some of the benefit of pgbouncer.

[wflanagan]

I'll throw out that Postgres uses this same function to notify for things like table updates. As people remove services like redis and elasticsearch and centralize into PostgreSQL, the need for this is only going to increase.

We're stuck with this problem, because we use PgBouncer is the tool we use for scaling our workers. We have to come up with some solution to this long term.

[AndrewJackson2020]

Just wanted to post this link. Apparently there are purpose built listen/notify connection poolers that enable increased scalability on the listen/notify functionality. Still would be very nice to have this functionality in pgbouncer.

https://github.com/johto/allas

[jedwards1211]

allas uses an old version of Go libpq and doesn't support scram-sha-256, so the question is, is it easier to add scram-sha-256 support to allas, or add a Listen/Notify pool mode to pgbouncer?

[jedwards1211]

The Listen statement must be the only statement in the Query message. It must begin with Listen (case-insensitive), only include two standard single-quote marks, and no escape sequences.

LISTEN takes the channel name as an identifier, not a string literal. This could be an unquoted identifier or a double-quoted identifier. A double quote inside an identifier by using two double quotes, so foo"bar is quoted as "foo""bar".

[AndrewJackson2020]

allas uses an old version of Go libpq and doesn't support scram-sha-256, so the question is, is it easier to add scram-sha-256 support to allas, or add a Listen/Notify pool mode to pgbouncer?

I know little about allas but I suspect it would be easier to update that app. Pgbouncer does not parse queries because of its single threaded nature (time spent parsing a query is not spent servicing active queries). This may change if/when the multi threading PR gets merged.