getquill/quill

Compile-time Language Integrated Query for Scala

Alexander Ioffe

@deusaquilus

yeah, lots of that in postgres

Li Haoyi

@lihaoyi-databricks

doesn't compare to the time where deleting old records made the query planner go haywire and start doing table scans

Alexander Ioffe

@deusaquilus

heh, maybe liftUnnestQuery(list)

SQL query planners are the bane of my existance

half the time if they'd just cache a complex sub-view the entire problem would be solved but there's no directive in SQL to do that. You'd think that what CTFs do but it's not

Li Haoyi

@lihaoyi-databricks

basically SQL is the wrong level of abstraction. It tries to hide the implementation, but whether a query runs in 40milliseconds or 40minutes actually matters for a lot of use cases...

Most of the time I would be happier writing query plans directly

Alexander Ioffe

@deusaquilus

Lol, welcome to my life

Li Haoyi

@lihaoyi-databricks

Like I want to specify what index the query will use, and if I want a table scan I'll ask for it thank you very much

Alexander Ioffe

@deusaquilus

The problem is, if we start doing that we're basically back to writing stored-procs... that's essentially what they do

Li Haoyi

@lihaoyi-databricks

sounds good to me

I think this query plan funkiness is a large reason why "dumber" databases like Mongo took off

Alexander Ioffe

@deusaquilus

Nah, stored-procs are a nightmare to maintain. They're too low level.

Li Haoyi

@lihaoyi-databricks

sure talking to mongo may involve over-fetching tons of data, and lots of round-trips, but at least it's a predictable about of over-fetching and round trips

whereas postgres things hum along nicely until suddenly your query plan crosses some heuristic and all hell breaks loose

and naturally it only happens in production

hooray

Alexander Ioffe

@deusaquilus

... and that's databases in a nutshell!

that's why databases are a sub-speciality

that's why and entire class of pseudo-engineer was created to manage them

i.e. the DBA

Problem is, now we're in the Post-SQL, Post-DBMS and by the time a DBA time would effectively model the data, the requirements would totally change

I remember 10 years ago Gartner saying "With the rapid decrease of DBA staffing, data governance will increase to become a challenge in the modern corporation"

Li Haoyi

@lihaoyi-databricks

I don't really see how a DBA would deal with this stuff any better than I would though

Alexander Ioffe

@deusaquilus

He wouldn't. That's the problem

Li Haoyi

@lihaoyi-databricks

in the end its still "postgres decided to go haywire in production because N rows became N+1"

I complain a lot about postgres but I don't imagine MySQL is any better, it's not known for having a lack of footguns

Alexander Ioffe

@deusaquilus

I think MySQL is worse

nafg

@nafg

Postgres is way better than MySQL

Also, MySQL is way better than it used to be...

At least that's my understanding

Alexander Ioffe

@deusaquilus

SQL Server and Oracle are a bit more consistent in how the handle workloads but they'll cost you an arm and a leg (and your immortal soul for the latter as well)

I think a sophisticated system of Query-hinting would solve 95% of problems. I.e something like:

select foo, bar from #cache(select  bar baz from someplace) as s join something sn on #index(sn.foo = bar)

Alexander Ioffe

@deusaquilus

Anyhow, I really don't like liftQuery actually. I think it should be replaced with liftDataset in Spark, and the inLifted operator
(e.g. people.filter(p => p.name inLifted (set)) )

Maybe inLiftedSet

Then there should be liftUnest which does something like we did above

it would be really nice to have just people.filter(p => p.name inSet (lift(set))) though

Maybe for Dotty I could do that

I already have multiple interpretations of lift in Dotty

Li Haoyi

@lihaoyi-databricks

lol i feel like 80% of my postgres performance optimization efforts are just splitting up big queries into smaller ones and suffering the additional/unnecessary round trips just so the query planner doesn't do something stupid again

I just had to do that

again

"filtering a few dozen items from one table and joining into two other tables, time to do three huge table scans"

Alexander Ioffe

@deusaquilus

Reposting here. Quill ZIO JDBC and Quill ZIO Cassandra are both out. They will become available on repo1.maven.org as soon as their next re-index happens.
(right now you can get them from https://oss.sonatype.org/)

Alexander Ioffe

@deusaquilus

doing something like infix"""SELECT * from employee where name = ppp for update""".as[Query[Employee]] maybe?

@juanux Have a look at infix. There's FOR UPDATE is in our example code.
https://getquill.io/#extending-quill-infix

@TheMover Contains on multiple columns doesn't work. Sorry.

druhinsgoel

@druhinsgoel

@deusaquilus I have the following dynamic query function that worked fine on 3.5.3 but throws a nasty error during runtime in any subsequent version:

val ctx = new SqlMirrorContext(PostgresDialect, SnakeCase)

import ctx._

case class Person(id: Long, name: String, age: Int)

def queryPerson(
    idOpt: Option[Long] = None
) = {
  ctx.run(
    dynamicQuery[Person]
      .filterOpt(idOpt)((person, id) => quote(person.id == id))
   )
}

scala.ScalaReflectionException: class io.getquill.Udt in JavaMirror with ScalaReflectClassLoader(file:/home/sbtRunnerContainer/.cache/coursier/v1/https/repo1.maven.org/maven2/org/scala-lang/scala-reflect/2.13.4/scala-reflect-2.13.4.jar parent = WrappedClassLoader(CachedClassloader {
  parent: TestInterfaceLoader(/home/sbtRunnerContainer/.sbt/boot/scala-2.12.10/org.scala-sbt/sbt/1.3.13/test-interface-1.0.jar,xsbt.boot.BootFilteredLoader@74e28667)
  urls:
    file:/home/sbtRunnerContainer/.cache/coursier/v1/https/repo1.maven.org/maven2/org/scala-lang/scala-library/2.13.4/scala-library-2.13.4.jar
})) of type class sbt.internal.ScalaReflectClassLoader with classpath [file:/home/sbtRunnerContainer/.cache/coursier/v1/https/repo1.maven.org/maven2/org/scala-lang/scala-reflect/2.13.4/scala-reflect-2.13.4.jar] and parent being WrappedClassLoader(CachedClassloader {
  parent: TestInterfaceLoader(/home/sbtRunnerContainer/.sbt/boot/scala-2.12.10/org.scala-sbt/sbt/1.3.13/test-interface-1.0.jar,xsbt.boot.BootFilteredLoader@74e28667)
  urls:
    file:/home/sbtRunnerContainer/.cache/coursier/v1/https/repo1.maven.org/maven2/org/scala-lang/scala-library/2.13.4/scala-library-2.13.4.jar
}) of type class sbt.internal.classpath.WrappedLoader with classpath [file:/home/sbtRunnerContainer/.cache/coursier/v1/https/repo1.maven.org/maven2/org/scala-lang/scala-library/2.13.4/scala-library-2.13.4.jar] and parent being CachedClassloader {
  parent: TestInterfaceLoader(/home/sbtRunnerContainer/.sbt/boot/scala-2.12.10/org.scala-sbt/sbt/1.3.13/test-interface-1.0.jar,xsbt.boot.BootFilteredLoader@74e28667)
  urls:
    file:/home/sbtRunnerContainer/.cache/coursier/v1/https/repo1.maven.org/maven2/org/scala-lang/scala-library/2.13.4/scala-library-2.13.4.jar
} of type class sbt.internal.classpath.ClassLoaderCache$Key$CachedClassLoader with classpath [file:/home/sbtRunnerContainer/.cache/coursier/v1/https/repo1.maven.org/maven2/org/scala-lang/scala-library/2.13.4/scala-library-2.13.4.jar] and parent being TestInterfaceLoader(/home/sbtRunnerContainer/.sbt/boot/scala-2.12.10/org.scala-sbt/sbt/1.3.13/test-interface-1.0.jar,xsbt.boot.BootFilteredLoader@74e28667) of type class xsbt.boot.Launch$TestInterfaceLoader$3 with classpath [file:/home/sbtRunnerContainer/.sbt/boot/scala-2.12.10/org.scala-sbt/sbt/1.3.13/test-interface-1.0.jar] and parent being xsbt.boot.BootFilteredLoader@74e28667 of type class xsbt.boot.BootFilteredLoader with classpath [<unknown>] and parent being sun.misc.Launcher$AppClassLoader@70dea4e of type class sun.misc.Launcher$AppClassLoader with classpath [file:/app/sbt/sbt/bin/sbt-launch.jar] and parent being sun.misc.Launcher$ExtClassLoader@3c3d9b6b of type class sun.misc.Launcher$ExtClassLoader with classpath [file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/sunec.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/sunpkcs11.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/sunjce_provider.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/dnsns.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/cldrdata.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/jaccess.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/nashorn.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/zipfs.jar,file:/usr/lib/jvm/java-1.8-openjdk/jre/lib/ext/localedata.jar] and parent being primordial classloader with boot classpath [/usr/lib/jvm/java-1.8-openjdk/jre/lib/resources.jar:/usr/lib/jvm/java-1.8-openjdk/jre/lib/rt.jar:/usr/lib/jvm/java-1.8-openjdk/jre/lib/sunrsasign.jar:/usr/lib/jvm/java-1.8-openjdk/jre/lib/jsse.jar:/usr/lib/jvm/java-1.8-openjdk/jre/lib/jce.jar:/usr/lib/jvm/java-1.8-openjdk/jre/lib/charsets.jar:/usr/lib/jvm/java-1.8-openjdk/jre/lib/jfr.jar:/usr/lib/jvm/java-1.8-openjdk/jre/classes] not found.
    at scala.reflect.internal.Mirrors$RootsBase.staticClass(Mirrors.scala:145)
    at scala.reflect.internal.Mirrors$RootsBase.staticClass(Mirrors.scala:29)
    at io.getquill.quat.QuatMakingBase$DefiniteValue$1$$typecreator1$1.apply(QuatMaking.scala:238)
    at scala.reflect.api.TypeTags$WeakTypeTagImpl.tpe$lzycompute(TypeTags.scala:237)
    ...

That's the error ^^

Where communities thrive

People

Repo info

Activity