`result.next_id`/`result.next_state` is not a known variable pg(syntax)

[diraneyya]

Bug report

I just started using this extension as a replacement for UniqueVision's LSP extension, which seems abandoned and no longer maintained. I may have to go back to that extension, though, if this does not work.

Describe the bug

I am developing an extension with this table definition:

create table if not exists _fetch_cycle_continuation_data (
    next_id bigint,
    next_state jsonb null default '{}'::jsonb
    constraint abstract_no_data check(false) no inherit
);

It is an abstract table that I inherit from.

In my procedure I have the following variable and assignment:

create or replace function continue_fetch_cycle_prototype (
    previous_step fetch_cycle_buffer_prototype
) returns _fetch_cycle_continuation_data language plpgsql as $prototype$
declare
    result _fetch_cycle_continuation_data := null;
begin 
    result.next_id := 0;
    ...
    result.next_state := '{}'::jsonb

The error messages are of the likes of:

`result.next_id` is not a known variable pg(syntax)
`result.next_state` is not a known variable pg(syntax)

Expected behavior

No syntax complaint is expected since the code is correct and works.

System information

I am using this vscode extension without Supabase, in my own PostgreSQL (v17.4) database.

• OS: Linux
• Browser (if applies) NA
• Version of supabase-js: NA
• Version of Node.js: NA

Additional context

My config:

{
  "$schema": "https://pgtools.dev/latest/schema.json",
  "vcs": {
    "enabled": false,
    "clientKind": "git",
    "useIgnoreFile": false
  },
  "files": {
    "ignore": []
  },
  "linter": {
    "enabled": true,
    "rules": {
      "recommended": true
    }
  },
  "db": {
    "host": "127.0.0.1",
    "port": 5432,
    "username": "postgres",
    "password": "xxxxxxxxxxxxx",
    "database": "aajobs",
    "connTimeoutSecs": 10,
    "allowStatementExecutionsAgainst": ["127.0.0.1/*", "localhost/*"]
  }
}

[psteinroe]

hey, thanks for sharing! I migrated your issue to the main repo.

Unfortunately, this is an upstream issue: pganalyze/libpg_query#260

I fixed this now for the language server by ignoring is not a known variable errors for composite types.

I would however recommend using the plpgsql_check extension instead:

create extension plpgsql_check;

The language server will detect the extension and report much more fine-grained issues. The false positive you mention here is not reported by plpgsql_check.

[diraneyya]

On a positive note, I greatly appreciate your prompt reply and I am very grateful for having found this project, it is just a shame that I am overworked and in a desperate need for this extension to continue my development.

On a not so positive note. I am a little frustrated because I have merged your commit and been trying to build the thing for over two hours. I managed to built it but I am still running into the same issue.

If you help me, then I promise to volunteer to make clear build instructions for others to follow. What I have done so far is:

• Cloned the repository while initialising submodules
• Installed rust 1.86.0 or higher
• Ran cargo build --release successfully

The outcome is a dev binary with version 0.0.0 which still complaints about the same grammar error.

TL;DR I pulled the fix/plpgsql_check branch and merged it into main, which builds with no issues but the error is still present even after restarting the language server from the extension with a complaint about the version but I am not sure if this is really the issue.

PS: I solved the version issue and compiled the binary as version 0.12.0-fix but the issue persists:

[diraneyya]

How can I confirm that the extension and/or the language server has successfully connected to my local database? As this might as well be the issue. I would like a way to troubleshoot that.

Additionally, I also want to confirm that I have built the plpgsql_check extension from source and ran CREATE EXTENSION successfully in my target database.

I also disabled/enabled the extension, restarted, etc. I even tried to restart the code-server (VSCode running in the browser from company Coder). The issue still persists.

[diraneyya]

I just want to explain that in my perception the issue was not solved and I went back to using UniqueVision's vscode extension. I was hoping for a reply when the issue was closed

[psteinroe]

hey @diraneyya, sorry for the lack of transparency here.

I assume that at the time you checked out the pr with the fix it still had a bug (and a failing test). That bug is fixed and the pr was merged. The latest release should contain the fix for your issue, which is also why this issue was closed.

To debug why the language server is not working as expected, you can check its logs. The path to the logfiles are documented in our documentation (sorry, I am on mobile right now so can't easily send the direct link).

if you encounter further issues, feel free to reopen.

[diraneyya]

This is great news. Thank you for the update. I think it will take me a few weeks to try it again (until then I will continue to use the UniqueVision's extension) but when I do, I will be back to supporting this project.

Like this issue here supabase-community/postgrestools-vscode#37 I want to:

• Thank you and everyone else who is working on improving the dev experience around postgres
• Add my voices to those other voices that prefer that we all collaborate and put our hands together, instead of continuing to work on similar but disconnected projects

As the world moves towards a more meaningful incorporation of AI reasoning in our lives, the future is likely to rely more heavily on matching performed by relational and embedded data. PostgreSQL is unique in this area because it can readily accept and query AI embedding vectors.

Long story short, improving the dev experience of postgres is a strategic goal for our digital civilisation and a requirement for much of the needed evolution of our platforms. I tremendously appreciate the work you do as well as that of others. One huge pain point with postgres is debugging and I hope we eventually find a better way to do it.

Thanks!