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.
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
Foreign Key to partitioned table – part 3
Previously I tested performance of pl/PgSQL coded foreign keys to partitioned table.
Now, let's see if I can make creation of them a bit easier.
Foreign Key to partitioned table – part 2
Previously I wrote about how to create foreign key pointing to partitioned table.
Final solution in there required four separate functions and four triggers for each key between two tables.
Let's see how fast it is, and if it's possible to make it simpler.
Waiting for PostgreSQL 12 – Add pg_partition_tree to display information about partitions
On 30th of October 2018, Michael Paquier committed patch:
Add pg_partition_tree to display information about partitions This new function is useful to display a full tree of partitions with a partitioned table given in output, and avoids the need of any complex WITH RECURSIVE query when looking at partition trees which are deep multiple levels. It returns a set of records, one for each partition, containing the partition's name, its immediate parent's name, a boolean value telling if the relation is a leaf in the tree and an integer telling its level in the partition tree with given table considered as root, beginning at zero for the root, and incrementing by one each time the scan goes one level down. Author: Amit Langote Discussion: https://postgr.es/m/-9a51-ad02-d53e-@lab.ntt.co.jp