Schema Cache queries as boostrap prepared statements

[steve-chavez]

Problem

Modifying/maintaining the schema cache queries is a difficult process, to run them on psql we have to extract them from the Haskell code and replace the placeholders manually.

In the past, we've tried to put them in functions inside pg_temp (#1511), however creating functions is invasive (require privileges plus a no go on replicas).

Solution

Create prepared statements at PostgREST startup, then refer to them by name. This will allow us to develop schema cache queries in other files and run them easily. Unlike functions they're ephemeral database objects and don't require privileges.

The main idea. Take for example our accessibleTables query.

Which can be stored as:

PREPARE pgrst_accessible_tables (regnamespace[]) AS
SELECT
  n.nspname AS table_schema,
  c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','r','m','f','p')
AND c.relnamespace = ANY($1::regnamespace[])
AND (
  pg_has_role(c.relowner, 'USAGE')
  or has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
  or has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')
)
AND not c.relispartition
ORDER BY table_schema, table_name;

Which then allows us to call it like:

postgres=# execute pgrst_accessible_tables ('{test}');
-[ RECORD 1 ]+-------------------------------------------
table_schema | test
table_name   | Escap3e;
-[ RECORD 2 ]+-------------------------------------------
table_schema | test
table_name   | Foo
-[ RECORD 3 ]+-------------------------------------------
table_schema | test
table_name   | Server Today
...

And from PostgREST codebase we can use the same way to call them and pass them parameters.

Notes

• In theory this could allow users to override/customize the schema cache queries too but this hasn't been a demand lately (it used to be).
• Whenever a session dies the "bootstrap prepared statements" would have to be created again.

[steve-chavez]

I was mostly thinking of this in the context of embedding postgrest-openapi somehow, to avoid telling the users to create the extension first. I've been checking the code and it's mostly functions (with a few types, which are not strictly necessary). If we could somehow embed the postgrest-openapi main function as a prepared statement, this could work.

By removing the types and the plpgsql functions, it seems possible to have a build step that inlines all the SQL and then creates a prepared statement.

[wolfgangwalther]

Prepared statements are only available in the same session, so I don't see how this can help debugging things.

[steve-chavez]

This is mostly for development. Although for debugging we could provide something like:

$ postgrest --output-scache-queries > scache.sql

PREPARE pgrst_accessible_tables (regnamespace[]) AS
SELECT
  n.nspname AS table_schema,
  c.relname AS table_name
...

$ psql -f scache.sql

Then the user can do execute pgrst_accessible_tables ('{test}'); same way as above.

[wolfgangwalther]

This is mostly for development. Although for debugging we could provide something like:

$ postgrest --output-scache-queries > scache.sql

PREPARE pgrst_accessible_tables (regnamespace[]) AS
SELECT
  n.nspname AS table_schema,
  c.relname AS table_name
...

$ psql -f scache.sql

Then the user can do execute pgrst_accessible_tables ('{test}'); same way as above.

Oh, I like this idea.. but instead of outputting a PREPARE statement, how about just the SQL command directly, with the respective config values (schema names) put in the right places?

Then I can just pipe that query directly into psql and get a result immediately.

So something like: postgrest --dump-query=all_tables | psql.

[steve-chavez]

how about just the SQL command directly, with the respective config values (schema names) put in the right places?

Hm, I guess it's possible but we would have to do the replacing in Haskell code. I was thinking to leave most of the work to SQL/psql.

Another option could be to add our scache config as psql variables (for another flag) to do:

\set pgrst_db_schemas test,api

PREPARE pgrst_accessible_tables...

And then:

$ postgrest --output-scache-queries-psql | psql -c "execute pgrst_accessible_tables (:'pgrst_db_schemas');"