Support for multi-query statements

[matthughes]

The postgres protocol supports sending multiple queries across different tables/views (separated by semi-colon) in one request and receiving the results. Currently Skunk explicitly does not support this:

From Query.scala

          case ReadyForQuery(_) =>
            new SkunkException(
              message   = "Multi-statement queries and commands are not supported.",
              hint      = Some("Break up semicolon-separated statements and execute them independently."),
              sql       = Some(stmt.sql),
              sqlOrigin = Some(stmt.origin),
            ).raiseError[F, Unit].whenA(multipleStatements)

Even if you have parallelizeable queries in a transaction, currently clients are forced to submit the queries one at a time, waiting for the results of the first query before submitting the second query. If you have a lot of queries and a decent amount of latency between your app/db servers this can add up.

I'm interested in adding support for this and am digging around but wouldn't mind if anyone had any pointers or reasons why this could never work with the Skunk model.

[tpolecat]

How would you imagine it looking? What would the type of such a query be?

[rolang]

Hello, I had another use case for this, just to provide some input. I am trying to replace flyway that we use for forward-migrations only in our codebase using skunk and bootstrapped dumbo.

I started by reading the files which can contain multiple statements and then just fileContent.split(';') to split it into single-query statements which worked fine until I encountered cases with like CREATE FUNCTION statements having ; all over the place which made it more difficult to parse.

Later I found out that one can just send the whole file content to Postgres as it supports multi-query statements which made it much easier. In this case I was interested on running multiple statements within a transaction and don't really care about the output as long as it is not an error, similar to how Flyway would handle it. Flyway also doesn't care whether you run queries or commands in your migration script, as long as there is no error from Postgres it will execute it and update the migration state.

I currently applied a workaround, basically by changing the signature of finishUp to keep accumulating received backend messages instead of failing together with a multiple completion case case class Multiple(list: List[Completion]) extends Completion. It did the job for this case, but not sure about a good model for this in skunk.

// from
def finishUp(stmt: Statement[_], multipleStatements: Boolean = false): F[Unit]
// to
def finishUp(stmt: Statement[?], messages: List[BackendMessage] = Nil): F[List[BackendMessage]]

Sources:

• https://github.com/rolang/dumbo/blob/main/modules/core/shared/src/main/scala/skunk/net/protocol/Query.scala#L37
• https://github.com/rolang/dumbo/blob/main/modules/core/shared/src/main/scala/skunk/data/Completion.scala

[rolang]

I believe I found a better solution for the case I described above 🤔 Just raised #1023