PGX v5

[weusego]

There's a new pgx v5 beta announced and available for testing already.

jackc/pgx#1273

Will pggen be ported to support pgx v5? Some major incompatible changes at it seems.

[jschaf]

I've been following the beta threads closely. I'd like to upgrade but looks like a pretty decent lift. I'll wait until it's released before planning the migration.

[weusego]

Thank for for the switch response! Yes, lots of changes there.

We're currently in the situation to start a project with pgx v4 (with pggen of course) but then needing to migrate at some (soon?) point.

[vikstrous2]

FYI The official release happened 10 days ago.

[jschaf]

I've started figuring out the work involved here. The overall goal is that pggen will migrate to pgx v5.

Some helpful migration threads:

• https://github.com/jackc/pgx/blob/master/CHANGELOG.md#v500-september-17-2022
• https://brandur.org/fragments/pgx-v5-sqlc-upgrade

The upgrades in PGX v5 that affect pggen:

• A bunch of pgtype types moved or were deleted.
• Use of codecs for encoding and decoding--separate from values.
• Support for tracing and query rewriting.
• The batch interface now takes a callback to decode.

As a high-level plan:

• Start a pgxv5 branch
• Rewire the types so that simple queries work.
• Support query tracing and rewriting in pggen-generated code.
• Allow custom codec overrides per query column and arg.
• Add support for batch queries.

I think most code should be straightforward to upgrade. The tricky upgrades will involve batch queries since the API dramatically changed, and maybe some--go-type flag usage.

[farazfazli]

Some changes are easier than others. Looking at the errors after pointing the generated file to v5, here are some of the simpler changes:

1. Change []pgtype.DataType to []pgtype.Type
2. Change *pgtype.ConnInfo to *pgtype.Map
3. Change pgtype.NewConnInfo to pgtype.NewMap()
4. Change ci.RegisterDataType(&typ) to ci.RegisterType(&typ)
5. Change tr.connInfo.DataTypeForName(name) to tr.connInfo.TypeForName(name)

Some other changes are needed for newTypeResolver, NewTypeValue, findValue, and setValue but unsure what those are. @darkxanter I'm curious what manual changes you did between v4 and v5 to get everything working properly.

@jschaf What's the current status of the migration to pgx v5?

[jschaf]

I would like to migrate, but it's been a low priority for me. The main reason is that we use the batch interfaces almost exclusively for our API endpoints, and I'm not looking forward to migrating to the callback approach (which is better).

Some other changes are needed for newTypeResolver, NewTypeValue, findValue, and setValue

Some of those probably get easier. In particular, pgxv5 has a nicer interface for building composite types.

[MarioIshac]

I am interested in a migration to pgx5, since pgx5 exposes a pipelining API I'd like to use on the same connection that I create the querier object from.

[jschaf]

Okay, how about this for a plan to build some momentum?

• Upgrade to pgxv5 with a new release for the "easy" changes--no batch queries, and maybe skip composite types
• Add new releases to catch up on features.

How valuable is pggen if it uses pgxv5 with a more limited feature set?

[aight8]

For me 100% valuable because it allows to use pggen with pgx v5 in general.
Maybe you should put batching behind an opt-in flag anyway because a lot of code is generated but if you don't use batching then it is unnecessary.
I personally create pg functions.

[MarioIshac]

I agree with aight. For multiple queries, I use either stored procedures / functions or pipelining. Having pgx5 for the pipelining support, as well as a flag to disable batch creation, would be great.

[kirk-anchor]

It would be great to have pgxv5 support. Batch queries are not essential to me but composite types are. I considered switching to sqlc for pgxv5 support but its lack of composite type support makes it not a viable alternative to pggen.