search

pgpartman / pg_partman

Partition management extension for PostgreSQL
Other
2.05k stars 279 forks source link

Issue with dashes in schema name when calling create_parent function: ""staging-boo-foo"" does not exist. #669

Open alizdavoodi opened 2 months ago

alizdavoodi commented 2 months ago

Hey, We have an issue if the schema name has a dash in it, like: staging-boo-foo

We created this schema and enabled the extension, but when we tried to call the create_parent function, we received an error.

SELECT create_parent( p_parent_table => 'staging-boo-foo.realtime_metrics_test',
                              -- The column on which the partitioning is to be based. The data type must be an integer or time-based.
                              p_control => 'generated_at',
                              p_type => 'native',
                              -- The time interval or integer range for each partition
                              p_interval=> 'hourly',
                              -- The number of partitions to create in advance to support new inserts.
                              p_premake => 24);

The output 

psql:test_partition.sql:8: ERROR:  schema ""staging-boo-foo"" does not exist
CONTEXT: PL/pgSQL function create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 159 at EXECUTE
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 788 at RAISE

But the schema is already there.

keithf4 commented 2 months ago

Can you tell me what version of pg_partman you have installed? You can see this by running \dx in psql

keith=# \dx
                                          List of installed extensions
     Name     | Version |    Schema     |                              Description                              
--------------+---------+---------------+-----------------------------------------------------------------------
 pg_cron      | 1.6     | pg_catalog    | Job scheduler for PostgreSQL
 pg_partman   | 5.1.0   | partman       | Extension to manage partitioned tables by time or ID
keithf4 commented 2 months ago

I did a test with 5.1 and was able to make a partition set in your example schema without any issues. I don't have a system with partman 4.x installed at the moment, but there were not any changes for handling non-standard characters in the 5.x release. So please make sure you're on at least 4.7.4

keith=# \d+ "staging-boo-foo"."Time_Taptest_Table"
                                     Partitioned table "staging-boo-foo.Time_Taptest_Table"
 Column |           Type           | Collation | Nullable |    Default    | Storage  | Compression | Stats target | Description 
--------+--------------------------+-----------+----------+---------------+----------+-------------+--------------+-------------
 Col1   | integer                  |           |          |               | plain    |             |              | 
 Col2   | text                     |           |          | 'stuff'::text | extended |             |              | 
 Col3   | timestamp with time zone |           | not null | now()         | plain    |             |              | 
Partition key: RANGE ("Col3")
Partitions: "staging-boo-foo"."Time_Taptest_Table_p20240704" FOR VALUES FROM ('2024-07-04 00:00:00-04') TO ('2024-07-05 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240705" FOR VALUES FROM ('2024-07-05 00:00:00-04') TO ('2024-07-06 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240706" FOR VALUES FROM ('2024-07-06 00:00:00-04') TO ('2024-07-07 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240707" FOR VALUES FROM ('2024-07-07 00:00:00-04') TO ('2024-07-08 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240708" FOR VALUES FROM ('2024-07-08 00:00:00-04') TO ('2024-07-09 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240709" FOR VALUES FROM ('2024-07-09 00:00:00-04') TO ('2024-07-10 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240710" FOR VALUES FROM ('2024-07-10 00:00:00-04') TO ('2024-07-11 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240711" FOR VALUES FROM ('2024-07-11 00:00:00-04') TO ('2024-07-12 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240712" FOR VALUES FROM ('2024-07-12 00:00:00-04') TO ('2024-07-13 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240713" FOR VALUES FROM ('2024-07-13 00:00:00-04') TO ('2024-07-14 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240714" FOR VALUES FROM ('2024-07-14 00:00:00-04') TO ('2024-07-15 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240715" FOR VALUES FROM ('2024-07-15 00:00:00-04') TO ('2024-07-16 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240716" FOR VALUES FROM ('2024-07-16 00:00:00-04') TO ('2024-07-17 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_p20240717" FOR VALUES FROM ('2024-07-17 00:00:00-04') TO ('2024-07-18 00:00:00-04'),
            "staging-boo-foo"."Time_Taptest_Table_default" DEFAULT
alizdavoodi commented 2 months ago

Thanks for the prompt response! Indeed, we're using 4.6, and I'm not sure if we can actually upgrade this extension because we're using an AWS Aurora instance. Also, I couldn't find any documentation about PostgreSQL extension upgrades in AWS documentation. afbeelding

I've also tried with RDS postgres 16.2 which has newer version of the extension and I could run create_parent function with a schema with dash in the name. afbeelding

keithf4 commented 2 months ago

I tested with 4.6.0 and am able to do this with your example schema as well. So there is something odd going on with your setup. Maybe try putting a support ticket in with AWS?

alizdavoodi commented 2 months ago

Did you use the same sql statement?

SELECT create_parent( p_parent_table => 'staging-boo-foo.realtime_metrics_test',
                              -- The column on which the partitioning is to be based. The data type must be an integer or time-based.
                              p_control => 'generated_at',
                              p_type => 'native',
                              -- The time interval or integer range for each partition
                              p_interval=> 'hourly',
                              -- The number of partitions to create in advance to support new inserts.
                              p_premake => 24);
keithf4 commented 2 months ago 
keith=# \dx
                                          List of installed extensions
     Name     | Version |    Schema     |                              Description                              
--------------+---------+---------------+-----------------------------------------------------------------------
 pg_partman   | 4.6.0   | partman       | Extension to manage partitioned tables by time or ID
 pgtap        | 1.2.0   | public        | Unit testing for PostgreSQL
 plpgsql      | 1.0     | pg_catalog    | PL/pgSQL procedural language

keith=# \d+ "staging-boo-foo".realtime_metrics_test 
                                   Partitioned table "staging-boo-foo.realtime_metrics_test"
    Column    |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id           | integer                  |           |          |         | plain   |             |              | 
 generated_at | timestamp with time zone |           |          |         | plain   |             |              | 
Partition key: RANGE (generated_at)
Number of partitions: 0
keith=# SELECT partman.create_parent( p_parent_table => 'staging-boo-foo.realtime_metrics_test',
                              -- The column on which the partitioning is to be based. The data type must be an integer or time-based.
                              p_control => 'generated_at',
                              p_type => 'native',
                              -- The time interval or integer range for each partition
                              p_interval=> 'hourly',
                              -- The number of partitions to create in advance to support new inserts.
                              p_premake => 24);
 create_parent 
---------------
 t
(1 row)
keith=# \d+ "staging-boo-foo".realtime_metrics_test 
                                   Partitioned table "staging-boo-foo.realtime_metrics_test"
    Column    |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 id           | integer                  |           |          |         | plain   |             |              | 
 generated_at | timestamp with time zone |           |          |         | plain   |             |              | 
Partition key: RANGE (generated_at)
Partitions: "staging-boo-foo".realtime_metrics_test_p2024_07_08_1100 FOR VALUES FROM ('2024-07-08 11:00:00-04') TO ('2024-07-08 12:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1200 FOR VALUES FROM ('2024-07-08 12:00:00-04') TO ('2024-07-08 13:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1300 FOR VALUES FROM ('2024-07-08 13:00:00-04') TO ('2024-07-08 14:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1400 FOR VALUES FROM ('2024-07-08 14:00:00-04') TO ('2024-07-08 15:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1500 FOR VALUES FROM ('2024-07-08 15:00:00-04') TO ('2024-07-08 16:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1600 FOR VALUES FROM ('2024-07-08 16:00:00-04') TO ('2024-07-08 17:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1700 FOR VALUES FROM ('2024-07-08 17:00:00-04') TO ('2024-07-08 18:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1800 FOR VALUES FROM ('2024-07-08 18:00:00-04') TO ('2024-07-08 19:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_1900 FOR VALUES FROM ('2024-07-08 19:00:00-04') TO ('2024-07-08 20:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_2000 FOR VALUES FROM ('2024-07-08 20:00:00-04') TO ('2024-07-08 21:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_2100 FOR VALUES FROM ('2024-07-08 21:00:00-04') TO ('2024-07-08 22:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_2200 FOR VALUES FROM ('2024-07-08 22:00:00-04') TO ('2024-07-08 23:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_08_2300 FOR VALUES FROM ('2024-07-08 23:00:00-04') TO ('2024-07-09 00:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0000 FOR VALUES FROM ('2024-07-09 00:00:00-04') TO ('2024-07-09 01:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0100 FOR VALUES FROM ('2024-07-09 01:00:00-04') TO ('2024-07-09 02:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0200 FOR VALUES FROM ('2024-07-09 02:00:00-04') TO ('2024-07-09 03:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0300 FOR VALUES FROM ('2024-07-09 03:00:00-04') TO ('2024-07-09 04:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0400 FOR VALUES FROM ('2024-07-09 04:00:00-04') TO ('2024-07-09 05:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0500 FOR VALUES FROM ('2024-07-09 05:00:00-04') TO ('2024-07-09 06:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0600 FOR VALUES FROM ('2024-07-09 06:00:00-04') TO ('2024-07-09 07:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0700 FOR VALUES FROM ('2024-07-09 07:00:00-04') TO ('2024-07-09 08:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0800 FOR VALUES FROM ('2024-07-09 08:00:00-04') TO ('2024-07-09 09:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_0900 FOR VALUES FROM ('2024-07-09 09:00:00-04') TO ('2024-07-09 10:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1000 FOR VALUES FROM ('2024-07-09 10:00:00-04') TO ('2024-07-09 11:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1100 FOR VALUES FROM ('2024-07-09 11:00:00-04') TO ('2024-07-09 12:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1200 FOR VALUES FROM ('2024-07-09 12:00:00-04') TO ('2024-07-09 13:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1300 FOR VALUES FROM ('2024-07-09 13:00:00-04') TO ('2024-07-09 14:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1400 FOR VALUES FROM ('2024-07-09 14:00:00-04') TO ('2024-07-09 15:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1500 FOR VALUES FROM ('2024-07-09 15:00:00-04') TO ('2024-07-09 16:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1600 FOR VALUES FROM ('2024-07-09 16:00:00-04') TO ('2024-07-09 17:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1700 FOR VALUES FROM ('2024-07-09 17:00:00-04') TO ('2024-07-09 18:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1800 FOR VALUES FROM ('2024-07-09 18:00:00-04') TO ('2024-07-09 19:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_1900 FOR VALUES FROM ('2024-07-09 19:00:00-04') TO ('2024-07-09 20:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_2000 FOR VALUES FROM ('2024-07-09 20:00:00-04') TO ('2024-07-09 21:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_2100 FOR VALUES FROM ('2024-07-09 21:00:00-04') TO ('2024-07-09 22:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_2200 FOR VALUES FROM ('2024-07-09 22:00:00-04') TO ('2024-07-09 23:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_09_2300 FOR VALUES FROM ('2024-07-09 23:00:00-04') TO ('2024-07-10 00:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0000 FOR VALUES FROM ('2024-07-10 00:00:00-04') TO ('2024-07-10 01:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0100 FOR VALUES FROM ('2024-07-10 01:00:00-04') TO ('2024-07-10 02:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0200 FOR VALUES FROM ('2024-07-10 02:00:00-04') TO ('2024-07-10 03:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0300 FOR VALUES FROM ('2024-07-10 03:00:00-04') TO ('2024-07-10 04:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0400 FOR VALUES FROM ('2024-07-10 04:00:00-04') TO ('2024-07-10 05:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0500 FOR VALUES FROM ('2024-07-10 05:00:00-04') TO ('2024-07-10 06:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0600 FOR VALUES FROM ('2024-07-10 06:00:00-04') TO ('2024-07-10 07:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0700 FOR VALUES FROM ('2024-07-10 07:00:00-04') TO ('2024-07-10 08:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0800 FOR VALUES FROM ('2024-07-10 08:00:00-04') TO ('2024-07-10 09:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_0900 FOR VALUES FROM ('2024-07-10 09:00:00-04') TO ('2024-07-10 10:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_1000 FOR VALUES FROM ('2024-07-10 10:00:00-04') TO ('2024-07-10 11:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_p2024_07_10_1100 FOR VALUES FROM ('2024-07-10 11:00:00-04') TO ('2024-07-10 12:00:00-04'),
            "staging-boo-foo".realtime_metrics_test_default DEFAULT
alizdavoodi commented 2 months ago

Ok, I have found the actual problem. This is not because of the p_parent_table but because of the name of the schema that we have enabled partman in.

SELECT "staging-boo-foo".create_parent( p_parent_table => 'staging-boo-foo.realtime_reportable_metrics_test',
                              -- The column on which the partitioning is to be based. The data type must be an integer or time-based.
                              p_control => 'generated_at',
                              p_type => 'native',
                              -- The time interval or integer range for each partition
                              p_interval=> 'hourly',
                              -- The number of partitions to create in advance to support new inserts.
                              p_premake => 24);

So if you enable partman in a schema with dash in the name then you would get this error also.

psql:test_partition.sql:9: ERROR:  schema ""staging-boo-foo"" does not exist
CONTEXT: PL/pgSQL function "staging-boo-foo".create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 161 at EXECUTE
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function "staging-msa-primary-reserve-rt-reporter".create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 807 at RAISE
keithf4 commented 2 months ago

Looking through the code, I'm not sure I'm going to be able to easily support having partman installed to a schema with non-standard characters, so I'm not sure it's something I want to work on fixing.

In general, I wouldn't recommend installing an extension to a schema with special characters anyway. That means anyone that uses that extension's functionality would either need to know that they have to specially quote the schema or alter their search path for it. Is there any reason you cannot install it to a partman schema or some other schema without dashes?

alizdavoodi commented 2 months ago

Thanks for pointing this out. We have a user/role in PostgreSQL that has access to a schema with a dash in the name. We thought that if we enabled Partman in this schema, then the PostgreSQL user who has access to the schema could run the create_parent function(in a sense of permissions). This is an assumption, though; it might not work. Because of this limitation, we couldn't test this assumption. So we want to enable the extension with the super user, then we want to delegate the partman configuration e.g. create_parent to the non-super user.

keithf4 commented 2 months ago

I would recommend putting pg_partman in its own dedicated schema as the included instructions show. Then you can allow any users that need access to its functions access to that schema. Anyone without usage privileges on the schema will not have access to use partman.

GRANT USAGE ON SCHEMA partman TO myuser;

The grants required to allow a non-superuser full access to partman functionality are in the Setup instructions here - https://github.com/pgpartman/pg_partman?tab=readme-ov-file#setup

keithf4 commented 2 months ago

Note the one difference if you don't want to allow the user to actually create objects in the partman schema. Use the command above, not the following

GRANT ALL ON SCHEMA partman TO partman_user;

I may actually change this in my example instructions as well.