The question was asked relatively recently on irc. And it proved to be non-trivial.
Surely, if you want to have one row per category (one address per user), it's trivial – add user_id column to addresses, make it unique, and we're done. But what if we want to allow five addresses? Or five thousands?
Let's see.
Continue reading How to limit rows to at most N per category
On 14th of November 2020, Tom Lane committed patch:
Provide the OR REPLACE option for CREATE TRIGGER. This is mostly straightforward. However, we disallow replacing constraint triggers or changing the is-constraint property; perhaps that can be added later, but the complexity versus benefit tradeoff doesn't look very good. Also, no special thought is taken here for whether replacing an existing trigger should result in changes to queued-but-not-fired trigger actions. We just document that if you're surprised by the results, too bad, don't do that. (Note that any such pending trigger activity would have to be within the current session.) Takamichi Osumi, reviewed at various times by Surafel Temesgen, Peter Smith, and myself Discussion: https://postgr.es/m/0DDF369B45A1B44B8A687ED43F06557C010BC362@G01JPEXMBYT03
Continue reading Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.
On 13rd of November 2020, Tom Lane committed patch:
Support negative indexes in split_part(). This provides a handy way to get, say, the last field of the string. Use of a negative index in this way has precedent in the nearby left() and right() functions. The implementation scans the string twice when N < -1, but it seems likely that N = -1 will be the huge majority of actual use cases, so I'm not really excited about adding complexity to avoid that. Nikhil Benesch, reviewed by Jacob Champion; cosmetic tweakage by me Discussion: https://postgr.es/m/cbb7f861-6162-3a51-9823-97bc3aa0b638@gmail.com
Continue reading Waiting for PostgreSQL 14 – Support negative indexes in split_part().
Just committed some new changes to explain.depesz.com.
The less visible is that, in case of plans generated by plain EXPLAIN (without ANALYZE), site will no longer show “actual time=.. rows= loops=", which was clearly bogus info.
The more visible is that if you add query to your plan, you can also have it reformatted to (hopefully) nicer format, shown in “REFORMATTED QUERY" tab. For example, take a look at this example.
Reformatting is done using pgFormatter library by Gilles Darold.
I picked this library for the very simple reason – it's in Perl, just like explain.depesz.com site, which made using it a breeze.