Postgres Language Server: Implementing the Parser • Hacker News Board

Re: Postgres Language Server: Implementing the Parser

#31

Post by SR2Z » Fri, Dec 08, 2023, 10:43 PM UTC

atonse wrote: post #30

specialist wrote: post #15

Earlier quoted context omitted.

> ...getting the Parser right. It has been a lot of work. True. I have an ANTLR4 grammar for SQL. Am noob. Getting things "correct" was some effort. I have the quixotic goal of accepting multiple dialects. It's a lot of time referencing docs, playing with SQL fiddles, and learning how other grammars work. Now working on performance. Just as tough for noob me. TIL That means removing any potential ambiquities at runti…

If we're all being postgres-specific, isn't there presumably a grammar somewhere in postgres's code that is used to parse the query SQL? Wouldn't you all want to use that same file as a source so it's 100% identical to what Postgres is actually doing? Genuine question. But maybe the tooling is incompatible. Also, have you looked at Azure Data Studio? I'm wondering how much you can reuse from the work they did.

Not necessarily. It might be a recursive descent parser (to generate better error messages).

Re: Postgres Language Server: Implementing the Parser

#32

Post by paulddraper » Fri, Dec 08, 2023, 10:44 PM UTC

2mm3mm3 wrote: post #6

When will PG finally start using tasks instead of superheavyweight threads? When will they automatically precompile/hash statements to avoid reparsing? And when materialized views will be automatically updated? Where is an alternative to SQL Server Always On Availability Group released >10 years ago? PostgreSQL is the only database where guys recommend you to configure a separate connection pooler, because their data…

lol, salty

Re: Postgres Language Server: Implementing the Parser

#33

Post by CoastalCoder » Fri, Dec 08, 2023, 11:42 PM UTC

sjansen wrote: post #29

CoastalCoder wrote: post #27

Earlier quoted context omitted.

> use the actual parser from the Postgres server. so no grammar definition and the like. Doesn't Postgres use Lex/Yacc to describe the grammar?

It actually uses Flex/Bison, and does take advantage of the extended features they provide.

Thanks for the correction. But either way, doesn't this mean that a formal lexicon/grammar are available for the language server to draw upon?

Re: Postgres Language Server: Implementing the Parser

#34

Post by aidos » Sat, Dec 09, 2023, 12:08 AM UTC

atonse wrote: post #30

specialist wrote: post #15

Earlier quoted context omitted.

> ...getting the Parser right. It has been a lot of work. True. I have an ANTLR4 grammar for SQL. Am noob. Getting things "correct" was some effort. I have the quixotic goal of accepting multiple dialects. It's a lot of time referencing docs, playing with SQL fiddles, and learning how other grammars work. Now working on performance. Just as tough for noob me. TIL That means removing any potential ambiquities at runti…

If we're all being postgres-specific, isn't there presumably a grammar somewhere in postgres's code that is used to parse the query SQL? Wouldn't you all want to use that same file as a source so it's 100% identical to what Postgres is actually doing? Genuine question. But maybe the tooling is incompatible. Also, have you looked at Azure Data Studio? I'm wondering how much you can reuse from the work they did.

Yacc, I believe.

https://www.postgresql.org/docs/7.1/parser-stage.html

Re: Postgres Language Server: Implementing the Parser

#35

Post by Yasuraka » Sat, Dec 09, 2023, 12:50 AM UTC

Yasuraka wrote: post #9

2mm3mm3 wrote: post #6

When will PG finally start using tasks instead of superheavyweight threads? When will they automatically precompile/hash statements to avoid reparsing? And when materialized views will be automatically updated? Where is an alternative to SQL Server Always On Availability Group released >10 years ago? PostgreSQL is the only database where guys recommend you to configure a separate connection pooler, because their data…

I'll take vertical sharding until no longer possible and then dropping all further requests over touching Always On Availability Groups ever again

vertical scaling*

Re: Postgres Language Server: Implementing the Parser

#36

Post by emptysea » Sat, Dec 09, 2023, 2:22 AM UTC

claytongulick wrote: post #28

kiwicopple wrote: post #2

(i'm on the supabase team) this is an update from the launch here: https://news.ycombinator.com/item?id=37020610 The focus for the past few months has been getting the Parser right. It has been a lot of work. I'll ping Philipp and get him to join the discussion if there are any questions We'd also love more contributors, if this sort of thing is up your alley

Only tangentially related, but if it's of any interest I'm about 90% done with a PEG grammar for the PostgREST query DSL for similar reasons. Happy to share it if it'd be useful.

Do you have a link to the DSL? Curious to learn more

Re: Postgres Language Server: Implementing the Parser

#37

Post by _a_a_a_ » Sat, Dec 09, 2023, 2:28 AM UTC

specialist wrote: post #15

kiwicopple wrote: post #2

(i'm on the supabase team) this is an update from the launch here: https://news.ycombinator.com/item?id=37020610 The focus for the past few months has been getting the Parser right. It has been a lot of work. I'll ping Philipp and get him to join the discussion if there are any questions We'd also love more contributors, if this sort of thing is up your alley

> ...getting the Parser right. It has been a lot of work. True. I have an ANTLR4 grammar for SQL. Am noob. Getting things "correct" was some effort. I have the quixotic goal of accepting multiple dialects. It's a lot of time referencing docs, playing with SQL fiddles, and learning how other grammars work. Now working on performance. Just as tough for noob me. TIL That means removing any potential ambiquities at runti…

Where is the SQLite test suite, please? I'd be very interested.

There are already SQL grammars, check https://github.com/antlr/grammars-v4 specifically in here I think https://github.com/antlr/grammars-v4/tree/master/sql I contributed to one of them, and I wrote my own for some personal work. Be warned, it's very involved, very complex and MSSQL is rather ill-defined.

Names bracket identifiers) in SQL are bloody awful. Sometimes square brackets are even compulsory, and why you can usually replace [...] with the SQL standard "..." , not always! Trust me, it gets worse.

I don't find antlr grammars to be brittle, and while they can lose in performance (by how much I don't know, perhaps quite considerably) they are very easy to maintain and I am very fortunate to have antlr to work with.

Edit: unless you are very familiar with the SQL and also a masochist, please don't try and write your own grammar. For someone with my background it was very time-consuming, without that background it will be a nightmare. Friendly warning.

Re: Postgres Language Server: Implementing the Parser

#38

Post by specialist » Sat, Dec 09, 2023, 4:30 AM UTC

_a_a_a_ wrote: post #37

specialist wrote: post #15

Earlier quoted context omitted.

> ...getting the Parser right. It has been a lot of work. True. I have an ANTLR4 grammar for SQL. Am noob. Getting things "correct" was some effort. I have the quixotic goal of accepting multiple dialects. It's a lot of time referencing docs, playing with SQL fiddles, and learning how other grammars work. Now working on performance. Just as tough for noob me. TIL That means removing any potential ambiquities at runti…

Where is the SQLite test suite, please? I'd be very interested. There are already SQL grammars, check https://github.com/antlr/grammars-v4 specifically in here I think https://github.com/antlr/grammars-v4/tree/master/sql I contributed to one of them, and I wrote my own for some personal work. Be warned, it's very involved, very complex and MSSQL is rather ill-defined. Names bracket identifiers) in SQL are bloody awfu…

> Where is the SQLite test suite

Here's the scrapped tests:

https://github.com/bkiers/sqlite-parser/tree/master/src/test...

I didn't find a scrapper. I was cobbling together my own scrapper when I stumbled onto these files. My scrapper is turrible.

This SQLite grammar is included in grammars-v4. Looks like a copy vs a fork. I don't know why these tests weren't copied too.

> please don't try and write your own grammar

I must be a masochist.

Aside: Ages ago, during an job interview, when asked to talk about some of my prior work, I mentioned my SQL grammar. The self-described "bar raiser" cut me off, dismissed my effort as trivial, because the grammar generator (ANTLR) does all the work. I had no response.

Re: Postgres Language Server: Implementing the Parser

#39

Post by specialist » Sat, Dec 09, 2023, 4:46 AM UTC

atonse wrote: post #30

specialist wrote: post #15

Earlier quoted context omitted.

> ...getting the Parser right. It has been a lot of work. True. I have an ANTLR4 grammar for SQL. Am noob. Getting things "correct" was some effort. I have the quixotic goal of accepting multiple dialects. It's a lot of time referencing docs, playing with SQL fiddles, and learning how other grammars work. Now working on performance. Just as tough for noob me. TIL That means removing any potential ambiquities at runti…

If we're all being postgres-specific, isn't there presumably a grammar somewhere in postgres's code that is used to parse the query SQL? Wouldn't you all want to use that same file as a source so it's 100% identical to what Postgres is actually doing? Genuine question. But maybe the tooling is incompatible. Also, have you looked at Azure Data Studio? I'm wondering how much you can reuse from the work they did.

I'll check out Azure Data Studio, thank you.

For a bunch of reasons, probably all bad, I decided to roll my own, using ANTLR.

Being just a simple bear, LALR (yacc, bison) is beyond me.

With ANTLR4 the parse tree can be pretty close to the desired abstract syntax tree. Which is very nice.

Also, ANTLR4 has automagic left-recursion, so grammars can be very similar to the (reference) EBNF.

Lastly, ANTLR4 allows grammar inheritance. My hope is to have a subgrammar for each dialect which extends the one amazing core supergrammar. Looking at all the example SQL grammars, they're all pretty different, so it's not immediately clear how to generalize. Which is a bummer.

Re: Postgres Language Server: Implementing the Parser

#40

Post by Too » Sat, Dec 09, 2023, 6:22 AM UTC

anarazel wrote: post #18

2mm3mm3 wrote: post #6

When will PG finally start using tasks instead of superheavyweight threads? When will they automatically precompile/hash statements to avoid reparsing? And when materialized views will be automatically updated? Where is an alternative to SQL Server Always On Availability Group released >10 years ago? PostgreSQL is the only database where guys recommend you to configure a separate connection pooler, because their data…

When will you contribute?

The way Postgres is worshiped as The Holy Grail here on HN, some reality check on where it is lacking should be ok to rant about, just to balance the discussion, with no expectations on fixing it. One can also choose a different tool.

In this thread, it’s not exactly on topic though.