False positive for function does not exist
reteps opened this issue · 9 comments
Bug report
apps/prairielearn/src/lib/assessment.sql:1509:7 typecheck ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
✖ function array_avg(double precision[]) does not exist
1507 │ avg(iq.max_submission_score) AS max_submission_score,
1508 │ avg(iq.average_submission_score) AS average_submission_score,
> 1509 │ array_avg (iq.submission_score_array) AS submission_score_array,
│ ^^^^^^^^^
1510 │ array_avg (iq.incremental_submission_score_array) AS incremental_submission_score_array,
1511 │ array_avg (iq.incremental_submission_points_array) AS incremental_submission_points_array,
✖ Error Code: 42883
ℹ Hint: No function matches the given name and argument types. You might need to add explicit type casts.- I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
- Define a sproc in a non-public schema, it isn't picked up.
postgres=# select routine_schema, routine_name
from information_schema.role_routine_grants rrg
where routine_name ='array_avg' order by routine_schema asc, specific_name asc, routine_name asc;
routine_schema | routine_name
---------------------------------------------+--------------
server:3000_2025-08-21T17:47:18.085Z_YT0E23 | array_avg
server:3000_2025-08-21T17:47:18.085Z_YT0E23 | array_avg
server:3000_2025-08-22T19:00:29.534Z_5ZHDV4 | array_avg
server:3000_2025-08-22T19:00:29.534Z_5ZHDV4 | array_avg
To Reproduce
Reproduction in PrairieLearn/PrairieLearn#12682
yarn postgrestools check apps/prairielearn/src
Expected behavior
A clear and concise description of what you expected to happen.
Screenshots
If applicable, add screenshots to help explain your problem.
System information
- macOS, Postgres 16.9
Additional context
Config:
{
"$schema": "https://pgtools.dev/main/schema.json",
"vcs": {
"enabled": true,
"clientKind": "git",
"useIgnoreFile": true
},
"migrations": {
"migrationsDir": "apps/prairielearn/src/migrations"
},
"files": {
"ignore": []
},
"linter": {
"rules": {
"recommended": true
}
},
"db": {
"host": "127.0.0.1",
"port": 5432,
"username": "postgres",
"database": "postgres",
"connTimeoutSecs": 10
}
}
Hey there, thanks for reporting!
Quick question, is the array_avg function in the public schema? I read in your linked PR that you use randomly generated schemas.
How does that work in your case? Do you add these schemas to the search path on startup or something?
All the best!
Quick question, is the array_avg function in the public schema? I read in your linked PR that you use randomly generated schemas.
How does that work in your case? Do you add these schemas to the search path on startup or something?
We do add the latest schema to the search path on startup. They aren't in the public schema. A best case solution for us would be a glob for what schemas to search in, and then we can do some extra work for cleanup of old schemas. Alternatively, having the tool intelligently detect what the current search path is.
I guess intelligently detecting it is not possible because the LSP uses its own connection/session. The search path would have to be set there.
But I think it might be possible to configure some globs for this. Let me give it a try!
The pgt_typecheck is the only linting error you've had issues with, right? It should be the only one that uses a db connection, but I want to make sure I'm not forgetting anything.. 🙃
That is all I have run into so far -- I assume that pgt_typecheck powers the VSCode extension as well.
hey @reteps, when are the dynamic schemas created? Right now, the language server fetches the schema once at startup. If you generate schemas often, we will not see them. Maybe we can do something similar to what PostgREST does - refetch the schema when receiving a specific message on a LISTEN/NOTIFY channel.
Hi @psteinroe -- we are in the process of deprecating our sprocs and moving to typescript. A notify channel may be useful for others, but a glob is likely sufficient for us. If you do end up adding a channel NOTIFY feature, we may use it, but i'm not sure yet.
Our sprocs are recreated on every server startup in a new schema, and the search path is set to that schema.
@reteps With the next release, You can add a typecheck.searchPathPatterns string[] to the jsonc, that should cover it!
Keep in mind that you'll need to restart the LSP whenever your DB schemas change. But that should be easy via the command palette in vs code, there's a Postgrestools: Restart Server command.
Excellent, thank you!