Retention policy scope for delete commands

[kostasb]

Feature request: Support retention policy scope for series deletion commands such as DROP MEASUREMENT , DROP SERIES , DELETE FROM.

Current behavior: Delete commands have database-wide scope. As a result, it is not possible to delete a measurement under a specific retention policy without having it deleted from all retention policies in the database it lives in.

Consider:

db1.rp1.measurement
db2.rp2.measurement

There is currently no way to drop the measurement or some series of it, from a specific rp.

[jwyse]

+1

[ryantheleach]

Oh man.

This thoroughly confused me when attempting to delete data. (on 1.3, due to supported libraries on .net)

1. Chronograf fails to parse

DELETE FROM /.*/ WHERE "time" >= '2018-02-28T02:26:08.0000000Z' AND "time" <= '2018-02-28T02:27:08.0000000Z'

database not found

DELETE FROM "SensorData"../.*/ WHERE "time" >= '2018-02-28T02:26:08.0000000Z' AND "time" <= '2018-02-28T02:27:08.0000000Z'

received status code 400 from server: err: error parsing query: database not supported at line 1, char 1

When it works fine from command line as no database was specified, yet if you specific a database, it fails.

Additionally this prevents me from having a slightly more flexible schema. https://stackoverflow.com/questions/49022407/how-can-i-delete-measurements-within-a-time-range-for-a-given-rp

[EugenNY]

this missing feature makes the use of retention policies and CONTINUOUS QUERIES hardly usable...

[fanjun1980]

+1

[alespour]

+1

[nicolas17]

I created a few RPs to store aggregated data, created CQs to put new data in them, and backfilled them with some SELECT..INTO queries. Now I want to delete the old high-resolution data from the default RP.

• If I use DELETE FROM telegraf.autogen.cpu WHERE time < now() - 30d, I get "ERR: error parsing query: retention policy not supported" (ie. this bug).
• If I use DELETE FROM cpu WHERE time < now() - 30d, I would delete old cpu data for all RPs, including what I just aggregated.
• If I alter the autogen RP to retain data for 30 days, I would delete old data for all measurements in the autogen RP rather than only cpu.

Looks like the only solution is to do the aggregated backfilling for all my measurements and only alter the RP when I'm completely done... for which I'll have to give my server a bigger disk first :sparkles:

[lansalot]

This is a pretty serious hole - any ETA for implementation?

[akamac]

Any roadmap for this feature?

[xPaw]

This is silly.

I did use database.autogen which said it switched to the database using a retention policy, and then drop measurement name which ended up deleting data from autogen AND another retention policy I had.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[akamac]

This needs to be reopened and addressed.

[hanynowsky]

Oh My goodness. I cam across this issue to day when I had to delete some data from the default retention and the from another downgraded retention. And all I get is: ERR: error parsing query: retention policy not supported at line 1, char 1

This is a high priority issue.

What is the point of DELETE if it cannot be applied to retention policies.

[andydavidson]

'wontfix'ing this is sinful.

[James00001]

Yes, clearly "stale bot" closed this one in error, someone with the authority to do so needs to mark this as re-opened.

[foobar]

still needed

[dgnorton]

Re-opened. This issue was closed automatically for being stale but is a valid issue.

[stevenaldinger]

this is insane. data was written to a 360d retention policy accidentally. is there seriously no way to fix this? we're just stuck with ~15 minutes of data for all our users for 360d? redis timeseries is looking like its worth investigating at this point between this and other influxDB-isms...

Edit: check out https://questdb.io/ if you're not stuck with influx

[teichhei]

Same issue. 3 years ongoing, seriously? I can do a select * from "oneyear"."HVAC" but no way to get rid of the records.

[andriy-pankiv-lemberg]

Same issue, 3 years for basic delete feature) So the only way to do it is to:

1. move data to another table with default infinite RP
2. drop original table
3. delete needed data in this tmp table with default RP
4. move data back to new table with the same name and RP

It is ugly, but at least it will do the job

[kszarlej]

:+1: I do also need this feature

[SadSadko]

so over 4 years, no comments, no ETA. if the problem is not regarding Flux QL it's not getting attention? This is ridiculous

[LeonChadwick]

+1 also need this feature. Without it we have to get things perfect first time around, every time, which is unrealistic.

[sfawcett123]

+1 This is needed

[merrilmathew]

+1

[mrwogu]

Still waiting for it

[mac-lucky]

Still waiting

[barrettathome]

+1

[ascha191]

+1

[firesgc]

+1

[ferengi82]

+1

[alkissack]

+1

[tubemeister]

+1

Let me just add that I'm baffled this isn't implemented. Who on earth thought that was a good idea, this is almost as out there as the "weeks start on thursday because unix epoch was thursday" thing, another InfluxDB classic headscratcher. Whiskey Tango Foxtrot, Over?

[Muqeet1]

+1

[rayquaza912]

+1

[osulivanDE]

+1

[d123456temp]

+!

[Sesshoumaru-sama]

+1

[bcutter]

I simply can't delete any data.

Using delete from "%" where "entity_id"='entity_id=h5055_batt'; seems to work (no output, so it seems to fail silentsly), but doing a select shows/confirms: data still exists.

Retention policy is open end (Duration: ∞).

Using delete from "homeassistant"."autogen"."%" where "entity_id"='h5055_batt'; gives

ERR: error parsing query: retention policy not supported at line 1, char 1

⁉️⁉️⁉️ HOW CAN I FREAKING GET RID OF DATA (single series) ⁉️⁉️⁉️