Specifically – if explain was done using JSON/XML/YAML formats, and node type was Aggregate, the site didn't extract full info.
Recently someone asked, on irc, how to make table partitioned.
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?
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 …
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.
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?
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.
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
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:
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.
Yesterday I had an interesting discussion on irc.
A guy wanted to know why Perl script is causing problems when dealing with Pg
and unicode characters.
The discussion went sideways, I got (a bit) upset, and had to leave anyway, so
I didn't finish it. But it did bother me, as for me the reasons of the problem
seem obvious, yet the person I talked with was very adamant that I have the
whole thing wrong.
So, I figured I'll use my blog to elaborate a bit…