Docs: mention lock-mode analysis as a complementary CI step?

[flvmnt]

Hi — I recently submitted a PR (#693) adding a section about combining PGLT with a lock-mode analyzer in CI. It was closed, and I totally understand — unsolicited docs PRs from third-party tool authors can feel like marketing.

I wanted to ask in a lower-pressure way: would you be open to a small "Related tools" or "See also" mention in the CI guide? The idea is that PGLT validates SQL correctness (syntax, types, semantics), but doesn't analyze lock modes or runtime risk of DDL statements — which is a separate concern that teams running migrations care about.

I wrote a blog post explaining the two-tool CI pattern: syntax/semantic validation (PGLT) + lock-mode analysis. Happy to share the link once it's published if that's useful as a reference, even if it doesn't end up in the docs.

No pressure either way — just wanted to ask instead of showing up with another PR. Thanks for PGLT, it's a great tool.

[psteinroe]

hey, thanks for getting back! and sorry for auto-closing it, it really looked like a marketing-motivated and ai-generated pull request. Especially since the link was a 404 and google did not return any results for pgfence as a new project

I wanted to ask in a lower-pressure way: would you be open to a small "Related tools" or "See also" mention in the CI guide? The idea is that PGLT validates SQL correctness (syntax, types, semantics), but doesn't analyze lock modes or runtime risk of DDL statements — which is a separate concern that teams running migrations care about.

I am certainly open for a Related Work section in our documentation. But our tools overlap quite a bit, and I would rather just list alternative options with a honest summary of each and let the user decide instead of preparing the decision for them.

For example, we also have a few lint rules that do lock analysis, e.g. https://pg-language-server.com/latest/reference/rules/lock-timeout-warning, and plan to add more. I like your idea of parsing orm typescript migrations a lot though, something i have on my list too!

Would you be okay with this approach?

[flvmnt]

Thanks for getting back, and no worries about the auto-close. A docs PR from a third-party tool author was the wrong way to start the conversation.

Quick correction on the broken link: the original PR pointed to pgfence.dev, which was wrong on my end. The correct URL is pgfence.com, and the npm package is @flvmnt/pgfence. Sorry about the confusion.

A balanced "Related Work" section that lists alternatives honestly sounds like exactly the right approach. I'd be happy to submit a PR, or just provide input if you'd rather write it yourself.

I spent some time looking through PGLT's rules, and you're right that there's significant overlap on the core DDL checks. Your lock timeout warning, concurrent index enforcement, NOT VALID constraint handling, and destructive operation rules cover a lot of the same ground. And the rules you've ported from Eugene and Squawk are solid.

To be straightforward about where the tools genuinely differ:

What pgfence does that PGLT doesn't (currently):

• Extracts SQL from ORM migration files (TypeORM queryRunner.query(), Knex builder chains, Sequelize queryInterface calls, Drizzle, Prisma), so teams that don't write raw .sql files can still get analysis
• Adjusts risk levels based on table row counts when stats are provided (a CREATE INDEX on 100 rows vs on 50M rows are very different production concerns)
• Outputs complete multi-step expand/contract SQL sequences for dangerous patterns (not just "use NOT VALID", but the full recipe with the backfill strategy, the CHECK constraint, the VALIDATE, etc.)
• Tracks transaction state across statements for policy enforcement: lock_timeout ordering (must be set before dangerous DDL), detecting NOT VALID + VALIDATE in the same transaction, flagging compounding ACCESS EXCLUSIVE locks

Where PGLT is stronger:

• Real-time feedback in the editor via LSP (pgfence is CLI/CI only)
• Rust performance for large codebases
• Schema cache integration for checking function volatility against actual DB metadata
• Broader community and ecosystem

For teams already using PGLT in their editor, the biggest gap pgfence fills is ORM extraction and size-aware scoring. For teams that only have raw SQL migrations, PGLT's built-in rules already cover the core safety checks.

Re: ORM TypeScript migration parsing, I'd be happy to share how pgfence handles it (we built custom transpilers for Knex and Sequelize builder APIs that convert chained method calls to SQL before analysis). If that's useful for your roadmap, just let me know.

[psteinroe]

that sounds great @flvmnt! Would love a docs pr for this :)

as a heads up: Since pgfence is MIT licensed, I prompted a pgfence-inspired expansion to our rules into existence in #703. I still need to review it but it looks good overall. We always explicitly mention and link the source of the rule in our docs (see here and here for an example). I would love to merge it to make the world safer, and I hope you are fine with it?

On the TS parsing: I digged into the code already and it looks super fun. Will play around with it when i find the time - thanks! :)

[flvmnt]

Absolutely, go for it. Really cool to see pgfence's rules making their
way into PGLT, and the PLAN.md is impressively thorough.

I went through the PR since I know these rules inside out, and wanted
to share some notes that might save you review time. Mostly around the
TransactionState infrastructure, the individual statement rules are
solid.

Cross-statement / TransactionState:

• update_from_stmt() doesn't process BEGIN/COMMIT/ROLLBACK yet, so
  not_valid_constraints and access_exclusive_tables accumulate across
  the entire file and never reset. This would cause false positives in
  ban_not_valid_validate_same_transaction and warn_wide_lock_window
  when statements are in separate transactions. My transaction-state.ts
  has a state machine with depth tracking and savepoint support that
  handles this.

• access_exclusive_tables currently tracks all AlterTableStmt subtypes,
  but a few of them take lighter locks: AT_AddColumn is technically
  ACCESS EXCLUSIVE but instant on PG11+ (metadata-only, so the lock
  window is negligible), AT_ValidateConstraint is SHARE UPDATE
  EXCLUSIVE, and non-concurrent CREATE INDEX is SHARE, not ACCESS
  EXCLUSIVE. The isAccessExclusiveStatement function in policy.ts
  filters these out for wide-lock-window detection.

• Constraint names in the NOT VALID tracking aren't table-qualified,
  so two different tables with a constraint named e.g. "check_positive"
  would false-positive.

Minor things:

• ban_truncate severity is Warning, I have it as CRITICAL since
  TRUNCATE is irreversible data loss
• ban_alter_enum_add_value always reports ACCESS EXCLUSIVE, but on
  PG12+ it's instant and doesn't block table operations
• is_dangerous_lock_stmt() only checks AlterTableStmt and IndexStmt
  but misses DropStmt, TruncateStmt, VacuumStmt, ReindexStmt,
  RenameStmt, RefreshMatViewStmt for the timeout rules

Things the Rust version actually does better than pgfence (I just
pushed fixes for some of these):

• ban_vacuum_full correctly handles VACUUM (FULL false) by checking
  the DefElem arg value. I was only checking defname.
• CREATE TRIGGER lock mode is right as SHARE ROW EXCLUSIVE. I had
  ACCESS EXCLUSIVE, which was wrong. Fixed.
• ban_add_exclusion_constraint also checks CREATE TABLE inline
  constraints, not just ALTER TABLE. Nice catch.
• warn_refresh_matview_concurrent correctly uses EXCLUSIVE lock. I
  had SHARE UPDATE EXCLUSIVE. Also fixed.
• The timeout rules skip checks when the table was created in the
  same transaction, which is a smart optimization I didn't have.

Happy to answer questions on any of the Postgres locking semantics
if it helps with the review.

I'll get the Related Work docs PR up this week.