Question about Postgres-15-specific keywords not being parseable

[nith2001]

Hello! I'm a developer new to open-source that just started working on the Supabase codebase and we are indirectly using your library via pgsql-parser library. We appreciate the work you do!

Problem: We've noticed Postgres 15 specific keywords are not parseable, like UNIQUE NULLS NOT DISTINCT. See issue here.

Environment: Using the pgsql-parser repository and updated the libpg-query version to 15.0.2. Ran tests using PG15 keywords and got errors, such as syntax error at or near "NULLS".

Question: Does version 15.0.2 not support parsing Postgres 15 keywords? Or is there a later version that we can import?

[lfittl]

@nith2001 Thanks for reaching out!

Postgres 15 keywords should be fully supported on the 15 version of the parser ("15-latest" branch), if they are not that would be a bug.

I just ran a quick test using the latest version of the pg_query Ruby library (which is currently on the 15-4.2.0 tag of libpg_query), and for example this schema definition works as expected:

CREATE TABLE null_new_style
(
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    val1 TEXT NOT NULL,
    val2 TEXT NULL,
    CONSTRAINT uq_val1_val2_new
        UNIQUE NULLS NOT DISTINCT (val1, val2)
);

(example from https://blog.rustprooflabs.com/2022/07/postgres-15-unique-improvement-with-null)

I believe the root cause might be that more steps are necessary beyond what you worked on over in https://github.com/pyramation/pgsql-parser/issues/93 - did you actually update the underlying parser source to be 15 based?

[nith2001]

Hi @lfittl! So, I was upgrading the libpg-query dependency on the pgsql-parser library from 13.x.x to 15.0.2 because that was the latest possible version I could update it to.

I tried editing the version of libpg-query in the package.json file to 15-4.20 and then I ran yarn install and got the following message:

Please choose a version of "libpg-query" from this list: 
  13.2.3 
  13.2.2 
  13.2.1 
  13.2.0 
  13.1.2 
  13.1.1 
  13.1.0 
  13.0.1 
  13.0.0 
❯ 15.0.2 
  15.0.1 
  15.0.0 
  14.0.0 
  13.3.1 
  13.3.0 
  13.2.5 
  13.2.4 
  13.2.4-rc2

It seems like the 15-4.2.0 version wasn't an option? Unless I'm doing this wrong. If I selected 15.0.2, the yarn-lock.json file would keep the 15-4.2.0 in there. But after running yarn bootstrap afterwards, it results in an error:

Couldn't find any versions for "libpg-query" that matches "15-4.2.0"

But let's say I stuck with 15.0.2. When I ran the tests using some Postgrest 15 keywords, the error would occur here, where the parseQuery function is directly imported from libpg-query. What would it take to actually upgrade the underlying parser code (not deparser) if this isn't enough?

[data-envoy]

I believe the root cause might be that more steps are necessary beyond what you worked on over in https://github.com/pyramation/pgsql-parser/issues/93 - did you actually update the underlying parser source to be 15 based?

@lfittl hi, I've just seen this issue, but there has also been some prior discussion on the supabase issues board. Does this advice sound right https://github.com/supabase/supabase/issues/13267#issuecomment-1500822506

you could try libpg-query locally with an updated commit hash in script/buildAddon.sh.

[lfittl]

@lfittl hi, I've just seen this issue, but there has also been some prior discussion on the supabase issues board. Does this advice sound right supabase/supabase#13267 (comment)

Yeah, I think that sounds like a good starting point - it actually appears that @pyramation has prepared a PR for this (https://github.com/pyramation/libpg-query-node/pull/27), but that hasn't yet been merged.

[nith2001]

Oh wow, so we've just been looking at the wrong repository, I did not realize that libpg_query for the node was in a different repo. Guess I'll just have to put it on their radar. I would like to avoid doing anything too hacky just to get the parsing to work. Better to wait for the libpg-query-node library to get updated to 15 first.

[lfittl]

@nith2001 Sounds good!

Since this is not a problem on this repository here itself (which is the underlying C library that is then used in e.g. libpg-query-node), I'll close this issue for now.