doing some googling and reading but not a lot of great results so far, but maybe folks here know more about these things :)

Bit of a curve ball: You can try to look as to how GraphQL relay connections are implemented in Postgres based systems. They deal with similar problems as you are describing 🙂. (Not quite the direct answer you were looking for, I guess, but may be easier googleable ^^)

from what I gather you would have to keep the connection exclusive to the resultset as long as you plan to use it, since cursors are automatically closed on transaction boundaries https://www.postgresql.org/docs/11/plpgsql-cursors.html

Yeah I got a bit intrigued, definitely not expert on this; One solution I found is encoding primary key / sort order in your cursors, then you won’t have limit/offset style problems. But not super satisfactory yet

another concern I have is that read transaction can block write transactions, depending on isolation level. Long reads bring overhead to writes

Out of curiosity, is the reason the result set is "unstable" that the database is constantly being updated and therefore paging includes previous results?

not sure what the rate of update is in this case @UGTAV6LR2, but yes that is the general issue with limit/offset style pagination, results across pages are not consistent in the face of inserts/deletes

cursor pagination

and yep

For GraphQL relay they make the client deal with that. Every node has a stable cursor for the basic query arguments, so that if one is deleted and you get an error while paginating, you can use an adjacent cursor and recover. Storing the whole resultset seems like it would be expensive to me, and I'd avoid it, especially for public APIs.

If I understand you correctly, this would limit your ability to scale horizontally as you need to direct your client to the same physical machine. And you need to keep a connection open, which is a precious resource. For those reason alone I would avoid such an approach

Have you considered a continuation token?

here's an example ... https://gist.github.com/mccraigmccraig/df68ad7c76f1709f2c776199c30412f0

the VALUES table expressions contain some literal values which need any quotes in them escaping... i can do this myself ofc, but wondered if there are any utility methods around jdbc.next to do it for me

the number of rows in the VALUES expressions varies though, and the number of VALUES expressions also varies

Sounds like you should be building the SQL with HoneySQL -- it'll lift those VALUES out as parameters so next.jdbc can use a prepared statement.

By the time you've built the string, it's too late for next.jdbc to do anything about it. Hence, build the SQL string and its parameters with HoneySQL and then pass the result to next.jdbc.

oh, yeah, i want to escape while i'm building the string, not after the fact

You can basically give HoneySQL your data structure as part of the overall DSL and let it figure stuff out for you.

and i don't really want to use honeysql because (at least last time i used it) it was hard to get it to generate exactly the sql i wanted

Was that with the 1.x version?

don't know. it was a while back. it worked ok, but it was quite hard work

You'll find the 2.x version is a lot more flexible and a lot more consistent. And it supports a lot more syntax than 1.x used to.

I guess I could ask "was I maintaining HoneySQL last time you tried it?" 🙂

lol, don't know

hmm. looks like honeysql 2.x is the solution i want for the future then... but sadly i don't have time to convert these queries now, so i'll have to hack some string escaping together for now

Maybe you could leverage HoneySQL 2.x just to build the VALUES substring?

dev=> (sql/format {:values [["abc'def" "x" 42 nil]]} {:inline true})
["VALUES ('abc''def', 'x', 42, NULL)"]

ooo - now that's a good idea - thanks @U04V70XH6!

hmm. this was trivial, until uuids - honeysql formats them without any quotes by default:

(sql/format {:values [["abc'def" (clj-uuid/v1) 42 nil]]} {:inline true})
;; => ["VALUES ('abc''def', c28fa6e0-8986-11ec-8ca3-a50d2de755f7, 42, NULL)"]

Create an issue - it's a bug (well, an omission).

What's our to inline today is a pretty small set of types. I can add support for more.

submitted, with a thought on a possible difficulty: https://github.com/seancorfield/honeysql/issues/385

fixed 🙂

speedy! 😄