Non-nullable types generate pointer types

[abijr]

When using JOIN in the query, non-nullable types become pointer types in the result struct.

Is this expected behaviour? And if so, why?

Here's a minimal example

[jschaf]

Thanks for the bug report with a minimal example, much appreciated!

Yes, this is a known limitation. The readme has a blurb describing why the limitation exists. For this specific example, pggen gives up if the string "JOIN" appears in the query: internal/pginfer/nullability.go. From the docs:

Choosing an appropriate type is more difficult than might seem at first glance due to null. When Postgres reports that a column has a type text, that column can have both text and null values. So, the Postgres text represented in Go can be either a string or nil. pgtype provides nullable types for all built-in Postgres types. pggen tries to infer if a column is nullable or non-nullable. If a column is nullable, pggen uses a pgtype Go type like pgtype.Text. If a column is non-nullable, pggen uses a more ergonomic type like string. pggen's nullability inference in internal/pginfer/nullability.go is rudimentary; a proper approach requires a full explain plan with some control flow analysis.

I'd like to improve the nullability propagation but it seems to be a hard (or at least not-easy) problem. As far as I can tell, the way to do it is:

• Generate and parse the EXPLAIN plan. I started down this road and have an unused plan parser sitting in https://github.com/jschaf/pggen/blob/main/internal/pgplan/pgplan.go.

• Start at the bottom of the plan and keep track of column type info, including nullability as you traverse up. I'm not a compiler expert, but I think this is control flow analysis. As an example, rules for some common cases:

  • For inner joins, nullability of projected columns stays the same.
  • For left joins, nullability of the left columns stays the same and all right columns become nullable.

The docs are a bit out of date after https://github.com/jschaf/pggen/issues/22. pggen now uses pointer types to represent nullable strings and integers and pgtype structs for everything else.

As a workaround, you can instruct pggen to use non-pointer types with:

pggen gen go --schema-glob file --query-file glob \
    --go-type int8=int \
    --go-type text=string

[abijr]

Thanks for the details!