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.
On 19th of January 2018, Robert Haas committed patch:
Allow UPDATE to move rows between partitions. When an UPDATE causes a row to no longer match the partition constraint, try to move it to a different partition where it does match the partition constraint. In essence, the UPDATE is split into a DELETE from the old partition and an INSERT into the new one. This can lead to surprising behavior in concurrency scenarios because EvalPlanQual rechecks won't work as they normally did; the known problems are documented. (There is a pending patch to improve the situation further, but it needs more review.) Amit Khandekar, reviewed and tested by Amit Langote, David Rowley, Rajkumar Raghuwanshi, Dilip Kumar, Amul Sul, Thomas Munro, Álvaro Herrera, Amit Kapila, and me. A few final revisions by me. Discussion: http://postgr.es/m/CAJ3gD9do9o2ccQ7j7+tSgiE1REY65XRiMb=yJO3u3QhyP8EEPQ@mail.gmail.com
Continue reading Waiting for PostgreSQL 11 – Allow UPDATE to move rows between partitions.
Bricklen Anderson reported a problem with explains that were using parallel processing.
TL;DR: in case of parallel plans make “inclusive" and “exclusive" be wall-clock times, and not “how much time CPU did spend on it".
On 9th of November 2017, Robert Haas committed patch:
Add hash partitioning. Hash partitioning is useful when you want to partition a growing data set evenly. This can be useful to keep table sizes reasonable, which makes maintenance operations such as VACUUM faster, or to enable partition-wise join. At present, we still depend on constraint exclusion for partitioning pruning, and the shape of the partition constraints for hash partitioning is such that that doesn't work. Work is underway to fix that, which should both improve performance and make partitioning pruning work with hash partitioning. Amul Sul, reviewed and tested by Dilip Kumar, Ashutosh Bapat, Yugo Nagata, Rajkumar Raghuwanshi, Jesper Pedersen, and by me. A few final tweaks also by me. Discussion: http://postgr.es/m/CAAJ_b96fhpJAP=ALbETmeLk1Uni_GFZD938zgenhF49qgDTjaQ@mail.gmail.com
Continue reading Waiting for PostgreSQL 11 – Add hash partitioning.
On 12nd of September 2017, Tom Lane committed patch:
Add psql variables to track success/failure of SQL queries. This patch adds ERROR, SQLSTATE, and ROW_COUNT, which are updated after every query, as well as LAST_ERROR_MESSAGE and LAST_ERROR_SQLSTATE, which are updated only when a query fails. The expected usage of these is for scripting. Fabien Coelho, reviewed by Pavel Stehule Discussion: https://postgr.es/m/alpine.DEB.2.20..12290@lancre
On 5th of September 2017, Tom Lane committed patch:
Add \gdesc psql command. This command acts somewhat like \g, but instead of executing the query buffer, it merely prints a description of the columns that the query result would have. (Of course, this still requires parsing the query; if parse analysis fails, you get an error anyway.) We accomplish this using an unnamed prepared statement, which should be invisible to psql users. Pavel Stehule, reviewed by Fabien Coelho Discussion: https://postgr.es/m/CAFj8pRBhYVvO34FU=EKb=nAF5t3b++krKt1FneCmR0kuF5m-QA@mail.gmail.com
Continue reading Waiting for PostgreSQL 11 – Add \gdesc psql command.
Long time ago I wrote about my project – Versioning.
Since then nothing really changed. But recently I found a case where I could use some more logic from versioning, so I changed it. In proceess, I also added somewhat better docs.
The change itself is that now, when you write patch using Versioning you can add some assertions about the user that will be loading the patch, like:
Not a big change, but helps me quite a bit.
This question appeared couple of times on irc, so I figured I can do a blogpost about it.
Continue reading How to make sure there is always at least one “sub" record?
I assume that you're familiar with explain.depesz.com and paste.depesz.com services.
From today they are also available as services in TOR, using respectively: explain4pg4j5wbw.onion and paste4sql64vzyry.onion (of course you need to have TOR Browser or some other way to browse darknet).
Why I did that? Well, I believe that TOR is one of the greatest inventions ever, and I'd like to do something so that it will be less associated with drugs, porn, or other illegal activities. Don't realistically think that there will be many people using TOR to access my Pg-related services, but it is a thing that I could have done, so I did it.
While working on it, I also updated pgFormatter code under paste-site to newest version.
Continue reading explain.depesz.com, paste.depesz.com – TOR announcement and some updates
Some (long) time ago, someone on irc suggested that I add option to keep track of optimizations of queries.
Sorry, I forgot your name, and the mails disappeared in some crash.
Anyway – right now, when you are on some plan page, you can press “Add optimization" button, and you will be redirected to index page, but when you will add plan there, it will be understood to be plan from optimization of the query. Like this one.
You can have any number of optimizations per plan, and when viewing plan that has optimizations, or is an optimization of earlier plan – you will see this above plan table.
Whether you'll use it – it's up to you. Someone wanted it, and it looked like sensible thing to add, so there it is 🙂