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".

Read more »

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.

Read more »

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

Read more »

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
Pavel Stehule, reviewed by Fabien Coelho

Read more »

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.

This question appeared couple of times on irc, so I figured I can do a blogpost about it.

Read more »

I assume that you're familiar with and 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.

Read more »

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.

Read more »

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 🙂

Read more »

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
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:
though this commit does not use that code.)
Author: Tomas Vondra.  Some code rework by Álvaro.
    Ideriha Takeshi

Afterwards, 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

Read more »