Postgres Session Variables

[ohuu]

I'm using quill-doobie with postgres and I'm trying to set a session variable like so SET myvar = 42;. Quill offers the ability to run raw sql so I tried to implement this query using that feature.

def setVar(value: Int): ConnectionIO[Unit] =
    val rawQuery = quote { (v: Int) =>
      infix"SET myvar = $v".as[Unit]
    }
    run(rawQuery(42)

However the compiler complains about not having a decoder for Unit which seems odd because surely the implementation of that would be to just return a Unit.

I tried changing the type to Int and then it compiles but I get an class java.lang.IllegalStateException exception (alas I haven't managed to get more info from that exception yet).

Is this possible to do in Quill? Maybe there's an example of doing exactly this or something similar I could take inspiration from?

[jilen]

You may want to cast result type to Query[Int] if the sql returns no rows.

[ohuu]

Thanks @jilen, I managed to solve it using Query[Int]. I decided to use the built in set_config function too:

def as(userId: Int): ConnectionIO[Int] =
    val rawQuery = quote { (id: Int) =>
      infix"select set_config('auth.id', '$id', false);".as[Query[Int]]
    }
    run(rawQuery(1)).map(_.head)

[ohuu]

@jilen actually this doesn't quite work! It works if I hard code the value passed to rawQuery (in this case 1) but if I replace that with userId I get an exception.

The even bigger problem is that if I try to handle the error using .handleError(err => ...) the handleError function is not called, instead the exception bubbles all the way up to the http4s logging middleware and I just get a single line in the log:

root 01:27:07.206 [io-compute-7] INFO org.http4s.server.middleware.Logger - service raised an error: class java.lang.IllegalStateException

I'm having a real hard time understanding what's going on here, why doesn't handleError do anything and why is the query failing when passing userId to it (I'm guessing it has something to do with dynamic queries?)

[guizmaii]

@ohuu What's the generated SQL query, please?