Some time ago I was contacted by Adam Smith – he pointed out that subquery names in “Subquery Scan" nodes were not properly anonymized.

Now, they are, which you can see in here:

While working on it, I also added (helpful?) links from node types to my blogposts about reading explain output – Explaining the unexplainable.

Read more »

On 3rd of October, Andres Freund committed patch:

Without CASCADE, if an extension has an unfullfilled dependency on
another extension, CREATE EXTENSION ERRORs out with "required extension
... is not installed". That is annoying, especially when that dependency
is an implementation detail of the extension, rather than something the
extension's user can make sense of.
In addition to CASCADE this also includes a small set of regression
tests around CREATE EXTENSION.
Author: Petr Jelinek, editorialized by Michael Paquier, Andres Freund
Reviewed-By: Michael Paquier, Andres Freund, Jeff Janes
Discussion: <a class="text" href="/gitweb/?p=postgresql.git;a=object;h=557E0520">557E0520</a>

Read more »

October 8th, 2015 by depesz | Tags: , , , , , | 2 comments »

In case you're not familiar – there is a thing called LVM – it's a layer between physical disks, and filesystems, and allow certain interesting things, like extending, migrating, snapshotting and others.

At one of systems I've been dealing with, we stumbled upon specific requirement – change LV into striped. It took me a while to figure it out, so I'm writing it down, so I'll never have to research it again.

Read more »

On 8th of September, Alvaro Herrera committed patch:

Allow per-tablespace effective_io_concurrency
Per discussion, nowadays it is possible to have tablespaces that have
wildly different I/O characteristics from others.  Setting different
effective_io_concurrency parameters for those has been measured to
improve performance.
Author: Julien Rouhaud
Reviewed by: Andres Freund

Read more »

On 7th of September, Jeff Davis committed patch:

Add log_line_prefix option 'n' for Unix epoch.
Prints time as Unix epoch with milliseconds.
Tomas Vondra, reviewed by Fabien Coelho.

Read more »

On 2nd of September, Teodor Sigaev committed patch:

Allow usage of huge maintenance_work_mem for GIN build.
Currently, in-memory posting list during GIN build process is limited 1GB
because of using repalloc. The patch replaces call of repalloc to repalloc_huge.
It increases limit of posting list from 180 millions
(1GB / sizeof(ItemPointerData)) to 4 billions limited by maxcount/count fields
in GinEntryAccumulator and subsequent calls. Check added.
Also, fix accounting of allocatedMemory during build to prevent integer
overflow with maintenance_work_mem > 4GB.
Robert Abraham <robert> with additions by me</robert>

Read more »

September 7th, 2015 by depesz | Tags: , , , , , | 5 comments »

There exists an extension to PostgreSQL, which lets you use hypothetical indexes.

What are there? That's simple – these are indexes that don't really exist. So what good are they?

Let's see.

Read more »

On 30th of July, Andrew Dunstan committed patch:

Fabrízio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera and
Michael Paquier.

Read more »

On 17th of July, Robert Haas committed patch:

Add new function pg_notification_queue_usage.
This tells you what fraction of NOTIFY's queue is currently filled.
Brendan Jurd, reviewed by Merlin Moncure and Gurjeet Singh.  A few
further tweaks by me.

Read more »

On 7th of July, Andres Freund committed patch:

Add psql PROMPT variable showing the pid of the connected to backend.
The substitution for the pid is %p.
Author: Julien Rouhaud
Discussion: <a href=";a=object;h=116262CF971C844FB6E793F8809B51C6E99D48">116262CF971C844FB6E793F8809B51C6E99D48</a>

Read more »