search

qonto / postgresql-partition-manager

Simplified PostgreSQL partioning management
MIT License
6 stars 2 forks source link

Incorrect bounds with uuid type? #3

Closed dverite closed 6 months ago

dverite commented 7 months ago

I'm starting from a parent table with no partition:

 \d+ chipcvc_token_vault_transient
                               Partitioned table "public.chipcvc_token_vault_transient"
   Column   |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
------------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 token_id   | uuid              |           | not null |         | plain    |             |              | 
 context_id | character varying |           | not null |         | extended |             |              | 
 crypto_iv  | bytea             |           | not null |         | extended |             |              | 
 ciphertext | bytea             |           | not null |         | extended |             |              | 
Partition key: RANGE (token_id)
Indexes:
    "chipcvc_token_vault_transient_pkey" PRIMARY KEY, btree (token_id)

and this config:

partitions:
  tokens:
     schema: public
     table: chipcvc_token_vault_transient
     partitionKey: token_id
     interval: monthly
     retention: 2
     preProvisioned: 3

The provisioning produces overlapping partitions. Debug output:

Using config file: conf.yaml
time=2024-04-11T19:16:19.693+02:00 level=DEBUG msg="Creating partition" schema=public table=chipcvc_token_vault_transient_2024_03
time=2024-04-11T19:16:19.695+02:00 level=DEBUG msg="Table already exists, skip" schema=public table=chipcvc_token_vault_transient_2024_03
time=2024-04-11T19:16:19.702+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_03 FOR VALUES FROM ('018df74f-8400-7300-0000-000000000000') TO ('018e96f4-a800-7e00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_03 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:19.710+02:00 level=INFO msg="Partition attached to parent table" schema=public table=chipcvc_token_vault_transient_2024_03 parent_table=chipcvc_token_vault_transient
time=2024-04-11T19:16:19.710+02:00 level=DEBUG msg="Creating partition" schema=public table=chipcvc_token_vault_transient_2024_02
time=2024-04-11T19:16:19.711+02:00 level=DEBUG msg="Create table" query="CREATE TABLE public.chipcvc_token_vault_transient_2024_02 (LIKE chipcvc_token_vault_transient)"
time=2024-04-11T19:16:19.713+02:00 level=INFO msg="Table created" schema=public table=chipcvc_token_vault_transient_2024_02
time=2024-04-11T19:16:19.717+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_02 FOR VALUES FROM ('018d61f7-1800-7400-0000-000000000000') TO ('018df74f-8400-7c00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_02 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:19.717+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_02\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_02 attempt=1 max_retries=3
time=2024-04-11T19:16:20.717+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_02 FOR VALUES FROM ('018d61f7-1800-7400-0000-000000000000') TO ('018df74f-8400-7c00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_02 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:20.719+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_02\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_02 attempt=2 max_retries=3
time=2024-04-11T19:16:22.720+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_02 FOR VALUES FROM ('018d61f7-1800-7400-0000-000000000000') TO ('018df74f-8400-7c00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_02 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:22.721+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_02\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_02 attempt=3 max_retries=3
time=2024-04-11T19:16:25.723+02:00 level=ERROR msg="Failed to create partition" error="failed to attach partition after retries: failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_02\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_02
time=2024-04-11T19:16:25.724+02:00 level=DEBUG msg="Creating partition" schema=public table=chipcvc_token_vault_transient_2024_05
time=2024-04-11T19:16:25.725+02:00 level=DEBUG msg="Create table" query="CREATE TABLE public.chipcvc_token_vault_transient_2024_05 (LIKE chipcvc_token_vault_transient)"
time=2024-04-11T19:16:25.733+02:00 level=INFO msg="Table created" schema=public table=chipcvc_token_vault_transient_2024_05
time=2024-04-11T19:16:25.748+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_05 FOR VALUES FROM ('018f3173-7000-7300-0000-000000000000') TO ('018fd118-9400-7800-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_05 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:25.754+02:00 level=INFO msg="Partition attached to parent table" schema=public table=chipcvc_token_vault_transient_2024_05 parent_table=chipcvc_token_vault_transient
time=2024-04-11T19:16:25.754+02:00 level=DEBUG msg="Creating partition" schema=public table=chipcvc_token_vault_transient_2024_06
time=2024-04-11T19:16:25.755+02:00 level=DEBUG msg="Table already exists, skip" schema=public table=chipcvc_token_vault_transient_2024_06
time=2024-04-11T19:16:25.757+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_06 FOR VALUES FROM ('018fd118-9400-7400-0000-000000000000') TO ('01906b97-5c00-7f00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_06 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:25.757+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_06\" would overlap partition \"chipcvc_token_vault_transient_2024_05\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_06 attempt=1 max_retries=3
time=2024-04-11T19:16:26.757+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_06 FOR VALUES FROM ('018fd118-9400-7400-0000-000000000000') TO ('01906b97-5c00-7f00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_06 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:26.759+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_06\" would overlap partition \"chipcvc_token_vault_transient_2024_05\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_06 attempt=2 max_retries=3
time=2024-04-11T19:16:28.760+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_06 FOR VALUES FROM ('018fd118-9400-7400-0000-000000000000') TO ('01906b97-5c00-7f00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_06 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:28.761+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_06\" would overlap partition \"chipcvc_token_vault_transient_2024_05\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_06 attempt=3 max_retries=3
time=2024-04-11T19:16:31.764+02:00 level=ERROR msg="Failed to create partition" error="failed to attach partition after retries: failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_06\" would overlap partition \"chipcvc_token_vault_transient_2024_05\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_06
time=2024-04-11T19:16:31.764+02:00 level=DEBUG msg="Creating partition" schema=public table=chipcvc_token_vault_transient_2024_07
time=2024-04-11T19:16:31.765+02:00 level=DEBUG msg="Create table" query="CREATE TABLE public.chipcvc_token_vault_transient_2024_07 (LIKE chipcvc_token_vault_transient)"
time=2024-04-11T19:16:31.773+02:00 level=INFO msg="Table created" schema=public table=chipcvc_token_vault_transient_2024_07
time=2024-04-11T19:16:31.780+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_07 FOR VALUES FROM ('01906b97-5c00-7c00-0000-000000000000') TO ('01910b3c-8000-7e00-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_07 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:31.783+02:00 level=INFO msg="Partition attached to parent table" schema=public table=chipcvc_token_vault_transient_2024_07 parent_table=chipcvc_token_vault_transient
time=2024-04-11T19:16:31.783+02:00 level=DEBUG msg="Creating partition" schema=public table=chipcvc_token_vault_transient_2024_04
time=2024-04-11T19:16:31.783+02:00 level=DEBUG msg="Create table" query="CREATE TABLE public.chipcvc_token_vault_transient_2024_04 (LIKE chipcvc_token_vault_transient)"
time=2024-04-11T19:16:31.786+02:00 level=INFO msg="Table created" schema=public table=chipcvc_token_vault_transient_2024_04
time=2024-04-11T19:16:31.790+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_04 FOR VALUES FROM ('018e96f4-a800-7800-0000-000000000000') TO ('018f3173-7000-7500-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_04 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:31.791+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_04\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_04 attempt=1 max_retries=3
time=2024-04-11T19:16:32.792+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_04 FOR VALUES FROM ('018e96f4-a800-7800-0000-000000000000') TO ('018f3173-7000-7500-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_04 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:32.792+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_04\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_04 attempt=2 max_retries=3
time=2024-04-11T19:16:34.794+02:00 level=DEBUG msg="Attach partition" query="ALTER TABLE public.chipcvc_token_vault_transient ATTACH PARTITION public.chipcvc_token_vault_transient_2024_04 FOR VALUES FROM ('018e96f4-a800-7800-0000-000000000000') TO ('018f3173-7000-7500-0000-000000000000')" partition=chipcvc_token_vault_transient_2024_04 table=chipcvc_token_vault_transient
time=2024-04-11T19:16:34.795+02:00 level=WARN msg="fail to attach partition" error="failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_04\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_04 attempt=3 max_retries=3
time=2024-04-11T19:16:37.796+02:00 level=ERROR msg="Failed to create partition" error="failed to attach partition after retries: failed to attach partition: ERROR: partition \"chipcvc_token_vault_transient_2024_04\" would overlap partition \"chipcvc_token_vault_transient_2024_03\" (SQLSTATE 42P17)" schema=public table=chipcvc_token_vault_transient_2024_04

The bounds for chipcvc_token_vault_transient_2024_03 should be :

select min_uuid_v7('2024-03-01')
             min_uuid_v7              
--------------------------------------
 018df74f-8400-7000-0000-000000000000
(1 row)

select min_uuid_v7('2024-04-01');
             min_uuid_v7              
--------------------------------------
 018e96f4-a800-7000-0000-000000000000
(1 row)
vmercierfr commented 6 months ago

This should be fixed with the method to generate UUIDv7 from init branch in https://github.com/qonto/postgresql-partition-manager/pull/4.

Can you try the latest version?