404 on RPCs after upgrading to 12.2.2 when using the in-db config #3660
Comments
|
How can we reproduce? Do you have a sample function and curl request? |
|
I'll try to extract a function which works without additional data from the database. |
|
If it's not, activate the admin server port for a while, like |
|
Sorry that it took so long. Our Test-server is quite ocupied. this is the function definition and that's the log of postgrest What makes me wonder, the log quotes 75 found functions but pg_admin says 402.
|
|
@MHC2000 Have you refreshed the schema cache after creating the function? Try doing And grep for your function. |
|
I've stopped the whole server and started it new after creating the function. |
|
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: The good log with 12.2.1 like this: Looks like a lot of stuff is not in the schema cache anymore after the update. |
|
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 Probably related to 6be5906. |
Do the numbers match better with the |
In public there are 248 functions, so the numbers don't match either.
I also figured that in 12.2.1 the command |
I'm pretty sure this is it. I can also reproduce this by setting
Can confirm. Using the
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 Functions Query
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[]);
The |
|
@laurenceisla
Thanks for the hint, will try that asap. |
|
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 reloadedThe schema cache is loaded after the in-db config. |
Fixes PostgREST#3660. Load the in-db config before the schema cache. The regression happened on f09655b.
steve-chavez
changed the title
Environment
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
The text was updated successfully, but these errors were encountered: