waiting – Page 6 – select * from depesz;

Waiting for PostgreSQL 14 – Allow subscripting of hstore values.

On 11st of December 2020, Tom Lane committed patch:

Allow subscripting of hstore values.
 
This is basically a finger exercise to prove that it's possible for
an extension module to add subscripting ability.  Subscripted fetch
from an hstore is not different from the existing "hstore -> text"
operator.  Subscripted update does seem to be a little easier to
use than the traditional update method using hstore concatenation,
but it's not a fundamentally new ability.
 
However, there may be some value in the code as sample code, since
it shows what's basically the minimum-complexity way to implement
subscripting when one needn't consider nested container objects.
 
Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us

Continue reading Waiting for PostgreSQL 14 – Allow subscripting of hstore values.

Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

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.

Waiting for PostgreSQL 14 – Support negative indexes in split_part().

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().

Waiting for PostgreSQL 14 – Support for OUT parameters in procedures

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

Waiting for PostgreSQL 13 – psql: Display stats target of extended statistics

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

Waiting for PostgreSQL 13 – Support FETCH FIRST WITH TIES

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

Waiting for PostgreSQL 14 – Add support for partitioned tables and indexes in REINDEX

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

Continue reading Waiting for PostgreSQL 14 – Add support for partitioned tables and indexes in REINDEX

Waiting for PostgreSQL 14 – Improvements for handling large number of connections

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.

Waiting for PostgreSQL 14 – pg_stat_statements: track number of rows processed by some utility commands.

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

Continue reading Waiting for PostgreSQL 14 – pg_stat_statements: track number of rows processed by some utility commands.

Waiting for PostgreSQL 14 – Rename wal_keep_segments to wal_keep_size.

On 20th of July 2020, Fujii Masao committed patch:

Rename wal_keep_segments to wal_keep_size.
 
max_slot_wal_keep_size that was added in v13 and wal_keep_segments are
the GUC parameters to specify how much WAL files to retain for
the standby servers. While max_slot_wal_keep_size accepts the number of
bytes of WAL files, wal_keep_segments accepts the number of WAL files.
This difference of setting units between those similar parameters could
be confusing to users.
 
To alleviate this situation, this commit renames wal_keep_segments to
wal_keep_size, and make users specify the WAL size in it instead of
the number of WAL files.
 
There was also the idea to rename max_slot_wal_keep_size to
max_slot_wal_keep_segments, in the discussion. But we have been moving
away from measuring in segments, for example, checkpoint_segments was
replaced by max_wal_size. So we concluded to rename wal_keep_segments
to wal_keep_size.
 
Back-patch to v13 where max_slot_wal_keep_size was added.
 
Author: Fujii Masao
Reviewed-by: Álvaro Herrera, Kyotaro Horiguchi, David Steele
Discussion: https://postgr.es/m/574b4ea3-e0f9-b175-ead2-ebea7faea855@oss.nttdata.com

Continue reading Waiting for PostgreSQL 14 – Rename wal_keep_segments to wal_keep_size.

=$
|

Tag: waiting