Postgresql and Haskell differ in how they print numbers at the limits of
a double's precision. When these numbers have whole digits, we have less
precision to give to the decimal part, so we need to be less accurate
than 1e15.
Not only is this not necessary, it can actually act as a barrier to optimisation. The reason I added it was because it seemed like a cheap way to stop someone writing `query' <- materialize query id` — if you return the materialized query from `materialized`, it won't work. Really we would need some sort of `runST` type trick here to do this properly, but that would be too invasive a change.
* Fix some unescaped markup in haddocks
This addresses #315
* Expand documentation on unsafeDefault
This copies the documentation in the cookbook to the haddocks,
addressing #233.
* Update docs/concepts/insert.rst
Co-authored-by: Ollie Charles <ollie@ocharles.org.uk>
---------
Co-authored-by: Ollie Charles <ollie@ocharles.org.uk>
Somewhere between PostgreSQL 11 and PostgreSQL 15, PostgreSQL's optimiser gained the ability to see "through" subqueries, and it seems to choose to do this even when we don't really want it to.
E.g., it started transforming the following:
```haskell
SELECT
x * y + x * y
FROM (
SELECT
a + b + c AS x
d + e + f AS y
FROM
foo
) _
```
into:
```haskell
SELECT
(a + b + c) * (d + e + f) + (a + b + c) * (d + e + f)
FROM
foo
```
before evaluating.
You can see how more complicated expressions nested several levels deep could get expanded into crazy big expressions. This seems to be what PostgreSQL actually does on Rel8 code that uses `rebind`. Compared to older versions of PostgreSQL, this increases the planning time and execution time dramatically.
Given that Rel8's `rebind` is intended to function as a "let binding", and the user needs to go out of their way to choose to use it (they could just use `pure` if they wanted the fully expanded expression), we want a way to force PostgreSQL to evaluate the `a + b + c` and the `d + e + f` first before worrying about trying to simplify `x * y + x * y`. Adding `OFFSET 0` to the inner query seems to achieve that.
```haskell
SELECT
x * y + x * y
FROM (
SELECT
a + b + c AS x
d + e + f AS y
FROM
foo
OFFSET
0
) _
```
The `Sql DBEq a` constraint on the return type of the aggregator was wrong. It also isn't quite right to have a `EqTable i` constraint on the input type of the `Aggregator`, because technically what we want is a `Sql DBEq` constraint on whichever column(s) within `i` are used by aggregation functions, but we don't know which columns were used at this point. We could give `distinctAggregate` a type like `Sql DBEq i => Aggregator (Expr i) a` and make people run it through `lmap` manually, but that makes it impractical to use with `ListTable` without exposing more machinery. So we just drop the equality constraint for now.
This is one possible "fix" to #168. With this we can `catListTable` arbitrarily deep trees of `ListTable`s.
It comes at a relatively high cost, however.
Currently we represent nested arrays with anonymous records. This works reasonably well, except that we can't extract the field from the anonymous record when we need it (PostgreSQL [theoretically](https://www.postgresql.org/docs/13/release-13.html#id-1.11.6.16.5.6) suports `.f1` syntax since PG13 but it only works in very limited situations). But it does mean we can decode the results using Hasql's binary decoders, and ordering works how we expect ('array[row(array[9])] < array[row(array[10])]'.
What this PR does is instead represent nested arrays as text. To be able to decode this, we need each 'DBType' to supply a text parser in addition to a binary decoder. It also means that ordering is no longer intuitive, because `array[array[9]::text] > array[array[10]::text]`. However, it does mean we can nest `catListTable`s to our heart's content and it will always just work.
Types in PostgreSQL can also be qualified with a schema. However, it's not sufficient to just change the type of `TypeInformation`'s `typeName` to `QualifiedName`, because a type isn't *just* a name. Postgres types can also be parameterised by modifiers (e.g., `numeric(7, 2)`) and array types of arbitrary depth (e.g., `int4[][]`).
To accomodate this, a new type is introduced, `TypeName`. Like `QualifiedName`, it has an `IsString` instance, so the common case (`schema` set to `Nothing`, no modifiers, scalar type) will continue working as before.
The main reason I wasn't happy with this before is that there was nothing stopping you from writing `materialize query pure`. This returned query would produce invalid SQL if you actually tried to use it, because it would attempt to reference a common table expression outside the scope of the `WITH` statement.
The "solution" here is just to throw a `rebind` around the result such that `materialize` incurs an extra `Table Expr b` constraint, which means that you can't return `Query (Query a)` because `Query a` can't satisfy a `Table Expr` constraint.
This does away with the weird variadic arguments thing we had going on with `function`.
Functions with no arguments are now written as:
```haskell
now :: Expr UTCTime
now = function "now" ()
```
Functions with multiple arguments are now written as:
```haskell
quot :: Sql DBIntegral a => Expr a -> Expr a -> Expr a
quot n d = function "div" (n, d)
```
Single-argument functions are written exactly as before.
This adds a new type `QualifiedName` for named PostgreSQL objects (tables, views, functions and sequences) that can optionally be qualified by a schema. Previously only `TableSchema` could be qualified in this way.
`QualifiedName` has an `IsString` instance so the common case (where the schema is `Nothing`) doesn't have to care about schemas (if `OverloadedStrings` is enabled).
This also refactors `TableSchema` to use `QualifiedName` for its `name` field and drops its `schema` field.
Thanks to @elldritch for the bug report and the inspiration.