Feature Request: Consider allowing relocation to a different schema during CREATE EXTENSION

[eeeebbbbrrrr]

Why do you want this feature?

In offering both pg_duckdb and timescaledb on PlanetScale's Postgres offering, these extensions, by default, cannot be created at the same time as they both contain a function named time_bucket.

Timescale is able to be relocated to a different schema during CREATE EXTENSION, but pg_duckdb is not:

postgres=> create schema ddb; create extension pg_duckdb schema ddb;
CREATE SCHEMA
Time: 19.503 ms
ERROR:  extension "pg_duckdb" must be installed in schema "public"
Time: 16.968 ms

We're evaluating how to handle this and I think our preference would be to tell users to put both extensions into separate schemas.

I see in pg_duckdb's .control file that it's both relocatable=false and schema=public and it's this combination that prevents it from being relocated (third bullet point here: https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION).

I'm sure this was intentional, however I don't know if it would be as easy as removing the schema=public line.

Thanks for your consideration.

[YuweiXiao]

A simple workaround is to create timescaledb first, then pg_duckdb. There was a issue, and they solved it by detecting and skipping the dup function creation in pg_duckdb.

[YuweiXiao]

#934

[eeeebbbbrrrr]

Thanks @YuweiXiao. That indeed works and it's clear pg_duckdb has some specific accounting for this:

postgres=> create extension timescaledb;
CREATE EXTENSION
Time: 1029.114 ms (00:01.029)
postgres=> create extension pg_duckdb;
WARNING:  time_bucket function already exists, use duckdb.time_bucket instead. This is usually because the timescale extension is installed.
CREATE EXTENSION
Time: 987.062 ms

However, from the perspective of a hosting provider, this isn't ideal. While we can document that things need to happen in a certain order, ultimately we have no control over what our users do and when they do it.

I hope the kind folks here at duckdb will take the hosting provider use-case under consideration. Ideally both pg_duckdb and timescaledb could co-exist in the public schema, but that would require changing the time_bucket function name. So the alternative is to allow pg_duckdb to be relocated to a different schema during CREATE EXTENSION.

[eeeebbbbrrrr]

Hi, I'm following up to ask if the pg_duckdb team has had a chance to consider this request. It would be great for our users @planetscale, and it would also allow pg_duckdb to be a good citizen in the Postgres extension ecosystem.

[TylerHillery]

Just noticing this discussion now but I opened a PR for this. I agree this would be nice to configure.

[TylerHillery]

I'm sure this was intentional, however I don't know if it would be as easy as removing the schema=public line.

From what I could tell, that's all that was needed to change this. Would be nice for someone to confirm. My change still doesn't allow relocating the extension, you have have to drop and recreate. Not sure if that still causes issues for you.