The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/protocol-flow.html Description:
Greeting! Please consider the following exchange with a PG database (Kotlin + Reactor+ r2dbc-postgresql): ``` 295 1.063166 127.0.0.1 50591 127.0.0.1 32797 PGSQL 111 >Q ---> BEGIN ISOLATION LEVEL REPEATABLE READ, READ WRITE 296 1.063219 127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=1 Ack=56 Win=6373 Len=0 TSval=3266177882 TSecr=3728690767 301 1.069912 127.0.0.1 32797 127.0.0.1 50591 PGSQL 73 <C/Z 302 1.069938 127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=56 Ack=18 Win=6370 Len=0 TSval=3728690774 TSecr=3266177889 712 1.099829 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551 >Q ------> select * from ... (#2) 713 1.099858 127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=551 Win=6365 Len=0 TSval=3266177919 TSecr=3728690804 715 1.099985 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551 >Q ------> select * from ... (#4) 717 1.100009 127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=1046 Win=6358 Len=0 TSval=3266177919 TSecr=3728690804 719 1.100082 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551 >Q ------> select * from ... (#1) 720 1.100106 127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=1541 Win=6350 Len=0 TSval=3266177919 TSecr=3728690804 722 1.100164 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551 >Q ------> select * from ... (#3) 723 1.100192 127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=18 Ack=2036 Win=6342 Len=0 TSval=3266177919 TSecr=3728690804 735 1.114695 127.0.0.1 32797 127.0.0.1 50591 PGSQL 424 <T/D/C/Z ------> Results for #2 737 1.114741 127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=386 Win=6364 Len=0 TSval=3728690818 TSecr=3266177933 773 1.121732 127.0.0.1 32797 127.0.0.1 50591 PGSQL 1468 <T/D/D/D/D/D/D/D/D/D/D/D/D/C/Z ------> Results for #4 774 1.121757 127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=1798 Win=6342 Len=0 TSval=3728690826 TSecr=3266177941 785 1.126793 127.0.0.1 32797 127.0.0.1 50591 PGSQL 594 <T/D/D/D/C/Z ------> Results for #1 786 1.126820 127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=2336 Win=6334 Len=0 TSval=3728690831 TSecr=3266177946 805 1.135197 127.0.0.1 32797 127.0.0.1 50591 PGSQL 497 <T/D/D/C/Z ------> Results for #3 806 1.135222 127.0.0.1 50591 127.0.0.1 32797 TCP 56 50591 → 32797 [ACK] Seq=2036 Ack=2777 Win=6327 Len=0 TSval=3728690839 TSecr=3266177954 847 1.138848 127.0.0.1 50591 127.0.0.1 32797 PGSQL 68 >Q ------> COMMIT 848 1.138876 127.0.0.1 32797 127.0.0.1 50591 TCP 56 32797 → 50591 [ACK] Seq=2777 Ack=2048 Win=6342 Len=0 TSval=3266177958 TSecr=3728690843 853 1.144624 127.0.0.1 32797 127.0.0.1 50591 PGSQL 74 <C/Z ``` The application code is the following (for the sake of clarity): ``` val transactionalOperator = TransactionalOperator.create( transactionManager, DefaultTransactionDefinition() .apply { isolationLevel = TransactionDefinition.ISOLATION_REPEATABLE_READ } ) val users = Flux.defer { Flux.create { it.next(1) it.next(2) it.next(3) it.next(4) it.complete() } .parallel(4) .runOn(Schedulers.parallel()) .flatMap { databaseClient.sql( """ select * from … """.trimIndent()) .map { row, _ -> EntityConverter().convert(row) } .all() .reduce(EntityReducer()) } }.`as`(transactionalOperator::transactional) ``` I’m aware that Postgres doesn’t support cursor(portal) multiplexing so its impossible to fetch data for two selects simultaneously but should I make use of selects pipelining when I want to make the most of the DB? I mean to say does a single Postgres backend employs concurrent processing and its possible for a DB to transmit results for a query #2 (see the traffic) and concurrently parse/rewrite/plan/optimize the subsequent (pipelined) queries (issued from the same transaction). Neither chapter 55 nor 52 expand on it (or it’s hard to spot at least). Thanks!