pg_repack can helpfully be installed in the extensions tab of Supabase Studio. However (and reasonably so), this does not give one access to the pg_repack local binary, nor does it actually run if I build the binary locally and run it against a hosted Supabase database. Attempting to run pg_repack against a Supabase managed database yields:
ERROR: pg_repack failed with error: You must be a superuser to use pg_repack
Using the -k flag in pg_repack to ignore the superuser check then yields:
INFO: repacking table "public.bloated_table"
ERROR: query failed: ERROR: permission denied for schema repack
CONTEXT: SQL statement "CREATE TYPE repack.pk_30458 AS (id uuid)"
PL/pgSQL function repack.create_index_type(oid,oid) line 3 at EXECUTE
DETAIL: query was: SELECT repack.create_index_type(73396,30458)
ERROR: query failed: ERROR: must be superuser to use repack_drop function
DETAIL: query was: SELECT repack.repack_drop($1, $2)
ERROR: query failed: ERROR: must be superuser to use repack_drop function
DETAIL: query was: SELECT repack.repack_drop($1, $2)
will then continue until the pg_repack process is killed
pg_repack was even mentioned as a part of this nice blog https://supabase.com/blog/postgres-bloat, alongside supabase cli commands, suggesting I should be able to use it.
To Reproduce
Steps to reproduce the behavior, please provide code snippets or a repository:
Enable pg_repack either through Studio or in SQL
Build and run pg_repack cli binary against table, following examples from blog
pg_repack fails with above error messages
Expected behavior
I would expect one of two outcomes:
pg_repack does not appear in Studio, as it doesn't seem particularly supported
pg_repack should successfully run to completion, either through some psuedo-superuser access, or some other mechanism
Additional context
It's weird to me that a blog post on debloating Supabase-managed databases starts off by mentioning a mechanism almost universally discouraged (VACUUM FULL), followed by a mechanism that seemingly cannot be used in these environments (pg_repack). I guess one could argue that the blog is referring to Postgres in general, but then the use of the Supabase CLI doesn't make a lot of sense.
encima
commented
1 day ago
Bug report
Describe the bug
pg_repack can helpfully be installed in the extensions tab of Supabase Studio. However (and reasonably so), this does not give one access to the
pg_repacklocal binary, nor does it actually run if I build the binary locally and run it against a hosted Supabase database. Attempting to runpg_repackagainst a Supabase managed database yields:Using the
-kflag in pg_repack to ignore the superuser check then yields:will then continue until the
pg_repackprocess is killedpg_repack was even mentioned as a part of this nice blog https://supabase.com/blog/postgres-bloat, alongside supabase cli commands, suggesting I should be able to use it.
To Reproduce
Steps to reproduce the behavior, please provide code snippets or a repository:
pg_repackcli binary against table, following examples from blogpg_repackfails with above error messagesExpected behavior
I would expect one of two outcomes:
pg_repackdoes not appear in Studio, as it doesn't seem particularly supportedpg_repackshould successfully run to completion, either through some psuedo-superuser access, or some other mechanismAdditional context
It's weird to me that a blog post on debloating Supabase-managed databases starts off by mentioning a mechanism almost universally discouraged (VACUUM FULL), followed by a mechanism that seemingly cannot be used in these environments (pg_repack). I guess one could argue that the blog is referring to Postgres in general, but then the use of the Supabase CLI doesn't make a lot of sense.