Getting code actions super slow

[mpayette-3rdeyecam]

Bug report

Describe the bug

Using the tool for real-time formatting, I'm getting incredibly slow results in VSCode. Many times I will save changes, and the IDE will load and show the bottom right popup saying it's getting "code actions" from postgrestools. The waiting can be anywhere from like half a second to even 10 seconds roughly in the worst instances. My PC specs are very good and I'm not running heavy stuff while testing that.

I'm using this postgrestools.jsonc:

{
  "$schema": "https://pgtools.dev/schemas/0.0.0/schema.json",
  "vcs": {
    "enabled": false,
    "clientKind": "git",
    "useIgnoreFile": false
  },
  "files": {
    "ignore": []
  },
  "linter": {
    "enabled": true,
    "rules": {
      "recommended": true
    }
  }
}

and this workspace setup:

{
  "folders": [
    {
      "path": ".",
    },
  ],
  "settings": {
    "editor.defaultFormatter": "dbaeumer.vscode-eslint",
    "editor.formatOnSave": true,
    ...
    "typescript.tsserver.experimental.enableProjectDiagnostics": true,
    "editor.codeActionsOnSave": {
      "source.fixAll": "always",
      "source.fixAll.eslint": "always",
      "source.organizeImports": "always",
      "source.sortMembers": "always",
    },
    "files.associations": {
      "*.code-workspace": "json",
      "*.mjs": "javascript",
      "*.jsonc": "json",
    },
    ...
    "[jsonc]": {
      "editor.defaultFormatter": "esbenp.prettier-vscode",
    },
    "[sql]": {
      "editor.defaultFormatter": "Supabase.postgrestools",
    },
    ...
  },
}

To Reproduce

Hard to consistently repro, but I'm getting this latency on almost every save action.

Expected behavior

At most a second of delay between me saving an sql file and the tool doing its job.

Screenshots

System information

• OS: Windows 11
• Version of Node.js: 22.12.0

[psteinroe]

that is unusual, thanks for sharing! Would it be possible to share a logfile with us?

You can find them here

https://pgtools.dev/#daemon-logs

[psteinroe]

also - we do not support formatting (yet). Maybe that's the reason for the slowness - it's trying to fetch things that do not exist?

[psteinroe]

Code actions are just "execute the statement under the cursor" and are meant to be actively called by the user, not on every save.

[mpayette-3rdeyecam]

Here are some logs I generated today with just a couple of save actions which were about 5-10 seconds loading. Moreover, when I start going too fast for it, it just doesn't manage to catch up and remains broken even if I close and reopen the file. By broken, I mean that it will show red squigglies on completely valid SQL (pg).

I also tried doing this in my workspace (setting the format on save to false) but it didn't change anything:

"[sql]": {
  "editor.defaultFormatter": "Supabase.postgrestools",
  "editor.formatOnSave": false
},

logs-postgrestools.txt

[juleswritescode]

Regarding your VSCode settings, you shouldn't add any "Supabase.postgrestools" to your settings.json. The extension doesn't yet support formatting, so the config is invalid. You can see all valid configurations under the "postgrestools" namespace, for example: "postgrestools.allowDownloadPrereleases": true.

If the problem persists after you've removed the [sql] settings, can you add

  "db" : {
    "connTimeoutSecs": 30
  }

to your postgrestools.jsonc and see if that makes the problem worse?

My guess is that the server is trying to connect to a database but not receiving any response. I can't reproduce it on my machine though..

[mpayette-3rdeyecam]

If I remove the [sql] part of my workspace config, when I work in a .sql file I'm not gonna get the error checking anymore, right?
Am I understanding this correctly that me getting the error checking is merely an effect of the incomplete formatting feature that you guys will implement later on?

My main usecase for this tool is the error checking, since it saves devs time and effort not having to run the tool manually all the time, and the visual squigglies for error pinpointing.

[juleswritescode]

If I remove the [sql] part of my workspace config, when I work in a .sql file I'm not gonna get the error checking anymore, right?

You should get squiggly error lines without that, as long as the extension is running 👍🏻

But if you do not add a db connection, you'll only a fraction of the errors.

The main benefit of the tool is that you write a statement and the extension tells you "Hey man, that 'email' column does not exist in your database".

I recommend this:

1. Remove the [sql] part from your global and/or local vscode settings.json
2. In the project you're working on, add a postgrestools.jsonc on top level.
3. If you're working with a local database, add the connection settings to the postgrestools.jsonc file. Here's a sample: https://pgtools.dev/#configuration
4. If you're working with a remote database, you can add its connection settings too, but be aware that the squiggly lines might take longer. Also be aware that you add do not commit database settings to github
5. If you use node_modules, run rm -rf node_modules && npm i.
6. Reinstall/Restart the postgrestools-vscode extension.

[mpayette-3rdeyecam]

I appreciate the help, however connecting the tool to our database is not something we're currently interested in. All we're looking for is a tool that efficiently finds SQL errors in our files. I have many devs who aren't too used to pgsql, and this would help a lot.

As for the recommended steps:

1. Tried that, and I indeed still see the squigglies, however the slowness on saves remains problematic.
2. Already had that, see OP.
3. Not doing.
4. Not doing.
5. Tried it, no change.
6. Tried it, no change (even after restarting vscode). Did a reinstall, not just restart.

I legit just go in any sql file, add a comma after an existing comma to create an error, and then ctrl+s, and the save takes 5-10 seconds. Lemme know if I can provide anything else to help debug that. There really aren't other VSC extensions that seem to properly run realtime checks on sql files (with pg dialect) and show the problems, so I have faith that this tool can become extremely useful.

[juleswritescode]

Thank you for trying all of that!

Hmm. Ok, I'll try to reproduce the delay, or get back to you if I have an idea on how we could debug this better.

One last thing, have you tried settings the db.connTimeoutSecs to 1 or maybe 30? If that makes things better or worse, that would hint at something involving an (attempted) db connection.

[psteinroe]

another option would be to disable the db connection entirely.

[mpayette-3rdeyecam]

Hmmm, the connTimeoutSecs values didn't change anything.
However, I did notice that it's specific statements that make it struggle. Here's what I put in a new .sql file and saw delays for:

INSERT INTO use_cases (
    a,
    b,
    c,
    d,
    e
)
VALUES
('foo', 'bar', 'baz', 'qux', TRUE)
ON CONFLICT (name) DO NOTHING;

In that snippet, if I add a wrong comma after the values line (i.e. ('foo', 'bar', 'baz', 'qux', TRUE),), I get an error immediately. Then when I remove that comma, it take like a second or so for it to remove the squigglies and save the file.

Here's a clip of me playing around with that snippet. You can see that it isn't responsive enough, and in some cases it even just stops working altogether.

2025-04-13.11-53-45.mp4

[juleswritescode]

Quick update: I found out that the LSP would try to connect to 127.0.0.1:5432 if no settings were specified. This PR should fix that.

I'll release as soon as possible once the PR is merged, maybe you can try again – I hope that was the issue!

[juleswritescode]

Version 0.6.1 is out, would you mind giving that a try?