Recently someone asked, on irc, how to make table partitioned.
The thing is that it was supposed to be done with new partitioning, and not the old way.
The problem is that while we can create table that will be seen as partitioned – we can't alter table to become partitioned.
So. Is it possible?
Continue reading Migrating simple table to partitioned. How?
On 16th of March 2019, Alexander Korotkov committed patch:
Partial implementation of SQL/JSON path language SQL 2016 standards among other things contains set of SQL/JSON features for JSON processing inside of relational database. The core of SQL/JSON is JSON path language, allowing access parts of JSON documents and make computations over them. This commit implements partial support JSON path language as separate datatype called "jsonpath". The implementation is partial because it's lacking datetime support and suppression of numeric errors. Missing features will be added later by separate commits. Support of SQL/JSON features requires implementation of separate nodes, and it will be considered in subsequent patches. This commit includes following set of plain functions, allowing to execute jsonpath over jsonb values: * jsonb_path_exists(jsonb, jsonpath[, jsonb, bool]), * jsonb_path_match(jsonb, jsonpath[, jsonb, bool]), * jsonb_path_query(jsonb, jsonpath[, jsonb, bool]), * jsonb_path_query_array(jsonb, jsonpath[, jsonb, bool]). * jsonb_path_query_first(jsonb, jsonpath[, jsonb, bool]). This commit also implements "jsonb @? jsonpath" and "jsonb @@ jsonpath", which are wrappers over jsonpath_exists(jsonb, jsonpath) and jsonpath_predicate(jsonb, jsonpath) correspondingly. These operators will have an index support (implemented in subsequent patches). Catversion bumped, to add new functions and operators. Code was written by Nikita Glukhov and Teodor Sigaev, revised by me. Documentation was written by Oleg Bartunov and Liudmila Mantrova. The work was inspired by Oleg Bartunov. Discussion: https://postgr.es/m/-b497-f39a-923d-%402ndQuadrant.com Author: Nikita Glukhov, Teodor Sigaev, Alexander Korotkov, Oleg Bartunov, Liudmila Mantrova
Continue reading Waiting for PostgreSQL 12 – Partial implementation of SQL/JSON path language
I wrote previously about tmux_send_to_many and group_by shell tools that I authored.
Since then I got some more ideas for changes, and for new tool, so figured I'll make an honest git repo for it. Repo is on GitLab, and all the tools inside can be freely used.
While I was doing it, I fixed some things in tmux_send_to_many.
And then – I remembered that for long time I was missing “visual-sleep" type of tool. One that will show some progress information while it's working.
Enter vsleep.
Continue reading visual sleep in shell, and shell_utils repo information
I just added search functionality to why-upgrade.depesz.com. Now you can limit change list to only changes with specific words.
For example, if you just want to see what changed in relation to GIN between versions 10 and 11.2, you can.
Hope you'll find it useful 🙂
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:
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://postgr.es/m/87sh48ffhb.fsf@news-spur.riddles.org.uk
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.
Continue reading Automation for doing stuff in multiple windows in tmux
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
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