Author: depesz

Waiting for PostgreSQL 13 – Add support for –jobs in reindexdb

On 27th of July 2019, Michael Paquier committed patch:

Add support for --jobs in reindexdb
 
When doing a schema-level or a database-level operation, a list of
relations to build is created which gets processed in parallel using
multiple connections, based on the recent refactoring for parallel slots
in src/bin/scripts/.  System catalogs are processed first in a
serialized fashion to prevent deadlocks, followed by the rest done in
parallel.
 
This new option is not compatible with --system as reindexing system
catalogs in parallel can lead to deadlocks, and with --index as there is
no conflict handling for indexes rebuilt in parallel depending in the
same relation.
 
Author: Julien Rouhaud
 
Discussion: https://postgr.es/m/CAOBaU_YrnH_Jqo46NhaJ7uRBiWWEcS40VNRQxgFbqYo9kApUsg@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add support for –jobs in reindexdb

Changes on explain.depesz.com

Recently got two bug reports:

  • plans with “COSTS OFF" do not parse, and error out (bugreport by Marc Dean Jr)
  • WorkTable Scan is not properly parsed (bugreport by Ivan Vergiliev)

Additionally, I was kinda upset because plans that include trigger calls did not display properly.

All of this has been fixed today:

Continue reading Changes on explain.depesz.com

New shell_util to watch output in multiple tmux panes at once

I assume most of you are familiar with watch program. If not – it shows, periodically, output of a command.

I was missing similar functionality for tmux, especially when I have multiple windows/panes.

So I wrote tmux_watch_many.

How to use it?

Continue reading New shell_util to watch output in multiple tmux panes at once

How to play with upcoming, unreleased, PostgreSQL?

Lately reader of my blog asked about some detail, and then in next comment said that he doesn't have PostgreSQL 12 (which is currently developed version of Pg) available.

Well, I answered the original question, but I figured that if someone is reading my Waiting for … series, then it would make sense that such person could play with devel Pg herself.

So, here is how to get it done.

Continue reading How to play with upcoming, unreleased, PostgreSQL?

Wait for program(s) in tmux panes to end…

I am doing quite a lot of work inside tmux. I especially love that I can start multiple windows/panes and use them to run the same thing across multiple servers, while still having normal shell access between steps.

This thing was greatly improved when I wrote tmux_send_to_many, but one thing was missing – waiting for the thing, running in tmux window to end.

Generally, I had to look at all the windows, and decide when to launch next step on my own.

Not anymore.

Continue reading Wait for program(s) in tmux panes to end…

Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled.

On 4th of April 2019, Robert Haas committed patch:

Allow VACUUM to be run with index cleanup disabled.
 
 
This commit adds a new reloption, vacuum_index_cleanup, which
controls whether index cleanup is performed for a particular
relation by default.  It also adds a new option to the VACUUM
command, INDEX_CLEANUP, which can be used to override the
reloption.  If neither the reloption nor the VACUUM option is
used, the default is true, as before.
 
Masahiko Sawada, reviewed and tested by Nathan Bossart, Alvaro
Herrera, Kyotaro Horiguchi, Darafei Praliaskouski, and me.
The wording of the documentation is mostly due to me.
 
Discussion: http://postgr.es/m/CAD21AoAt5R3DNUZSjOoXDUY=naYPUOuffVsRzuTYMz29yLzQCA@mail.gmail.com

Continue reading Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled.

Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

On 3rd of April 2019, Tomas Vondra committed patch:

Add SETTINGS option to EXPLAIN, to print modified settings.
 
 
Query planning is affected by a number of configuration options, and it
may be crucial to know which of those options were set to non-default
values.  With this patch you can say EXPLAIN (SETTINGS ON) to include
that information in the query plan.  Only options affecting planning,
with values different from the built-in default are printed.
 
This patch also adds auto_explain.log_settings option, providing the
same capability in auto_explain module.
 
Author: Tomas Vondra
 
Discussion: https://postgr.es/m/e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com

Continue reading Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

Waiting for PostgreSQL 12 – Support foreign keys that reference partitioned tables

On 3rd of April 2019, Alvaro Herrera committed patch:

Support foreign keys that reference partitioned tables
 
 
Previously, while primary keys could be made on partitioned tables, it
was not possible to define foreign keys that reference those primary
keys.  Now it is possible to do that.
 
Author: Álvaro Herrera
 
Discussion: https://postgr.es/m/20181102234158.735b3fevta63msbj@alvherre.pgsql

Continue reading Waiting for PostgreSQL 12 – Support foreign keys that reference partitioned tables

Waiting for PostgreSQL 12 – Log all statements from a sample of transactions

On 3rd of April 2019, Alvaro Herrera committed patch:

Log all statements from a sample of transactions
 
This is useful to obtain a view of the different transaction types in an
application, regardless of the durations of the statements each runs.
 
Author: Adrien Nayrat

Continue reading Waiting for PostgreSQL 12 – Log all statements from a sample of transactions

Waiting for PostgreSQL 12 – Report progress of CREATE INDEX operations

On 2nd of April 2019, Alvaro Herrera committed patch:

Report progress of CREATE INDEX operations
 
 
This uses the progress reporting infrastructure added by ,
adding support for CREATE INDEX and CREATE INDEX CONCURRENTLY.
 
There are two pieces to this: one is index-AM-agnostic, and the other is
AM-specific.  The latter is fairly elaborate for btrees, including
reportage for parallel index builds and the separate phases that btree
index creation uses; other index AMs, which are much simpler in their
building procedures, have simplistic reporting only, but that seems
sufficient, at least for non-concurrent builds.
 
The index-AM-agnostic part is fairly complete, providing insight into
the CONCURRENTLY wait phases as well as block-based progress during the
index validation table scan.  (The index validation index scan requires
patching each AM, which has not been included here.)
 
Reviewers: Rahila Syed, Pavan Deolasee, Tatsuro Yamada
Discussion: https://postgr.es/m/20181220220022.mg63bhk26zdpvmcj@alvherre.pgsql

Continue reading Waiting for PostgreSQL 12 – Report progress of CREATE INDEX operations