Month: February 2019

why-upgrade updates

Recent change in layout of PG Docs broke my spider for why-upgrade.depesz.com.

Today got some time and decided to bite the bullet.

Fixed spider code, used it to get new changelog, and while I was at it, did couple of slight modifications of the site:

  • display count of all changes that are there in given upgrade path (in section header)
  • fixed a bug that caused empty Security fixes: box to display, even when there are no security fixes.
  • added metainfo page, which shows some stats about why-upgrade “database".

All in all, not big changes, but I hope you'll find it useful.

Waiting for PostgreSQL 12 – Allow user control of CTE materialization, and change the default behavior.

On 16th of February 2019, Tom Lane committed patch:

Allow user control of CTE materialization, and change the default behavior.
 
Historically we've always materialized the full output of a CTE query,
treating WITH as an optimization fence (so that, for example, restrictions
from the outer query cannot be pushed into it).  This is appropriate when
the CTE query is INSERT/UPDATE/DELETE, or is recursive; but when the CTE
query is non-recursive and side-effect-free, there's no hazard of changing
the query results by pushing restrictions down.
 
Another argument for materialization is that it can avoid duplicate
computation of an expensive WITH query --- but that only applies if
the WITH query is called more than once in the outer query.  Even then
it could still be a net loss, if each call has restrictions that
would allow just a small part of the WITH query to be computed.
 
Hence, let's change the behavior for WITH queries that are non-recursive
and side-effect-free.  By default, we will inline them into the outer
query (removing the optimization fence) if they are called just once.
If they are called more than once, we will keep the old behavior by
default, but the user can override this and force inlining by specifying
NOT MATERIALIZED.  Lastly, the user can force the old behavior by
specifying MATERIALIZED; this would mainly be useful when the query had
deliberately been employing WITH as an optimization fence to prevent a
poor choice of plan.
 
Andreas Karlsson, Andrew Gierth, David Fetter
 
Discussion: https://postgr.es/m/87sh48ffhb.fsf@news-spur.riddles.org.uk

Continue reading Waiting for PostgreSQL 12 – Allow user control of CTE materialization, and change the default behavior.

Automation for doing stuff in multiple windows in tmux

Tmux is terminal multiplexer. Kinda like old screen, but with much more functionality.

When I work on my servers, it's pretty common that I have to do the same things to multiple servers. To make my life easier I start tmux, and in there start many “windows", each related to work on single server.

I name the windows in a way that let's me quickly find them, without false positives.

For example, if I'd have to upgrade servers db1..db5 then I'd create windows “up-db1" .. “up-db5", and each window would work on single server.

This is already scriptable – let's assume I'd want to show uptime in all of the windows, I can:

tmux lsw -F '#W' | grep -E '^up-db[0-9]+$' | xargs -r -d$'\n' -I% tmux send-keys -t % uptime Enter

But this gets tedious fast.

Continue reading Automation for doing stuff in multiple windows in tmux

Converting list of integers into list of ranges

Yesterday someone on irc asked:

i've a query that returns sequential numbers with gaps (generate_series + join) and my question is: can is somehow construct ranges out of the returned values? sort of range_agg or something?

There was no further discussion, aside from me saying

sure you can. not trivial task, but possible.
you'd need window functions.

but it got me thinking …

Continue reading Converting list of integers into list of ranges

Waiting for PostgreSQL 12 – Allow COPY FROM to filter data using WHERE conditions

On 19th of January 2019, Tomas Vondra committed patch:

Allow COPY FROM to filter data using WHERE conditions
 
Extends the COPY FROM command with a WHERE condition, which allows doing
various types of filtering while importing the data (random sampling,
condition on a data column, etc.).  Until now such filtering required
either preprocessing of the input data, or importing all data and then
filtering in the database. COPY FROM ... WHERE is an easy-to-use and
low-overhead alternative for most simple cases.
 
Author: Surafel Temesgen
 
Discussion: https://www.postgresql.org/message-id/flat/CALAY4q_DdpWDuB5-Zyi-oTtO2uSk8pmy+dupiRe3AvAc++1imA@mail.gmail.com

Continue reading Waiting for PostgreSQL 12 – Allow COPY FROM to filter data using WHERE conditions