Using the create_parent function

[christiansalako]

Hi everyone.

I seem to be having problems using the create_parent function.

I can do steps 1-4 below without any issues:

1. CREATE SCHEMA partman_schema
2. CREATE EXTENSION pg_partman SCHEMA partman_schema
3. CREATE TABLE partman_schema.sessions ( id bigint NOT NULL, device character varying, finished_at timestamp(6) without time zone, created_at timestamp(6) without time zone NOT NULL ) PARTITION BY RANGE (created_at);
4. CREATE TABLE partman_schema.sessions_template (LIKE partman_schema.sessions)

5. SELECT partman_schema.create_parent( p_parent_table := 'partman_schema.sessions' , p_control := 'created_at' , p_interval := '1 month' , p_template_table := 'partman_schema.sessions_template' , p_premake := '5' )

Posted below is the error I get once I try to do step 5 (above) when migrating a production database - locally I face no difficulties.

ERROR: function partman_schema.create_parent(p_parent_table => unknown, p_control => unknown, p_interval => unknown, p_template_table => unknown, p_premake => unknown) does not exist (PG::UndefinedFunction) LINE 1: SELECT partman_schema.create_parent( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I've trawled through the docs and can't seem to locate my issue. I'm using AWS RDS Aurora if that's at all helpful.

Thanks in advance

[keithf4]

What version of the extension is installed? You can see that by running \dx in psql

[christiansalako]

Thanks for the speedy reply - It looks like it's 4.7.3

[keithf4]

I don't see how your step 3 is succeeding. You're missing a column definition

keith=# CREATE TABLE partman_schema.sessions ( id bigint NOT NULL, device character varying ) PARTITION BY RANGE (created_at)
;
ERROR:  column "created_at" named in partition key does not exist
LINE 1: ...L, device character varying ) PARTITION BY RANGE (created_at...

[christiansalako]

Forgive me, that was my desire to not take up more space than was actually needed.

I've updated my question to show the full psql command

[keithf4]

The premake column is an integer, not a text parameter.

[christiansalako]

Good spot - sadly, changing the premake column doesn't rectify this particular error.

[keithf4]

You're missing the p_type parameter. Any function parameter without a default value must be given.

github654=# CREATE SCHEMA partman_schema
CREATE EXTENSION pg_partman SCHEMA partman_schema
github654-# ^C
github654=# CREATE SCHEMA partman_schema;
CREATE SCHEMA
github654=# CREATE EXTENSION pg_partman SCHEMA partman_schema;
CREATE EXTENSION
github654=# CREATE TABLE partman_schema.sessions ( id bigint NOT NULL, device character varying, finished_at timestamp(6) without time zone, created_at timestamp(6) without time zone NOT NULL ) PARTITION BY RANGE (created_at);
CREATE TABLE
github654=# CREATE TABLE partman_schema.sessions_template (LIKE partman_schema.sessions)
github654-# ;
CREATE TABLE
github654=# SELECT partman_schema.create_parent(p_parent_table := 'partman_schema.sessions', p_control := 'created_at', p_type := 'native', p_interval := '1 month', p_template_table := 'partman_schema.sessions_template', p_premake := 5 );
 create_parent 
---------------
 t
(1 row)

github654=# \d+ partman_schema.sessions
                                             Partitioned table "partman_schema.sessions"
   Column    |              Type              | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-------------+--------------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id          | bigint                         |           | not null |         | plain    |             |              | 
 device      | character varying              |           |          |         | extended |             |              | 
 finished_at | timestamp(6) without time zone |           |          |         | plain    |             |              | 
 created_at  | timestamp(6) without time zone |           | not null |         | plain    |             |              | 
Partition key: RANGE (created_at)
Partitions: partman_schema.sessions_p2023_11 FOR VALUES FROM ('2023-11-01 00:00:00') TO ('2023-12-01 00:00:00'),
            partman_schema.sessions_p2023_12 FOR VALUES FROM ('2023-12-01 00:00:00') TO ('2024-01-01 00:00:00'),
            partman_schema.sessions_p2024_01 FOR VALUES FROM ('2024-01-01 00:00:00') TO ('2024-02-01 00:00:00'),
            partman_schema.sessions_p2024_02 FOR VALUES FROM ('2024-02-01 00:00:00') TO ('2024-03-01 00:00:00'),
            partman_schema.sessions_p2024_03 FOR VALUES FROM ('2024-03-01 00:00:00') TO ('2024-04-01 00:00:00'),
            partman_schema.sessions_p2024_04 FOR VALUES FROM ('2024-04-01 00:00:00') TO ('2024-05-01 00:00:00'),
            partman_schema.sessions_p2024_05 FOR VALUES FROM ('2024-05-01 00:00:00') TO ('2024-06-01 00:00:00'),
            partman_schema.sessions_p2024_06 FOR VALUES FROM ('2024-06-01 00:00:00') TO ('2024-07-01 00:00:00'),
            partman_schema.sessions_p2024_07 FOR VALUES FROM ('2024-07-01 00:00:00') TO ('2024-08-01 00:00:00'),
            partman_schema.sessions_p2024_08 FOR VALUES FROM ('2024-08-01 00:00:00') TO ('2024-09-01 00:00:00'),
            partman_schema.sessions_p2024_09 FOR VALUES FROM ('2024-09-01 00:00:00') TO ('2024-10-01 00:00:00'),
            partman_schema.sessions_default DEFAULT

[christiansalako]

@keithf4 you're an absolute star that's done the trick!

Please let me know how I can buy you a coffee