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.
On 5th of October 2020, Peter Eisentraut committed patch:
Support for OUT parameters in procedures Unlike for functions, OUT parameters for procedures are part of the signature. Therefore, they have to be listed in pg_proc.proargtypes as well as mentioned in ALTER PROCEDURE and DROP PROCEDURE. Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndquadrant.com> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/2b8490fe-51af-e671-c504-47359dc453c5@2ndquadrant.com
Continue reading Waiting for PostgreSQL 14 – Support for OUT parameters in procedures
On 11st of September 2020, Alvaro Herrera committed patch:
psql: Display stats target of extended statistics The stats target can be set since commit d06215d03, but wasn't shown by psql. Author: Justin Pryzby <justin@telsasoft.com> Discussion: https://postgr.es/m/20200831050047.GG5450@telsasoft.com Reviewed-by: Georgios Kokolatos <gkokolatos@protonmail.com> Reviewed-by: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
Continue reading Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics
I have no idea how I missed that, but: if it wasn't for Alvaro's blog post I wouldn't know that: on 7th of April 2020, Alvaro Herrera committed patch:
Support FETCH FIRST WITH TIES WITH TIES is an option to the FETCH FIRST N ROWS clause (the SQL standard's spelling of LIMIT), where you additionally get rows that compare equal to the last of those N rows by the columns in the mandatory ORDER BY clause. There was a proposal by Andrew Gierth to implement this functionality in a more powerful way that would yield more features, but the other patch had not been finished at this time, so we decided to use this one for now in the spirit of incremental development. Author: Surafel Temesgen <surafel3000@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com> Discussion: https://postgr.es/m/CALAY4q9ky7rD_A4vf=FVQvCGngm3LOes-ky0J6euMrg=_Se+ag@mail.gmail.com Discussion: https://postgr.es/m/87o8wvz253.fsf@news-spur.riddles.org.uk
Continue reading Waiting for PostgreSQL 13 – Support FETCH FIRST WITH TIES
On 8th of September 2020, Michael Paquier committed patch:
Add support for partitioned tables and indexes in REINDEX Until now, REINDEX was not able to work with partitioned tables and indexes, forcing users to reindex partitions one by one. This extends REINDEX INDEX and REINDEX TABLE so as they can accept a partitioned index and table in input, respectively, to reindex all the partitions assigned to them with physical storage (foreign tables, partitioned tables and indexes are then discarded). This shares some logic with schema and database REINDEX as each partition gets processed in its own transaction after building a list of relations to work on. This choice has the advantage to minimize the number of invalid indexes to one partition with REINDEX CONCURRENTLY in the event a cancellation or failure in-flight, as the only indexes handled at once in a single REINDEX CONCURRENTLY loop are the ones from the partition being working on. Isolation tests are added to emulate some cases I bumped into while developing this feature, particularly with the concurrent drop of a leaf partition reindexed. However, this is rather limited as LOCK would cause REINDEX to block in the first transaction building the list of partitions. Per its multi-transaction nature, this new flavor cannot run in a transaction block, similarly to REINDEX SCHEMA, SYSTEM and DATABASE. Author: Justin Pryzby, Michael Paquier Reviewed-by: Anastasia Lubennikova Discussion: https://postgr.es/m/db12e897-73ff-467e-94cb-4af03705435f.adger.lj@alibaba-inc.com
Title:
There is a thread on pgsql-hackers mailing list, dating back to 1st of March 2020 about changes to Pg to optimize handling of larger number of connections.
The thread is pretty long, and the discussion took 5 months, but lately we got couple of commits:
that (supposedly) should improve handling of large numbers of concurrent connections.
Unfortunately, I don't have ready test servers that would allow me to sensibly tests thousands of connections, but I think we can all safely assume that PostgreSQL 14 should handle large connection counts better than any PostgreSQL before. Thanks a lot, to all involved, but specifically to mastermind of this project: Andres Freund.
Title: Waiting for PostgreSQL 14 – pg_stat_statements: track number of rows processed by some utility commands.
On 29th of July 2020, Fujii Masao committed patch:
pg_stat_statements: track number of rows processed by some utility commands. This commit makes pg_stat_statements track the total number of rows retrieved or affected by CREATE TABLE AS, SELECT INTO, CREATE MATERIALIZED VIEW and FETCH commands. Suggested-by: Pascal Legrand Author: Fujii Masao Reviewed-by: Asif Rehman Discussion: https://postgr.es/m/1584293755198-0.post@n3.nabble.com