crate/cratedb-toolkit

Prevent multiple strategies operating on the same table

amotl opened this issue · 3 comments

amotl commented

About

The idea behind the composite primary key PRIMARY KEY ("strategy", "table_schema", "table_name") was to prevent duplicate strategies on the same table. Too bad we don't have UNIQUE constraints in CrateDB.

Regression?

Is there elsewhere in the code a check to prevent duplicates (i.e., for the same table, one entry with DELETE and 3 days retention, and another with DELETE and 5 days retention on the same table)?

Originally posted by @hammerhead in #20 (comment)

amotl commented

Hi Niklas,

thank you for picking that up from #20 (review). I agree with you, it is sad that we can not hold up that UNIQUEness constraint because adding a sane record editing API just needs a dedicated unique primary key instead.

I also agree that it is a regression. The constraint will need to be re-added by implementing it through code, right. I will add it back on behalf of a subsequent patch.

With kind regards,
Andreas.

amotl commented

Hi again.

  • GH-28 intends to resolve this issue.
amotl commented

Hi again,

@hammerhead added a few valuable thoughts to this topic at #28 (comment). Thank you very much.

It can make sense to have multiple policies for one table. Especially, to combine the REALLOCATE strategy with others. Example: For table x, I want to reallocate partitions after 30 days from a hot to a warm node, and after 60 days I want to permanently delete it (or snapshot with deletion).

But indeed it doesn't make sense to have a combination of the DELETE + SNAPSHOT strategies on the same table, as both will erase the data.

A REALLOCATE strategy with a shorter retention period than a DELETE/SNAPSHOT strategy makes sense, though.

With kind regards,
Andreas.