libpg_query VS pgrx

This in combination with [pg_query](https://github.com/pganalyze/libpg_query) could be a very powerful combination that allows writing generic static analyzers.

It's true that the core PG code isn't written in a modular way that's friendly to integration piecemeal in other projects (outside of libpq).

For THIS PARTICULAR case, the pganalyze team has actually extracted out the parser of PG for including in your own projects:

https://github.com/pganalyze/libpg_query

#. SQLite WAL mode

From https://www.sqlite.org/isolation.html https://news.ycombinator.com/item?id=32247085 :

> [sqlite] WAL mode permits simultaneous readers and writers. It can do this because changes do not overwrite the original database file, but rather go into the separate write-ahead log file. That means that readers can continue to read the old, original, unaltered content from the original database file at the same time that the writer is appending to the write-ahead log

#. superfly/litefs: aFUSE-based file system for replicating SQLite https://github.com/superfly/litefs

#. sqldiff: https://www.sqlite.org/sqldiff.html https://news.ycombinator.com/item?id=31265005

#. dolthub/dolt: https://github.com/dolthub/dolt

> Dolt can be set up as a replica of your existing MySQL or MariaDB database using standard MySQL binlog replication. Every write becomes a Dolt commit. This is a great way to get the version control benefits of Dolt and keep an existing MySQL or MariaDB database.

#. pganalyze/libpg_query: https://github.com/pganalyze/libpg_query :

> C library for accessing the PostgreSQL parser outside of the server environment

#. Ibis + Substrait [ + DuckDB ]

> ibis strives to provide a consistent interface for interacting with a multitude of different analytical execution engines, most of which (but not all) speak some dialect of SQL.

> Today, Ibis accomplishes this with a lot of help from `sqlalchemy` and `sqlglot` to handle differences in dialect, or we interact directly with available Python bindings (for instance with the pandas, datafusion, and polars backends).

> [...] `Substrait` is a new cross-language serialization format for communicating (among other things) query plans. It's still in its early days, but there is already nascent support for Substrait in Apache Arrow, DuckDB, and Velox.

#. benbjohnson/postlite: https://github.com/benbjohnson/postlite

> postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.

> The proxy works by translating Postgres frontend wire messages into SQLite transactions and converting results back into Postgres response wire messages. Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables. The proxy also performs minor rewriting on these system queries to convert them to usable SQLite syntax.

> Note: This software is in alpha. Please report bugs. Postlite doesn't alter your database unless you issue INSERT, UPDATE, DELETE commands so it's probably safe. If anything, the Postlite process may die but it shouldn't affect your database.

#. > "Hosting SQLite Databases on GitHub Pages" (2021) re: sql.js-httpvfs, DuckDB https://news.ycombinator.com/item?id=28021766

#. awesome-db-tools https://github.com/mgramin/awesome-db-tools

Generally I agree that this would be great to have, and Postgres does have a set of libraries it already maintains as part of the main source tree (i.e. libpq, etc), and there is a shared set of code between the backend and the "frontend" (https://github.com/postgres/postgres/tree/master/src/common). So theoretically you could imagine the parser moving into that shared code portion, sharing code but not necessarily requiring linking to a library from the backend.

However, the challenge from what I've understood from past conversations with some folks working on Postgres core is that the parser is currently heavily tied into the backend - note the parser isn't just the scan.l/gram.y file, but also the raw parse node structs that it outputs. You can see how many files we pull in from the main tree that are prefixed with "src_backend": https://github.com/pganalyze/libpg_query/tree/15-latest/src/...

Further, there isn't a canonical way to output node trees into a text format today in core, besides the rather hard to work with output of debug_print_parse - there have been discussions on -hackers to potentially utilize JSON here, which may make this a bit easier. Note that in libpg_query we currently use Protobuf (but used to use JSON), which does have the benefit of getting auto-generated structs in the language bindings - but Protobuf is not used in core Postgres at all today.

All in all, I think there is some upstream interest, but its not clear that this is a good idea from a maintainability perspective.

Neat project!

Just in case its helpful to you, I (together with colleagues at pganalyze) maintain pg_query, which packages the Postgres parser as a library: https://github.com/pganalyze/libpg_query

Might be useful to include in your product as a way to run a quick syntax check on the query output by the LLM, without actually connecting to the database and causing an error in the logs.

I like that everyone is trying to make something like SQL that reads more naturally to them. More alternatives is good! SQL is a widely accepted standard, and has strictly defined and super broadly accepted semantics.

As someone who has written quite a few half-baked-for-general-use but fit-for-purpose SQL generator utilities over the years, I'll suggest that if you intend for a novel syntax to be a general SQL replacement then being isomorphic to SQL would massively increase usefulness and uptake:

1. novel syntax to SQL; check! Now novel syntax works with all the databases!

2. any valid SQL to novel syntax; a bit harder, but I'd start by using a SQL parser like https://github.com/pganalyze/libpg_query and translating the resulting AST into the novel syntax.

3. novel syntax to SQL back to novel syntax is idempotent; a nice side effect is a validator/formatter for "novel syntax"

4. SQL to novel syntax back to SQL is idempotent; a nice side effect is a validator/formatter for SQL, which would be awesome. (See also https://go.dev/blog/gofmt, which is where I learned this "round trip as formatter" trick.)

I don't mean for this to sound negative, and I know that 2, 3, and 4 are kind of hard. Thank you for building prql!

I feel like https://github.com/pganalyze/libpg_query should be the default choice for anything that needs a SQL parser. PL/SQL parsing is included there.

Rules are enforced by parsing statements into a syntax tree and checking all of the nodes against the provided rules. Statement parsing is done through bindings to the excellent libpg_query library, which uses Postgres's own statement parser to generate the syntax tree.

libpg_query extracts the parser (written in C) from the postgres project and packages it as a stand-alone library. This library is wrapped in other languages by other projects like:

Consider also the companies and work behind pgrx [0] and pgzx [1]:

[0] https://github.com/pgcentralfoundation/pgrx

[1] https://github.com/xataio/pgzx

If you like this (I do very much), you might also like pg_idkit[0] which is a little extension with a bunch of other kinds of IDs that you can generate inside PG, thanks to the seriously awesome pgrx[1] and Rust.

[0]: https://github.com/VADOSWARE/pg_idkit

[1]: https://github.com/pgcentralfoundation/pgrx

(disclosure, i work at supabase and have been developing TLEs with the RDS team)

Trusted Language Extensions refer to an extension written in any trusted language. In this case Rust, but it also includes: plpgsql, plv8, etc. See [0]

> PL/Rust is a more performant and more feature-rich alternative to PL/pgSQL

This is only partially true. plpgsql has bindings to low-level Postgres APIs, so in some cases it is just as fast (or faster) than Rust.

> Building a vector index (or any index for that matter) inside Postgres is a more involved process and can not be done via the UDF interface, be it Rust, C or PL/pgSQL

Most PG Rust extensions are written with the excellent pgrx framework [1]. While it doesn't have index bindings right now, I can certainly imagine a future where this is possible[2].

All that said - I think there are a lot of hoops to jump through right now and I doubt it's worth it for the Latern team. I think they are right to focus on developing a separate C extension

[0] TLE: https://supabase.com/blog/pg-tle

[1] pgrx: https://github.com/pgcentralfoundation/pgrx

[2] https://github.com/pgcentralfoundation/pgrx/issues/190#issue...

I’m currently playing with PostgreSQL, foreign data wrappers, and pgrx rust extensions. My development experience has been surprisingly smooth and enjoyable.

My main issue is that joins will be processed locally, so all the foreign data will be fetched before the join happens. But otherwise basic CRUD is easy.

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

https://github.com/pgcentralfoundation/pgrx

https://github.com/supabase/wrappers

I think maybe what you’re really looking for are the files here: https://github.com/pgcentralfoundation/pgrx/tree/c2eac033856...

Those are the internals we currently expose as unsafe “sys” bindings.

As we/contributors identify more that are desired we add them.

pgrx’ focus is on providing safe wrappers and general interfaces to the Postgres internals, which is the bulk of our work and is what will take many years.

As unsafe bindings go, we could just expose everything, and likely eventually will. There’s just some practical management concerns around doing that without a better namespace organization —- something we’ve been working.

The Postgres sources are not small. They are very complex, inconsistent in places, and often follow patterns that are specific to Postgres and not easy to generalize.

If you’ve never built an extension with pgrx, give it a shot one afternoon. It’s very exciting to see your own code running in your database.

pgrx is one of the greatest enabling innovations in the PG ecosystem in a long time.

Awesome to see so many high quality extensions come out of it.

https://github.com/pgcentralfoundation/pgrx

If you're interested in what my Threadripper 3970X does with it, there's some numbers in this PR: https://github.com/tcdi/pgrx/pull/1147