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".
Waiting for PostgreSQL 11 – Add hash partitioning.
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.
Waiting for PostgreSQL 11 – Add psql variables to track success/failure of SQL queries.
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
Waiting for PostgreSQL 11 – Add \gdesc psql command.
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.
New functions in versioning
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:
- SELECT _v.assert_user_is_superuser();
- SELECT _v.assert_user_is_not_superuser();
- SELECT _v.assert_user_is_one_of( ‘depesz', ‘postgres', ‘dba' );
Not a big change, but helps me quite a bit.
How to make sure there is always at least one “sub” record?
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?
explain.depesz.com, paste.depesz.com – TOR announcement and some updates
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
Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.
Another one missed, quite a long time ago, too..:
On 4th of November 2016, Kevin Grittner committed patch:
Implement syntax for transition tables in AFTER triggers. This is infrastructure for the complete SQL standard feature. No support is included at this point for execution nodes or PLs. The intent is to add that soon. As this patch leaves things, standard syntax can create tuplestores to contain old and/or new versions of rows affected by a statement. References to these tuplestores are in the TriggerData structure. C triggers can access the tuplestores directly, so they are usable, but they cannot yet be referenced within a SQL statement.
New change on explain.depesz.com
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 🙂
Waiting for PostgreSQL 10 – Implement multivariate n-distinct coefficients
I missed it completely, but on 24th of March 2017, Alvaro Herrera committed patch:
Implement multivariate n-distinct coefficients
Add support for explicitly declared statistic objects (CREATE
STATISTICS), allowing collection of statistics on more complex
combinations that individual table columns. Companion commands DROP
STATISTICS and ALTER STATISTICS ... OWNER TO / SET SCHEMA / RENAME are
added too. All this DDL has been designed so that more statistic types
can be added later on, such as multivariate most-common-values and
multivariate histograms between columns of a single table, leaving room
for permitting columns on multiple tables, too, as well as expressions.
This commit only adds support for collection of n-distinct coefficient
on user-specified sets of columns in a single table. This is useful to
estimate number of distinct groups in GROUP BY and DISTINCT clauses;
estimation errors there can cause over-allocation of memory in hashed
aggregates, for instance, so it's a worthwhile problem to solve. A new
special pseudo-type pg_ndistinct is used.
(num-distinct estimation was deemed sufficiently useful by itself that
this is worthwhile even if no further statistic types are added
immediately; so much so that another version of essentially the same
functionality was submitted by Kyotaro Horiguchi:
https://postgr.es/m/.173334..horiguchi.kyotaro@lab.ntt.co.jp
though this commit does not use that code.)
Author: Tomas Vondra. Some code rework by Álvaro.
Ideriha Takeshi
Discussion: https://postgr.es/m/.4080608@fuzzy.cz
https://postgr.es/m/.ixlaueanxegqd5gr@alvherre.pgsqlAfterwards, there were couple more commits related to it:
- On 5th of April 2017, patch committed by Simon Riggs
- On 17th of April 2017, patch committed by Alvaro Herrera
- On 12nd of May 2017, patch committed by Alvaro Herrera
Continue reading Waiting for PostgreSQL 10 – Implement multivariate n-distinct coefficients