False positive for pg_temp schema

[mplanchard]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

This was asked as a question here, but it is a bug, since it rejects valid sql.

Describe the bug

Each session has access to a temporary table schema, pg_temp_nnn, which is aliased to pg_temp. It is documented here.

We often use pg_temp in our migrations to create temporary functions or tables that we do not need after the migration completes.

postgrestools reports an error for any use of pg_temp:

✖ schema "pg_temp" does not exist

However, this is not an error, as code using pg_temp runs just fine.

To Reproduce

create table pg_temp.foo (id int);
select * from pg_temp.foo

Expected behavior

The above should not yield an error.

System information

• OS: linux

[psteinroe]

thanks for reporting! Unfortunately, this is "by design" right now. Typecheck works by simply asking Postgres to EXPLAIN the query, and pg_temp does not exist there. We are now at a point where we might start with a custom type checker that does not rely on EXPLAIN. Can't promise it though. We could also add an intermediate fix that ignores EXPLAIN errors for tables and columns that are mutated in the same file. Will check.