search

tembo-io / pgmq

A lightweight message queue. Like AWS SQS and RSMQ but on Postgres.
PostgreSQL License
2.68k stars 71 forks source link

pg_partman assumed to be in the public schema #306

Closed olirice closed 2 months ago

olirice commented 2 months ago

Currently pg_partman is assumed to be in the public schema

It's hard coded in several places https://github.com/tembo-io/pgmq/blob/18465f5f9344b9bcaca8566563d6a628f90bfa9a/pgmq-extension/sql/pgmq.sql#L750 https://github.com/tembo-io/pgmq/blob/18465f5f9344b9bcaca8566563d6a628f90bfa9a/pgmq-extension/sql/pgmq.sql#L764

which prevents pg_partman being referenced by pgmq if pg_patman is installed in e.g. an extensions schema

ChuckHend commented 2 months ago

Hi @olirice , do you think it would be sufficient to remove the hard coding of the public schema and instead rely on search path settings? Or would you rather see this configured some other way?

olirice commented 2 months ago

instead rely on search path

there's no guarantee that search path would be set appropriately for all roles so I think it'd have to be something like 

create function pgmq._get_pg_partman_schema()
  returns text
  stable
  language sql
as $$
  select
    extnamespace::regnamespace::text
  from
    pg_extension
  where
    extname = 'pg_partman';
$$;

and use that with a literal format anywhere its needed

perform pgmq._ensure_pg_partman_installed();
execute format('%L.create_parent(...)', pgmq.get_pg_partman_schema());

if you're okay with that solution I can PR it

ChuckHend commented 2 months ago

A PR for this would be great. Thank you @olirice