How do I delete selected values from Postgres DB?

[distans]

@Gunner don’t like when ppl are bumping old threads so I’m feeling obligated to create a new.

Problem:

I’ve have bad sensor data in my db.

So?

That screws up my otherwise very beautiful SuperChart on my Android phone.

Interesting! But is this really a Blynk issue?

You could argue that it is a PostgreSQL-thing. Or perhaps app related if the data is cached/saved on/to the phone for some reason. I don’t know, that’s why I’m asking

Have you tried to find a solution yourself? Like… tried the search function?

Yes, but I couldn’t find any applicable information. All my bad data is from when I first set up my local server and was messing around with the sketch. A smart move would have been to delete all data [from the app] once everything was running smooth, but I didn’t So, with my limited knowledge in PSQL I tried something a bit more drastic he he:

I deleted all data from the tables starting with reporting_ older than 2017-11-18:

postgres=# DELETE from reporting_XXX WHERE ts < '2017-11-18';

and then:

postgres=# REINDEX DATABASE blynk;

Which I thought would do the trick, but data pre 2017-11-18 still shows up in my chart!

Part of my server.properties:

#enable DB
enable.db=true

#enable raw data storage to DB
enable.raw.db.data.store=true

The db gets updated, but the data is also written to the files in /[install dir]/data/[username]

Info about my server:

Linux ubuntu 4.4.0-103-generic #126-Ubuntu SMP Mon Dec 4 16:23:28 UTC 2017 x86_64 GNU/Linux
psql (PostgreSQL) 9.5.10
java 9.0.1
Java™ SE Runtime Environment (build 9.0.1+11)

What am I missing? Am I getting values from the files and not the db?

[Gunner]

@distans

Problem:

So?

Interesting! But is this really a Blynk issue?

Have you tried to find a solution yourself? Like… tried the search function?

I love this!! My job is complete… the people have been conditioned… Now I can retire in the lap of luxury from my overpaid, under worked, moderator position

---

@distans

data pre 2017-11-18 still shows up in my chart!

Having very little in-depth experience with DB idiosyncrasies… All I can think of is to clear out all the data from within the Graphing Widget option… But perhaps backup the DB folder first if you want to try to revert back… or extrapolate otherwise wanted current data up to the clear point.

[distans]

@Gunner

I love this!! My job is complete… the people have been conditioned… Now I can retire in the lap of luxury from my overpaid, under worked, moderator position

After checking my - thesaurus I think the correct word for what has happen during your reign is oppressed!

You get PAID for doing CTRL+C CTRL+V with your jibberish after nearly each and every new post? My God! Where do I sign up for that deal??

All I can think of is to clear out all the data from within the Graphing Widget option

I’ll give it a go. Worst case I loose my non critical temp data history. But I rather keep it. If it fixes the problem I’ll be confused about the benefits of using PSQL when it still use plain files

I’ll be back to hold your pants pocket and give you an update.

[distans]

Stopping PostgreSQL service = data in chart widget

Deleting all data in /[install dir]/data/[username] = no data in chart widget!

So… what’s the use of PostgreSQL?

[Gunner]

@distans

oppressed!

I agree… that is precisely how I feel

@distans

You get PAID for doing CTRL+C CTRL+V with your jibberish

Nope, no pay… strictly volunteering, and I have no qualms about my ‘popularity’ for doing a job correctly And it has actually helped my typing and vocabulary considerably… I can now type with BOTH index fingers

[Gunner]

@distans

So… what’s the use of PostgreSQL?

Probably so the developers don’t need to take time and effort creating and supporting an advanced database of their own… more time for actual Blynk creations.

[Fettkeewl]

@Gunner

I can now type with BOTH index fingers

Congratulations, you are now at the level of my father who is 59

[distans]

@Gunner

Probably so the developers don’t need to take time and effort creating and supporting an advanced database of their own… more time for actual Blynk creations.

Not really the answer I was hoping for. But is it a bug or a feature? I checked the docs on GitHub and it says “raw data storage”. Perhaps it’s just that - storage! Who can clarify this for me? Dimitriy? Costas?

Shall I create a new topic “How do I delete selected values from local data files?”

Can’t really edit the files manually because they look like this:

?▒_▒▒?▒UUUUUU_▒?▒UUUUUU_▒Fd?▒333333_▒R▒?▒_▒yD?▒_▒?▒UUUUUU_▒▒▒?▒UUUUUU_▒▒X?▒UUUUUU▒?▒UUUUUU9?▒_l?▒UUUUUU▒▒?▒▒$?▒UUUUUUҀ?▒UUUUUU#▒▒?▒UUUUUU)8?▒.E▒r`

[Fettkeewl]

@distans

Can’t really edit the files manually because they look like this:

If you are referring to the .Bin files then there are guides out there
https://www.techwalla.com/articles/how-to-edit-a-bin-file

[distans]

@Fettkeewl

Congratulations, you are now at the level of my father who is 59

He’s evolving from pupa to grasshopper men förstår inte svenska

[Fettkeewl]

Vi får lära honom lite svenska ;]

@Gunner, stand in front of a mirror at repeat
“sex laxar i en lax ask”
Once you master it you are ready to learn Swedish. If you need some help getting started I’m sure there are good videos on YouTube!
Cheers

[Gunner]

@Fettkeewl

“sex laxar i en lax ask”

I think I already sound like that upon awakening Besides I am still learning Canaderpian, so I will hold off on new linguistic challenges.

@distans

Not really the answer I was hoping for.

Sorry, I knew it likely wouldn’t be… but then I understood that you just wanted to purge some bad data. So short of learning something myself in order to answer a users question (my usual modus operandi), I kept my answer within the realms of Blynk control.

[distans]

@Fettkeewl

If you are referring to the .Bin files then there are guides out there

But they are not “real” bin/iso files as far as I can tell. iat gives me this

Iso9660 Analyzer Tool v0.1.3 by Salvatore Santagati
Licensed under GPL v2 or later

Image is broken
This image is not CD IMAGE

[distans]

@Gunner

Sorry, I knew it likely wouldn’t be… but then I understood that you just wanted to purge some bad data. So short of learning something myself in order to answer a users question (my usual modus operandi), I kept my answer within the realms of Blynk control

Don’t sweat it, I understand! But I’m not really in the mode for learning Java and plow thru 186564* lines of code and text and eventually find out how 1) the bin-files are encoded and if they can be edited and if 2) psql is only for storing data and never read or 3) I’ve done a terrible mistake and my config is totally wrong.

This is out of my grasp! All I can do is to ping @Dmitriy and hope he can sacrifice a couple of minutes for my cause. I will be satisfied with a yes / no / go to bed As long as I get some answer.

*The blynk-server.git actually contains a total of 186564 lines in the regular files.

~/blynk-server# find . -type f -exec cat {} \; | wc -l
186564

@Gunner this is your homework for tomorrow - explain the above commands

[Gunner]

@distans

2. psql is only for storing data and never read

That would be my guess… and big fat guess it is Primarily due to the fact that I use graphs in a few projects and they work fine… I can even export my CSV files as needed… and yet I do not have PSQL installed in my Local Server.

Therefore, I suspect it is available, as stated, for optional expert/advanced DB use that the developers don’t need to spend much time supporting… since it already has it’s own support system.

@distans

~/blynk-server# find . -type f -exec cat {} ; | wc -l

explain the above commands

Nope… It looks Linuxy to me… therefore I shall run away ing like a little schoolgirl…

[distans]

@Gunner

That would be my guess… and big fat guess it is Primarily due to the fact that I use graphs in a few projects and they work fine… I can even export my CSV files as needed… and yet I do not have PSQL installed in my Local Server.

Therefore, I suspect it is available, as stated, for optional expert/advanced DB use that the developers don’t need to spend much time supporting… since it already has it’s own support system.

I mean, either way is fine by me, as long as I know what’s working and what’s not. It’s not a bad idea per se to store all values in a db for later mining. But depending on how much junk that’s attach to the board and how hard the poor timers gets violated in the sketch - files grow quickly! In the long run I think a db is necessary - or the ability to trim the local files.

From the thread we both posted in yesterday (combining sensors):

@distans

reading the sensors once every 2 seconds equals 43.200 readings a day!

I realized that that’s just half the truth (literally)! He’s going to use TWO sensors.

2 x 30 x 60 x 24 x 365 = 31.536.000

I don’t know how well Java and Blynk handles that many posts, but I have a bad feeling

@Gunner

Nope… It looks Linuxy to me… therefore I shall run away ing like a little schoolgirl…

I will train you well my young apprentice (closest to Starwars emoji I could find )