BLYNK
HOME       📲 GETTING STARTED       📗 DOCS       ❓HELP CENTER       👉 SKETCH BUILDER

How do I delete selected values from Postgres DB?

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

Problem:

I’ve have bad sensor data in my db.

So?

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

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 :smile:

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 :angry: 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?

I love this!! :smiley: 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 :stuck_out_tongue_winking_eye:


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.

image

1 Like

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

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?? :stuck_out_tongue_closed_eyes: :rofl:

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

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

1 Like

Stopping PostgreSQL service = data in chart widget

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

So… what’s the use of PostgreSQL?

I agree… that is precisely how I feel :stuck_out_tongue_winking_eye:

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

2 Likes

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.

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

Not really the answer I was hoping for. :thinking: 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?” :stuck_out_tongue_winking_eye:

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`

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

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

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

@GunnerTechTools, 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 :tropical_drink:

1 Like

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

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.

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

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 :rofl: 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

@GunnerTechTools this is your homework for tomorrow - explain the above commands :hugs:

That would be my guess… and big fat guess it is :thinking: 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.

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

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):

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 :scream:

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