bug: pindexer db init fails

[conorsch]

Describe the bug
I'm unable to get pindexer running locally in the dev env.

To Reproduce
Steps to reproduce the behavior:

1. Use nix dev env https://guide.penumbra.zone/dev/dev-env
2. cargo run --release --bin pd -- network unsafe-reset-all
3. just dev
4. Observe failure in pindexer startup

Expected behavior
I expect that pindexer will start fine in the default dev env. Instead, it fails on startup, with error screenshotted below.

Screenshots

Additional context
It's possible that the ad-hoc postgres grants in the deployments/compose/process-compose-postgres.yml are no longer correct, or the problem could be in the sql logic within pindexer. Whatever it is, I could use a hand sorting out the correct settings, so I can continue setting up pindexer in other contexts.

[conorsch]

Unsurprisingly, @aubrika was a huge help here. While debugging locally, I stumbled upon the [sqlfluff] tool, which can be used to lint sql files and find parsing errors:

❯ sqlfluff lint --dialect postgres crates/bin/pindexer/src/dex/dex.sql
== [crates/bin/pindexer/src/dex/dex.sql] FAIL
L:   7 | P:   1 | LT05 | Line is too long (81 > 80). [layout.long_lines]
L:   9 | P:   1 | LT05 | Line is too long (82 > 80). [layout.long_lines]
L:  12 | P:   1 |  PRS | Line 12, Position 1: Found unparsable section: 'CREATE
                       | DOMAIN IF NOT EXISTS Amount AS NU...'
WARNING: Parsing errors found and dialect is set to 'postgres'. Have you configured your dialect correctly?

That's probably worth sticking in CI.

[ejmg]

my PR for cometindex fixes some logic that is connected to this type of bug which IMO falls from the tacit contract on AppView that an implementer's init_chain requires sanitary SQL that makes sure to check/tear down anything that is defined for that specific indexer.

[conorsch]

Resolved by #4760 & #4762.