In case you're not familiar with this site, why-upgrade.depesz.com shows you aggregated changelog between any two releases, with optionally searching for some keywords.
Yesterday azeem on irc pointed me towards a problem on why-upgrade.depesz.com. Specifically, when displaying changes from 9.5.24 to 13.1 site showed 30 security fixes. But in reality there should be only 10.
Continue reading Fixes on why-upgrade.depesz.com
Couple of days ago RhodiumToad reported, on irc, a bug in explain.depesz.com.
Specifically – if explain was done using JSON/XML/YAML formats, and node type was Aggregate, the site didn't extract full info.
Continue reading Fix for displaying aggregates on explain.depesz.com
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?
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
Another one missed, quite a long time ago, too..:
On 4th of November 2016, Kevin Grittner committed patch:
Implement syntax for transition tables in AFTER triggers.
This is infrastructure for the complete SQL standard feature. No
support is included at this point for execution nodes or PLs. The
intent is to add that soon.
As this patch leaves things, standard syntax can create tuplestores
to contain old and/or new versions of rows affected by a statement.
References to these tuplestores are in the TriggerData structure.
C triggers can access the tuplestores directly, so they are usable,
but they cannot yet be referenced within a SQL statement.
Continue reading Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.
Today, on irc, someone asked interesting question.
Basically she ran a query like:
SELECT a, b, c, d, e, f FROM TABLE ORDER BY a
then, she processed the query to get, for each a array of unique values of b, c, d, e, and f, and then he inserted it back to database, to some other table.
It was a problem, because the table had many rows (millions I would assume), and the whole process was slow.
So, how to make it faster?
Continue reading Getting list of unique elements in table, per group
Some time ago someone on irc asked about creating fast counters for something (banners I think).
I talked with her (him?) about it, but figured, I can as well write a blogpost, so others can use it too.
Continue reading Incrementing counters in database
On 5th of February, Tom Lane committed patch:
Add num_nulls() and num_nonnulls() to count NULL arguments.
An example use-case is "CHECK(num_nonnulls(a,b,c) = 1)" to assert that
exactly one of a,b,c isn't NULL. The functions are variadic, so they
can also be pressed into service to count the number of null or nonnull
elements in an array.
Marko Tiikkaja, reviewed by Pavel Stehule
Continue reading Waiting for 9.6 – Add num_nulls() and num_nonnulls() to count NULL arguments.
Someone asked today on irc about grouping data, that contains timestamps, into “partitions".
Usually when someone wants something like this, you can do grouping by date_trunc(), but this time, this person, wanted to group data that all timestamps are within given interval from each other.
I'm not sure I understood him/her right, but I think he/she wanted something like this:
Continue reading Grouping data by timestamp and interval
Some time ago someone on irc asked interesting question. One that I couldn't answer then (didn't have an immediate idea, and didn't have time to spend on looking into it).
Now, I have some more time, and despite the fact that the person that had this problem no longer cares about it (he found some solution himself if I recall correctly), decided to look into it.
Continue reading Filling in the blanks