Some time ago I wrote a site to paste SQL queries with reformatting/pretty-printing using pgFormatter library.
Today, I figured out that I should update the library since it has quite some changes recently, so it would be good to incorporate its fixes to paste site.
Unfortunately – new version is not backward compatible, and I currently have no time to figure out what has changed and how to work around it.
So – until I will have time to work on it, paste.depesz.com is no longer working. Sorry.
Couple of times, in various places, I was asked: what is the benefit from upgrading to some_version.
So far, I just read release docs, and compiled list of what has changed.
But this is not necessarily simple – consider upgrade from 9.3.2 to 10.2. That's a lot of changes.
So, to be able to answer these questions faster in future, I created a site: Why upgrade PostgreSQL?.
Usage should be simple – pick from which version you want to upgrade, to which version you want to upgrade, and press gives me… button.
Hope you'll find it useful.
On Friday, Zr40 reported on irc that explain.depesz.com doesn't show table/index names for Parallel Seq Scan nodes.
Checked it and found couple of other omissions of the same kind with other Parallel* scans.
Fixed (I hope) all of them in:
The change is not really big, but just figured I'll let you know.
Long time ago I wrote first version of explain.depesz.com. Since then I gradually improve it. But, what was lacking was a way to paste queries too – explain.depesz.com handles explains, but not plain queries.
Now this has changed. I created new site: paste.depesz.com which allows for sharing queries.
Thanks to pgFormatter it also does query pretty-printing (which is not something readily available on other paste sites).
Obviously, code to the site is publicly available in GitHub repo.
Now, goes my request – if you have designer skills, I would greatly appreciate someone that could make the site nicer (prettier, more responsive). My CSS/JS knowledge is pretty limited, and I'm happy anyway about what I did with the look right now, but if you could make it nicer/prettier, that would be amazing.
Have fun, and if you have any feature requests, please post them in here…
Today, on irc, someone asked interesting question.
Basically she ran a query like:
SELECT a, b, c, d, e, f FROM TABLE ORDER BY a
then, she processed the query to get, for each a array of unique values of b, c, d, e, and f, and then he inserted it back to database, to some other table.
It was a problem, because the table had many rows (millions I would assume), and the whole process was slow.
So, how to make it faster?
Continue reading Getting list of unique elements in table, per group
Quite a long time ago (in October), Oskar Liljeblad reported a bug in anonymization. Namely – group keys were not anonymized.
You can see example of such plan here.
I finally got to it, fixed the bug, pushed new version to live site, and now such plans will be correctly anonymized.
Thanks Oskar, and sorry for long delay.
Continue reading explain.depesz.com – new change and some stats
Just thought I'll share a “fun" story. Friend reported weird bug – OmniPITR reported that xlogs are sent to archive, but they actually weren't.
After some checking we found out that he was giving custom rsync-path (–rsync-path – path to rsync program) – and the path was broken.
In this case – OmniPITR was not reporting error, and quite happily was working under assumption that it works OK.
Continue reading Fixed a bug in OmniPITR
Recently I saw this discussion on LinkedIn.
In there a guy asks whether modifying script while it's executing will change
the way it executes.
Continue reading Reloading of Perl script while it's running
There are some cases where you might want to get your own copy of explain.depesz.com. You might not trust me with your explains. You might want to use it without internet access. Or you just want to play with it, and have total control over the site.
Installing, while obvious to me, and recently described by John Poole, is not always 100% clear. So, I decided to write about how to set it up, from scratch.
Continue reading How to install your own copy of explain.depesz.com
Uploaded new version to the server – straight from GitHub. There are two changes – one visible, and one not really.
The invisible change, first, is one for people hosting explain.depesz.com on their own. As you perhaps know you can get sources of explain.depesz.com and install it on any box you want (as log as you can get there PostgreSQL, Perl, and some perl modules). While working on it on my own, I figured I could use a way to tell which version of module-xxx the site is running right now. So I build /info page (which is inaccessible to everyone, but manually-marked admins), which lists versions and interesting paths.
The second change – the one visible to users, is that I made explain.depesz.com commify numbers. Sometimes it can be hard to read value like 12325563, but now it will be displayed as 12,325,563 making is simpler to grasp.
This second change was suggested by Jacek Wielemborek, so if you hate it – blame him. Of course if you love the change – it's all on me 🙂
Hope you'll find it helpful.