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

Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

On 29th of November 2018, Alvaro Herrera committed patch:

Add log_statement_sample_rate parameter
 
This allows to set a lower log_min_duration_statement value without
incurring excessive log traffic (which reduces performance).  This can
be useful to analyze workloads with lots of short queries.
 
Author: Adrien Nayrat
 
Discussion: https://postgr.es/m/-ee1e-db9f-fa97-@anayrat.info

Continue reading Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

Waiting for PostgreSQL 12 – Add CSV table output mode in psql.

On 26th of November 2018, Tom Lane committed patch:

Add CSV table output mode in psql.
 
"\pset format csv", or --csv, selects comma-separated values table format.
This is compliant with RFC 4180, except that we aren't too picky about
whether the record separator is LF or CRLF; also, the user may choose a
field separator other than comma.
 
This output format is directly compatible with the server's COPY CSV
format, and will also be useful as input to other programs.  It's
considerably safer for that purpose than the old recommendation to
use "unaligned" format, since the latter couldn't handle data
containing the field separator character.
 
Daniel Vérité, reviewed by Fabien Coelho and David Fetter, some
tweaking by me
 
Discussion: https://postgr.es/m/a8de371e-006f-4f92-ab72-2bbe3ee78f03@manitou-mail.org

Continue reading Waiting for PostgreSQL 12 – Add CSV table output mode in psql.

Waiting for PostgreSQL 12 – Integrate recovery.conf into postgresql.conf

On 25th of November 2018, Peter Eisentraut committed patch:

Integrate recovery.conf into postgresql.conf
 
recovery.conf settings are now set in postgresql.conf (or other GUC
sources).  Currently, all the affected settings are PGC_POSTMASTER;
this could be refined in the future case by case.
 
Recovery is now initiated by a file recovery.signal.  Standby mode is
initiated by a file standby.signal.  The standby_mode setting is
gone.  If a recovery.conf file is found, an error is issued.
 
The trigger_file setting has been renamed to promote_trigger_file as
part of the move.
 
The documentation chapter "Recovery Configuration" has been integrated
into "Server Configuration".
 
pg_basebackup -R now appends settings to postgresql.auto.conf and
creates a standby.signal file.
 
Author: Fujii Masao
Author: Simon Riggs
Author: Abhijit Menon-Sen
Author: Sergei Kornilov
Discussion: https://www.postgresql.org/message-id/flat/@web3g.yandex.ru/

Continue reading Waiting for PostgreSQL 12 – Integrate recovery.conf into postgresql.conf