artifacthub/hub

Compatibility with pg_partman version 5.0.1 and PostgreSQL 14+

Closed this issue · 6 comments

suprajapaturi commented

Is your feature request related to a problem? Please describe.

During the migration script execution using Cloud SQL for PostgreSQL version 15, we are encountering an issue with the pg_partman extension during schema migrations. The specific error is related to the monthly partitioning type not being recognized by pg_partman version 5.0.1 when using PostgreSQL 14 and above.

Error message:

2024-07-09 09:39:25 executing 032_packages_views.sql up
create extension pg_partman;

create table if not exists package_views (
    package_id uuid not null references package on delete cascade,
    version text not null,
    day date not null,
    total integer not null,
    unique (package_id, version, day)
) partition by range (day);

select create_parent('public.package_views', 'day', 'native', 'monthly', p_start_partition := current_date::text);

ERROR: monthly is not a valid partitioning type for pg_partman
CONTEXT: PL/pgSQL function create_parent(text,text,text,text,text,integer,text,boolean,text,text[],text,boolean,text) line 111 at RAISE
DETAIL: 
HINT:  (SQLSTATE P0001)

Release notes of Cloud SQL for PostgreSQL

Requires update on https://github.com/artifacthub/hub/blob/master/database/migrations/schema/032_packages_views.sql
https://github.com/artifacthub/hub/blob/master/database/migrations/schema/034_partman_schema.sql

Describe the solution you'd like
The create_parent function should support the monthly partitioning type as it does in PostgreSQL 13 or PG_PARTMAN version lower than 5.0.1.

Additional context
This issue impacts the ability to run schema migrations on newer PostgreSQL versions, causing potential downtime and maintenance challenges.

tegioz commented

Thanks for reporting this @suprajapaturi, we'll take care of it 👍

  • 👍1
tegioz commented

Hi @suprajapaturi

#3942 should fix this issue. Would you like to give it a try before we merge?

Thanks!

suprajapaturi commented

Hi @tegioz, I've tested the fix and it is working as expected. Thank you for addressing this!

tegioz commented

Awesome, thanks for testing it! ❤️

  • 😄1
perlybird commented

Any chance to have a new release soon with this fix integrated?
pg_partman 4.x is broken with postgres 17, it means that the last released version of artifacthub cannot run with pg17 atm.

tegioz commented

Hi @perlybird

We have plans to release a new version this week 🙂

  • 👍4