使用Slick的3.0.0流结果和Postgresql的正确方法是什么？ [英] What is the right way to work with slick's 3.0.0 streaming results and Postgresql?
问题描述
我正在尝试弄清如何使用流畅的流媒体。我将slick 3.0.0与postgres驱动程序配合使用
I am trying to figure out how to work with slick streaming. I use slick 3.0.0 with postgres driver
情况如下：服务器必须将客户端数据序列分成大小限制的块（以字节为单位）。因此，我编写了以下简单查询：
The situation is following: server have to give client sequences of data split into chunks limited by size(in bytes). So, I wrote following slick query:
val sequences = TableQuery[Sequences]
def find(userId: Long, timestamp: Long) = sequences.filter(s ⇒ s.userId === userId && s.timestamp > timestamp).sortBy(_.timestamp.asc).result
val seq = db.stream(find(0L, 0L))
我将seq与akka流结合在一起 Source ，编写了自定义的 PushPullStage ，它限制数据大小（以字节为单位），并在达到大小限制时在上游完成。它工作正常。问题是-当我查看postgres日志时，我看到这样的查询：
select *从user_id = 0和timestamp> 0按时间戳排序；
I combined seq with akka-streams Source, wrote custom PushPullStage, that limits size of data(in bytes) and finishes upstream when it reaches size limit. It works just fine. The problem is - when I look into postgres logs, I see query like that
select * from sequences where user_id = 0 and timestamp > 0 order by timestamp;
因此，乍一看似乎正在进行很多（且不必要）的数据库查询，仅使用了每个查询中只有几个字节。用Slick进行流传输以最小化数据库查询并充分利用每个查询中传输的数据的正确方法是什么？
So, at first glance it appears to be much (and unnecessary) database querying going on, only to use a few bytes in each query. What is the right way to do streaming with Slick so as to minimize database querying and to make best use of the data transferred in each query?
推荐答案
使用Slick和Postgres进行流传输的正确方法包括三件事：
The "right way" to do streaming with Slick and Postgres includes three things:
-
必须使用db.stream （）
Must use db.stream()
必须在JDBC驱动程序中禁用 autoCommit 。一种方法是通过在 .transactionally 后缀后缀使查询在事务中运行。
Must disable autoCommit in JDBC-driver. One way is to make the query run in a transaction by suffixing .transactionally.
必须设置 fetchSize 设置为0以外的值，否则postgres会一次性将整个resultSet推送到客户端。
Must set fetchSize to be something else than 0 or else postgres will push the whole resultSet to the client in one go.
例如：
DB.stream(
find(0L, 0L)
.transactionally
.withStatementParameters(fetchSize = 1000)
).foreach(println)
有用的链接：
https：/ /github.com/slick/slick/issues/1038
https://github.com/slick/slick/issues/809
这篇关于使用Slick的3.0.0流结果和Postgresql的正确方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
