404 on RPCs after upgrading to 12.2.2 when using the in-db config

[MHC2000]

Environment

• PostgreSQL version: 13
• PostgREST version: 12.2.2
• Operating system: SLES 15

Description of issue

I've upgraded from release 12.2.1 to 12.2.2. After restarting the server the functions (RPC) are not working anymore. Getting 404 from my web server. Tables seam to work and are reachable.

Didn't change anything on the configuration or something else, just replaced the binary and restarted.

12.2.1 worked, functions and views were reachable

[steve-chavez]

How can we reproduce? Do you have a sample function and curl request?

[MHC2000]

I'll try to extract a function which works without additional data from the database. But didn't change anything at the functions and looks like every function.

[laurenceisla]

If it's not, activate the admin server port for a while, like admin-server-port=3001. Then query localhost:3001/config and check if you get the same result from both versions.

[MHC2000]

Sorry that it took so long. Our Test-server is quite ocupied. If created a test function which works with 12.2.1 and with 12.2.2 doesn't work anymore: The error im getting from postgrest

{
  "code": "PGRST202",
  "details": "Searched for the function api.test without parameters, but no matches were found in the schema cache.",
  "hint": null,
  "message": "Could not find the function api.test without parameters in the schema cache"
}

this is the function definition

-- FUNCTION: api.test()

-- DROP FUNCTION IF EXISTS api.test();

CREATE OR REPLACE FUNCTION api.test(
    )
    RETURNS jsonb
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL SAFE 
AS $BODY$
SELECT null::jsonb;
$BODY$;

ALTER FUNCTION api.test()
    OWNER TO admin_users;

GRANT EXECUTE ON FUNCTION api.test() TO admin_users;

GRANT EXECUTE ON FUNCTION api.test() TO qqzcms2;

REVOKE ALL ON FUNCTION api.test() FROM PUBLIC;

and that's the log of postgrest

What makes me wonder, the log quotes 75 found functions but pg_admin says 402. But there are a lot of computed relation functions in the api, so I'm not sure if the count includes them

[steve-chavez]

@MHC2000 Have you refreshed the schema cache after creating the function?

Try doing curl "http://localhost:3001/schema_cache" https://docs.postgrest.org/en/v12/references/admin_server.html#runtime-schema-cache

And grep for your function.

[MHC2000]

I've stopped the whole server and started it new after creating the function. As said it's just a test function. all the other already existing functions don't work as well. just wanted to show even a simple function doesn't work. After switching back to 12.2 1 the test function and all the others work with any problems

[wolfgangwalther]

I am seeing a similar CI failure in one of my projects after updating to v12.2.2. I haven't had the time to look deeper or try to reproduce outside of that repo.

The failing log looks like this:

17/Jul/2024:20:18:30 +0000: Starting PostgREST 12.2.2...
17/Jul/2024:20:18:30 +0000: Admin server listening on port 3001
17/Jul/2024:20:18:30 +0000: Listening on port 3000
17/Jul/2024:20:18:30 +0000: Successfully connected to PostgreSQL 15.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
17/Jul/2024:20:18:30 +0000: Listening for notifications on the "pgrst" channel
17/Jul/2024:20:18:30 +0000: Schema cache queried in 6.5 milliseconds
17/Jul/2024:20:18:30 +0000: Schema cache loaded 0 Relations, 0 Relationships, 0 Functions, 17 Domain Representations, 4 Media Type Handlers, 598 Timezones
17/Jul/2024:20:18:30 +0000: Config reloaded
curl: (22) The requested URL returned error: 404

The good log with 12.2.1 like this:

22/Jul/2024:11:48:05 +0000: Starting PostgREST 12.2.1...
22/Jul/2024:11:48:05 +0000: Attempting to connect to the database...
22/Jul/2024:11:48:05 +0000: Admin server listening on port 3001
22/Jul/2024:11:48:05 +0000: Listening on port 3000
22/Jul/2024:11:48:05 +0000: Successfully connected to PostgreSQL 15.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
22/Jul/2024:11:48:05 +0000: Listening for notifications on the "pgrst" channel
22/Jul/2024:11:48:05 +0000: Config reloaded
22/Jul/2024:11:48:05 +0000: Schema cache queried in 8.7 milliseconds
22/Jul/2024:11:48:05 +0000: Schema cache loaded 31 Relations, 45 Relationships, 50 Functions, 17 Domain Representations, 25 Media Type Handlers

Looks like a lot of stuff is not in the schema cache anymore after the update.

[wolfgangwalther]

Also note how the order of "Config reloaded" is different. I suspect that the config reload is broken. It needs to happen before the schema cache load, to load db-schemas, which I set via database config. Maybe it's now happening simultaneously for some reason?

Probably related to 6be59066df9185e1e603ab6a4a9aa5c3b5d6d49a.

[wolfgangwalther]

@MHC2000

What makes me wonder, the log quotes 75 found functions but pg_admin says 402.

Do the numbers match better with the public schema? I assume v12.2.2 is falling back to reading this for the schema cache, because it hasn't loaded the db-schemas variable from the database, yet.

[MHC2000]

@MHC2000

What makes me wonder, the log quotes 75 found functions but pg_admin says 402.

Do the numbers match better with the public schema? I assume v12.2.2 is falling back to reading this for the schema cache, because it hasn't loaded the db-schemas variable from the database, yet.

In public there are 248 functions, so the numbers don't match either. In API are in total 404 functions (pg_admin count)

• 48 normal functions
• the rest are computed relations

I also figured that in 12.2.1 the command killall -SIGUSR2 postgrest doesn't change the cache. In my PROD system I've deactivated the notify channel due the problems we had in the past with a forced disconnect after 30 minutes in our envrionment. Because of that I have to kill postgrest and restart it to get a new schema cache.

[laurenceisla]

I assume v12.2.2 is falling back to reading this for the schema cache, because it hasn't loaded the db-schemas variable from the database, yet.

I'm pretty sure this is it. I can also reproduce this by setting pgrst.db_schemas = 'api' and only the public functions are loaded at startup (needs reloading to get the correct schema).

Probably related to https://github.com/PostgREST/postgrest/commit/6be59066df9185e1e603ab6a4a9aa5c3b5d6d49a.

Can confirm. Using the v12.2.2 tag, checking out to the parent commit 8715e426c0a63fbb41f36636a77157f157a0fad0 loads the api schema at startup. Checking out to the commit with the change f09655b7a6f9961eda609b182e072e84472dcec9, it loads the public schema.

In public there are 248 functions, so the numbers don't match either.

That depends, if those functions do not have named parameters, then they won't be included (unless it has a single unnamed parameter of certain types). This also applies to computed relationships, if the parameters are not named, then they're not included.

Try executing the following query that PostgREST uses to retrieve functions. It should return 75 from the public schema:

Functions Query

```sql WITH base_types AS ( WITH RECURSIVE recurse AS ( SELECT oid, typbasetype, COALESCE(NULLIF(typbasetype, 0), oid) AS base FROM pg_type UNION SELECT t.oid, b.typbasetype, COALESCE(NULLIF(b.typbasetype, 0), b.oid) AS base FROM recurse t JOIN pg_type b ON t.typbasetype = b.oid ) SELECT oid, base FROM recurse WHERE typbasetype = 0 ), arguments AS ( SELECT oid, array_agg(( COALESCE(name, ''), -- name type::regtype::text, -- type CASE type WHEN 'bit'::regtype THEN 'bit varying' WHEN 'bit[]'::regtype THEN 'bit varying[]' WHEN 'character'::regtype THEN 'character varying' WHEN 'character[]'::regtype THEN 'character varying[]' ELSE type::regtype::text END, -- convert types that ignore the length and accept any value till maximum size idx <= (pronargs - pronargdefaults), -- is_required COALESCE(mode = 'v', FALSE) -- is_variadic ) ORDER BY idx) AS args, CASE COUNT(*) - COUNT(name) -- number of unnamed arguments WHEN 0 THEN true WHEN 1 THEN (array_agg(type))[1] IN ('bytea'::regtype, 'json'::regtype, 'jsonb'::regtype, 'text'::regtype, 'xml'::regtype) ELSE false END AS callable FROM pg_proc, unnest(proargnames, proargtypes, proargmodes) WITH ORDINALITY AS _ (name, type, mode, idx) WHERE type IS NOT NULL -- only input arguments GROUP BY oid ) SELECT pn.nspname AS proc_schema, p.proname AS proc_name, d.description AS proc_description, COALESCE(a.args, '{}') AS args, tn.nspname AS schema, COALESCE(comp.relname, t.typname) AS name, p.proretset AS rettype_is_setof, (t.typtype = 'c' -- if any TABLE, INOUT or OUT arguments present, treat as composite or COALESCE(proargmodes::text[] && '{t,b,o}', false) ) AS rettype_is_composite, bt.oid <> bt.base as rettype_is_composite_alias, p.provolatile, p.provariadic > 0 as hasvariadic, lower((regexp_split_to_array((regexp_split_to_array(iso_config, '='))[2], ','))[1]) AS transaction_isolation_level, coalesce(func_settings.kvs, '{}') as kvs FROM pg_proc p LEFT JOIN arguments a ON a.oid = p.oid JOIN pg_namespace pn ON pn.oid = p.pronamespace JOIN base_types bt ON bt.oid = p.prorettype JOIN pg_type t ON t.oid = bt.base JOIN pg_namespace tn ON tn.oid = t.typnamespace LEFT JOIN pg_class comp ON comp.oid = t.typrelid LEFT JOIN pg_description as d ON d.objoid = p.oid LEFT JOIN LATERAL unnest(proconfig) iso_config ON iso_config LIKE 'default_transaction_isolation%' LEFT JOIN LATERAL ( SELECT array_agg(row( substr(setting, 1, strpos(setting, '=') - 1), substr(setting, strpos(setting, '=') + 1) )) as kvs FROM unnest(proconfig) setting WHERE setting ~ ANY('{}') ) func_settings ON TRUE WHERE t.oid <> 'trigger'::regtype AND COALESCE(a.callable, true) AND prokind = 'f' AND p.pronamespace = ANY('{public}'::regnamespace[]); ```

I also figured that in 12.2.1 the command killall -SIGUSR2 postgrest doesn't change the cache.

The SIGUSR2 signal reloads only the config, SIGUSR1 reloads the schema cache. Try using it after starting v12.2.2 to check if it loads the correct schema.

[MHC2000]

@laurenceisla Executing your query gives me the count of 86 for publicschema, and 404 for apischema

The SIGUSR2 signal reloads only the config, SIGUSR1 reloads the schema cache. Try using it after starting v12.2.2 to check if it loads the correct schema.

Thanks for the hint, will try that asap.

[steve-chavez]

Can reproduce by doing:

$ echo "alter role postgrest_test_authenticator set pgrst.db_schemas = 'test';" >> test/spec/fixtures/schema.sql 
$ PGRST_DB_SCHEMAS="public" postgrest-with-postgresql-16  -f test/spec/fixtures/load.sql postgrest-run

28/Jul/2024:19:54:31 -0500: Schema cache loaded 10 Relations, 9 Relationships, 8 Functions, 15 Domain Representations, 4 Media Type Handlers, 1194 Timezones
28/Jul/2024:19:54:31 -0500: Config reloaded

The schema cache is loaded after the in-db config.