I just added search functionality to why-upgrade.depesz.com. Now you can limit change list to only changes with specific words.
Hope you'll find it useful 🙂
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:
All in all, not big changes, but I hope you'll find it useful.
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://firstname.lastname@example.org
I assume that everyone reading my blog understands GROUP BY clause in SQL.
Lately I've been doing some maintenance work, and found myself in a position that I could really use similar thing in shell.
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.
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 …
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